Build your own mysql utility

When you`re developing a website, database access is very important, in order to finish your job
quickly, you need to have the right tool to boost your work, in here we will show you how to create a
simple database utility class, allows you to perform a variety of queries.


We use C# as our programming language and MySQL as database. In order to use C#.net and connect to MySQL,
you need to install MySQL Connector first.

We need to import MySQL class, once you have installed MySQL Connector, you can import MySql.Data.MySqlClient
in Visual Studio. We also need to import System.Configuration, this allows us to get connectionStrings in Web.config.

Connection string in Web.config
<connectionStrings>
   <add name="MyConnectionString" connectionString="Server=localhost;port=3306;DataBase=example;
    uid=example;pwd=example;CharSet=utf8;" />
</connectionStrings>


Then we create constructor, in the constructor we read the connection string in web.config.
public MySQLUtility(){
    ConnString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
}

We also overload the constructors to create another option, if you have more the one connection string, 
you can choose other connection config names on calling constructor.
public MySQLUtility(string ConnConfigName){
    ConnString = ConfigurationManager.ConnectionStrings[ConnConfigName].ConnectionString;
}
 
Then we create a enumeration SQLExceType,you can use ExecScalar to return a value (Ex:Select Count(*) From table),
and ExecNonQuery is for executing queries that doesn`t return any rows (Ex:Update、Insert、Delete)
public enum SQLExecType : int{
    ExecScalar, ExecNonQuery
};

In ExecuteSQL requires two parameters, first one is sql query and the second one is SQLExecType,
use SQLExecType.ExecScalar will return value, and SQLExecType.ExecNonQuery is for Update or Insert.
In the beginning we check the database connection, if connection is not yet established then we call
GetConnection().
public string ExecuteSQL(string sql, SQLExecType execType){
    string result = null;

    if (conn == null) { conn = GetConnection(); }

    switch (execType)
    {
        case SQLExecType.ExecScalar: result = execScalar(sql, conn); break;
        case SQLExecType.ExecNonQuery: execNonQuery(sql, conn); break;
    }

    return result;
}
 
We also have function for DataReader
public void GetDataReader(string sql, ref MySqlDataReader myReader)
{
    if (conn == null) { conn = GetConnection(); }

    MySqlCommand oCmd = new MySqlCommand(sql, conn);

    try
    {            
        myReader = oCmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        oCmd.Dispose();
    }
}


How to use
Fetch single value
MySQLUtility mySQL;
string sql = "Select Count(*) From Example";

using(mySQL = new MySQLUtility()){
    int RecordCount = int.Parse(mySQL.ExecuteSQL(sql, MySQLUtility.SQLExecType.ExecScalar));
}

Execute Update query
MySQLUtility mySQL;
string sql = "Update table Set Count = Count + 1";

using(mySQL = new MySQLUtility()){
     mySQL.ExecuteSQL(sql, MySQLUtility.SQLExecType.ExecNonQuery);
}

DataReader for returning data rows
using (MySQL = new MySQLUtility())
{
    MySqlDataReader myDR;
    string sql = "Select * From table";
    MySQL.GetDataReader(sql, ref myDR);

    while (myDR.Read())
    {
        //your code here 
    }
    myDR.Close();
}

Complete MySQLUtility Class Expand
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Web;
using MySql.Data.MySqlClient;
using System.Collections.Generic;

public class MySQLUtilitySample : IDisposable
{
    private MySqlConnection conn;    
    private string ConnString;
    private bool disposed = false;

    public MySQLUtilitySample()
    {
        ConnString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;    
    }


    public MySQLUtilitySample(string ConnConfigName)
    {
        ConnString = ConfigurationManager.ConnectionStrings[ConnConfigName].ConnectionString;
    }

    public enum SQLExecType : int
    {
        ExecScalar, ExecNonQuery
    };

    protected virtual void Dispose(bool disposing)
    {
        if (!disposed)
        {
            if (disposing)
            {
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close(); 
                    conn.Dispose();                 
                }                
            }

            disposed = true;
        }
    }

    public void Dispose()
    {        
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    public MySqlConnection GetConnection()
    {
        MySqlConnection _conn = new MySqlConnection(ConnString);

        try
        {
            _conn.Open();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

        return _conn;
    }

    public string ExecuteSQL(string sql, SQLExecType execType)
    {
        string result = null;

        if (conn == null) { conn = GetConnection(); }

        switch (execType)
        {
            case SQLExecType.ExecScalar: result = execScalar(sql, conn); break;
            case SQLExecType.ExecNonQuery: execNonQuery(sql, conn); break;
        }

        return result;
    }

    protected string execScalar(string sql, MySqlConnection conn)
    {
        string result = null;
        MySqlCommand oCmd = new MySqlCommand();

        try
        {
            oCmd.CommandText = sql;
            oCmd.Connection = conn;
            object Result = oCmd.ExecuteScalar();
            if ((Result != DBNull.Value) && Result != null) result = Result.ToString();
        }
        catch (Exception ee)
        {
            throw new Exception(ee.Message);
        }
        finally
        {
            oCmd.Dispose();
        }

        return result;
    }


    protected string execNonQuery(string sql, MySqlConnection conn)
    {
        string result = null;
        MySqlCommand oCmd = new MySqlCommand();

        try
        {
            oCmd.Connection = conn;
            oCmd.CommandText = sql;
            oCmd.ExecuteNonQuery();
        }
        catch (Exception ee)
        {
            throw new Exception(ee.Message);
        }
        finally
        {
            oCmd.Dispose();
        }

        return result;
    }


    public void GetDataReader(string sql, ref MySqlDataReader myReader)
    {
        if (conn == null) { conn = GetConnection(); }

        MySqlCommand oCmd = new MySqlCommand(sql, conn);

        try
        {            
            myReader = oCmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            oCmd.Dispose();
        }
    }
}
comments powered by Disqus