Asp.net C# - Database acces

Database Layer - This demo will help you to access database secure and save your time in coding and testing.

Step 1 : Add one "CLASS PROJECT" in your current project 

             

Step 2: Create One Class which allow you to add fields which you use in database TableFieldsWithValue.cs. 

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

 public class TableFieldsWithValue
    {
        public List<string> Fields = new List<string>();
        public List<object> Values = new List<object>();

        public void addFieldWithValue(string fieldName, object fieldValue)
        {
            Fields.Add(fieldName);
            Values.Add(fieldValue);
        }
    }

Step 3 : Create one class in that  project  name is QueryGenerator.cs and paste this code 

Note :if you have MSSQL so just add the mssql object.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

class QueryGenerator
    {
        MySqlCommand cmd;
        public MySqlCommand generateInsertQuery(string tabelname,TableFieldsWithValue fv)
        {
            StringBuilder insertQuery = new StringBuilder();
            int noofField = fv.Fields.Count - 1;
            //----------------------------------------------Field Generation
            for (int i = 0; i <= noofField; i++)
            {
                if (i == 0)
                {
                    insertQuery.Append("Insert Into " + tabelname + "(" + fv.Fields[i].ToString());
                }
                else if (i > 0 && i < noofField)
                {
                    insertQuery.Append("," + fv.Fields[i].ToString());
                }
                else if (i == noofField)
                {
                    insertQuery.Append("," + fv.Fields[i].ToString() );
                }
            }
            insertQuery.Append(") ");
            //------------------------------------------------Values Filed Setting
            for (int i = 0; i <= noofField; i++)
            {
                if (i == 0)
                {
                    insertQuery.Append(" Values(@" + fv.Fields[i].ToString());
                }
                else if (i > 0 && i < noofField)
                {
                    insertQuery.Append(",@" + fv.Fields[i].ToString());
                }
                else if (i == noofField)
                {
                    insertQuery.Append(",@" + fv.Fields[i].ToString() );
                }
            }
            insertQuery.Append(") ");
            //---------------------------------------------------

            cmd = new MySqlCommand();
            cmd.CommandText = insertQuery.ToString();
            for (int i = 0; i <= noofField; i++)
            {
                if (fv.Values[i] == "")
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i],DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i], fv.Values[i].ToString());
                }
            }
            return cmd;
        }

        public MySqlCommand generateStoreProcedure(TableFieldsWithValue fv)
        {
            int noofField = fv.Fields.Count - 1;
            cmd = new MySqlCommand();
            for (int i = 0; i <= noofField; i++)
            {
                if (fv.Values[i] == "")
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i], DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i], fv.Values[i].ToString());
                }
            }
            return cmd;
        }
       
        public MySqlCommand generateUpdateQuery(string tabelname, TableFieldsWithValue fv,string updateFieldName,object updateFieldValue)
        {
            StringBuilder insertQuery = new StringBuilder();
            int noofField = fv.Fields.Count - 1;
            //----------------------------------------------Field Generation
            for (int i = 0; i <= noofField; i++)
            {
                if (i == 0)
                {
                    insertQuery.Append("Update " + tabelname + " Set " + fv.Fields[i].ToString() + "=@" + fv.Fields[i].ToString());
                }
                else
                {
                    insertQuery.Append("," + fv.Fields[i].ToString() + "=@" + fv.Fields[i].ToString());
                }
            }
            insertQuery.Append(" Where " + updateFieldName + "=@" + updateFieldName);
            //---------------------------------------------------

            cmd = new MySqlCommand();
            cmd.CommandText = insertQuery.ToString();
            for (int i = 0; i <= noofField; i++)
            {
                if (fv.Values[i] == "")
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i], DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@" + fv.Fields[i], fv.Values[i].ToString());
                }
            }
            cmd.Parameters.AddWithValue("@" + updateFieldName,updateFieldValue);
            return cmd;
        }

     
        public MySqlCommand generateDeleteQuery(string tabelname, string updateFieldName, object updateFieldValue)
        {
            StringBuilder insertQuery = new StringBuilder();
          
            //----------------------------------------------Field Generation
                      
            insertQuery.Append("Delete From " + tabelname + " Where " + updateFieldName + "=@" + updateFieldName);
            //---------------------------------------------------
            cmd = new MySqlCommand();
            cmd.CommandText = insertQuery.ToString();
            cmd.Parameters.AddWithValue("@" + updateFieldName, updateFieldValue);
            return cmd;
        }

        public MySqlCommand generateSelectQueryData(TableFieldsWithValue fv)
        {
            //---------------------------------------------------
            cmd = new MySqlCommand();
            if (fv.Fields.Count > 0)
            {
                int noofField = fv.Fields.Count - 1;
                for (int i = 0; i <= noofField; i++)
                {
                    if (fv.Values[i] == "")
                    {
                        cmd.Parameters.AddWithValue("@" + fv.Fields[i], DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@" + fv.Fields[i], fv.Values[i].ToString());
                    }
                }
            }
            return cmd;
        }
       
    }

 Step 4: Add one transaction class which will handle your request from Client side Transaction.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataBaseLayer;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

