Click here to Skip to main content
15,113,873 members
Articles / Web Development / ASP.NET
Posted 28 Nov 2005


76 bookmarked

An ASP.NET Application Using a MySQL Database

Rate me:
Please Sign up or sign in to vote.
4.66/5 (30 votes)
28 Nov 2005CPOL3 min read
How to use a MySQL 4.1 database from an ASP.NET application and some ODBC basics.


.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its programming model for web development. I'm creating a Web Form with a DataGrid for data entry using C#. How can I make a DataGrid cell display a textbox for showing a list of products in all the rows. This solution will teach you how to use a MySQL database with ASP.NET. I have developed this sample with ASP.NET 1.1 using C# as the code-behind using MySQL 4.1. I think this will be a more useful article for beginners studying .NET. In this sample, I have taken the DataGrid control for demonstration. All the basic operations of the DataGrid are explained in this article.

Overview of the Solution


MySQL Server 4.0 laid the foundation for the new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers. The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available. Clients can connect to a MySQL server using TCP/IP sockets on any platform. On Windows systems belonging the NT family (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can connect using UNIX domain socket files.

In MySQL 4.1, we cannot write stored procedures, functions, or views. The following are sample SELECT, UPDATE, and DELETE queries in MySQL.

SELECT column_names from table_name [WHERE ...conditions];

UPDATE table_name SET column_names = ‘’ WHERE ...conditions;

DELETE FROM table_name WHERE ...conditions;


The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems. The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.

The following will explain to you the architecture of ODBC:

Image 1

Namespace used for ODBC:

using System.Data.Odbc;

The System.Data.Odbc namespace is the .NET Framework Data Provider for ODBC.

The .NET Framework Data Provider for ODBC describes a collection of classes used to access an ODBC data source in the managed space. Using the OdbcDataAdapter class, you can fill a memory-resident DataSet, which you can use to query and update a data source.

Solution with Code

First, let us see how to connect ASP.NET with a MySQL database. The following code will explain how to connect with a MySQL 4.1 database:

// Connection String
private const string ConnStr = 
   "Driver={MySQL ODBC 3.51 Driver};Server=localhost;" + 
// DataBinding

private void BindDataGrid()
    using(OdbcConnection con = new OdbcConnection(ConnStr))
    using(OdbcCommand cmd = 
          new OdbcCommand("SELECT * FROM Sample", con))
        DataGrid1.DataSource = cmd.ExecuteReader(
                    CommandBehavior.CloseConnection | 

This snippet shows the code to bind data from a MySQL database to a DataGrid control. Each and every basic operation is written as a separate function. The following are the functions for the basic operations to be performed in the DataGrid:

// Insert Operation
private void InsertInfo()
        HtmlTable2.Visible = false;

        using(OdbcConnection con = new OdbcConnection(ConnStr))
        using(OdbcCommand cmd = new OdbcCommand("INSERT INTO sample" + 
                                "(name, address) VALUES (?,?)", con))
            cmd.Parameters.Add("@name", OdbcType.VarChar, 
                               255).Value = TextBox3.Text.Trim();
            cmd. Parameters.Add("@address", OdbcType.VarChar, 
                                255).Value = TextBox4.Text.Trim();

// Update Operation

private void UpdateInfo(int id, string name, string address)
    using(OdbcConnection con = new OdbcConnection(ConnStr))
    using(OdbcCommand cmd = new OdbcCommand("UPDATE sample " + 
                      "SET name = ?, address = ? WHERE ID = ?", con))
        cmd.Parameters.Add("@name", OdbcType.VarChar, 255).Value = name;
                       OdbcType.VarChar, 255).Value = address;
        cmd.Parameters.Add("@ID", OdbcType.Int).Value = id;
// Update Operation

private void DeleteInfo(int id)
    using(OdbcConnection con = new OdbcConnection(ConnStr))
    using(OdbcCommand cmd = new OdbcCommand("DELETE " + 
                      "FROM sample WHERE ID = ?", con))
        cmd.Parameters.Add("@ID", OdbcType.Int).Value = id;

The table design for this sample:

CREATE TABLE sample ( 
    id         int AUTO_INCREMENT NOT NULL,
    name       varchar(45) NOT NULL,
    address    varchar(45) NOT NULL,

Details about the DataGrid:

The DataGrid control displays tabular data and optionally supports selecting, sorting, paging, and editing of data. By default, a DataGrid generates a BoundColumn for each field in the data source (AutoGenerateColumns=true). Each field in the data is rendered in a separate column, in the order it occurs in the data. Field names appear in the grid's column headers, and values are rendered in text labels. A default format is applied to non-string values.

// Update Event
private void DataGrid1_UpdateCommand(object source, 
             System.Web.UI.WebControls.DataGridCommandEventArgs e)
        int cUsrID ;
        string strName;
        string strAddress;

        Literal ltID;
        TextBox txtTempName;
        TextBox txtTempAddress;

        ltID = (System.Web.UI.WebControls.Literal ) 
        cUsrID = Convert.ToInt32 (ltID.Text);

        txtTempName = (System.Web.UI.WebControls.TextBox)
        strName = txtTempName.Text;

        txtTempAddress = (System.Web.UI.WebControls.TextBox)
        strAddress = txtTempAddress.Text;
        UpdateInfo(cUsrID, strName, strAddress);

        DataGrid1.EditItemIndex = -1;
    catch(Exception ex)


After reading this article, you will have a basic knowledge about ODBC connections, DataGrids and most importantly about MySQL. I hope this article will be more useful for budding programmers.

Image 2


  • Online MSDN library.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Chief Technology Officer at Zealots
Australia Australia
No Biography provided

Comments and Discussions

GeneralMy vote of 4 Pin
Jagadeeswaran Natarajan16-Sep-14 23:09
professionalJagadeeswaran Natarajan16-Sep-14 23:09 
QuestionError1: Could not load type 'Speech.MySqlDataGrid' Pin
Vital8928-Sep-11 6:37
MemberVital8928-Sep-11 6:37 
AnswerRe: Error1: Could not load type 'Speech.MySqlDataGrid' Pin
nitintfs7-Feb-12 2:21
Membernitintfs7-Feb-12 2:21 
GeneralRe: Error1: Could not load type 'Speech.MySqlDataGrid' Pin
jay314116-May-12 10:39
Memberjay314116-May-12 10:39 
QuestionParser Error Pin
baijuep197019-Aug-11 6:12
Memberbaijuep197019-Aug-11 6:12 
GeneralMy vote of 5 Pin
Himanshupareek6616-Jul-10 4:26
MemberHimanshupareek6616-Jul-10 4:26 
GeneralNeed help.....Getting error in connecting to the mysql from my website... Pin
BhumikaP25-Jun-09 3:54
MemberBhumikaP25-Jun-09 3:54 
GeneralParser error Pin
manucoolen23-Apr-07 3:48
Membermanucoolen23-Apr-07 3:48 
GeneralRe: Parser error Pin
vivekthangaswamy26-Apr-07 21:25
professionalvivekthangaswamy26-Apr-07 21:25 
GeneralRe: Parser error Pin
Shmonya9-Mar-08 0:08
MemberShmonya9-Mar-08 0:08 
GeneralRe: Parser error Pin
edwarmartinez16-Jul-08 14:06
Memberedwarmartinez16-Jul-08 14:06 
GeneralMySQL UNICODE [modified] Pin
AmirAlilou29-Dec-06 0:49
MemberAmirAlilou29-Dec-06 0:49 
Generalerror Pin
rj_21_20034-Dec-06 17:11
Memberrj_21_20034-Dec-06 17:11 
GeneralRe: error Pin
vivekthangaswamy7-Dec-06 2:28
professionalvivekthangaswamy7-Dec-06 2:28 
GeneralRe: error Pin
vivekthangaswamy7-Dec-06 2:31
professionalvivekthangaswamy7-Dec-06 2:31 
QuestionCan't Update Pin
arif.hendrianto15-Oct-06 21:43
Memberarif.hendrianto15-Oct-06 21:43 
AnswerRe: Can't Update Pin
vivekthangaswamy20-Oct-06 7:26
professionalvivekthangaswamy20-Oct-06 7:26 
QuestionWhy ODBC Pin
FZelle28-Nov-05 9:47
MemberFZelle28-Nov-05 9:47 
AnswerRe: Why ODBC Pin
theDiver28-Nov-05 22:35
MembertheDiver28-Nov-05 22:35 
GeneralRe: Why ODBC Pin
Kanedogg18-Sep-07 4:30
MemberKanedogg18-Sep-07 4:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.