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
{
}
}
}
}
Congratulations @virajtakke09! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :
You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOPTo support your work, I also upvoted your post!
Vote for @Steemitboard as a witness to get one more award and increased upvotes!