In this article I am going to explain about stored procedure. What is stored procedure? How we will define and use a stored procedure in our programme?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. They accept data in the form of input parameters that are specified at execution time.
Benefits of Stored procedure
-
Precompiled Execution
-
Reduced Client/Server Traffic
-
Efficient reuse of code and programming abstraction
-
Enhanced Security control
Let see the stored procedure with this programme. I have stored the database inside the zip folder. Just attach the database and change the connection string to run the application
Firstly open the Microsoft SQL Server -> Enterprise Manager and right click on that database which you are going to use like this:
Figure 1.
After clicking here a window will open to write our stored procedure.
Figure 2: Write stored procedure here.
The AStoredProcedureAppliacation.aspx code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>A Stored Procedure</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="0" width="62%" align="center">
<tr><td>
<asp:Label ID="lblName" runat="server" Text="Name" Font-Bold="true" Width="100px"> </asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td></tr>
<tr><td>
<asp:Label ID="Label1" runat="server" Text="Address" Font-Bold="true" Width="100px"> </asp:Label>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
</td></tr>
<tr><td>
<asp:Label ID="Label2" runat="server" Text="City" Font-Bold="true" Width="100px"> </asp:Label>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</td></tr>
<tr><td>
<asp:Label ID="Label3" runat="server" Text="Country" Font-Bold="true" Width="100px"> </asp:Label>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
</td></tr>
<tr><td height="10px"></td></tr>
<tr><td>
<asp:Button ID="btnSubmit" runat="server" OnClick="SubmitRecord" Text="Submit" />
</td></tr>
</table>
</div>
</form>
</body>
</html>
The .cs code is:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
SqlConnection con;
SqlCommand cmd ;
SqlParameter sp1 = new SqlParameter();
SqlParameter sp2 = new SqlParameter();
SqlParameter sp3 = new SqlParameter();
SqlParameter sp4 = new SqlParameter();
protected void SubmitRecord(object sender, EventArgs e)
{
con = new SqlConnection("Data source=MCN101; Initial Catalog=databaseForProgramme; Uid=sa; pwd=");
sp1.ParameterName = "@Name";
sp1.Direction = ParameterDirection.Input;
sp1.Value = txtName.Text;
sp2.ParameterName = "@Address";
sp2.Direction = ParameterDirection.Input;
sp2.Value = txtAddress.Text;
sp3.ParameterName = "@City";
sp3.Direction = ParameterDirection.Input;
sp3.Value = txtCity.Text;
sp4.ParameterName = "@Country";
sp4.Direction = ParameterDirection.Input;
sp4.Value = txtCountry.Text;
cmd = new SqlCommand("InsertRecord", con);
cmd.CommandType = CommandType.StoredProcedure;
//Instead Of doing above code like as sp1.Nmae,sp1.Address,sp1.city,sp1.Country we can do it like as:
//cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value=txtName.Text;
//cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value=txtAddress.Text;
//cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value=txtCity.Text;
//cmd.Parameters.Add("@Country", SqlDbType.NVarChar).Value=txtCountry.Text;
cmd.Parameters.Add(sp1);
cmd.Parameters.Add(sp2);
cmd.Parameters.Add(sp3);
cmd.Parameters.Add(sp4);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
When user run the application the window will look like this:
Figure 3.