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
@strRetVal varchar(8000)='' OUTPUT
as
Declare 
begin
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
if
begin
set
if
begin
set
end
if
begin
set
end
end
else if
begin
set
end
else if
begin
set
end
else if
begin
set
end
else if
begin
set
end
Exec(
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
{
}
}
}
}