dotnetwarriors.com

Using Stored Procedures with ASP.NET

In this Article i will Explain what is Stored Procedure and How to Perform Insert, Update, Delete and Select Operation using Stored Procedures in Asp.Net, SQL Server using a simple Example.

In this Article i will Explain what is Stored Procedure and How to Perform Insert, Update, Delete and Select Operation using Stored Procedures in Asp.Net, SQL Server using a simple Example.

 

Stored Procedure:

Stored Procedure is set of Sql statement, which is perform a specific task. when we compile the stored procedure then one object of the stored procedure will be created in the database.There is no need to compile stored procedure again and again.

 

Tutorial:

In This Tutorial i am using a Sql Table Employee.

So first of all create a Employee Table as shown in below image:

 

Using Stored Procedures with ASP.NET

After that create Stored Procedures to Perform SelectInsertUpdateDelete Operation in Database Table in SQL Server as shown in Below images :

 

SELECT :

Using Stored Procedures with ASP.NET

INSERT :

Using Stored Procedures with ASP.NET

UPDATE :

Using Stored Procedures with ASP.NET

DELETE :

Using Stored Procedures with ASP.NET

Design a Web Form with 3 TextBox, 4 Button, 1 GridView and 1 Label Control to Perform Insert, Update, Delete, Select Operation as shown in below Images:

 

.aspx code :

<div><h2>Select, Insert, Update, Delete Using Stored Procedures</h2></div>
<div><b>Id: </b><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="ins1" runat="server" Text="Insert" OnClick="ins1_Click" /></div>
<div><b>Name: </b><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <asp:Button ID="upd1" runat="server" Text="Update" OnClick="upd1_Click" /></div>
<div><b>City: </b><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <asp:Button ID="del1" runat="server" Text="Delete" OnClick="del1_Click" /></div>
<div><b></b><asp:Button ID="sel1" runat="server" Text="Select all Data" OnClick="sel1_Click" /></div>
<div><b></b><asp:Label ID="Label1" runat="server" ForeColor="#990000"></asp:Label></div>
<div><b></b><asp:GridView ID="GridView1" runat="server"></asp:GridView></div>

 

C# Code :

 

Namespaces :
You will have to inherit the following namespaces –
 
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
 
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void ins1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd_ins=new SqlCommand("sp_insert",con);
            cmd_ins.CommandType=CommandType.StoredProcedure;
            cmd_ins.Parameters.AddWithValue("@a", TextBox1.Text);
            cmd_ins.Parameters.AddWithValue("@b", TextBox2.Text);
            cmd_ins.Parameters.AddWithValue("@c", TextBox3.Text);
            con.Open();
            cmd_ins.ExecuteNonQuery();
            Label1.Text = "";
            Label1.Text = "Data Inserted";
            con.Close();
            
        }
        protected void upd1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd_upd = new SqlCommand("sp_update", con);
            cmd_upd.CommandType = CommandType.StoredProcedure;
            cmd_upd.Parameters.AddWithValue("@a", TextBox1.Text);
            cmd_upd.Parameters.AddWithValue("@b", TextBox2.Text);
            cmd_upd.Parameters.AddWithValue("@c", TextBox3.Text);
            con.Open();
            cmd_upd.ExecuteNonQuery();
            Label1.Text = "";
            Label1.Text = "Data Updated";
            con.Close();
        }
        protected void del1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd_del = new SqlCommand("sp_delete", con);
            cmd_del.CommandType = CommandType.StoredProcedure;
            cmd_del.Parameters.AddWithValue("@a", TextBox1.Text);
            con.Open();
            cmd_del.ExecuteNonQuery();
            Label1.Text = "";
            Label1.Text = "Data Deleted";
            con.Close();
        }
        protected void sel1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd_sel = new SqlCommand("spselect", con);
            cmd_sel.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader dr = cmd_sel.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();
            Label1.Text = "";
            Label1.Text = "All Employee Data";
            con.Close();
        }

 

Connection String :

<connectionStrings>
    <add name="constr" connectionString="Data Source=hello\SQLEXPRESS;Initial Catalog=aspdotnetcorner;Integrated Security=True"/>
  </connectionStrings>

 

Now Run the code with Ctrl+F5 or F5

and Perform SelectInsertUpdateDelete Operation in Database Table as shown in below images :

 

Using Stored Procedures with ASP.NET

 

Using Stored Procedures with ASP.NET

 

Using Stored Procedures with ASP.NET

 

Using Stored Procedures with ASP.NET

 

 





Share This Article :

Comments


Add Comment


 
Security Code :
12 + 7 =