Stored Procedures in Asp.net

Stored Procedure :

Stored Procedure (SP) in Asp.net c# is used for writing code which will be called from the BAL file for database connectivity.
With the help of SP the program of three tier architecture will give faster answer to us compare to code written in the asp.net BAL.cs file.
Faster Execution of the query.
And Separation is Advantage of using SP in Asp.net C# Web Application.

Syntax of Stored Procedure
ALTER PROCEDURE Procedurename
/*Here the parameters used inside query is used and separated with semicolon.
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
Write your query here.

RETURN

Here Example code is given for Stored Procedure.

To Select Record from the database table tbladmin(id pk,username,password,mobileno,emailid)

ALTER PROCEDURE Select1

AS
select * from tbladmin
RETURN

To Delete record according to id number,

ALTER PROCEDURE deleteadmin
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@aid int
AS
delete from tbladmin where id=@aid
RETURN

To Update record according to id number,

ALTER PROCEDURE updateadmin
@aid int,
@user varchar(50),
@mobile decimal(10,0),
@email varchar(50)

AS
update tbladmin set username=@user,mobileno=@mobile,emailid=@email where id=@aid
RETURN

Here is the code of Asp.net Project file

Aspx file
Aspx.cs file
DAL.cs file
BAL.cs file

The BAL.cs File code of Database Connectivity.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for BAL
/// </summary>
public class BAL
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["constr"]);
public BAL()
{
//
// TODO: Add constructor logic here
//
}

public System.Data.DataSet SelectData(System.Data.DataSet ds)
{
DataSet ds1 = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select1", con);
da.Fill(ds1);
return ds1;
}

public void Deletedata(DAL d)
{
SqlCommand cmd = new SqlCommand("deleteadmin", con);
cmd.Parameters.AddWithValue("@aid",d.Id);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

public void Updatedata(DAL d1)
{
SqlCommand cmd = new SqlCommand("updateadmin", con);
cmd.Parameters.AddWithValue("@user",d1.Username);
cmd.Parameters.AddWithValue("@mobile", d1.Mobileno);
cmd.Parameters.AddWithValue("@aid", d1.Id);
cmd.Parameters.AddWithValue("@email", d1.Emailid);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

}

DAL.cs File Code for properties declaration.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
public DAL()
{
//
// TODO: Add constructor logic here
//
}
int id;

public int Id
{
get { return id; }
set { id = value; }
}
string username;

public string Username
{
get { return username; }
set { username = value; }
}

string emailid;

public string Emailid
{
get { return emailid; }
set { emailid = value; }
}
decimal mobileno;

public decimal Mobileno
{
get { return mobileno; }
set { mobileno = value; }
}

}

Default.Aspx.cs for coding of the events.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
DAL d1 = new DAL();
BAL b = new BAL();

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillgrid();
}
}

private void fillgrid()
{
DataSet ds = new DataSet();
ds = b.SelectData(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
fillgrid();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
d1.Id = id;
b.Deletedata(d1);
fillgrid();
lbl.ForeColor = System.Drawing.Color.Red;
lbl.Text = "Record is deleted from the database.";
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
TextBox tu = (TextBox)GridView1.Rows[e.RowIndex].FindControl("tusername");
TextBox tm = (TextBox)GridView1.Rows[e.RowIndex].FindControl("tmobileno");
TextBox te = (TextBox)GridView1.Rows[e.RowIndex].FindControl("temailid");
d1.Id = id;
d1.Username = tu.Text;
d1.Emailid = te.Text;
d1.Mobileno = decimal.Parse(tm.Text);
b.Updatedata(d1);
GridView1.EditIndex = -1;
fillgrid();
lbl.ForeColor = System.Drawing.Color.Green;
lbl.Text = "Record is updated successfully.";
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
fillgrid();
}

}

Default.aspx code for GUI of the gridview.

<%@ 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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lbl" runat="server"></asp:Label>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None" DataKeyNames="id"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="AdminId">
<ItemTemplate>
<asp:Label ID="ladminid" Text='<%#bind("id") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="leadminid" Text='<%#bind("id") %>' runat="server"></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Username">
<ItemTemplate>
<asp:Label ID="lusername" Text='<%#bind("username") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tusername" Text='<%#bind("username") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="MobileNo">
<ItemTemplate>
<asp:Label ID="lmobileno" Text='<%#bind("mobileno") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="tmobileno" Text='<%#bind("mobileno") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emailid">
<ItemTemplate>
<asp:Label ID="lemailid" Text='<%#bind("emailid") %>' runat="server"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="temailid" Text='<%#bind("emailid") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Edit" ShowEditButton="true" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="true" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</div>
</form>
</body>

</html>

Web.Config file to for Connection String.

<?xml version="1.0"?>

<!--

For more information on how to configure your ASP.NET application, please visit

http://go.microsoft.com/fwlink/?LinkId=169433

-->

<system.web>

</system.web>

<add key="constr" value="Data Source=LAB2-PC3\SQLEXPRESS;Initial Catalog=demo;Integrated Security=True"/>

</appSettings>

</configuration>

For Learn C#.Net, ASP.Net, VB.Net Visit Our Website

Asp.net Project - Engineering Students

Search This Blog

Tuesday, February 11, 2014