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

{

}

}

}

}