OOP DB Access Wrapper

[digioz]

Hello All,

I need some expert opinion on a set of Database Access Wrapper Classes which I am trying to funnel down to a single class.

Here is what I have:

clsData: The entry point
clsMSSQL: Microsoft SQL DB Access Wrapper
clsMySQL: MySQL DB Access Wrapper

The idea here was that the programmer can initiate a single object and depending on the database type he selects, that single object will instantiate other objects to get the DB query done. It would also make it easy to add other types of database support later on after the application creation.

My Question is regarding the clsData (entry point) Class. Is this the best way to code it?

clsData Class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace digioz
{

/// <summary>
/// Database Interface 
/// </summary>
public interface IData
{
    void openConnection();
    void closeConnection();
    //void QueryDBReader(string sql);
    void QueryDBDataset(string sql);
    DataTable getDBDataset(string sql, string _host, string _name, string _user, string _pass);
    void UpdateDBDataset();
    void DeleteDBDataset(int primarykey);
    void ExecDB(string sql);
    object ExecDBScalar(string sql);
}

/// <summary>
/// Base Class for Data retrieval from different database types
/// </summary>
public class clsData
{
    #region "Variable Declaration"
        private string m_host;
        private string m_name;
        private string m_user;
        private string m_pass;
        private string m_type;

        // Database Variables
        private clsMSSQL coMSSQL;
        private clsMySql coMySQL;
        public DataSet ds;
    #endregion

    public clsData()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public void openConnection()
    {
        if (m_type == "MSSQL")
        {
            coMSSQL = new clsMSSQL();          
            coMSSQL.dbhost = m_host;
            coMSSQL.dbname = m_name;
            coMSSQL.dbuser = m_user;
            coMSSQL.dbpass = m_pass;
            coMSSQL.openConnection();
        }
        else if (m_type == "MYSQL")
        {
            coMySQL = new clsMySql();
            coMySQL.dbhost = m_host;
            coMySQL.dbname = m_name;
            coMySQL.dbuser = m_user;
            coMySQL.dbpass = m_pass;
            coMySQL.openConnection();
        }
    }

    public void closeConnection()
    {
        if (m_type == "MSSQL")
        {
            coMSSQL.closeConnection();
        }
        else if (m_type == "MYSQL")
        {
            coMySQL.closeConnection();
        }
    }

    public void QueryDBDataset(string sql)
    {
        if (m_type == "MSSQL")
        {
            coMSSQL.QueryDBDataset(sql);
            ds = coMSSQL.ds;
        }
        else if (m_type == "MYSQL")
        {
            coMySQL.QueryDBDataset(sql);
            ds = coMySQL.ds;
        }
    }

    public object ExecDBScalar(string sql)
    {
        object loReturn = null;

        if (m_type == "MSSQL")
        {
            loReturn = coMSSQL.ExecDBScalar(sql);
        }
        else if (m_type == "MYSQL")
        {
            loReturn = coMySQL.ExecDBScalar(sql);
        }
        return loReturn;
    }

    public void ExecDB(string sql)
    {
        if (m_type == "MSSQL")
        {
            coMSSQL.ExecDB(sql);
        }
        else if (m_type == "MYSQL")
        {
            coMSSQL.ExecDB(sql);
        }
    }

    #region "Public Properties"
        public string dbhost
        {
            get { return m_host; }
            set { m_host = value; }
        }

        public string dbname
        {
            get { return m_name; }
            set { m_name = value; }
        }

        public string dbuser
        {
            get { return m_user; }
            set { m_user = value; }
        }

        public string dbpass
        {
            get { return m_pass; }
            set { m_pass = value; }
        }

        public string dbtype
        {
            get { return m_type; }
            set { m_type = value; }
        }
    #endregion
}
}

