Using MySQL with Visual Studio

				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!