Before moving to the actual topic that how to call a stored procedure , let's first discuss , what is actually a Stored Procedure.
What is an Stored Procedure
A stored procedure is nothing but a group of T-SQL statements , which can conatin 2 lines of Sql statement to 1000s of sql statements. If an application uses the same query multiple times then we create a stored procedure for the same and later they are called by their name in the application
Advantages of Stored procedure
There are several advantages of using Stored Procedures over adhoc queries (inline SQL)
1.If you will write In-Line SQL queries then maintaining them becomes a challenge also you can't reuse them, but Stored Procedure is created once and could be called several times whenever we need to execute some SQL queries, thus it enhances the reusability, at the same time it decreases the maintenance load, now if we need to change something we could just change the procedure so we just need to change at one place.
2.We also get security with a stored procedure, A database user can be granted access to an SP and could be prevented them from executing direct "select" statements against a table.
3. In-line SQL queries are build by concatenating user inputs which open the doors for SQL injection attack, but Stored procedures prevent SQL injection attack.
4.Stored Procedure is faster than In-Line queries because SQL Server retains the Execution plan for SP, Stored Procedures are compiled and their execution plan is cached and used again when the same SP is executed again
5.With the Stored procedure, we only need to pass the store procedure name from application to SQL server, which reduces the network traffic.
Due to all these advantages, it is very possible that you are asked to create SP and call them from your application.
Store procedure are mainly of three types
1. SP without parameters.
2. SP with input parameters.
3. SP with output parameters.
Let's understand each of them one by one. To understand SP , we will need a database table , so let's create one.
Step1.Open SQL Server Management Studio and Run below script, it will create a database with the name Test and Two tables Employee and Department and populate them with some Test Data.
Create database Test
go
use Test
go
CREATE TABLE [dbo].[Department] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
[Salary] INT NULL,
[DepartmentId] INT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
);
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
SET IDENTITY_INSERT [dbo].[Employee] OFF
Step 2.create an Empty webform application.
Step 3 : Include connection string in web.config file
<connectionStrings>
<add name="CS" connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"/>
</connectionStrings>
SP without parameters (Display all Employees with their department in a Gridview.)
Step1. Create the procedure.
create procedure spGetEmployees
as
begin
select emp.Name,emp.Salary,dep.Name from Employee emp
join Department dep
on emp.DepartmentId=dep.Id
end
Step 2. Drag and drop a GridView into the webform.
Step 3.Copy and paste below code into the code behind.
private void BindGrid()
{
using (SqlConnection con = new SqlConnection(cs))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand("spGetEmployees",con);
//Specify that the SqlCommand is a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//Open the connection ,execute the query and Bind Grid
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
}
Please notice to call a store procedure we simply specify the name of SP in the constructor of SqlCommand object and Then Specify the command type then as usual to execute the command we use ExecuteReader() method.
step 4. Call this method in page load event so that Grid could load automatically.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
SP with input parameters (Add employee details into the database.)
Step 1.create the store procedure.
create procedure spAddEmployee
@Name varchar(50),
@Salary int,
@DepId int
as
begin
insert into Employee values (@Name,@Salary,@DepId)
end
step 2. Design a form to take user input. copy paste below html.
<table border="1" style="background: brown; color: White">
<tr>
<td>Name</td>
<td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Salary</td>
<td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Department</td>
<td>
<asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click"/>
</td>
</tr>
</table>
step 3.Bind department so that Admin can select the department from dropdownlist.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
BindDepartment();
}
}
private void BindDepartment()
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(cs))
{
SqlDataAdapter da = new SqlDataAdapter("select * from Department", con);
da.Fill(ds);
}
ListItem li = new ListItem() { Text = "Please Select", Value = "-1" };
ddlDepartment.Items.Insert(0, li);
ddlDepartment.DataSource = ds;
ddlDepartment.DataBind();
}
Step 3.Double click on the button control to generate button click event handler and copy paste below code.
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(cs))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand("spAddEmployee", con);
//Specify that the SqlCommand is a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//Add the input parameters to the command object
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
cmd.Parameters.AddWithValue("@DepId", ddlDepartment.SelectedItem.Value);
//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();
BindGrid();
}
}
Please notice , here we are specifying SP name in the constructor of SqlCommand object and then specifying the command type as store procedure and then providing necessary input parameters using AddWithValue() method .
SP with output parameters (display total count of employee in HR department in a label)
Step 1.Create SP
To create an SP with output parameter, we use the keywords OUT or OUTPUT. @empCount is an OUTPUT parameter. Notice, it is specified with OUT keyword.
create procedure spGetHrEmployeeCount
@empCount int out
as
begin
select @empCount =Count(*) from Employee
where DepartmentId= (Select Id from Department where Name='HR')
end
Step 2. Call sp from code behind and set label's Text as output Parameter's value.
private void GetHrCount()
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetHrEmployeeCount", con);
cmd.CommandType = CommandType.StoredProcedure;
//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@EmployeeId";
outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();
//Retrieve the value of the output parameter and set the label Text
string HrCount = outPutParameter.Value.ToString();
lblMessage.Text = "Total Employee in HR department = " + HrCount;
}
The complete source code looks like below.
code behind (aspx.cs)
public partial class StoreProcedureExample : System.Web.UI.Page
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
BindDepartment();
GetHrCount();
}
}
private void BindGrid()
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetEmployees",con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
}
private void BindDepartment()
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(cs))
{
SqlDataAdapter da = new SqlDataAdapter("select * from Department", con);
da.Fill(ds);
}
ListItem li = new ListItem() { Text = "Please Select", Value = "-1" };
ddlDepartment.Items.Insert(0, li);
ddlDepartment.DataSource = ds;
ddlDepartment.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spAddEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
cmd.Parameters.AddWithValue("@DepId", ddlDepartment.SelectedItem.Value);
con.Open();
cmd.ExecuteNonQuery();
BindGrid();
}
}
private void GetHrCount()
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetHrEmployeeCount", con);
cmd.CommandType = CommandType.StoredProcedure;
//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@empCount";
outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();
//Retrieve the value of the output parameter
string HrCount = outPutParameter.Value.ToString();
lblMessage.Text = "Total Employee in HR department = " + HrCount;
}
}
}
The html (aspx page)
<form id="form1" runat="server">
<div>
<table border="1" style="background: brown; color: White">
<tr>
<td>Name</td>
<td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Salary</td>
<td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Department</td>
<td>
<asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id"></asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click"/>
</td>
</tr>
</table>
<asp:Label Text="text" runat="server" ID="lblMessage" />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Vertical">
<AlternatingRowStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#0000A9" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#000065" />
</asp:GridView>
</div>
</form>
