While I was working on some projects I needed an easy abstract access to the data that was in the MySQL database. So I wrote this class, to be used where I felt like to.

Of course it needs a mysql .NET connector (driver).

You can use the code in your program like this:

test1.cs

using System;
//using System.Collections.Generic;
using System.Collections;
//using System.Linq;
using System.Text;

namespace Project1
{
    class test1
    {
        static void Main(string[] args)
        {
            // change this of course
            MySQLConnection sqlserver = new MySQLConnection("server=DBADDRESS; user id=DBUSER; database=TESTDATABASE; password=DBPASS; ", true);

            // and this...
            string table = "`TESTDATABASE`.`_testtable`";

            sqlserver.SqlExecute("CREATE TABLE IF NOT EXISTS " + 
                table + " (  " +
                "`id` INT NOT NULL AUTO_INCREMENT, " +
                "`stuff` VARCHAR( 256 ) NOT NULL, " +
                "PRIMARY KEY ( `id` ) " +
                ") ENGINE = MYISAM", null);

            sqlserver.SqlExecute("TRUNCATE " + table, null);

            sqlserver.SqlExecute("INSERT INTO  " + table + 
                " (`id` ,`stuff`)" +
                "VALUES (NULL , 'aa'), (NULL , 'bb') ", null);

            Hashtable sql_param = new Hashtable();
            sql_param.Add("stuff1", "cc");
            sql_param.Add("stuff2", "dd");

            sqlserver.SqlExecute("INSERT INTO " + table + 
                " (`id` ,`stuff`)" +
                "VALUES (NULL , @stuff1), (NULL , @stuff2) ", 
                sql_param); // No quotes!

            ArrayList somedata = new ArrayList();
            somedata = sqlserver.SqlFetchList("SELECT stuff FROM " + 
                table, null);

            foreach (string d in somedata)
            {
                Console.WriteLine("'" + d.ToString() + "'");
            }
    
        }
    }
}

mysqlconnection.cs

the actual class

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


public class MySQLConnection
{
    public MySqlClient.MySqlConnection connection = null;
    public MySqlClient.MySqlTransaction transaction = null;
    public bool with_transaction = false;
    //public System.Data.DataSet dataset = null;

    public MySQLConnection(string DSNstring, bool with_transaction)
    {
        this.connection = new MySqlClient.MySqlConnection(DSNstring);
        this.connection.Open();
        if (with_transaction)
        {
            this.BeginTransaction();
        }
    }

    ~MySQLConnection() // destructor
    {
        // disconnect and cleanup code etc..
        if (this.with_transaction)
        {
            this.EndTransaction();
        }
        // BUG? sometimes I get an exception here, dunno why :(
        if (this.connection != null)
        {
            this.connection.Close();
        }
    }

    public void BeginTransaction()
    {
        this.transaction = this.connection.BeginTransaction();
        this.with_transaction = true;
    }

    public void EndTransaction()
    {
        if ((this.transaction != null) && (this.transaction.Connection != null))
        {
            try
            {
                Console.WriteLine("COMMIT");
                //Console.WriteLine("Commiting transaction to the DB...");
                this.transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine("COMMIT EXCEPTION:" + ex.Message.ToString());
                //this.transaction.Rollback(); // I dont know if its reachable here...
                // I guess not
            }
        }

        this.transaction = null;
    }

    /**
     * A SAFE and PREFERED way of querying MySQL, using parameterized query
     * 
     * sample usage: create Hashtable sql_params 
     * like sql_params.Add("nameX", value); as much as you need
     * and then SqlFetchStringSafe("SELECT xxx from table where name_item = @nameX", sql_params);
     * 
     */
    public string SqlFetchString(string query, Hashtable query_params)
    {
        if (query == "")
        {
            return "";
        }

        MySqlClient.MySqlCommand MyCommand = new MySqlClient.MySqlCommand(query, this.connection);

        if (query_params != null)
        {
            foreach (string key in query_params.Keys)
            {
                MyCommand.Parameters.AddWithValue(key.ToString(), query_params[key].ToString());
            }
        }

        string res = "";

        // execute
        try
        {
            MySqlClient.MySqlDataReader MyDataReader = MyCommand.ExecuteReader();
            while (MyDataReader.Read())
            {
                res = MyDataReader.GetString(0);
            }
            MyDataReader.Close();

        }
        catch (Exception ex)
        {
            //Console.WriteLine(ex.ToString());
            throw new Exception("\nProblem with SqlFetchString().\n" + ex.Message.ToString());
        }

        return res.ToString();
    }


