A Program which uses MySql and Asp.Net in C#

[bhavyajyothinat]

can someone send the code for connecting with MySql in asp.net.
and code for inserting some entry to a table.
I have no idea about MySql.
Am having an interview on 16th March.They asked me to do with MySql.

Please someone help me..

[jomgrg]

Make your connection string, the following code will shows a standard MySQL connection string.

using MySql.Data.MySqlClient;
public static string GetConnectionString()
{
string connStr =
String.Format("server={0};user id={1}; password={2};
database=yourdb; pooling=false", "yourserver",
"youruser", "yourpass");


return connStr;
}

Then create an instance from MySql.Data.MySqlClient.MySqlConnection as shown below.

MySql.Data.MySqlClient.MySqlConnection mycon
= new MySqlConnection( GetConnectionString());Then Try to open the MySQL connection.
if(mycon .State != ConnectionState.Open)
try
{
mycon .Open();
}
catch (MySqlException ex)
{
throw (ex);
}


Using Query 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data.Common;
using MySql.Data.MySqlClient;
static void connectUsingFactory()
{
DbProviderFactory factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = "server=10.151.34.31;User Id=adonet;password=adonet;Persist Security Info=True;database=adonet";
connection.Open();


DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM item";


DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["itmid"]+"\t"+reader[1]+"\t"+reader[2]+"\t"+reader["price"]);
}
reader.Close();
connection.Close();
}
static void connectUsingReference()
{
DbConnection connection =
new MySqlConnection("server=10.151.34.31;User Id=adonet;password=adonet;Persist Security Info=True;database=adonet");
connection.Open();


DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM item";


DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["itmid"] + "\t" + reader[1] + "\t" + reader[2] + "\t" + reader["price"]);
}
reader.Close();
connection.Close();
}
static void Main(string[] args)
{
connectUsingFactory();
Console.WriteLine();
connectUsingReference();
Console.ReadKey();
}

There is one other way......

using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using MySql.Data.MySqlClient;


ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;


conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();


conn.ConnectionString = "server=127.0.0.1;uid=root;" +
"pwd=12345;database=test;";


try
{
cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
"country.name, country.population, country.continent " +
"FROM country, city ORDER BY country.continent, country.name";
cmd.Connection = conn;


myAdapter.SelectCommand = cmd;
myAdapter.Fill(myData);


myReport.Load(@".\world_report.rpt");
myReport.SetDataSource(myData);
myViewer.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message, "Report could not be created",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}

[jomgrg]

Insert Method using SP (U can write Query in SP)

     try
        {
            string sqlCmd = "SP_Insert";
            DbCommand dbCmd = dataBase.GetStoredProcCommand(sqlCmd);

            dataBase.AddInParameter(dbCmd, "@EventID", DbType.String, EventID);
            dataBase.AddInParameter(dbCmd, "@Name", DbType.String, Name);
            dataBase.AddInParameter(dbCmd, "@Code", DbType.String, Code);
            dataBase.AddInParameter(dbCmd, "@LocationID", DbType.String, LocationID);
            dataBase.AddInParameter(dbCmd, "@CenterCode", DbType.String, partline.CenterCode);
            dataBase.AddInParameter(dbCmd, "@InvoiceNo", DbType.String, partline.InvoiceNo);
            dataBase.AddInParameter(dbCmd, "@InvoiceDate", DbType.DateTime, InvoiceDate);
            dataBase.AddInParameter(dbCmd, "@ConfirmationDate", DbType.DateTime, ConfirmationDate);
            dataBase.AddInParameter(dbCmd, "@Date", DbType.DateTime, Date);
            dataBase.AddInParameter(dbCmd, "@OrderNo", DbType.String, OrderNo);
            dataBase.AddInParameter(dbCmd, "@LineID", DbType.Int32, LineID);
            dataBase.AddInParameter(dbCmd, "@InventoryID", DbType.String, InventoryID);
            dataBase.AddInParameter(dbCmd, "@InvoiceQty", DbType.Int32, InvoiceQty);
            dataBase.AddInParameter(dbCmd, "@ReceivedQty", DbType.Int32, ReceivedQty);
            dataBase.AddInParameter(dbCmd, "@PDRQty", DbType.Int32, Qty);
            dataBase.AddInParameter(dbCmd, "@RetailPrice", DbType.Decimal, RetailPrice);
            dataBase.AddInParameter(dbCmd, "@CostPrice", DbType.Decimal, CostPrice);
            dataBase.AddInParameter(dbCmd, "@ReasonCode", DbType.String, ReasonCode);

Etc you can pass ( Several methods are there to pass u can use XML also)

            dataBase.ExecuteNonQuery(dbCmd);

        }
        catch (Exception ex)
        {
            ExceptionFactory.DataAccessException.AddInnerException(ex).Raise();
        }

[bhavyajyothinat]

thanks a lot
it was really helpful....and so clearly explained...
thanks a lot.. :)