Programming Forums > Application Development > C#

Darkhack · Dec 9th, 2005, 12: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!

Polyphemus_ · Dec 9th, 2005, 12:10 PM

Welcome to the forums, nice first post

.

coldDeath · Dec 9th, 2005, 12:12 PM

Welcome,
thats a nice tutorial :-) great work man.

deathseeker25 · Dec 9th, 2005, 1:50 PM

Good tutorial...

Thank you

zorin · Dec 9th, 2005, 3:40 PM

nice tutorial

ivan · Dec 9th, 2005, 4:16 PM

Nice tutorial and welcome to the forums!

BlazingWolf · Dec 9th, 2005, 4:24 PM

Nice work.

Perhaps it should have gone in the "Programming Tutorials" forum but we will excuse you because your new. :p

Darkhack · Dec 9th, 2005, 5:21 PM

I think only mods/admins or people with a certain post count can post in the Programming Tutorials board. I was unable to. I kept getting an error about lacking permission or something, and yes I was logged in and everything.

Oh well... Thanks for all the feedback!

EDIT: here is what shows up when I try to post there... I think my account has not been activated yet by the admin.

Quote:

Darkhack, you do not have permission to access this page. This could be due to one of several reasons:

1. Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
2. If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Dameon · Dec 9th, 2005, 5:56 PM

Bravo. Coherent, informative, and it has screenshots. Welcome to the forums!

Ooble · Dec 9th, 2005, 6:23 PM

Awesome. Welcome to the club, mate.

Because lots of people were posting questions in the tutorial forums, you can't post new threads in them any more. Eventually a mod or admin will see this and move it.

Probably.

Dec 9th, 2005, 12:07 PM	#1
Darkhack Hobbyist Programmer Join Date: Dec 2005 Location: Kansas City Posts: 102 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!

Dec 9th, 2005, 12:12 PM	#3
coldDeath Expert Programmer Join Date: Aug 2005 Location: UK Posts: 862 Rep Power: 3	Welcome, thats a nice tutorial :-) great work man. __________________ Programming Tutorials - Linux Installation Guide - Learn Python - Learn C++ Got Slack? Join us at #programmingforums @ irc.freenode.net! My software never has bugs. It just develops random features.

Dec 9th, 2005, 1:50 PM	#4
deathseeker25 Programmer Join Date: May 2005 Posts: 48 Rep Power: 0	Good tutorial... Thank you __________________ FileSearcher BETA Portugal-a-Programar - the biggest portuguese programming community

Dec 9th, 2005, 4:24 PM	#7
BlazingWolf Hobbyist Programmer Join Date: Sep 2004 Posts: 207 Rep Power: 4	Nice work. Perhaps it should have gone in the "Programming Tutorials" forum but we will excuse you because your new. :p __________________ _______________________________ BlazingWolf

Dec 9th, 2005, 5:56 PM	#9
Dameon Troll Join Date: Apr 2005 Location: Texas Posts: 732 Rep Power: 4	Bravo. Coherent, informative, and it has screenshots. Welcome to the forums! __________________ MD5(sig) = bcef75433db02e9ad9bf81d6f7c5c270

Dec 9th, 2005, 12:10 PM	#2
Polyphemus_ Expert Programmer Join Date: Aug 2005 Location: Rotterdam, the Netherlands Posts: 942 Rep Power: 3	Welcome to the forums, nice first post .

Dec 9th, 2005, 3:40 PM	#5
zorin Hobbyist Programmer Join Date: Apr 2005 Posts: 218 Rep Power: 4	nice tutorial

Dec 9th, 2005, 4:16 PM	#6
ivan Professional Programmer Join Date: Sep 2005 Location: serbia & montenegro Posts: 484 Rep Power: 3	Nice tutorial and welcome to the forums!

Dec 9th, 2005, 6:23 PM	#10
Ooble I eat cake for breakfast. Join Date: Jul 2004 Location: In my box. Posts: 4,434 Rep Power: 9	Awesome. Welcome to the club, mate. Because lots of people were posting questions in the tutorial forums, you can't post new threads in them any more. Eventually a mod or admin will see this and move it. Probably. __________________ Me :: You :: Them

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

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

Programming Forums > Application Development > C#

Tutorial - Using MySQL in C#