65.9K
CodeProject is changing. Read more.
Home

C# and MySQL/Connector 5.2

starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.79/5 (7 votes)

Mar 22, 2008

CPOL

1 min read

viewsIcon

46740

An article on how to connect to a MySQL database using the Connector/NET 5.2

Introduction

I'm a junior C# developer and I had a heck of a time trying to find a "how to" article that explains how to connect to a MySQL database using the Connector/NET 5.2.

So here's a simple article explaining how to do it.

Background

To understand the code, you need an understanding of database technology.
I've got MySQL 5 up and running and I installed the Connector/NET 5.2

I created a database name="cart" with a table="members" with fields= "fname" & "lname"

In Visual Studio 2005, I created a consoleApp.
I then added the Mysql.Data provider to the Reference folder.

Understanding the Provider Object

MySql.Data
|
|--MySql.Data.MySqlClient
| |--MySqlCommand
| |--MySqlConnection
| |--MySqlDataAdapter
| |--MySqlDataReader
| |--MySqlException
| |--MySqlParameter
| |--MySqlDbType
| |--MySqlError
| |--MySqlHelper
| |--MySqlScript
|--MySql.Data.Types

You can see the total content of the Provider by using the Object Browser in Visual Studio 2005.

Using the Code

The code was tested using C# and Visual Studio 2005.

using System;
using System.Collections.Generic;
using System.Text;
using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConsoleApp
{
    class Program
       {
              static void Main(string[] args)
              {
                    //Set up connection string
                    string connString = @"
                        server = localhost;
                        database = cart;
                        user id = root;
                password =;
                ";
                    //Set up query string
                    string sql = @" select * from members ";

            MySqlConnection conn = null;
            MySqlDataReader reader = null;

            try
            {
                //open connection
                conn = new MySqlConnection(connString);
                conn.Open();

                //Execute the Query
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                reader = cmd.ExecuteReader();

                //Display output header
                Console.WriteLine("This program demonstrates the use of"
                    + "the MYSQL Server Data Provider");

                Console.WriteLine("Querying the database {0} with {1}\n"
                    , conn.Database
                    , cmd.CommandText
                    );

                Console.WriteLine("{0} | {1}"
                    ,"Firstname".PadLeft(10)
                    ,"Lastname".PadLeft(10)
                    );

                //Process the result set
                while (reader.Read())
                {
                    Console.WriteLine("{0} | {1}"
                        , reader["fname"].ToString().PadLeft(10)
                        , reader["lname"].ToString().PadLeft(10)
                        );
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error " + e);
            }
            finally
            {
                reader.Close();
                conn.Close();
            }
            }
        }
}  

To test, just use the Ctrl + F5 combination.

Points of Interest

Being a junior and a novice in writing an article, I had to use a reference.
A good book on the subject C# and SSE is "Beginning C# 2005 Databases".

History

  • 22nd March, 2008: Initial post