Monday, February 18, 2008

Connect MySQL to a C#.NET Project using VS2005 on XP Professional

Here is a helpful tutorial:
http://dev.mysql.com/tech-resources/articles/dotnet/#ODBC.NET

Here are my step-by-step instructions:

Set up the MySQL database

1. Download and install MySQL Community Server to your windows machine
http://dev.mysql.com/downloads/

2. Create a basic database for practice. Go to Start => MySQL => MySQL Command Line Client
http://dev.mysql.com/doc/refman/5.0/en/database-use.html

From this client create the basic db.
>CREATE DATABASE basicDB;
>USE basicDB;
>CREATE TABLE users (
LogonID VARCHAR(20) NOT NULL default '0',
Name VARCHAR(20) default null,
Password VARCHAR(20) default null,
LastLogon datetime default NULL,
PRIMARY KEY (LogonID));
>DESC users;
>INSERT INTO users (LogonID,Name,Password,LastLogon) VALUES ('amcferron','alex','12345678','2007-12-31');

3. Install the MySQL ODBC Driver-MyODBC 3.51
http://dev.mysql.com/downloads/connector/odbc/3.51.html#win32

4. On Windows XP Professional, go to Control Panal => Administrative Tools => Data Sources (ODBC)
Under the User DSN tab, choose ADD => scroll to the MySQL ODBC 3.51 Driver and click finish. A Login screen will pop up and you should fill out the following:
DataSourceName basicdb
Server localhost
User root
Password yourRootPasswordForSQLDB
Database => Here you should choose your database
once you see your database in the choices and you connect without issue, you can close this box down.

5. Open up a VS.NET 2005 project. Go to the toolbox and right click on the Data menu. Choose the "Choose Items..." option. Then under .net components add all of the components in the System.Data.ODBC namespace.
OdbcCommand
OdbcCommandBuilder
OdbcConnection
OdbcDataAdapter

also, in the code add using System.Data.Odbc;

6. Drag and Drop the ObdcDataAdapter from the toolbox to your Form and choose New Connection and fill out the form so that you are left with a tested connection to your database. This is pretty easy and now you are left with an odbcDataAdapter1 and an odbcConnection1 that can both be used


7. put the following code in your project. Put this code in a try catch block

this.odbcConnection1.Open();
string strSQL = "SELECT * FROM users";
OdbcCommand cmd = new OdbcCommand(strSQL);

cmd.Connection = odbcConnection1;

OdbcDataReader reader = cmd.ExecuteReader();

while (reader.Read()) {

Console.WriteLine("LoginID: " + reader.GetString(0));

}

reader.Close();

odbcConnection1.Close();

No comments: