Many of you might be trying to get MySQL working with Visual Studio 2005 or C# Express Edition. Well, Microsoft clearly made the DB manager for Microsoft SQL Server and Oracle Server. Although, the makers of MySQL Server have released an extension for VS 2005 and C# Express. You will need to download and install it on your development computer to continue. Here is the download link: MySQL Connector/Net 5.1.

References

If you’ve installed it, launch your C# compiler/IDE. Next, open the project you want to add MySQL support to.

Now, go to the Project menu => Add Reference. Under the .NET tab look for MySQL.Data under “Component Name.” Click “OK” and were are ready to start coding.

Import Namespaces

Now we are ready to import the “MySQL.Data” namespace. To do that. Just add this to the very beginning of your VB Code file:

using MySQL.Data;

Connection String

Before we can connect to the MySQL Server, we must create a connection string. Here is the format:

Database=yourdb;Data Source=yourIP/domain;User Id=userID;Password=yourPassword

To make your connection string, just start replacing “yourdb” with the name of the database you want to connect to. Next, replace “yourIP/domain” with the address of your server. It can be a domain or IP address. It can not be “localhost” if you want other people to use your application from the Internet. Replace “userID” with your login name to the server. Lastly, replace “yourPassword” with the password you use to login to the MySQL Server.

Example Connection String:

Database=CGFavs;Data Source=cg.nystic.com;User Id=Brent;Password=Friedman

Declarations

Next, let us declare our variables:

MySqlClient.MySqlCommand cmd = New MySqlClient.MySqlCommand;
Object returnValue;
MySqlClient.MySqlConnection connect = New MySqlClient.MySqlConnection(”Database=CGFavs;Data Source=cg.nystic.com;User Id=Brent;Password=Friedman”);

Replace my example connection string with the one you created in the step before this.

Select Statements That Return One Row

If you need to get the value of a certain column but that query will only return only one row. Here is how to do that.

cmd.CommandText = “SELECT `password` FROM `users` WHERE (username=’foobar’)”;
cmd.Connection = connect;
connect.Open();
returnValue = cmd.ExecuteScalar;
connect.Close();

Now replace “password” with your column’s name. Next replace “users” with the table’s name. and last just change your WHERE clause to how you need it. The syntax for that is “WHERE (column=’rowsColumnValue’)”

Select Statements That Return More Than One Row

MySqlClient.MySqlDataReader read;
cmd3.CommandText = “SELECT * FROM `favs` WHERE (column=’rowsColumnValue’)”;
cmd3.Connection = connect;
read = cmd3.ExecuteReader;
while (read.Read())
{
Messagebox.Show(read.GetString(1));
}
read.Close();
connect.Close();

To get the value of a colum just use the following command: “read.GetString()”
Put the colum number inside the parentheses so it knows which column to return. Like: “read.GetString(1)”
The column numbering starts with ‘0′ not ‘1′.

Insert, Delete, and Update Statements

For a query that do not return a value, here is how to do just that:

cmd.CommandText = “INSERT INTO `favs` VALUES (’1′, ‘foo’, ‘foobar’)”;
cmd.Connection = connect;
connect.Connect();
cmd.ExecuteNonQuery();
connect.Close();

It’s pretty basic. ExecuteNonQuery just executes a query that doesn’t return a value.

Variables Inside a Query

To insert a variable inside a query just do end the string by closing the quotes, add a space then the and symbol (&&) then the variable. Then add another space, another and symbol, then open a new quote. Then continue the string for the query. Example:

cmd.CommandText = “SELECT `password` FROM `cgfavs`.`users` WHERE (username=’” && TextBox1.Text && “‘)”

In Closing

That’s it. Just make sure that the MySQL.Data.dll is saved with the project and sent together. To do that. go to the Project Menu => Show All Files. Then, in the solution exploerer, go to the References folder and click on the MySQL.Data item. Next, go to the properties window, and make sure that “Copy Local” is set to True. Then, make sure you distribute the MySQL.Data.dll with your EXE and in the same folder.

See ya folks!

discuss this topic to forum