Connecting to a MySQL Database with C# ASP .NET

Introduction

If you’re creating a web application using C# ASP .NET, you will more than likely be using one of the three top database engines MySQL, MSSQL (Microsoft SQL Server), or Oracle to store your online data. From the developer’s view, all three databases are virtually the same. You use the same generic framework calls to connect, read, and write data. While it is more common to use Microsoft’s MSSQL Server with a .NET application, a large number of web hosts offer the free MySQL database. Therefore, connecting to a MySQL database from C# ASP .NET is an important task and is actually quite easy to do.

Using MySQL with C# ASP .NET

MySQL is a free database and is used very heavily by the linux developers usually when programming with Perl, CGI, or Ruby on Rails. Because of this, MySQL is, for the most part, catered and optimized directly towards the linux developing platform. However, using MySQL from a Windows web application is also supported and MySQL offers several drivers just for Windows users.

The actual library you choose to connect to the database from C# ASP .NET can make a huge impact on the speed and reliability of your database transactions. In addition, your web server may only be configured to use a specific database library from within C#. Choosing the correct database library to use is an important decision in your web application design.

MySQL and MySQLDriverCS

MySQLDriverCS is a free driver framework for connecting to MySQL from within a C# ASP .NET application. It uses the ADO .NET framework. To use it, you simply include a reference to MySQLDriverCS.dll from within your C# .NET application and include the file libMySQL.dll within your /bin folder. Below is an example of using MySQLDriverCS to connect to a MySQL database.

using MySQLDriverCS;
using System.Data;

MySQLConnection myConn;
MySQLDataReader MyReader = null;

try
{
  myConn = new MySQLConnection(new MySQLConnectionString("123.456.789.100",
                                                "mydatabase",
                                                "user",
                                                "pass").AsString);

  myConn.Open();
  string sql = "SELECT * FROM Table";
  MySQLCommand cmd = new MySQLCommand(sql, myConn);  

  MyReader = cmd.ExecuteReaderEx();

  while (MyReader.Read())
  {
    Console.WriteLine( MyReader["Product_Name"].ToString() );
  }

  MyReader.Close();       
}
catch(Exception ee)
{
  Console.WriteLine( ee.ToString() );
}
finally
{
   if (MyReader != null && !MyReader.IsClosed)
   {
      MyReader.Close();
   }

   if (myConn != null && myConn.State == ConnectionState.Open)
   {
      myConn.Close();
   }
}

Note in the above sample that the MySQLConnection and MySQLDataReader objects are defined outside of the try/catch block. In the finally block, the reader and connection are explicitly checked for and closed. This is extremely important. You must always close the reader and connection in the finally block. This allows you to properly close all database connections even if an error occurs within the code execution of the try block. Otherwise, you could end up with open connections leaking on the MySQL database server, resulting in an eventual “Too many connections” error.

MySQL and ODBC

A more supported format for connecting to MySQL from a C# .NET web application is by using the common ODBC driver. First, you will need to install the MySQL ODBC driver http://www.mysql.com/products/connector/odbc on your PC. This same driver (MyODBC) is usually installed on most web servers. As such, you may have no other choice except to use ODBC for connecting to MySQL.

Connecting to the MySQL database from your C# ASP .NET web application using ODBC is very similar to the above example. You just change a small part of each command definition from MySQL to Odbc.

using System.Data.Odbc;
using System.Data;

OdbcConnection myConn;
OdbcDataReader MyReader = null;

try
{
  myConn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=129.456.789.100;Database=mydatabase;User=username;Password=mypass;");

  myConn.Open();
  string sql = "SELECT * FROM Table";
  OdbcCommand cmd = new OdbcCommand(sql, myConn); 

  MyReader = cmd.ExecuteReader();

  while (MyReader.Read())
  {
    Console.WriteLine( MyReader["Product_Name"].ToString() );
  }

  MyReader.Close();       
}
catch(Exception ee)
{
  Console.WriteLine( ee.ToString() );
}
finally
{
   if (MyReader != null && !MyReader.IsClosed)
   {
      MyReader.Close();
   }

   if (myConn != null && myConn.State == ConnectionState.Open)
   {
      myConn.Close();
   }
}

Notice the difference in connection strings. While you can easily change between database libraries in your C# .NET application by doing a search and replace in Visual Studio, the only significant change you will need to make is the connection string you use.

Odbc is generally the most supported connection framework. Most web servers using Helm or Plesk already provide a DSN builder online that you can use in your connection string, such as strConnection = “Server=123.456.789.100;DSN=myDSNName”; This would allow you to use a DSN connection. The above source code examples show a DSN-less connection. DSN-less is just a term for including the full connection string options within the string itself, rather than in a .dsn file.

