C# and MySQL/Connector 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

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

22^nd March, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

kribo

Software Developer

Belgium

Member

Follow on Twitter

Developer within C#, Dynamics NAV (Navision), Php environments.

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

20:37 12 Mar '10

9:15 3 May '11

simplified mysql approach [modified]

anoftc

5:54 16 Oct '09

Hey,

I created simple classes (based on this article) to use mysql very easily (similar as in php)

Select:

            mysql my = new mysql("localhost", "root", "", "database");
            foreach (myrecord rec in my.ExecSelect("select * from users"))
            {
                MessageBox.Show(rec["user_id"] + " - " + rec["user_name"]);
            }

Others:

            mysql my = new mysql("localhost", "root", "", "database");
            MessageBox.Show("Create: " + my.ExecOther("create table x (y int)").ToString());
            MessageBox.Show("Insert: " + my.ExecOther("insert into x values (5)").ToString());
            MessageBox.Show("Insert: " + my.ExecOther("insert into x values (6)").ToString());
            MessageBox.Show("Insert: " + my.ExecOther("insert into x values (7)").ToString());
            MessageBox.Show("Update: " + my.ExecOther("update x set y=9 where y>5").ToString());
            MessageBox.Show("Delete: " + my.ExecOther("delete from x").ToString());
            //MessageBox.Show("Drop: " + my.ExecOther("drop table x").ToString());

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Windows.Forms;
 
namespace ConqSQL
{
    class myrecord
    {
        string[] onerow;
        string[] fields;
        int FNum;
 
        public myrecord(string[] newfields, string[] newrow)
        {
            onerow = newrow; fields = newfields;
            FNum = newfields.Length;
        }
 
        public string this[string idx_str]
        {
            get
            { 
                int idx_int=-1;
                for (int i = 0; i < FNum && idx_int == -1; i++)
                {
                    if (fields[i] == idx_str) idx_int = i;
                }
                if (idx_int != -1) return onerow[idx_int];
                return "";
            }
        }
    }
 
    class myresult : IEnumerable
    {
        string[] fields;
        List<myrecord> data;
 
        public myresult(string[] newfields)
        {
            fields = newfields;
            data = new List<myrecord>();
        }
 
        public myresult()
        {
            data = new List<myrecord>();
        }
        public void AddResult(string[] s)
        {
            myrecord newrec = new myrecord(fields, s);
            data.Add(newrec);
        }
 
        public IEnumerator GetEnumerator()
        {
            return data.GetEnumerator();
        }
    }
 
    class mysql
    {
        string FServer;
        string FDB;
        string FUser;
        string FPass;
        string FConnStr;
        MySqlConnection conn;
 
        public mysql(string server, string user, string pass, string db)
        {
            FServer = server; FUser = user; FPass = pass; FDB = db;
            FConnStr = "server = " + FServer + ";\n" +
                       "database = " + FDB + ";\n" +
                       "user id = " + FUser + ";\n" +
                       "password = " + FPass + ";\n";
            conn = new MySqlConnection(FConnStr);
            conn.Open();
        }
 
        public int ExecOther(string SQL)
        {
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            int num;
            MySqlDataReader reader=null;
            try
            {
                reader = cmd.ExecuteReader();
                num = reader.RecordsAffected;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                num = -1;
            }
            if (reader!=null && !reader.IsClosed) reader.Close();
            return num;
        }
 
        public myresult ExecSelect(string SQL)
        {
            MySqlCommand cmd = new MySqlCommand(SQL, conn);
            MySqlDataReader reader=null;
            try
            {
                reader = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                MessageBox.Show(ex.Message);
                return new myresult();
            }
            string[] fields;
            myresult res=null;
            int fnum=0;
            while (reader.Read())
            {
                if (res == null)
                {
                    fnum=reader.FieldCount;
                    fields=new string[fnum];
                    for (int i=0; i<fnum; i++) fields[i]=reader.GetName(i).ToLower();
                    res = new myresult(fields);
                }
                fields = new string[fnum];
                for (int i = 0; i < fnum; i++) 
                {
                    if (reader.IsDBNull(i))
                    {
                        fields[i] = "";
                    }
                    else
                    {
                        fields[i] = reader.GetString(i);
                    }
                }
                res.AddResult(fields);
            }
            reader.Close();
            return res;
        }
 
        ~mysql()
        {
            conn.Close();
        }
    }
}