article

Using MySQL with Visual Studio

Email
Submitted on: 1/1/2015 9:28:00 PM
By: Particle  
Level: Intermediate
User Rating: By 34 Users
Compatibility: VB.NET
Views: 30
 
     If you're just wondering how to use MySQL with VS.NET or are having problems, give this a try.

 
				Introduction
     Hello, my name's Geoff (aka Particle) and if you're here you're probably wondering how to get MySQL to work with Visual Studio.NET.  I had the same problem this morning and it took hours to find everything I needed.  Therefore, I thought I'd make a guide to save other people time.  If you're completely new to MySQL, please visit www.pcrpg.org/TRPGguides/mysqloledotnet.php for complete instructions on how to install and configure MySQL, useful tools such as phpMyAdmin, and everything needed to make phpMyAdmin work.  What I am posting here is just the last portion of the guide dealing with VS.NET itself.  I'll leave the "What You'll Need" table from the beginning of the guide in tact (except for the web components and installing MySQL, etc--for those please visit the link above).  Some of the links have been broken into two lines.  Sorry about the links--it appears that due to some immaturity, hyperlinks can no longer be used so I'll just bold them.
 
MyOLEDB Driver http://www.mysql.com/downloads/download.php?file=Downloads/
Win32/MyOLEDB3.exe&pick=mirror
Microsoft Visual Studio .NET http://msdn.microsoft.com/vstudio/
   
Other Stuff That's Nice to Have:  
.NET Framework 1.1 http://www.microsoft.com/downloads/details.aspx?FamilyID=
262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en
.NET Framework SDK 1.1 http://www.microsoft.com/downloads/details.aspx?FamilyId=
9B3A2CA6-3647-4070-9F41-A333C6B9181D&displaylang=en
MDAC v2.7 http://www.microsoft.com/downloads/details.aspx?FamilyID=
9ad000f2-cae7-493d-b0f3-ae36c570ade8&DisplayLang=en

     Alright.  As you can see, we'll be using MySQL 4.0, MDAC 2.7, and one of the many listed MyOLEDB drivers listed on MySQL's website.  I'm not using VS.NET 2003, so if you have that product I cannot ensure that this guide will work (or is even necessary).  If you were getting a message along the lines of "MySQLProv was not registered on localhost" then installing the MyOLEDB driver I linked to should fix that problem.

Ok, Now I've Done All of That Crap--What About Visual Studio?
     If you have a database setup, you can proceed to work in Visual Studio.  If not, use phpMyAdmin to create a database and a simple table.  Usage of phpMyAdmin is rather straight-forward.  If you need help, try the documentation for it above.  Also, there are links named "Documentation" everywhere throughout phpMyAdmin that can point you to help.  (Information on using phpMyAdmin is available at www.pcrpg.org/TRPGguides/mysqloledotnet.php)

     Now, assuming you've installed the MyOLEDB driver and your MySQL server is up and running, let's begin.  I only cover VB.NET code, but the methodology is similar for C# as well.  If you're programming and have come this far using C++, then you should be able to adapt this to your language.

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim
fdCom As New OleDb.OleDbCommand("SELECT * FROM TABLENAME ORDER BY DESIREDFIELD ASC", fdCon)

fdCom.Connection.Open()

Dim
fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

While fdRead.Read
     'Do what you want here.  Below is code that will pop up a message box for every record.
     'This code would work if your table had three fields.
     'This database's first field is an auto-incrementing ID medium integer, second field is a
     'VarChar, and the third is also a VarChar.  This code displays each field on its own line.

     MsgBox(fdRead.GetValue(0) & vbCrLf & fdRead.GetValue(1) & vbCrLf & fdRead.GetValue(2))

End
While

fdCon.Close()

     You will need to change the bold parts to the appropriate information.  For example, DATABASE should be the name of your MySQL database, YOURSQLUSERNAME is the username (probably root) for the database, and the same concept for YOURSQLPASSWORD.  Change TABLENAME to the name of your table.  It's a good idea to stay away from all capital names in SQL.  You don't have to use the ORDER BY DESIRED FIELD ASC statement, but you can to sort the data.  If you want to use it, change the DESIREDFIELD to the name of one of your fields.  Sorting by an ID field if you've got one is always a good idea.  ASC = ascending; DESC = descending.  For more information on SQL commands, please visit a site such as:
http://www.phpfreaks.com/postgresqlmanual/page/sql-commands.html
or for an explained easy-to-learn course of basic SQL commands such as INSERT, SELECT, UPDATE, and DELETE go to:
http://www.developerfusion.com/show/48/1/
Once you've learned the stuff at Developer Fusion, the PHP Freaks page will come in handy as you'll understand it better.

     Now that you've learned basic data retrieval, let's go over a non-query.

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim
fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim
fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES (""lookatme"", ""newline!"")"
fdCom.ExecuteNonQuery()


fdCon.Close()

Once again, replace TABLENAME and the FIELDNAMEx to appropriate values.  You can have more or less fields (depending on what's in your table) or select one the fields you want.  However, take notice of the order in which you list them as that's the order in which the VALUES will be placed.  If you take notice, "lookatme" would be inserted as FIELDNAME1 and "newline!" would be inserted for FIELDNAME2.  Using a double double-quote in VB acts as an escape character and actually inserts a real double-quote.  It's always a good practice to either use that or a solitary single-quote around your variable names.  If you were going to use the variables ImaVar1 and ImaVar2 with double-quotes, you could do it like this:

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim
fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim
fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES (""" & ImaVar1 & """, """ & ImaVar2 & """)"
fdCom.ExecuteNonQuery()


fdCon.Close()

Yes, that's three double-quotes together.  That's the double double-quote inside of a quoted string.  It stores an actual quote there.  Now, to use single quotes you might do it like below:

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim
fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim
fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES ('" & ImaVar1 & "', '" & ImaVar2 & "')"
fdCom.ExecuteNonQuery()


fdCon.Close()

That's a single-quote on the inside of the string declaration quotes.  ' " and " ' accordingly--no spaces between them.  I guess that about wraps it up.  If you need help on something, fire off an email to mysqlhelp@pcrpg.org and I'll get back to you as soon as I can.  Thanks for reading this tutorial--it's pretty long, I know.  I could have spent a Saturday better ways, trust me!


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.