C# and MySQL/Connector 5.2

kribo

Rate me:

2.79/5 (7 votes)

22 Mar 2008CPOL1 min read

46.3K

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

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)

Written By

kribo

Web Developer

Belgium

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

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(); } } }

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.