Using MySQL with Visual Studio
If you're just wondering how to use MySQL with VS.NET or are having problems, give this a try.
AI
AI Summary: This codebase represents a historical implementation of the logic described in the metadata. Our preservation engine analyzes the structure to provide context for modern developers.
Source Code
<font color="#0099CC"><b>Introduction</b></font><br>
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 <b>
www.pcrpg.org/TRPGguides/mysqloledotnet.php</b> 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.<br>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber1">
<tr>
<td width="31%">MyOLEDB Driver</td>
<td width="69%">
<b>
http://www.mysql.com/downloads/download.php?file=Downloads/<br>
Win32/MyOLEDB3.exe&pick=mirror</b></td>
</tr>
<tr>
<td width="31%">Microsoft Visual Studio .NET</td>
<td width="69%">
<b>http://msdn.microsoft.com/vstudio/ </b> </td>
</tr>
<tr>
<td width="31%"> </td>
<td width="69%"> </td>
</tr>
<tr>
<td width="31%">Other Stuff That's <i>Nice</i>
to Have:</td>
<td width="69%"> </td>
</tr>
<tr>
<td width="31%">.NET Framework 1.1</td>
<td width="69%">
<b>
http://www.microsoft.com/downloads/details.aspx?FamilyID=<br>
262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en</b></td>
</tr>
<tr>
<td width="31%">.NET Framework SDK 1.1</td>
<td width="69%">
<b>
http://www.microsoft.com/downloads/details.aspx?FamilyId=<br>
9B3A2CA6-3647-4070-9F41-A333C6B9181D&displaylang=en</b></td>
</tr>
<tr>
<td width="31%">MDAC v2.7</td>
<td width="69%">
<b>
http://www.microsoft.com/downloads/details.aspx?FamilyID=<br>
9ad000f2-cae7-493d-b0f3-ae36c570ade8&DisplayLang=en</b></td>
</tr>
</table>
<p> 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.</p>
<p><font color="#0099CC"><b>Ok, Now I've Done All of
That Crap--What About Visual Studio?<br>
</b></font> 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)</p>
<p> 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.</p>
<font SIZE="2">
<p></font><font size="2" COLOR="#0000ff">Dim</font><font size="2">
fdCon <font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font>
OleDb.OleDbConnection("Provider=MySQLProv;Data
Source=<b>DATABASE</b>;User Id=<b>YOURSQLUSERNAME</b>;Password=<b>YOURSQLPASSWORD</b>;")</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdCom
<font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font>
OleDb.OleDbCommand("SELECT * FROM <b>TABLENAME </b>
ORDER BY<b> DESIREDFIELD </b>ASC", fdCon)<br>
<br>
fdCom.Connection.Open()<br>
</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdRead
<font COLOR="#0000ff">As</font>
OleDb.OleDbDataReader =
fdCom.ExecuteReader(CommandBehavior.CloseConnection)</font></p>
<p><font size="2" COLOR="#0000ff">While</font><font size="2">
fdRead.Read<br>
<font color="#008000">'Do what you want here.
Below is code that will pop up a message box for
every record.<br>
'This code would work if your table had three fields.<br>
'This database's first field is an auto-incrementing ID
medium integer, second field is a<br>
'VarChar, and the third is also a VarChar. This
code displays each field on its own line.</font><br>
MsgBox(fdRead.GetValue(0) & vbCrLf & fdRead.GetValue(1)
& vbCrLf & fdRead.GetValue(2))</font><font size="2" COLOR="#0000ff"><br>
End</font><font size="2"> </font>
<font COLOR="#0000ff"><font size="2">While<br>
</font></font><font size="2"><br>
fdCon.Close()</font></p>
<p> 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:<br>
<b>http://www.phpfreaks.com/postgresqlmanual/page/sql-commands.html</b><br>
or for an explained easy-to-learn course of basic
SQL commands such as INSERT, SELECT, UPDATE, and
DELETE go to:<br>
<b>http://www.developerfusion.com/show/48/1/ </b> <br>
Once you've learned the stuff at Developer Fusion,
the PHP Freaks page will come in handy as you'll
understand it better.</p>
<p> Now that you've learned
basic data retrieval, let's go over a non-query.</p>
<font SIZE="2">
<p></font><font size="2" COLOR="#0000ff">Dim</font><font size="2">
fdCon <font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font>
OleDb.OleDbConnection("Provider=MySQLProv;Data
Source=<b>DATABASE</b>;User Id=<b>YOURSQLUSERNAME</b>;Password=<b>YOURSQLPASSWORD</b>;")</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdCom
<font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font> OleDb.OleDbCommand("",
fdCon)<br>
<br>
fdCom.Connection.Open()<br>
</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdRead
<font COLOR="#0000ff">As</font>
OleDb.OleDbDataReader =
fdCom.ExecuteReader(CommandBehavior.CloseConnection)</font></p>
<font SIZE="2">
<p>fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES (""lookatme"", ""newline!"")"<br>
fdCom.ExecuteNonQuery()</font><font size="2" COLOR="#0000ff"><br>
</font><font size="2"><br>
fdCon.Close()</font></p>
<p>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:</p>
<font SIZE="2">
<p></font><font size="2" COLOR="#0000ff">Dim</font><font size="2">
fdCon <font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font>
OleDb.OleDbConnection("Provider=MySQLProv;Data
Source=<b>DATABASE</b>;User Id=<b>YOURSQLUSERNAME</b>;Password=<b>YOURSQLPASSWORD</b>;")</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdCom
<font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font> OleDb.OleDbCommand("",
fdCon)<br>
<br>
fdCom.Connection.Open()<br>
</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdRead
<font COLOR="#0000ff">As</font>
OleDb.OleDbDataReader =
fdCom.ExecuteReader(CommandBehavior.CloseConnection)</font></p>
<font SIZE="2">
<p>fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES (""" & ImaVar1 &
""", """ & ImaVar2 & """)"<br>
fdCom.ExecuteNonQuery()</font><font size="2" COLOR="#0000ff"><br>
</font><font size="2"><br>
fdCon.Close()</font></p>
<p>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:</p>
<font SIZE="2">
<p></font><font size="2" COLOR="#0000ff">Dim</font><font size="2">
fdCon <font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font>
OleDb.OleDbConnection("Provider=MySQLProv;Data
Source=<b>DATABASE</b>;User Id=<b>YOURSQLUSERNAME</b>;Password=<b>YOURSQLPASSWORD</b>;")</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdCom
<font COLOR="#0000ff">As</font>
<font COLOR="#0000ff">New</font> OleDb.OleDbCommand("",
fdCon)<br>
<br>
fdCom.Connection.Open()<br>
</font><font size="2" COLOR="#0000ff"><br>
Dim</font><font size="2"> fdRead
<font COLOR="#0000ff">As</font>
OleDb.OleDbDataReader =
fdCom.ExecuteReader(CommandBehavior.CloseConnection)</font></p>
<font SIZE="2">
<p>fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES ('" & ImaVar1 & "',
'" & ImaVar2 & "')"<br>
fdCom.ExecuteNonQuery()</font><font size="2" COLOR="#0000ff"><br>
</font><font size="2"><br>
fdCon.Close()</font></p>
<p>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 <b>mysqlhelp@pcrpg.org</b> 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!</p>
Original Comments (3)
Recovered from Wayback Machine