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

2년 전

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

            {


            }

        }


    }

}

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
STEEMKR.COM IS SPONSORED BY
ADVERTISEMENT
Sort Order:  trending

Congratulations @virajtakke09! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You made more than 50 upvotes. Your next target is to reach 100 upvotes.

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 STOP

To support your work, I also upvoted your post!

Vote for @Steemitboard as a witness to get one more award and increased upvotes!