public class Transaction
    {

        string constr;
        MySqlConnection con;
        MySqlCommand cmd;
        MySqlDataAdapter da;
        MySqlTransaction tran;
        QueryGenerator queg = new QueryGenerator();


        public Transaction()
        {
            if (constr == null)
            {
                constr = ConfigurationManager.ConnectionStrings["webdb"].ToString();
            }
            if (con == null)
            {
                con = new MySqlConnection(constr);
            }
        }
        // This is Function is use to execute the all insert query
        public Boolean Insert(string tablename, TableFieldsWithValue fv)
        {
            try
            {
                cmd = new MySqlCommand();
                cmd = queg.generateInsertQuery(tablename, fv);
                cmd.Connection = con;
                con.Open();
                tran = con.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
            finally
            {
                con.Close();
            }
        }
        public Boolean Update(string tablename, TableFieldsWithValue fv, string updateFieldName, object updateFieldValue)
        {
            try
            {
                cmd = new MySqlCommand();
                cmd = queg.generateUpdateQuery(tablename, fv, updateFieldName, updateFieldValue);
                cmd.Connection = con;
                con.Open();
                tran = con.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
        
          
            finally
            {
                con.Close();
            }
        }

        public Boolean StaticCommand(string query)
        {
            try
            {
                cmd = new MySqlCommand();
                cmd.CommandText = query.ToString();
                cmd.Connection = con;
                con.Open();
                tran = con.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
           
            finally
            {
                con.Close();
            }
        }
        public Boolean Delete(string tablename, string updateFieldName, object updateFieldValue)
        {
            try
            {
                cmd = new MySqlCommand();
                cmd = queg.generateDeleteQuery(tablename, updateFieldName, updateFieldValue);
                cmd.Connection = con;
                con.Open();
                tran = con.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
                return true;
            }
         
            finally
            {
                con.Close();
            }
        }
        public DataSet Select(string SelectQuery, TableFieldsWithValue fv)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd = new MySqlCommand();
                //Geberate The Select Query Parameter Values
                cmd = queg.generateSelectQueryData(fv);
                // End Here
                cmd.CommandText = SelectQuery.ToString();
                cmd.Connection = con;
                con.Open();
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        public DataSet StoreProcedure(string procedureName,TableFieldsWithValue fv)
        {
            DataSet ds= new DataSet();
            DataTable dt = new DataTable();
            try
            {
                MySqlDataReader rdr = null;
                cmd = queg.generateStoreProcedure(fv);
                cmd.CommandText = procedureName;
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                rdr = cmd.ExecuteReader();
                dt.Load(rdr);
                ds.Tables.Add(dt);
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        public Boolean StoreProcedureExecute(string procedureName, TableFieldsWithValue fv)
        {
          
            try
            {
                cmd = queg.generateStoreProcedure(fv);
                cmd.CommandText = procedureName;
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                int  result =cmd.ExecuteNonQuery();
                if (result == 1)
                    return true;
                else
                    return false;
            }
            finally
            {
                con.Close();
            }
        
        }

        public Boolean multipleTransaction(List<multipleQuery> queryValues)
        {
            try
            {
                cmd = new MySqlCommand();
                for (int i = 0; i < queryValues.Count; i++)
                {
                    if (queryValues[i].queryType == "Insert")
                    {
                        cmd = queg.generateInsertQuery(queryValues[i].tablename, queryValues[i].fields);
                    }
                    else if (queryValues[i].queryType == "Update")
                    {
                        cmd = queg.generateUpdateQuery(queryValues[i].tablename, queryValues[i].fields, queryValues[i].updateFieldName, queryValues[i].updateFieldValue);
                    }
                    else if (queryValues[i].queryType == "Delete")
                    {
                        cmd = queg.generateDeleteQuery(queryValues[i].tablename, queryValues[i].updateFieldName, queryValues[i].updateFieldValue);
                    }
                    else if (queryValues[i].queryType == "Static")
                    {
                        cmd = queg.generateSelectQueryData(queryValues[i].fields);
                        cmd.CommandText = queryValues[i].queryString.ToString();
                    }
                    cmd.Connection = con;
                    if (i == 0)
                    {
                        con.Open();
                        tran = con.BeginTransaction();
                    }
                    cmd.Transaction = tran;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.CommandText = "";

                }
                tran.Commit();
                return true;
            }
          
            finally
            {
                con.Close();
            }

        }

Step 5 : Add one class which will handle you transaction for multiple transaction at once. multipleQuery.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataBaseLayer;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

public class multipleQuery
    {
       public string queryType;
       public TableFieldsWithValue fields =new TableFieldsWithValue();
       public string tablename;
       public string updateFieldName;
       public object updateFieldValue;
       public string queryString;
       public void getdata()
       {
           //
       }
}

Step 6: Add the References of this Project to your  project and use it below some example ho to access this layer...

Note : First You have to import the database layer in your class file and then you create this type of file and user the layer

Create Two instance      

 Transaction transactionDataObj = new Transaction();
 TableFieldsWithValue fv;

public Boolean addUser()
        {
          
            fv = new TableFieldsWithValue();
            fv.addFieldWithValue("UserID", _UserID);

            fv.addFieldWithValue("FirstName", _FirstName);
            fv.addFieldWithValue("LastName", _LastName);
            return   transactionDataObj.Insert("userRegister",fv);
           
        }

 public Boolean updateUser()
        {
            fv = new TableFieldsWithValue();
            fv.addFieldWithValue("FirstName", _FirstName);
            fv.addFieldWithValue("LastName", _LastName);
         

            string updateFieldName = "UserId";
            object updateFieldValue = _UserID;
            return transactionDataObj.Update("userRegister", fv, updateFieldName, updateFieldValue) == true);
                    }

   public DataSet selectUserDetails()
        {
            DataSet ds = new DataSet();
            fv = new TableFieldsWithValue();
            fv.addFieldWithValue("UserID", _UserID);
            string conditionQuery = "SELECT * From  userRegister Where UserID=@UserID;";
            ds = transactionDataObj.Select(conditionQuery, fv);
            return ds;
        }

public Boolean deleteUser()
        {
           
            string updateFieldName = "UserId";
            object updateFieldValue = _UserID;
            return transactionDataObj.Delete("userRegister", updateFieldName, updateFieldValue) == true);
                    }

   public Boolean mutipleTransactionOnce()
        {
            multipleQuery objMulTemp;
            List<multipleQuery> objmul = new List<multipleQuery>();

            //----------Insert agent login details
            fv = new TableFieldsWithValue();
   
            fv.addFieldWithValue("Password", _Password);
           fv.addFieldWithValue("RegDateTime", _RegDateTime);
            fv.addFieldWithValue("LastLoginDateTime", _LastLoginDateTime);
            fv.addFieldWithValue("IsVerfied", _IsVerfied);
            fv.addFieldWithValue("IsActive", _IsActive);

            objMulTemp = new multipleQuery();
            objMulTemp.queryType = "Insert";
            objMulTemp.tablename = "loginmaster";
            objMulTemp.fields = fv;
            objmul.Add(objMulTemp);


            //-----------------User Personal Details
            fv = new TableFieldsWithValue();
            fv.addFieldWithValue("UserID", _UserID);
            fv.addFieldWithValue("FirstName", _FirstName);
            fv.addFieldWithValue("LastName", _LastName);
           
            objMulTemp = new multipleQuery();
            objMulTemp.queryType = "Insert";
            objMulTemp.tablename = "userRegister";
            objMulTemp.fields = fv;
            objmul.Add(objMulTemp);

            if (transactionDataObj.multipleTransaction(objmul) == true)
            {
                return true;
            }
            else
            {
                return false;
            }
        }