MySQL and OLEDB

OLEDB is another method for connecting to MySQL and can be used in the same way as defined above. Simply change the Odbc or MySql part of each command to OleDb. Then configure a valid connection string for OleDB.

using System.Data.OleDb;
using System.Data;

OleDbConnection myConn;
OleDbDataReader MyReader = null;

try
{
  myConn = new OleDbConnection("Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=MyUsername; Password=MyPassword;");

  myConn.Open();
  string sql = "SELECT * FROM Table";
  OleDbCommand cmd = new OleDbCommand(sql, myConn); 

  MyReader = cmd.ExecuteReader();

  while (MyReader.Read())
  {
    Console.WriteLine( MyReader["Product_Name"].ToString() );
  }

  MyReader.Close();       
}
catch(Exception ee)
{
  Console.WriteLine( ee.ToString() );
}
finally
{
   if (MyReader != null && !MyReader.IsClosed)
   {
      MyReader.Close();
   }

   if (myConn != null && myConn.State == ConnectionState.Open)
   {
      myConn.Close();
   }
}

MySQL Pooling and the Too Many Connections or Sleeping Connections Error

When working with MySQL, it is important to monitor the performance of your database calls by using MySQLAdministrator, even on a remote server. This allows you to track any leaking connections that remain open. However, if you are using the try catch finally method to close all connections after using them, you should not have any stray connections left open. If you do see any left open, you will need to do a search for myConn.Open() in Visual Studio and verify each call to myConn.Open() is within a try catch finally block and that the connection and reader are closed in the finally block.

One thing you might see in the MySQLAdministrator is that you have several connections available in the Sleep state. This is usually related to connection pooling in MySQL. Pooling allows connections to remain available, but in a sleeping state, for immediate use by the web application. This greatly increases speed and performance of your C# ASP .NET web application. However, many ASP .NET users have complained that the MySQL pooling does not seem to perform correctly and leaves too many sleeping connections available. Depending on the configuration of the MySQL database on the server, this could result in your web application being locked out from MySQL with a “Too Many Connections” error, even though the connections are in a Sleep state. Users have commented that this is not seen on the linux platform when working with Perl or CGI and seems to be occuring generally on the Windows platform.

Benefits of MySQL vs. MSSQL

MySQL is a free database. If you are concerned with price alone, then MySQL beats them all. However, MySQL was initially geared towards the linux enviornment and, as such, may not be as supported in the Windows platform as Microsoft’s MSSQL Server.

MySQL is fast. MySQL tables are designed in such a way that they are extremely fast to read and write to. In many C# ASP .NET web applications, MySQL will appear faster than MSSQL. However, this is ususally related to the hardware installed on the web server. For web servers with top of the line hardware, this may not be an issue. But for many web servers out there, especially on shared virtual hosting, CPU power is limited and MySQL may win with speed.

MySQL has a tendancy to corrupt easier than MSSQL. If the server reboots, your application crashes, or a mysterious error creeps in, MySQL may be the one to suffer a corrupt database table sooner than MSSQL. The usual suspect is:
Can’t open file: ‘mytable.MYI’ (errno: 145)

To fix the database table, this error requires you to issue a: repair table mytable

Even with the repair command, the table may remain corrupt and need to be deleted and restored with a backup. This may be an important factor in which database you choose to use, especially if reliability is the top factor for importance.

MySQL and MSSQL both offer full text searching. However, MSSQL requires an additional Full-Text Service to be installed and running on the database server in order to make full text searching active. MySQL, on the other hand, simply lets you define a set of columns as a full text index. You can then query for a full text search using the MATCH command, as follows:
SELECT * FROM MyTable WHERE MATCH (Name, Nickname) AGAINST (‘John’)

Full Text searching provides greatly enhanced speed when searching the database. Although, in some cases, using a standard SELECT may provide enough performance. This largely depends on the size of your database, index, and the type of content contained within.

Stored procedures, advanced triggers, and functions, available in MSSQL and Oracle, can greatly increase the speed of database calls. Often, complex database queries are moved to a stored procedure to allow the database to more efficiently process the calls, rather than letting the web application code do it. MSSQL and Oracle both provide rich tools for developing and using stored procedures. However, the early versions of MySQL, which are usually installed on many web hosts, do not support these. If your web server has a more recent version of MySQL installed, you may have the benefit of using stored procedures.

Conclusion

MySQL is a free database and offers fast performance for web applications on both the Linux and Windows platform. Connecting to MySQL from a C# ASP .NET web application is virtually the same as connecting to a MSSQL or Oracle database. Choosing the right database platform is an important choice in the design of your C# ASP .NET web application.

About the Author

This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.

Sponsor Me