Everything Science, Technology, Development, Gaming, Hardware and Music

Wednesday, April 19, 2006

MySQL & ASP.NET 2.0 - Part 1 - Connecting to a Database

I have recently seen a spate of posts in various forums on the net about people asking how to use MySQL and the .NET 2.0 Framework. After having implemented various production sites on the MySQL 5.0 / ASP.NET 2.0 model in IIS, the implementation of the model is simplistic.

I have found that using a mix of the ODBC providers along with the MySQL providers works best for most applications. As ODBC connectivity can provide an almost universal connection state to your MySQL DB, and the MySQL providers adds IDE functionality in Visual Studio equivalent to that of System.Data.SqlClient. It’s a bit of having the best of both worlds.

To implement the use of this model successfully, I recommend you do the following:

Requirements:

  • MySQL 5.0 (has support for stored procedures).
  • Microsoft Windows with IIS support (XP or Server 2003).
  • Microsoft Visual Studio 2005 (includes .NET Framework 2.0).
  • A database that has been set-up in MySQL – I will be using a database with the name BOOKS for this blog.
  • A Web Application designed in Visual Studio 2005.

Implementation:

  • Download the MySQL Connector .NET 1.0.7 from MySQL AB, and extract the installation. Also be sure to extract the MySql.Data.DLL file from the release folder in the installation ZIP.
  • In the Windows ODBC Control Panel Applet, define a system DSN for your BOOKS database and save this DSN with a name (e.g.: BooksDSN).
  • In your Web Application, add the extracted DLL to the Bin Directory of the application, right click on the Solution Name, and click on Add Reference. Browse to the DLL and click on OK.
  • In your web.config file, add a ConnectionString key for easy definition when using the SqlDataSource control e.g: (add name="BooksConnectionString" connectionString="
    DATABASE=books; DSN=BooksDSN; OPTION=0; PORT=0; SERVER=localhost;
    UID=username; providerName=System.Data.ODBC").
    In actual fact you can copy this key I have used here, changing just the key name, DSN, SERVER and UID to your own values.
  • In your ASPX pages, you need to inherit the usage of the MySQL provider by adding a using MySql.Data.MySqlClient statement in C# or imports MySql.Data.MySqlClient in Visual Basic.
  • You can now go to the Visual Editor, add a SqlDataSource, select the connection string out of the drop downlist and specify your SELECT query. The MySQL provider however does NOT provide UPDATE or DELETE statements for you, which I presume will be resolved in future releases of the connector.
  • You can now tie any DataBound Control to the SqlDataSource you have specified.

As mentioned, this I a simple example that works, for the more advanced users you can create a DataReader, DataSet, etc and populate these with the data from your Datasource. I have merely illustrated the shortest, straightest route to getting information out of your MySQL DB. In the next blog in the series I will discuss how to populate these controls with data from your datasource and handling addition, deletion and updating of data in your application.


0 Comments:

Post a Comment

<< Home