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());
}
}
}
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());
}
}
}
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.
