Common Data Class for MySql Connection

Asp.net Database connection Class File, Which can be used as a common connection class file to create connection, inserting, updating and deletion of data from MySQL database .
Don’t write db connection code on every page.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Collections;
using System.Configuration;

public class DataClass : IDisposable
{
    private OdbcCommand _objCommand = new OdbcCommand();
    public string strConnectionString = "";
    private OdbcConnection objConnection;
    public DataClass()
    {
        OdbcConnection _objConnection = new OdbcConnection();
        strConnectionString = ConfigurationManager.ConnectionStrings["AUTHENTICATE"].ConnectionString;
        _objConnection.ConnectionString = strConnectionString;
        _objCommand.Connection = _objConnection;
    }

    public DataClass(OdbcConnection objConnection)
    {
        _objCommand.Connection = objConnection;
    }


    public string ConnectionString
    {
        get
        {
            return strConnectionString;
        }
        set
        {
            strConnectionString = value;
        }
    }
    public void Open()
    {
        _objCommand.Connection.Open();
    }
    public void Close()
    {

        _objCommand.Connection.Close();
    }
    public void Dispose()
    {
        _objCommand.Dispose();
    }
    public int Insert(string _commandText)
    {
        int i = -1;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            i = _objCommand.ExecuteNonQuery();

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public int InsertStored(string _commandText)
    {
        int i = -1;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            i = _objCommand.ExecuteNonQuery();
            this.Close();
            this.Dispose();
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public OdbcDataReader ExecuteReader(string _commandText)
    {
        OdbcDataReader _objDtReader = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objDtReader = _objCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _objDtReader;
    }
    public OdbcDataReader ExecuteReaderStored(string _commandText)
    {
        OdbcDataReader _objDtReader = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            _objDtReader = _objCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _objDtReader;
    }
    public DataSet ExecuteAdapterStored(string _commandText)
    {
        DataSet _ds = null;
        OdbcDataAdapter _objDtAdapter = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            _objDtAdapter = new OdbcDataAdapter();
            _objDtAdapter.SelectCommand = (OdbcCommand)_objCommand;
            _ds = new DataSet();
            _objDtAdapter.Fill(_ds);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _ds;
    }
    public int ExecuteScalarStored(string _commandText)
    {
        int i;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            i = Convert.ToInt32(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public string ExecuteScalarString(string _commandText)
    {
        string var;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;

            var = Convert.ToString(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return var;
    }
    public int ExecuteScalarInt(string _commandText)
    {
        int var;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;

            var = Convert.ToInt32(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return var;
    }
    public DataTable ExecuteSelect(string _commandText)
    {
        DataSet _ds = null;
        OdbcDataAdapter _objDtAdapter = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objDtAdapter = new OdbcDataAdapter();
            _objDtAdapter.SelectCommand = (OdbcCommand)_objCommand;
            _ds = new DataSet();
            _objDtAdapter.Fill(_ds);
            return _ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
    }



    public Boolean InsertUpdateData(OdbcCommand cmd)
    {
      
        String strConnString = strConnectionString;

        OdbcConnection con = new OdbcConnection(strConnString);

        cmd.CommandType = CommandType.Text;

        cmd.Connection = con;

        try
        {

            con.Open();

            cmd.ExecuteNonQuery();

            return true;

        }

        catch (Exception ex)
        { 
            return false;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

    }

}
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s