    /**
     * UNSAFE example, if you want to just query with straigth text query...
     */
    public string SqlFetchStringRAW(string query)
    {
        if (query == "")
        {
            return "";
        }

        MySqlClient.MySqlCommand MyCommand = new MySqlClient.MySqlCommand(query, this.connection);
        string res = "";

        try
        {
            MySqlClient.MySqlDataReader MyDataReader = MyCommand.ExecuteReader();
            while (MyDataReader.Read())
            {
                res = MyDataReader.GetString(0);
            }
            MyDataReader.Close();
        }
        catch
        {
            Console.Write("C");
        }

        return res.ToString();
    }



    /**
     * Execute a non query 
     */
    public bool SqlExecute(string query, Hashtable query_params)
    {
        if (query == "")
        {
            return false;
        }

        MySqlClient.MySqlCommand MyCommand = new MySqlClient.MySqlCommand(query, this.connection);

        if (query_params != null)
        {
            foreach (string key in query_params.Keys)
            {
                MyCommand.Parameters.AddWithValue(key.ToString(), query_params[key].ToString());
            }
        }

        // execute non query
        try
        {
            MyCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            if (this.with_transaction)
            {
                this.transaction.Rollback();
                this.transaction = null;
            }

            Console.WriteLine("R");
            throw new Exception("\nProblem with SqlExecute(), rolling back transaction.\n" + ex.Message.ToString());
            //return false; // unreachable because of the exception
        }
    }

    /**
     * different type of fetch
     */
    public ArrayList SqlFetchList(string query, Hashtable query_params)
    {
        if (query == "")
        {
            return null;
        }

        MySqlClient.MySqlCommand MyCommand = new MySqlClient.MySqlCommand(query, this.connection);
        ArrayList dynList = new ArrayList();

        if (query_params != null)
        {
            foreach (string key in query_params.Keys)
            {
                MyCommand.Parameters.AddWithValue(key.ToString(), query_params[key].ToString());
            }
        }


        try
        {
            MySqlClient.MySqlDataReader MyDataReader = MyCommand.ExecuteReader();
            while (MyDataReader.Read())
            {
                dynList.Add(MyDataReader.GetString(0));
            }
            MyDataReader.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        return dynList;
    }

    /**
     * This function is more for fetching IDs not integers as it might look from the name
     * TODO: Needs revising.
     * 
     * returns -1 if somethings is wrong
     */
    public int SqlFetchInt(string query, Hashtable query_params)
    {
        if (query == "")
        {
            return -1;
        }

        MySqlClient.MySqlCommand MyCommand = new MySqlClient.MySqlCommand(query, this.connection);
        int res = -1;

        if (query_params != null)
        {
            foreach (string key in query_params.Keys)
            {
                MyCommand.Parameters.AddWithValue(key.ToString(), query_params[key].ToString());
            }
        }

        try
        {
            //res = (int)MyCommand.ExecuteScalar(); // does not work in .NET 2.0
            //res = (int)((decimal)MyCommand.ExecuteScalar()); // aaaarrrgh
            object anObj = MyCommand.ExecuteScalar();
            res = (anObj == null ? -1 : Convert.ToInt32(anObj.ToString()));
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        return res;
    }


    /**
     * returns connection info from the server
     */
    public string PrintConnectionInfo()
    {
        string ret;
        ret = "MySQL connection information:\n";
        //ret += "\tConnection String: \t" + this.connection.ConnectionString +"\n";
        ret += "\tDataSource Address: \t" + this.connection.DataSource + "\n";
        ret += "\tDatabase name: \t\t" + this.connection.Database + "\n";
        ret += "\tServer Version: \t" + this.connection.ServerVersion.ToString() + "\n";
        ret += "\tConnection Timeout: \t" + this.connection.ConnectionTimeout + "\n";

        return ret;
    }

}

If you like it - use it. And if you can suggest better aproaches, please comment! :)

_Disclaimer:

_{I'm not responsible for any issues this code produces (e.g. blows you computer or eats your cat).}

_{Batteries not included.}