Page 1 of 1

Using The ODBC Driver To Connect To a MySQL Server

#1 Gavisann  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 103
  • View blog
  • Posts: 382
  • Joined: 01-July 11

Posted 06 July 2011 - 12:23 PM

I would like the start off by stating that this is my first tutorial, I am welcome to any suggestions you may have for me to improve this tutorial or any future ones I may create.

This tutorial is broken up into four parts. Part one deals with installing and configuring MySQL and its ODBC connector. Part two deals with setting up MySQL. Part three deals with using the ODBC driver in your code. Finally, part four is an example on how to use what you learned in this tutorial.

Part three explains each line of code needed to connect and will not work if copy and pasted into a compiler. Part four is an example that will work when copy and pasted, but requires some set up.

Part 1:
First off we need to download MySQL Community Server so we can test our connections later on.
Navigate to mysql.com/downloads/mysql and select the installer at the bottom of the page that relates to your computers configuration.
Quick shortcuts to the 32 and 64 bit versions:
(0) 32 bit installer
(0) 64 bit installer

Next, we need to download the Connector at http://dev.mysql.com...onnector/odbc/.
Quick shortcuts to the 32 and 64 bit versions:
(0) 32 bit installer
(0) 64 bit installer

Now it is time to configure MySQL server and ODBC.

Part 2:
Run the MySQL server installer. When prompted, select typical installation.
When configuring the MySQL server, the only important option for us right now is set the password to “password” without the quotes. If you would like to use a different password, make sure you edit the code accordingly.
After the server is installed, we need to install ODBC. Run the installer, select Typical Installation, Next, let it install, then click Finish.


Now navigate to Start, all programs, MySQL, MySQL server 5.5, and run the MySQL command line.
Enter the password you set earlier and press enter.
We need to create the database. In this example we will use the database named testing.

Use this command to create the database:
Create database testing;


This should be the result:
Posted Image
Next, we need to switch to our newly created database. This can be done by using the following command:
 
Use testing;


Example:
Posted Image
Now we must create our names table. This is the command needed to do so:
Create table names (first VARCHARS(255), last VARCHARS(255));


Your screen should look something like this:
Posted Image
You are finished setting up the database, close out of the command line and move on to part three.

Part 3

First we need to import a couple of namespaces.
 
using System.Data;
 using System.Data.Odbc ;
 

Then, we need our connection string.
 string ConnStr = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Database=test;uid=root;pwd=password";
OdbcConnection con = new OdbcConnection(ConnStr);
 

More info
• The driver is the version of MySQL ODBC you are using, in our case it is 5.1
• The server is the server’s address
• The database is the database we want to use
• uid is the username required to assess the MySQL server
• and you all know what a password is.

Now we need to add our command, or query.
We are going to use two commands, one to insert data and the other to select and read data.
OdbcCommand cmd = new OdbcCommand("insert into names (first, last)values ('" + firstbox.Text + "', '" + lastbox.Text + ”')", con);
OdbcCommand cmd2 = new OdbcCommand("select first from names where last = '" + searchbox.Text + "'", con);


After that, we need to open the connection, execute the command and assign it to a variable, then close the connection. When executing a command that is inserting data, you want to use cmd.executenonquery(), but when you want to receive a single line, a last name, you want to use cmd.executeScalar().
 
con.Open();
cmd.ExecuteNonQuery();
resultlb.Text = Convert.ToString(cmd2.ExecuteScalar());
con.Close();



Part 4:


I will be using VS Pro 2010, but you are welcome to use any compiler you would like.
First, create a Windows Form project. I will be naming mine odbcexample.
Add a few textboxes, buttons, and labels so your form looks something like mine.

Posted Image

The black texts with the red arrows pointing at them are the names of those objects. If you change the name of the objects, remember to change them in the code as well.
Your code should look similar to this
        private void insert_Click(object sender, EventArgs e)
        {
            string ConnStr = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Database=testing;uid=root;pwd=password";
            OdbcConnection con = new OdbcConnection(ConnStr);
            OdbcCommand cmd = new OdbcCommand("insert into names (first, last)values ('" + firstbox.Text + "', '" + lastbox.Text + "')", con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            insert.Text = "Done...";
        }

        private void select_Click(object sender, EventArgs e)
        {
            string ConnStr = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Database=testing;uid=root;pwd=password";
            OdbcConnection con = new OdbcConnection(ConnStr);
            OdbcCommand cmd = new OdbcCommand("select first from names where last = '" + searchbox.Text + "'", con); 
            con.Open();
            resultlb.Text = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
            select.Text = "Done...";
        }





To test the code, enter a first and last name and wait for the insert button’s text to change to “Done…”. Enter the same last name you entered about in searchbox and press select. If all goes according to plan, you should see the first name you entered in the resultlb label.

If you have any questions or suggestions, please do not hesitate to post them.
Thanks for reading!

Is This A Good Question/Topic? 2
  • +

Replies To: Using The ODBC Driver To Connect To a MySQL Server

#2 giggly kisses  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 80
  • View blog
  • Posts: 391
  • Joined: 29-March 09

Posted 11 July 2011 - 08:06 PM

Nice job on the tutorial man!
Was This Post Helpful? 1
  • +
  • -

#3 seier  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 1
  • Joined: 07-May 12

Posted 07 May 2012 - 01:03 PM

Thank you very much for the tutorial, however it's always VARCHAR and never VARCHARS, please edit your post.
Was This Post Helpful? 1
  • +
  • -

#4 freeman015  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 28-July 10

Posted 03 November 2012 - 03:36 PM

Dear Gavisann,

at first great tutorial.
the only thing i wanted to know is how do you come to the drivers name like the one you used.
MySQL ODBC 5.1 Driver or is this to give the beast a name?

Refering to

View PostGavisann, on 06 July 2011 - 12:23 PM, said:

Then, we need our connection string.
 string ConnStr = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Database=test;uid=root;pwd=password";
OdbcConnection con = new OdbcConnection(ConnStr);
 

More info
• The driver is the version of MySQL ODBC you are using, in our case it is 5.1
• The server is the server’s address
• The database is the database we want to use
• uid is the username required to assess the MySQL server
• and you all know what a password is.


I would love to know how that part works.
maybe you can help out.

Greetings,
Freeman015
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1