How to Implement Store Procedure in Asp.net
In Previous post,I told how to make Store Procedure so in this post we will study how to implement it
Store Procedure Click this link to know how to make Store Procedure
# Implementing Stored Procedure
File> new> new project>visual c#>asp.net > empty
* Open Web.config
Add connection String
<configuration>
<connectionStrings>
<add name = "cn" connectionString="server=DESKTOP-I3R20OL\SQLEXPRESS;
database=dbemployee; integrated security=true"/>
</connectionStrings>
|
Add webform and click on design
Double click on webform
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace WebApplication5
{
public partial class WebForm1 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
protected void Page_Load(object sender,
EventArgs e)
{
con.ConnectionString =
ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
if (con.State==ConnectionState.Closed)
{
con.Open();
}
|
# How to read connection string and assign sql connection
{
con.ConnectionString =
ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
if (con.State==ConnectionState.Closed)
{
con.Open();
}
|
ConfigurationManager = class to
read data from web.config
ConnectionState.Closed =
ConnectionState is enumeration and closed is used if connection is closed then
open it
#Double Click Save
protected void Button1_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "insemp";
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value =
TextBox2.Text;
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value =
TextBox3.Text;
cmd.Parameters.Add("@es", SqlDbType.Int).Value =
Convert.ToInt32(TextBox4.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
}
private void Clear_Rec()
{
TextBox1.Text =
String.Empty;
TextBox2.Text =
String.Empty;
TextBox3.Text =
String.Empty;
TextBox4.Text =
String.Empty;
TextBox1.Focus();
}
}
|
# Display button
protected void Button4_Click(object sender,
EventArgs e)
{
SqlCommand cmd = Disp_Rec();
cmd.Dispose();
}
private SqlCommand Disp_Rec()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "dispemp";
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Connection = con;
SqlDataReader dr =
cmd.ExecuteReader();
ListBox1.DataTextField
= "ename";
ListBox1.DataValueField = "empno";
ListBox1.DataSource =
dr;
ListBox1.DataBind();
dr.Close();
return cmd;
}
}
}
|
ExecuteReader(); - To store
reference
ListBox1.DataTextField = "ename";- To display
ListBox1.DataValueField = "empno"; - To use
internally but not displaying
Dr.read(); - Skip 1st record because its forward approach
and when used 2 times it skips 2 record.
# In Page load event
protected void Page_Load(object sender,
EventArgs e)
{
con.ConnectionString =
ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
if (con.State==ConnectionState.Closed)
{
con.Open();
}
if (Page.IsPostBack==false)
{
Disp_Rec();
}
|
# Double Click on Save button
Add Disp_Rec();
# To retrieve data by clicking in Name in Listbox
Click in Listbox> enable autopostback
Double click on listbox
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "findemp";
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value =
Convert.ToInt32(ListBox1.SelectedValue);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
TextBox1.Text =
dr[0].ToString();
TextBox2.Text =
dr[1].ToString();
TextBox3.Text =
dr[2].ToString();
TextBox4.Text =
dr[3].ToString();
}
dr.Close();
cmd.Dispose();
}
|
We can use empno, ename in place of of 0,1 also(column no)
# For update
protected void Button2_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "updemp";
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value =
TextBox2.Text;
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value =
TextBox3.Text;
cmd.Parameters.Add("@es", SqlDbType.Int).Value =
Convert.ToInt32(TextBox4.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
Disp_Rec();
}
|
# For Delete
protected void Button3_Click(object sender,
EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delemp";
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@eno", SqlDbType.Int).Value =
Convert.ToInt32(TextBox1.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
Clear_Rec();
Disp_Rec();
|
