Natively these two products don’t work together very easily, but MySQL has since released a set of standard .NET data access objects including a MySqlConnection, MySqlCommand, MySqlDataAdapter and MySqlDataReader. These are just a few of many types and classes included in the MySql.Data namespace and here I will show you how easy it is to create code that you can quickly switch between Microsoft SQL Server 2005 (or above) and MySQL using our generic System.Data.Common classes.
Goto to our forum to post more questions here.
The first thing that you will need to do is go and download the MySQL connector for .NET from here. Now, once we have that installed we can start coding. I’m not going to write and entire .NET sample application, instead, I will show you the code that you will need to use to connect to the two types of databases.
Second thing to do is to make sure we have all of our using statements (imports for VB.NET), we should have the following for MySQL integration:
using MySql.Data;
using MySql.Data.MySqlClient;
These for Microsoft SQL Server
using System.Data;
using System.Data.SqlClient;
Now because we want to make this usable for MS SQL and MySQL, we need one more further namespace to be used.
using System.Data.Common;
The System.Data.Common namespace contains all our generic data access classes that MySQL and MS SQL adapters derive from. Now we have our using statements for the data access components, we can start writing code the connect to these databases and start executing queries.
First thing we will do is create our basic types. We do this as follows:
DbConnection myDataConnection;
DbCommand myDataCommand;
DbReader myDataReader;
As these are abstract data type, we do not construct objects of them.
The next step is to connect to the database. We are going to set this up so it will attempt to connect to a MySQL database, but fail-over to a MS SQL database if the MySQL database is not found. Finally, if neither database is connected too, it will report an error.
try
{
myDataConnection = new MySqlConnection(“Data Source=myserver;Database=mydatabase;User Id=dbuser;Password=dbuserpass”);
myDataConnection.Open();
}
catch(MySqlException e)
{
try
{
myDataConnection = new SqlConnection(“Data Source=mssqlserver;Inital Catalog=database;User Id=username;Password=userpassword”);
myDataConnection.Open();
}
catch(SqlException e)
{
System.Windows.Forms.MessageBox.Show(“No database could be connected!”);
}
}
It’s a simple as that. This code will attempt to connect to a MySQL database and if for any reason it fails, it will attempt to connect to a MS SQL database. If neither is connected, it will tell the user.
Now for the command object. This is used to execute a command and is used in conjunction with the data reader, which access the information that has been retrieved from the database.
The base object that we will use is the DbDataReader and DbCommand. Again, these are abstract classes and as such we do not create instances of them directly, but assign them instances of MySqlDataReader/MySqlCommand or SqlDataReader/SqlCommand.
We are going to expand our current code to allow for the these to be executed with in the current try blocks for each database type. This will mean that if any part of connecting, retrieving or processing the data fails, the program will attempt to perform the process on the other database.
So how do we execute a query? We use a database command. Now a command can be made for many purpose, SQL queries, Stored Procedures, MDX queries for MSSQL2K5 and the like. The first thing we need to do is know what type of command we are executing. There 3 main types, Query, Non-Query and Scalar. We will be using a query, which will return to us a DataReader object. A non-query is query that does not return a result, such as a standalone insert or update or delete. A scalar returns the first column of the first row as an object and no other data.
So we execute our query by using something similar too:
myDataReader = myDataCommand.ExecuteQuery(“SELECT * FROM test”);
So lets put it in practice!
try
{
myDataConnection = new MySqlConnection(“Data Source=myserver;Database=mydatabase;User Id=dbuser;Password=dbuserpass”);
//Create our data command using the connection
myDataCommand = (DbCommand) new MySqlCommand(myDataConnection);
myDataReader = (DbDataReader) myDataCommand.ExecuteQuery(“SELECT * FROM test”);
}
catch(MySqlException e)
{
try
{
myDataConnection = new SqlConnection(“Data Source=mssqlserver;Inital Catalog=database;User Id=username;Password=userpassword”);
//Create our data command using the connection
myDataCommand = (DbCommand) new SqlCommand(myDataConnection);
myDataReader = (DbDataReader) myDataCommand.ExecuteQuery(“SELECT * FROM test”);
}
catch(SqlException e)
{
System.Windows.Forms.MessageBox.Show(“No database could be connected!”);
}
}
So that’s basically it! The program will connect to the database and execute a query and give us a dbDataReader object back. Now, finally, we need to use the data reader object, and this really is quite simple so long as you know 2 things. The ordinal (or index, base 0) of the item you want to retrieve, and the data type of the item. To iterate through the result set, we use dbDataReader.NextResult() or dbDataReader.Read(), I believe they do the exact same but I’m not 100% sure.
So the code to use it is as follows. I’ve also added in an extra check to make sure that there is data in the result, otherwise we get messy exceptions that need to be dealt with. This way is much easier.
if(myDataReader.HasRows)
{
while(myDataReader.NextResult())
{
System.Windows.Forms.MessageBox.Show(String.Format(“Column1(text): {0}, Column2 (integer): {1}”, myDataReader.GetString(0), myDataReader.GetInt32().ToString()));
}
}
And that is it! Everything we were going to cover! So know you know how to create a program that will connect to both a MySQL and a MSSQL server or one or the other. We’ve also looked at how to use the ExecuteQuery() of the dbCommand object and also how to use the results of a query by using a dbDataReader object.
If you have any other questions. Then post them in the forum! And we will get back to you!
For now…
Cheers,
Allan
MSP Curtin
MCP
MCDST
MCAD (Exams Pending)
Add A Comment