MySql Database In ASP.NET Using C# With ODBC Connector

ODBC stands for Open Database connectivity and using ODBC, we can connect any database. We all know that MySql database is an open source, so many companies prefer MySQL database for their uses. First of all, we need MySql database in our system. We can click here to download MySQL database. While installing MySQL database, it will prompt for the password, we can give any password but in this example, I am taking “password” as a password.

Now, we can use the command prompt to create the database and table operations, but generally people prefer GUI tools for this, as SQLyog, Workbench, etc. In this article, we are going use SQLyog to create the database and table operations. We can click here to download SQLyog. Now, we have to install ODBC connector for connecting MySQL database in C#, using ODBC. We can click here to download ODBC connector.

First, I am going to create the database for our sample example, as given below-



We can also create the database by right clicking on the root menu, which is on the left top side of SQLyog community and create the database. Afterwards, we can either write SQL query to create the table or we can click on the newly created database sample - > right click on the table, as given below-



We are creating Employee table for our sample database, as given below-



Here, we are giving table name as Employee. After giving column name, data type and length, click the save button.

After creating a table, we are going to insert some record into the employee table, as given below-

INSERT INTO `sample`.`employee`(`id`,`name`,`department`)VALUES('1','vivek','IT');
INSERT INTO `sample`.`employee`(`id`,`name`,`department`)VALUES('2','ranjeet','IT');
INSERT INTO `sample`.`employee`(`id`,`name`,`department`)VALUES('3','sharath','IT');

Now, start Visual Studio and choose new project as File -> New -> Project.



We are giving our project name as Sample and click OK.

Now, select a template as Empty and Click OK, as given below-



Now, right click on the sample project from Solution Explorer and choose Add -> Web Form, as given below-



Enter item name as default in the new Window screen and click OK, as given below-



Now, open Web.config page from the Solution Explorer and write the connection strings inside the configuration, as given below-

<connectionStrings>
   <add name="conn" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=sample;uid=root;pwd=password"/>
</connectionStrings>

It is the best practice to put your connection string in Web.config in .NET project.

In the code, given above-

• Driver is the version of MySQL ODBC, which is 5.1 in our case.
• Server is the server’s address, which is localhost in our case.
• Database is the database name, which is sample in our case
• uid is the username of MySQL Server, which is root in our case.
• Pwd is the password of MySql server, which is password in our case.

Now, click Default.aspx page and write the code for the grid view, as given below-

<asp:GridView ID="gvEmployee" runat="server"></asp:GridView>

Now, go to the Default.aspx.cs page and import some namespaces, as given below-

using System.Data;
using System.Data.Odbc;
using System.Web.Configuration;

Write the code, given below, to get all the employee data from the MySQL Server database and assign to the grid view in Page_Load method, as given below-

string connString = WebConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using(OdbcConnection con = new OdbcConnection(connString))
{
    OdbcCommand cmd = new OdbcCommand("select * from Employee", con);
    OdbcDataAdapter da = new OdbcDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    gvEmployee.DataSource = ds;
    gvEmployee.DataBind();
}

Now, run the project and see the result, as given below-


Working with the stored Procedure

Generally, we prefer the stored procedure for any CRUD operations. Below is the sample code for using the stored procedure.

OdbcCommand cmd = new OdbcCommand("{CALL StoredProcedureName(?,?)}", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ParameterName1", ParameterValue1);
cmd.Parameters.AddWithValue("ParameterName2", ParameterValue2);
cmd.ExecuteNonQuery();

Where “?” is used for the number of parameters in the stored procedure.

In above example, we are taking two parameters as ParameterValue1 and ParameterValue2 due to which we are taking two question marks(?,?).

Quick view

Install MySQL database, click here to download MySQL database.
Install SQLyog, click here to download the SQLyog.
Install ODBC, click here to download the ODBC connector.
Use System.Data.Odbc namespace and start programming.