Accessing and using MySQL in .NET and Visual Studio
Posted: Tue Oct 04, 2011 3:44 am
This is something I have been wanting for some time. While I enjoy SQL Server, it does have some virtually needless features that can complicate simple application development, not to mention the fact that it is commercial software. MySQL does not make you pay more for additional CPUs in your server, and it can make hosting cheaper for web applications. It also arguably scales better, but some data is contradictory, so I would not hang my hat on that reason.
Anyway, on to how to get them to work together!
ADO.NET - A Brief Background
Basic database connections and work are traditionally done through ADO.NET, the successor to ActiveX Data Objects (ADO), the main data access solution for the Visual Basic 6 days. There are a handful of main parts to ADO.NET that you will see with any database:
1. Connection: This is the type that will actually make the connection to the database, and must be opened and closed.
2. Command: This is the type that will represent the actions you want to make on your database, whether they are SELECT queries, INSERT, etc.
3. DataReader: These are the most simple way of accessing the data you retrieve from the database.
There are several others, such as Parameter and Transaction, but they are outside of the scope of this tutorial. This is not meant to be a full ADO.NET tutorial, but rather, just some background to get everyone on the same page.
The way that ADO.NET connects to various databases is through "Data Providers". Every database requires one, even Microsoft SQL Server. There is also a generic Data Provider for OLE DB connections, which represents generic database handling that allows you to connect to basically any db in a "lowest common denominator" fashion, but it is mostly considered legacy due to virtually every conceivable database out there having their own Data Providers.
Getting the MySQL Data Provider
First go to the MySQL Connector/Net download page linked here. Choose the 32-bit .msi installer (even if you use 64-bit, as there is no native 64-bit version). Close Visual Studio if you have it open and then run the Connector/Net installer. After a few moments, it should finish. It automatically installs the tools needed to access MySQL through Visual Studio 2005, 2008 and 2010 (note that this tutorial is aimed at VS 2010, although it should be about the same on any other versions).
Connection to MySQL Through Visual Studio
Open a project you wish to add MySQL data access to. Click on the "Server Explorer" tab, which should be grouped with the Solution Explorer window. If it is not open, click the View menu option, and then click Server Explorer near the top. Right-click the Data Connections node, and choose "Add Connection". Under Data Source, click the Change button, and choose MySQL Database as the Data Source. The Data Provider should be ".NET Framework Data Provider for MySQL". Click OK.
On the next part of the Add Connection dialog box, for the Server Name, type "localhost" if you are connecting to a local database for testing (such as your WAMP or XAMPP MySQL installation). Enter your User Name and Password just like you would for a PHP database connection script. Enter the name of the actual database under the Database Name field. Now make sure your database is running (as simple as ensuring WAMP or XAMPP are running if you are using that db install), and then click "Test Connection" to make sure you have everything entered right. Note that this is not the actual information used by your application to connect to the db. The info you are adding right now is for Visual Studio integration, which is excellent and will be covered shortly. If you get a message that the test was successful, click Ok. Your database is now available in Visual Studio!
Working with a MySQL Database in Visual Studio
One of the most time-saving features of Visual Studio is being able to connect to, open, query and manage databases right in the IDE, and MySQL is no exception!
If you connected to a database on your local machine, you will see something like "localhost(dbName)", where "dbName" is the name of your database. Click the arrow next to it to expand out the top-level folders of the database that divide the database into Tables, Views, Stored Procedures, Stored Functions and UDFs. Expand Tables, and right-click one of your tables. In the shortcut menu, click "Retrieve Data" to see your data, and you can also edit data directly through here. You can also double-left-click a table name in the Server Explorer to see the table's properties. From here you can alter any of the properties of the table, such as column value ranges and more.
You can also test your queries directly through Visual Studio. Right-click a table name or the main node of your database (localhost(dbName)) and choose "New Query". You will get a dialog box listing the tables in your database which you can add. If you want to use the visual, WYSIWYG query-building tools (somewhat similar to Microsoft Access), choose the tables you want to involve in the query. Close the Add Table dialog box. I personally prefer to just write the queries manually, and thus I am not intimately familiar with everything this interface has to offer in visual, WYSIWYG query building. So, of course if you want to write your queries manually (and you probably do, since you have to write them manually in your application) go down to the pane with the SQL query skeleton in it ("SELECT FROM table"). You can edit this query to your heart's content, and then when you are ready to run it, click the red exclamation mark in the menu at the top. A list of the results will appear directly below the query entry pane. This can be quite a convenience versus having to constantly switch back-and-forth from phpMyAdmin or another MySQL tool and Visual Studio.
Setting Up References
You must reference the correct assembly to be able to use the MySQL Data Provider. In the Solution Explorer, right-click the References folder, and choose "Add Reference". On the Add Reference dialog box, click the "Browse" tab, and go to your C: drive. Open your Program Files folder (Program Files (x86) on 64-bit Windows), and find the MySQL folder. Open the "MySQL Connector Net x.x.x", then open Assemblies and then v4.0. Inside the v4.0 folder, click "MySql.Data.dll" and click OK. This will add the correct assembly reference to your project.
In your code file, ensure Visual Studio added "using System;" and "using System.Data;" using statements for you. Add in two more using statements:
If you added the assembly references correctly, they should be found by VS and are likely in the Intellisense menu. You are now ready to write MySQL .NET code!
Writing MySQL ADO.NET Code
I am going to include a simple Windows Forms application written in C#, and then go over some of the important aspects of it. The form itself is very simple. It includes a button at the top with the default name of "button1". Directly below it is a Multiline text box with ScrollBars set to "Vertical" named "output". The goal of the application is to open and when the button is clicked, run a SELECT query and display the results in the multiline text box, one record after another, separated by "=========". Here is the code:
Notice that I have 2 class fields: A MySqlConnection object and a string that will be used as the connection string. I set the connection string equal to "server=127.0.0.1;uid=dbuser;pwd=thisdbpass;database=test", which is the server, db user name, password and database name, respectively. This will basically mirror the info you added in the Add Connection dialog box earlier.
In the constructor of this form, I instantiate the MySqlConnection object, and then set its ConnectionString property to the connection string I made earlier. Once you do this the MySqlConnection object is ready for use.
At the point where you want to access the database, you need to open a Try/Catch exception-handling block. This is to prevent any errors we may come across trying to access the database from crashing the program. If you are not familiar with this construct or exception-handling, read here.
The first thing we do inside the Try block is call the Open() method of the MySqlConnection object. You now have a live link to the database, and these resources are limited, so don't open them well before you are going to use them, and close them as soon as you are done. After opening them, set a new string equal to the SQL query you want to run (this string is just called "sql" here). Then create a new MySqlCommand object, passing into the constructor first the SQL query string and then the MySqlConnection object you made earlier. Next create a MySqlDataReader object and set its value to the returned value of the ExecuteReader() method of the MySqlCommand object (if this sounds crazy, just find the line that starts "MySqlDataReader" in the code above, as it is really not that bad).
You will then iterate through calls to the Read() method of your MySqlDataReader object, with each call retrieving the next record your query retrieved. I use a simple While loop here, since Read() returns false when there are no more records to retrieve, which conveniently closes the loop. Inside the loop, simply reference your MySqlDataReader object as an array. For example, in my code I called the MySqlDataReader object rdr, so rdr[0] is the value of the first column of whichever record the While loop is currently on, while rdr[1] is the second, etc.
You then can use this data however you wish. In my case, I am adding them to a string I defined earlier along with some escape characters for line breaks in the multiline text box, and then immediately outside of the While loop I am making it the text of the text box. I then immediately close the connection to the db by calling the Close() method of the MySqlConnection object.
------------
Well, that is about it! I hope someone finds this useful for using MySQL with .NET and C#! If you have any questions, let me know and I will do my best to help!
Anyway, on to how to get them to work together!
ADO.NET - A Brief Background
Basic database connections and work are traditionally done through ADO.NET, the successor to ActiveX Data Objects (ADO), the main data access solution for the Visual Basic 6 days. There are a handful of main parts to ADO.NET that you will see with any database:
1. Connection: This is the type that will actually make the connection to the database, and must be opened and closed.
2. Command: This is the type that will represent the actions you want to make on your database, whether they are SELECT queries, INSERT, etc.
3. DataReader: These are the most simple way of accessing the data you retrieve from the database.
There are several others, such as Parameter and Transaction, but they are outside of the scope of this tutorial. This is not meant to be a full ADO.NET tutorial, but rather, just some background to get everyone on the same page.
The way that ADO.NET connects to various databases is through "Data Providers". Every database requires one, even Microsoft SQL Server. There is also a generic Data Provider for OLE DB connections, which represents generic database handling that allows you to connect to basically any db in a "lowest common denominator" fashion, but it is mostly considered legacy due to virtually every conceivable database out there having their own Data Providers.
Getting the MySQL Data Provider
First go to the MySQL Connector/Net download page linked here. Choose the 32-bit .msi installer (even if you use 64-bit, as there is no native 64-bit version). Close Visual Studio if you have it open and then run the Connector/Net installer. After a few moments, it should finish. It automatically installs the tools needed to access MySQL through Visual Studio 2005, 2008 and 2010 (note that this tutorial is aimed at VS 2010, although it should be about the same on any other versions).
Connection to MySQL Through Visual Studio
Open a project you wish to add MySQL data access to. Click on the "Server Explorer" tab, which should be grouped with the Solution Explorer window. If it is not open, click the View menu option, and then click Server Explorer near the top. Right-click the Data Connections node, and choose "Add Connection". Under Data Source, click the Change button, and choose MySQL Database as the Data Source. The Data Provider should be ".NET Framework Data Provider for MySQL". Click OK.
On the next part of the Add Connection dialog box, for the Server Name, type "localhost" if you are connecting to a local database for testing (such as your WAMP or XAMPP MySQL installation). Enter your User Name and Password just like you would for a PHP database connection script. Enter the name of the actual database under the Database Name field. Now make sure your database is running (as simple as ensuring WAMP or XAMPP are running if you are using that db install), and then click "Test Connection" to make sure you have everything entered right. Note that this is not the actual information used by your application to connect to the db. The info you are adding right now is for Visual Studio integration, which is excellent and will be covered shortly. If you get a message that the test was successful, click Ok. Your database is now available in Visual Studio!
Working with a MySQL Database in Visual Studio
One of the most time-saving features of Visual Studio is being able to connect to, open, query and manage databases right in the IDE, and MySQL is no exception!
If you connected to a database on your local machine, you will see something like "localhost(dbName)", where "dbName" is the name of your database. Click the arrow next to it to expand out the top-level folders of the database that divide the database into Tables, Views, Stored Procedures, Stored Functions and UDFs. Expand Tables, and right-click one of your tables. In the shortcut menu, click "Retrieve Data" to see your data, and you can also edit data directly through here. You can also double-left-click a table name in the Server Explorer to see the table's properties. From here you can alter any of the properties of the table, such as column value ranges and more.
You can also test your queries directly through Visual Studio. Right-click a table name or the main node of your database (localhost(dbName)) and choose "New Query". You will get a dialog box listing the tables in your database which you can add. If you want to use the visual, WYSIWYG query-building tools (somewhat similar to Microsoft Access), choose the tables you want to involve in the query. Close the Add Table dialog box. I personally prefer to just write the queries manually, and thus I am not intimately familiar with everything this interface has to offer in visual, WYSIWYG query building. So, of course if you want to write your queries manually (and you probably do, since you have to write them manually in your application) go down to the pane with the SQL query skeleton in it ("SELECT FROM table"). You can edit this query to your heart's content, and then when you are ready to run it, click the red exclamation mark in the menu at the top. A list of the results will appear directly below the query entry pane. This can be quite a convenience versus having to constantly switch back-and-forth from phpMyAdmin or another MySQL tool and Visual Studio.
Setting Up References
You must reference the correct assembly to be able to use the MySQL Data Provider. In the Solution Explorer, right-click the References folder, and choose "Add Reference". On the Add Reference dialog box, click the "Browse" tab, and go to your C: drive. Open your Program Files folder (Program Files (x86) on 64-bit Windows), and find the MySQL folder. Open the "MySQL Connector Net x.x.x", then open Assemblies and then v4.0. Inside the v4.0 folder, click "MySql.Data.dll" and click OK. This will add the correct assembly reference to your project.
In your code file, ensure Visual Studio added "using System;" and "using System.Data;" using statements for you. Add in two more using statements:
Code: Select all
using MySql.Data;
using MySql.Data.MySqlClient;Writing MySQL ADO.NET Code
I am going to include a simple Windows Forms application written in C#, and then go over some of the important aspects of it. The form itself is very simple. It includes a button at the top with the default name of "button1". Directly below it is a Multiline text box with ScrollBars set to "Vertical" named "output". The goal of the application is to open and when the button is clicked, run a SELECT query and display the results in the multiline text box, one record after another, separated by "=========". Here is the code:
Code: Select all
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace AppWindowsForms {
public partial class Form1 : Form {
MySqlConnection conn;
string myConnectionString = "server=127.0.0.1;uid=dbuser;pwd=thisdbpass;database=test";
public Form1() {
InitializeComponent();
conn = new MySqlConnection();
conn.ConnectionString = myConnectionString;
}
private void button1_Click(object sender, EventArgs e) {
string displayText = "";
try {
conn.Open();
string sql = "SELECT userid, username, email FROM users WHERE security='pet'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
displayText += rdr[0] + "\r\n" + rdr[1] + "\r\n" + rdr[2] + "\r\n=========\r\n";
}
output.Text = displayText;
conn.Close();
output.Text = displayText;
} catch (MySqlException ex) {
MessageBox.Show(ex.Message);
}
}
}
} In the constructor of this form, I instantiate the MySqlConnection object, and then set its ConnectionString property to the connection string I made earlier. Once you do this the MySqlConnection object is ready for use.
At the point where you want to access the database, you need to open a Try/Catch exception-handling block. This is to prevent any errors we may come across trying to access the database from crashing the program. If you are not familiar with this construct or exception-handling, read here.
The first thing we do inside the Try block is call the Open() method of the MySqlConnection object. You now have a live link to the database, and these resources are limited, so don't open them well before you are going to use them, and close them as soon as you are done. After opening them, set a new string equal to the SQL query you want to run (this string is just called "sql" here). Then create a new MySqlCommand object, passing into the constructor first the SQL query string and then the MySqlConnection object you made earlier. Next create a MySqlDataReader object and set its value to the returned value of the ExecuteReader() method of the MySqlCommand object (if this sounds crazy, just find the line that starts "MySqlDataReader" in the code above, as it is really not that bad).
You will then iterate through calls to the Read() method of your MySqlDataReader object, with each call retrieving the next record your query retrieved. I use a simple While loop here, since Read() returns false when there are no more records to retrieve, which conveniently closes the loop. Inside the loop, simply reference your MySqlDataReader object as an array. For example, in my code I called the MySqlDataReader object rdr, so rdr[0] is the value of the first column of whichever record the While loop is currently on, while rdr[1] is the second, etc.
You then can use this data however you wish. In my case, I am adding them to a string I defined earlier along with some escape characters for line breaks in the multiline text box, and then immediately outside of the While loop I am making it the text of the text box. I then immediately close the connection to the db by calling the Close() method of the MySqlConnection object.
------------
Well, that is about it! I hope someone finds this useful for using MySQL with .NET and C#! If you have any questions, let me know and I will do my best to help!