clsMSSQL Class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace digioz
{

/// <summary>
/// MSSQL Database Communication Class
/// </summary>
public class clsMSSQL : IData
{
    #region "Variable Declaration"
        private string m_host;
        private string m_name;
        private string m_user;
        private string m_pass;

        public DataSet ds;
        public System.Data.SqlClient.SqlDataAdapter da;
        public DataTable dt = new DataTable();
        public System.Data.SqlClient.SqlCommandBuilder cd;
        public SqlCommandBuilder cb;
        public SqlConnection cn;
        public System.Data.SqlClient.SqlDataReader reader;
        public string cnString;
        public string dllversion = "1.0.0";
        public string Err;
        public int ErrNumber;
    #endregion
    
    public clsMSSQL()
    {
        //
        // TODO: Add constructor logic here
        // 
    }

    public void openConnection()
    {
        cnString = "Server=" + m_host + ";Database=" + m_name + ";User ID=" + m_user + ";Password=" + m_pass + ";Trusted_Connection=False;";
        cn = new SqlConnection(cnString);
        try
        {
            cn.Open();
            Err = "";
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
    }

    public void closeConnection()
    {
        cn.Close();
    }

    //public void QyeryDBReader(string sql)
    //{
    //    reader = null;
    //    SqlCommand cmd = new SqlCommand(sql, conn);
    //    try
    //    {
    //        reader = cmd.ExecuteReader();
    //    }
    //    catch (SqlException ex)
    //    {
    //        err += "Error: " + ex.Message.ToString();
    //        errNumber = ex.Number;
    //    }
    //    finally
    //    {
    //        if (!(reader == null))
    //        {
    //            reader.Close();
    //        }
    //    }
    //    return reader;
    //}

    public void QueryDBDataset(string sql)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(sql, cn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.SelectCommand = cmd;
            cb = new SqlCommandBuilder(da);
            da.Fill(ds);
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
    }

    public DataTable getDBDataset(string sql, string _host, string _name, string _user, string _pass)
    {
        m_host = _host;
        m_name = _name;
        m_user = _user;
        m_pass = _pass;

        openConnection();

        try
        {
            SqlCommand cmd = new SqlCommand(sql, cn);
            da.SelectCommand = cmd;
            cb = new SqlCommandBuilder(da);
            da.Fill(ds);
        }
        catch (SqlException ex)
        {
            Err += "Error: " + ex.Message.ToString();
            ErrNumber = ex.Number;
        }
        closeConnection();

        return ds.Tables[0];
    }

    public System.Data.SqlClient.SqlDataReader QueryDBReader(string sql)
    {
        reader = null;

        SqlCommand cmd = new SqlCommand(sql, cn);
        try
        {
            reader = cmd.ExecuteReader();
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
        finally
        {
            if ((reader != null))
                reader.Close();
        }

        return reader;
    }

    public object ExecDBScalar(string sql)
    {
        SqlCommand cmd = new SqlCommand(sql, cn);
        object oReturn;

        try
        {
            oReturn = cmd.ExecuteScalar().ToString();
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
            ErrNumber = ex.Number;
            oReturn = null;
        }
        return oReturn;
    }

    public void ExecDB(string sql)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
    }

    public void UpdateDBDataset()
    {
        try
        {
            dt = ds.Tables[0];
            DataTable changes = dt.GetChanges();

            da.Update(changes);
            dt.AcceptChanges();
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
    }

    public void DeleteDBDataset(int primarykey)
    {
        try
        {
            ds.Tables[0].Rows[primarykey].Delete();
        }
        catch (SqlException ex)
        {
            Err += "Error: " + ex.Message.ToString();
            ErrNumber = ex.Number;
        }
    }

    public void CreateDatabase(string DatabaseName)
    {
        try
        {
            this.ExecDB("CREATE DATABASE " + DatabaseName + ";");
        }
        catch (SqlException ex)
        {
            Err = ex.Message.ToString();
        }
    }

    public string GetDLLVersion()
    {
        return "DigiOz MSSQL DLL Version " + dllversion;
    }

    #region "Public Properties"

    public string dbhost
    {
        get { return m_host; }
        set { m_host = value; }
    }

    public string dbname
    {
        get { return m_name; }
        set { m_name = value; }
    }

    public string dbuser
    {
        get { return m_user; }
        set { m_user = value; }
    }

    public string dbpass
    {
        get { return m_pass; }
        set { m_pass = value; }
    }

    #endregion

} 

}

clsMySQL Class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

namespace digioz
{

/// <summary>
/// MySQL Database Communication Class
/// </summary>
public class clsMySql : IData 
{
    #region "Variable Declaration"
            private string pr_dbhost;
            private string pr_dbuser;
            private string pr_dbpass;
            private string pr_dbname;
            private string connStr;
            public MySqlConnection conn;
            public MySqlDataReader reader;
            public DataSet ds = new DataSet();
            public MySqlDataAdapter da = new MySqlDataAdapter();
            public MySqlCommandBuilder cb;
            public DataTable dt = new DataTable();
            public string Err;
            public int ErrNumber;
            public string dllversion = "1.0.0"; 
    #endregion

	public clsMySql()
	{
		//
		// TODO: Add constructor logic here
		//
	}
        public void openConnection()
        {
            connStr = string.Format("server={0};user id={1}; password={2}; database={3}; pooling=false; Allow Zero DateTime=False;", dbhost, dbuser, dbpass, dbname);
            if (!(conn == null))
            {
                conn.Close();
            }
            try
            {
                conn = new MySqlConnection(connStr);
                conn.Open();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

        public void closeConnection()
        {
            try
            {
                conn.Close();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

    //public void QueryDBReader(string sql)
    //{
    //    reader = null;
    //    MySqlCommand cmd = new MySqlCommand(sql, conn);
    //    try
    //    {
    //        reader = cmd.ExecuteReader();
    //    }
    //    catch (MySqlException ex)
    //    {
    //        Err += "Error: " + ex.Message.ToString();
    //        errNumber = ex.Number;
    //    }
    //    finally
    //    {
    //        if (!(reader == null))
    //        {
    //            reader.Close();
    //        }
    //    }
    //    return reader;
    //}

        public void QueryDBDataset(string sql)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                da.SelectCommand = cmd;
                cb = new MySqlCommandBuilder(da);
                da.Fill(ds);
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

        public DataTable getDBDataset(string sql, string _host, string _name, string _user, string _pass)
        {
            pr_dbhost = _host;
            pr_dbname = _name;
            pr_dbuser = _user;
            pr_dbpass = _pass;

            openConnection();

            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                da.SelectCommand = cmd;
                cb = new MySqlCommandBuilder(da);
                da.Fill(ds);
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
            closeConnection();

            return ds.Tables[0];
        }

        public void UpdateDBDataset()
        {
            try
            {
                dt = ds.Tables[0];
                DataTable changes = dt.GetChanges();
                da.Update(changes);
                dt.AcceptChanges();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

        public void DeleteDBDataset(int primarykey)
        {
            try
            {
                ds.Tables[0].Rows[primarykey].Delete();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

        public void ExecDB(string sql)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
            }
        }

        public object ExecDBScalar(string sql)
        {
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            string oReturn = "";
            try
            {
                oReturn = cmd.ExecuteScalar().ToString();
            }
            catch (MySqlException ex)
            {
                Err += "Error: " + ex.Message.ToString();
                ErrNumber = ex.Number;
                oReturn = null;
            }
            return oReturn;
        }

        public string dbhost
        {
            get
            {
                return pr_dbhost;
            }
            set
            {
                pr_dbhost = value;
            }
        }

        public string dbuser
        {
            get
            {
                return pr_dbuser;
            }
            set
            {
                pr_dbuser = value;
            }
        }

        public string dbpass
        {
            get
            {
                return pr_dbpass;
            }
            set
            {
                pr_dbpass = value;
            }
        }

        public string dbname
        {
            get
            {
                return pr_dbname;
            }
            set
            {
                pr_dbname = value;
            }
        }
}

}

Thanks,
Pete

P.S. This will be part of an open source .NET portal I am writing, so you are welcome to use the code if you wish.

[gaylo565]

The code looks pretty good. If the way you did it is working I would go with it. The code is clearly maintainable and upgradable wich pretty much satisfies you architectual needs. There are usaually a few ways to get to your end point but if your way is working efficiently there is no good reason to change. I didn't look over all of your code but from what I went through so far it looks good. The multiple class aproach for different types of db's works.

[digioz]

Yeah, the code has been tested and it is fully functional. The only improvement that I think I could make is to eitherr use an enum with "typeof" or some sort of a pointer (so I don't have to put all these ugly if statements in clsData), unless someone has any other suggestions.


Thanks,
Pete

[gaylo565]

I am an if else person. Usually if there is a way to do something with if else statements thats what I do first. This isn't always best but it works for me because thats the way my brain thinks. Even with an enumerable you will still have to do some sort of type checking for each of your methods so I don't think it would be any more efficient. The only other thing I see is that you use a lot of new regions in the data class that arn't really nescessary I guess it keeps it clean but you only seperated out your property blocks in the first class and not the other ones. Not a big deal but it wouldn't hurt to keep them the same.

[Xav]

I'm a bit obsessed with clean code. Even if code works, I sometimes find myself going back to make it look nicer or with less code, especially with XHTML markup. It depends how important it is for you.

[digioz]

I'm a bit obsessed with clean code. Even if code works, I sometimes find myself going back to make it look nicer or with less code, especially with XHTML markup. It depends how important it is for you.

Right. And there are alot of other people that feel the same way about code. Which is why I am trying to find a cleaner way of coding the clsData class. The question is what is a cleaner way of coding the same thing.

Pete

[Xav]

Do some experimenting, but if the code works efficiently, it's probably OK.