How To Create Common Store Procedure For All Your Master Table in Sql Server

Here In This Blog I Will Show You How To Create Comma Store Procedure For All Your Master Table in Sql Server

Logically.

Step 1 :- Create Comma Procedure 

CREATE procedure Master_Table_Transaction

@tablename nvarchar(200), 

@trantype nvarchar(50),

@fieldname nvarchar(max),

@insertvalues nvarchar(max),

@filter nvarchar(max),

@orderby nvarchar(300),

@relationtable nvarchar(max),

@strRetVal varchar(8000)='' OUTPUT

as

Declare @strsql nvarchar(max)

begin

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

if @trantype='Select' 

begin

set @strsql='Select '+@fieldname+' from '+@tablename+' with (nolock)  '+@relationtable+'';

if @filter!=''

begin

set @strsql=@strsql+' where '+@filter;

end

if @orderby!=''

begin

set @strsql=@strsql+' order by '+@orderby;

end  

end

else if @trantype='Insert'

begin

set @strsql='Insert Into '+@tablename+ ' ('+@fieldname+')  values ('+@insertvalues+')';

end 

else if @trantype='Update'

begin

set @strsql='UPDATE '+@tablename+' SET ' +@insertvalues+' where '+@filter;

end 

else if @trantype='Delete'

begin

set @strsql='Delete from '+@tablename+' where '+@filter;

end 

else if @trantype='Mutiple'

begin

set @strsql='Insert Into '+@tablename+ ' ('+@fieldname+')  ('+@insertvalues+')';

end 

Exec(@strsql);

COMMIT

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION outProc;

SELECT @strRetVal= ERROR_MESSAGE();

END CATCH

end

Step 2 :- To Call Store Procedure 

 public string MasterTransaction(string tablename, string trantype, string fieldname, string insertvalues, string filter, string orderby, string relationtable)

        {

            alSQLParams.Clear();

            alSQLParams.Add(new SqlParameter("tablename", tablename)); ----(Pass Table Name)

            alSQLParams.Add(new SqlParameter("trantype", trantype)); ----(Pass Action Type)

            alSQLParams.Add(new SqlParameter("fieldname", fieldname)); ----(Pass FieldName)

            alSQLParams.Add(new SqlParameter("insertvalues", insertvalues)); ----(Pass Value)

            alSQLParams.Add(new SqlParameter("filter", filter)); ----(Pass All Where clauses)

            alSQLParams.Add(new SqlParameter("orderby", orderby));  ----(Pass All orderby values)

            alSQLParams.Add(new SqlParameter("relationtable", relationtable)); ----(Pass All relationtable)

            return new SQLDB().ExecuteStoreProcedure("Master_Table_Transaction", alSQLParams);

        }

Step 3 :- Database Connection

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Data.SqlTypes;

using System.Data.OleDb;

using System.Globalization;

namespace WebApplication.Models

{

    public class SQLDB

    {

        public const string CONST_USER_ID = "";

        public string connStr = "Data Source=AMOL-PC\\SQLDIAMAX;Initial Catalog=EasyDHSPay;Integrated Security=True";

        //public string connStr = "Data Source=VMI103306\\SQLEXPRESS01;Initial Catalog=suggestiondb;Persist Security Info=True;User ID=sa;Password=compaq@550";

        private SqlTransaction sqlTrans;

        private SqlConnection sqlConn = null;

        public SQLDB()

        {

            this.connStr = "Data Source=AMOL-PC\\SQLDIAMAX;Initial Catalog=EasyDHSPay;Integrated Security=True";

            //this.connStr = "Data Source=VMI103306\\SQLEXPRESS01;Initial Catalog=suggestiondb;Persist Security Info=True;User ID=sa;Password=compaq@550";

        }

        public string sqlcon()

        {

            return connStr;

        }

        public void BeginTransaction()

        {

            sqlTrans = dbConnection.BeginTransaction();

        }

        public void CommitTransaction()

        {

            sqlTrans.Commit();

        }

        public void RollbackTransaction()

        {

            sqlTrans.Rollback();

        }

        public void CloseConnection()

        {

            dbConnection.Close();

        }

        private SqlConnection dbConnection

        {

            get

            {

                if (sqlConn == null)

                {

                    sqlConn = new SqlConnection();

                    try

                    {

                        if (sqlConn.State == ConnectionState.Open)

                            sqlConn.Close();

                        sqlConn.ConnectionString = connStr;

                        if (sqlConn.State == ConnectionState.Closed)

                        {

                            sqlConn.Open();

                        }

                    }

                    catch (Exception)

                    {

                    }

                }

                return sqlConn;

            }

        }

        public SqlDataReader getSqlDataReader(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand();

                    objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandTimeout = 0;

                    objCommand.CommandText = strQuery;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    SqlDataReader sqlDR = objCommand.ExecuteReader();

                    objCommand.Parameters.Clear();

                    return sqlDR;

                }

            }

            catch

            {

                return null;

            }

        }

        public DataTable getDataTable(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand();

                    objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandTimeout = 0;

                    objCommand.CommandText = strQuery;

                    objCommand.Parameters.Clear();

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    SqlDataAdapter daSQL = new SqlDataAdapter(objCommand);

                    DataTable dt = new DataTable();

                    daSQL.Fill(dt);

                    objCommand.Parameters.Clear();

                    return dt;

                }

            }

            catch (Exception)

            {

                return null;

            }

        }

        public DataTable getDataTableQuery(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand();

                    objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandText = strQuery;

                    objCommand.CommandType = CommandType.StoredProcedure;

                    objCommand.CommandTimeout = 0;

                    objCommand.Parameters.Clear();

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    SqlDataAdapter daSQL = new SqlDataAdapter(objCommand);

                    DataTable dt = new DataTable();

                    daSQL.Fill(dt);

                    objCommand.Parameters.Clear();

                    return dt;

                }

            }

            catch (Exception ex)

            {

                string meassge = ex.ToString();

                return null;

            }

        }

        public string runExecuteQuery(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandText = strQuery;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    objCommand.ExecuteNonQuery();

                    objCommand.Parameters.Clear();

                    return "done";

                }

            }

            catch (Exception ex)

            {

                return ex.Message;

            }

        }

   

        public string ExecuteStoreProcedure(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandText = strQuery;

                    objCommand.CommandType = CommandType.StoredProcedure;

                    objCommand.CommandTimeout = 0;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    objCommand.ExecuteNonQuery();

                    objCommand.Parameters.Clear();

                    return "done";

                }

            }

            catch (Exception ex)

            {

                return ex.Message;

            }

            finally

            {

            }

        }

    }

}