Programming Forums > Application Development > C#

Darkhack · Dec 9th, 2005, 1:07 PM

Hello guys, I am new here and wanted to start off posting something useful for you all to use. I couldn't post to the tutorials forum (admin only I assume) so I am posting a tutorial here and maybe the mods/admins might consider making it an official tutorial.

.NET uses a technology called ADO.NET to connect to databases and run SQL queries. One problem with it is that it only works for MS-SQL and their is no official support for MySQL. Luckily, someone way smarter than I has created a library that we can use. Full documentation is also aviable, but for this tutorial I will be only showing you the basics. Also I will be using a free development tool called SharpDevelop for this tutorial, although I've also used C# Express from MS so I know that they are similar enough for you guys to be able to adapt.

1. Download MySQL Connector/Net

2. Assuming you've downloaded the windows binaries, go ahead and run through the install. I believe the default directory is C:\Program Files\MySQL\ with a folder for the connector there. The latest version is 1.0.7 which is compatible with 1.0/1.1/2.0. Once inside the connector folder, click on "bin" (binary) and you will see folders for the different versions of .net.

NOTE: Screenshot is of version 1.0.6. The latest version WILL have a folder for .net 2.0.

3. Now that you know where the file is located. We can add to to our project/combine. Open up your IDE and file the project/combine properties panel. Right click on the References folder and click add.

4. Now select the Assembly Browser and click the browse button and find the correct dll that is associated with your version of .NET. You should now see MySql.Data.dll under your references folder.

5. YAY! We have everything set up correctly. Now lets put it to use! Lets look at a very basic program that accesses a MySQL database and we'll step through it.

First, add your includes. You should notice a new one that we've created that comes from the mysql dll file. Second you will see we have a connection string. It should be pretty self explanitory. Simply edit the values that are used for your server. Third Below the connection string are three lines establishing some new variables. The first one holds the actual connection. The second establishes what I guess you could think of as a socket. Its basically another connection that will actually send the SQL commands. The third line is our reader which will retrieve the information sent back from the server. Fourth we've now defined our command which in this example will select usernames from an account table. We use try and catch statements to safely exit incase we are unable to connect to the database. Fifth This is the complicated part. What it is doing here is cycling through our returned entries and displaying them into the console window. In this example only one column is selected but the for loop we've established will be sure to cycle through multiple columns per row if they apply. The row variable will increment and count the number of rows returned. Simply exit the connection when your finished and resume on with your application once you've got the data you need!!!

(Toggle Plain Text)

using System;
using System.Net;
using System.Net.Sockets;
using MySql.Data.MySqlClient; //first

class MySQL
{
	public static void main()
	{
		string conn = "server=127.0.0.1;userid=root;password=root;database=test";  //second
		MySqlConnection sqlConn = new MySqlConnection(conn);
		MySqlCommand command = sqlConn.CreateCommand();
		MySqlDataReader Reader;  // third
		command.CommandText = "SELECT username FROM accounts";  //fourth
		try 
		{
			sqlConn.Open();
			Reader = command.ExecuteReader();
		}
		catch
		{
			Console.WriteLine("Error Connecting to Database\n");
		}
		int row = 0;  // Fifth
		while (Reader.Read())
		{
			for (int i=0;i<Reader.FieldCount;i++)
			{
				Console.WriteLine( Reader.GetValue(i).ToString() );
			}
			row++;
		}
		sqlConn.Close();
	}
}

I hope you liked my first tutorial. Obviously it isn't very detailed, but I am assuming the end user has enough programming expierence to be able to fill in the gaps and also this is just a quick beginner guide. Obviously there are a TON more things you can do with the MySQL Connector and a lot of cool things can be accomplished. I hope I atleast got some of you started on the right track though!

I hope to be a proud member of your community and I am sure I can learn a lot from all of you and from time to time I can return the favor. Thanks again!

