To modify MySQL databases using C#.NET or any language using .NET framework, you must first install the MySQL Connector for .NET. You can download it from here. Avoid v6.3.6 for now, as I've discussed in a previous post, that version might have some bugs. Go the the previous versions section and download v6.3.4 instead. After you've downloaded it, install. Now run Visual Studio. Open your project. Go to View menu > Solution Explorer > right click on References > Add References > under the tab .NET, select MySQL.Data > Ok. That's almost it. Now you've added the reference to your project. To start coding, add this line to the top of your source -
using MySql.Data.MySqlClient;
Now you can write the codes just like you do with SQL server, just add My in front of SQL :P. See the code below for example. I wrote two abstract function for my use.
private string MySQLConnectionString = "SERVER=localhost;DATABASE=db_name;UID=root;PASSWORD=;";
public int mysql_query_execute_noReturn(string query)
{
    Int32 row_affected = 0;
    try
    {
        MySqlConnection con = new MySqlConnection(MySQLConnectionString);
        if (con.State == System.Data.ConnectionState.Closed)
        {
            con.Open();
        }
        MySqlCommand command = new MySqlCommand(query, con);
        row_affected = command.ExecuteNonQuery();

        if (con.State == System.Data.ConnectionState.Open)
        {
            con.Close();
        }
    }
    catch (Exception ex)
    {

    }
    return row_affected;
}

public DataSet mysql_query_execute_return(string query)
{
    DataSet dataSet = new DataSet();
    try
    {
        MySqlConnection con = new MySqlConnection(MySQLConnectionString);
        if (con.State == System.Data.ConnectionState.Closed)
        {
            con.Open();
        }
        MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, con);
        dataAdapter.Fill(dataSet);
        if (con.State == System.Data.ConnectionState.Open)
        {
            con.Close();
        }
    }
    catch (Exception ex)
    {

    }
    return dataSet;
}
You can pass any query string to these functions, mysql_query_execute_noReturn will take a query, execute it and return the number of rows affected. It's only for queries like INSERT, UPDATE, DELETE etc. And mysql_query_execute_return will take a query, execute it and return a DataSet containing all the records. It's for queries like SELECT. So, try it out. :)

Bibhas Debnath

iambibhas bibhasdn


Published

blog comments powered by Disqus