How to Write a Single Stored Procedure for Delete/Retrieve all Table of Records

Introduction

I have worked on a school site. At that time, we wrote the stored procedure methods (getrecords_sample, delete_sample) for every table. Back then, I got an idea to implement a concept like Single Stored Procedure for retrieving/deleting the records from one stored procedure using SQL Server 2008 and ASP.NET and for that, we need to follow the steps given below.

Background

You should know how to create/work on Stored Procedures. For reference, check out this link in MSDN and for benefits of Stored Procedures, check out this link in MSDN.

Using the Code

Suppose there are tables called Sample and book whose structure is given below:

 Create Table Sample
(
Id int IDENTITY(1,1) NOT NULL,
Description nvarchar(max) NOT NULL
)
 Create Table Book
(
bookNo int IDENTITY(1,1) NOT NULL,
bookName nvarchar(250) NOT NULL
)

Support we insert the following data into the above tables:

Sample Table

INSERT INTO Sample(Description) values('Sample test')
INSERT INTO Sample(Description) values('Sample test 1')

Books Table

INSERT INTO book (bookName) values('Asp.Net')
INSERT INTO book (bookName) values('c#')

Here KeyID and TABLENAME are parameters for the DeleteMaster stored procedure whereas KeyID is getting selected record id from front end (web side) and TABLENAME is the tablename of database, i.e., which table to delete the particular record, Message is a temporary variable to return the message result after success/failure whereas before deleting, I'm just checking whether the given record id is there or not if have a recorded (KeyId) then delete the record otherwise simply through a message . Now, suppose we need to create a Stored Procedure which will return a deleted record status is given as the input parameter to the stored procedure. Then the Stored Procedure will be:

Create PROC [dbo].[DeleteMaster] (@KeyID     INT, @TABLENAME VARCHAR(250)) 
AS
BEGIN 
DECLARE @TABLE VARCHAR(250) 
DECLARE @Message VARCHAR(250) 
DECLARE @MessageResult int
SET @TABLE=Rtrim(Ltrim(@TABLENAME)) 
IF @TABLE = 'Sample' 
IF EXISTS(SELECT * FROM   sample WHERE  id = @KeyID) 
BEGIN 
DELETE FROM sample WHERE  id = @KeyID 
SET @Message='the selected record deleted successfully.' 
SELECT @Message AS Result_Message 
RETURN @Message
END 
ELSE 
BEGIN
SET @Message='the ' + @KeyID + ' does not exists' 
SELECT @Message AS Result_Message 
RETURN @Message
END 
ELSE IF @TABLE = 'book' 
IF EXISTS(SELECT * FROM   book WHERE  bookno = @KeyID) 
BEGIN 
DELETE FROM book WHERE  bookno = @KeyID 
SET @Message='the selected record deleted successfully.' 
SELECT @Message AS Result_Message
RETURN @Message
END 
ELSE 
BEGIN 
SET @Message='the ' + @KeyID + ' does not exists' 
SELECT @Message AS Result_Message 
RETURN @Message
END 
ELSE 
BEGIN
SET @Message='the ' + @TABLE + ' does not exists' 
SELECT @Message AS Result_Message 
RETURN @Message
END 
END

Here TABLENAME is the parameter for the Getalltableofrecordsmaster stored procedure whereas TABLENAME is the tablename of database, i.e., which table to get/retrieve the records, Message is a temparary variable to return the message result after failure whereas I am just checking whether the given tablename is there or not if it has table then get/retrieve records otherwise simply through a message. Now, we need to create a Stored Procedure which will return a record given as the input parameter to the stored procedure. Then the Stored Procedure will be:

CREATE PROC [dbo].[Getalltableofrecordsmaster](@tablename NVARCHAR(250)) 
AS
BEGIN 
DECLARE @Message VARCHAR(250) 
DECLARE @MessageResult INT 
DECLARE @TABLE NVARCHAR(250) 
SET @TABLE=Rtrim(Ltrim(@tablename)) 
IF @TABLE = 'Sample' 
BEGIN 
SELECT * FROM   sample 
END 
ELSE IF @TABLE = 'book' 
BEGIN 
SELECT * FROM   book 
END 
ELSE 
BEGIN 
SET @Message='the ' + @TABLE + ' does not exists' 
SELECT @Message AS Result_Message
RETURN @Message 
END 
END

GetAllRecords.aspx page code is like this.

Here, I have taken two gridviews for displaying data. Now, we need to write code for web application. The code will be shown below:

<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<br />
<div>
<asp:GridView ID="GridView2" runat="server">
</asp:GridView>
</div>

GetAllRecords.aspx.cs page code is like this.

Before working with a database, we have to add (here) the SQL Server .NET Data Provider namespace, by placing the following code:

using System.Data;

Similarly for the SqlClient .NET Data Provider namespace:

using System.Data.SqlClient;

Now, we have to declare a connection string in web.Config file.