Dec 9th, 2005, 1:07 PM	#1
Darkhack Hobbyist Programmer Join Date: Dec 2005 Location: Kansas City Posts: 105 Rep Power: 3	Tutorial - Using MySQL in C# Hello guys, I am new here and wanted to start off posting something useful for you all to use. I couldn't post to the tutorials forum (admin only I assume) so I am posting a tutorial here and maybe the mods/admins might consider making it an official tutorial. .NET uses a technology called ADO.NET to connect to databases and run SQL queries. One problem with it is that it only works for MS-SQL and their is no official support for MySQL. Luckily, someone way smarter than I has created a library that we can use. Full documentation is also aviable, but for this tutorial I will be only showing you the basics. Also I will be using a free development tool called SharpDevelop for this tutorial, although I've also used C# Express from MS so I know that they are similar enough for you guys to be able to adapt. 1. Download MySQL Connector/Net 2. Assuming you've downloaded the windows binaries, go ahead and run through the install. I believe the default directory is C:\Program Files\MySQL\ with a folder for the connector there. The latest version is 1.0.7 which is compatible with 1.0/1.1/2.0. Once inside the connector folder, click on "bin" (binary) and you will see folders for the different versions of .net. NOTE: Screenshot is of version 1.0.6. The latest version WILL have a folder for .net 2.0. 3. Now that you know where the file is located. We can add to to our project/combine. Open up your IDE and file the project/combine properties panel. Right click on the References folder and click add. 4. Now select the Assembly Browser and click the browse button and find the correct dll that is associated with your version of .NET. You should now see MySql.Data.dll under your references folder. 5. YAY! We have everything set up correctly. Now lets put it to use! Lets look at a very basic program that accesses a MySQL database and we'll step through it. First, add your includes. You should notice a new one that we've created that comes from the mysql dll file. Second you will see we have a connection string. It should be pretty self explanitory. Simply edit the values that are used for your server. Third Below the connection string are three lines establishing some new variables. The first one holds the actual connection. The second establishes what I guess you could think of as a socket. Its basically another connection that will actually send the SQL commands. The third line is our reader which will retrieve the information sent back from the server. Fourth we've now defined our command which in this example will select usernames from an account table. We use try and catch statements to safely exit incase we are unable to connect to the database. Fifth This is the complicated part. What it is doing here is cycling through our returned entries and displaying them into the console window. In this example only one column is selected but the for loop we've established will be sure to cycle through multiple columns per row if they apply. The row variable will increment and count the number of rows returned. Simply exit the connection when your finished and resume on with your application once you've got the data you need!!! (Toggle Plain Text) using System; using System.Net; using System.Net.Sockets; using MySql.Data.MySqlClient; //first class MySQL { public static void main() { string conn = "server=127.0.0.1;userid=root;password=root;database=test"; //second MySqlConnection sqlConn = new MySqlConnection(conn); MySqlCommand command = sqlConn.CreateCommand(); MySqlDataReader Reader; // third command.CommandText = "SELECT username FROM accounts"; //fourth try { sqlConn.Open(); Reader = command.ExecuteReader(); } catch { Console.WriteLine("Error Connecting to Database\n"); } int row = 0; // Fifth while (Reader.Read()) { for (int i=0;i<Reader.FieldCount;i++) { Console.WriteLine( Reader.GetValue(i).ToString() ); } row++; } sqlConn.Close(); } } using System; using System.Net; using System.Net.Sockets; using MySql.Data.MySqlClient; //first class MySQL { public static void main() { string conn = "server=127.0.0.1;userid=root;password=root;database=test"; //second MySqlConnection sqlConn = new MySqlConnection(conn); MySqlCommand command = sqlConn.CreateCommand(); MySqlDataReader Reader; // third command.CommandText = "SELECT username FROM accounts"; //fourth try { sqlConn.Open(); Reader = command.ExecuteReader(); } catch { Console.WriteLine("Error Connecting to Database\n"); } int row = 0; // Fifth while (Reader.Read()) { for (int i=0;i<Reader.FieldCount;i++) { Console.WriteLine( Reader.GetValue(i).ToString() ); } row++; } sqlConn.Close(); } } I hope you liked my first tutorial. Obviously it isn't very detailed, but I am assuming the end user has enough programming expierence to be able to fill in the gaps and also this is just a quick beginner guide. Obviously there are a TON more things you can do with the MySQL Connector and a lot of cool things can be accomplished. I hope I atleast got some of you started on the right track though! I hope to be a proud member of your community and I am sure I can learn a lot from all of you and from time to time I can return the favor. Thanks again!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > C#

Tutorial - Using MySQL in C#