<connectionStrings>
    <add name="strconn" connectionString="User ID=sa;
    Initial Catalog=Test;Data Source=.;password=pass@word1" 
    providerName="System.Data.SqlClient"/>
  </connectionStrings>

The next step is to create an SqlConnection object. We then pass the connection string to this SqlConnection object. You can code now to create a new ADO.NET Connection object in order to connect to an SQL Server provider database, and then create an SqlCommand object to pass the SqlConnection object and Stored procedure name, to query the data pass the parameterized queries with values. In more recent versions of SQL Server, they are actually as fast as stored procedures. Here Getalltableofrecordsmaster is the stored procedure, Sample/book is the TABLENAME.

string strconn = ConfigurationManager.ConnectionStrings
["strconn"]"ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataSet ds; 
            SqlDataAdapter da;
            try
            {
                ds = new DataSet();
                da = new SqlDataAdapter();
                using (SqlConnection SqlConn = new SqlConnection(strconn))
                {
                    using (SqlCommand SqlComm = 
                    new SqlCommand("Getalltableofrecordsmaster", SqllConn))
                    {
                        SqlComm.CommandType = CommandType.StoredProcedure;
                        SqlComm.Parameters.AddWithValue("@tablename", "Sample");
                        SqlConn.Open();
                        da.SelectCommand = SqlComm;
                        da.Fill(ds, "Sample");
                        GridView1.DataSource = ds;
                        GridView1.DataBind();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
 
            try
            {
                ds = new DataSet();
                da = new SqlDataAdapter();
                using (SqlConnection SqlConn = new SqlConnection(strconn))
                {
                    using (SqlCommand SqlComm = 
                    new SqlCommand("Getalltableofrecordsmaster", SqlConn))
                    {
                        SqlComm.CommandType = CommandType.StoredProcedure;
                        SqlComm.Parameters.AddWithValue("@tablename", "book");
                        SqlConn.Open();
                        da.SelectCommand = SqlComm;
                        da.Fill(ds, "book");
                        GridView2.DataSource = ds;
                        GridView2.DataBind();
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            catch (Exception ey)
            {
            }
        }
    }

DeleteRecords.aspx page code is like this:

Here, I have taken two Labels for displaying message. Now, we need to write a code for web application. The code will be shown below:

 <div>
<asp:Label ID="lblMessage" runat="server" 
Text=""></asp:Label>
 </div>

DeleteRecords.aspx.cs page code is like this:

The next step is to create an SqlConnection object. We then pass the connection string to this SqlConnection object. You can code now to create a new ADO.NET Connection object in order to connect to an SQL Server provider database, and then create an SqlCommand object to pass the SqlConnection object and Stored procedure name, to pass the parameterized queries with values. In more recent versions of SQL Server, they are actually as fast as stored procedures. Here DeleteMaster is the stored procedure, Sample/book is the TABLENAME, 1 <code>is the selected record id.

string strconn = 
ConfigurationManager.ConnectionStrings["strconn"].ConnectionString;
    private string Message_;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            try
            {
 
                using (SqlConnection SqlConn = new SqlConnection(strconn))
                {
                    using (SqlCommand SqlComm = 
                    new SqlCommand("DeleteMaster", SqlConn))
                    {
                        SqlComm.CommandType = CommandType.StoredProcedure;
                        SqlComm.Parameters.AddWithValue("@KeyID", 1);
                        SqlComm.Parameters.AddWithValue
                        ("@TABLENAME", "Sample");
                        SqlConn.Open();
                        Message_ = Convert.ToString
                        (SqlComm.ExecuteScalar()); // this will give you message

                    }
                }
            }
            catch (SqlException ex)
            {
            }
 
            try
            { 
                using (SqlConnection SqlConn = new SqlConnection(strconn))
                {
                    using (SqlCommand SqlComm = 
                    new SqlCommand("DeleteMaster", SqlConn))
                    {
                        SqlComm.CommandType = CommandType.StoredProcedure;
                        SqlComm.Parameters.AddWithValue("@KeyID", 1);
                        SqlComm.Parameters.AddWithValue("@TABLENAME", "book");
                        SqlConn.Open();
                        Message_ = Convert.ToString
                        (SqlComm.ExecuteScalar()); // this will give you message
                    }
                }
            }
            catch (SqlException ex)
            {
            }
            catch (Exception ey)
            {
            }
            lblMessage.Text = Message_;
        }
    }

Points of Interest

• Add comments. Just add some general comments, some thoughts before a class or a function is created.
• Development/Testing. Designing the web forms and writing the code. Fix it, test it and then write new lines of code.

Summary

In the end, we can say that a Stored procedure gives the possibility of reusing the code and execution plan, but it also increases the performance of the database by reducing the traffic of the network by reducing the amount of information sent over the network.

Note

This is my first tip. If you have any suggestions/bugs/comments, etc., please feel free to shoot out comments which may improve my skills.