ASP.NET – Use SQL Stored Procedure on WebForms

ASH

5 years ago

In this post we will see how to use stored procedure on asp.net web forms. We will create a SQL table and a Stored Procedure to add data to the database table. Using asp.net web form we will add data using the created stored procedure.

ASP.NET – Use SQL Stored Procedure.DOWNLOAD SOURCE CODE

SQL Table creation:

script:

Create table Products
(
Id int primary key identity(1,1) not null,
ProName nvarchar(50) not null,
ProDesc nvarchar(100) not null,
OnDate datetime default getdate()
)

Stored Procedure script:

create procedure sp_AddProducts
(
@ProName nvarchar(50), @Prodesc nvarchar(100)
)
as
begin
insert into Products (ProName,ProDesc) values (@ProName,@ProDesc)
end

Now let’s use this stored procedure on a ASP.NET web form:

Create a new ASPX webform with name AddProducts.aspx and edit it as below.

AddProducts.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AddProducts.aspx.cs" Inherits="WebApplication2.AddProducts" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Content/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div style="padding:5px;">
<h2>Add Data using SQL Stored Procedure</h2>
<table class="table table-condensed" style="width:500px;">
<tr>
<td>
Product Name:
</td>
<td>
<asp:TextBox runat="server" ID="txtProductName" placeholder="Enter Product Name"
Width="150px"/>
</td>
</tr>
<tr>
<td>
Product Description:
</td>
<td>
<asp:TextBox TextMode="MultiLine" runat="server" ID="txtProductDesc"
Width="150px" placeholder="Enter Product Description"/>
</td>
</tr>
<tr>
<td colspan="2" align="left">
<asp:Button runat="server" Text="Submit" ID="btnSubmit" OnClick="btnSubmit_Click"/>
</td>
</tr>
</table>
<asp:Label runat="server" ID="lblInfo" />

<br /><br />
<asp:GridView runat="server" ID="proDataGrid" CssClass="table table-condensed" Width="500px"/>
</div>
</form>
</body>
</html>

This form contains two asp.net textboxes to get data for product name and its description. It also has asp.net Gridview to view data already present in the table. When user clicks on “Submit” button, we will add the supplied values using “sp_AddProducts” stored procedure which we created.

Now edit the code-behind class as below:

AddProducts.aspx.cs:

using System;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication2
{
public partial class AddProducts : System.Web.UI.Page
{
public static String connectionString = @"Data Source=192.168.0.106;Initial Catalog=ParallelCodes;User ID=sa;Password=789;Trusted_Connection=false;";
SqlConnection con;
SqlCommand cmd;

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

protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if(txtProductDesc.Text==null || txtProductName.Text==null||
txtProductDesc.Text.ToString().Trim().Equals("") || txtProductName.Text.ToString().Trim().Equals(""))
lblInfo.Text = "Please enter all fields";
else
{
con = new SqlConnection(connectionString);
cmd = new SqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "sp_AddProducts";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProName", txtProductName.Text.ToString());
cmd.Parameters.AddWithValue("@Prodesc", txtProductDesc.Text.ToString());
cmd.ExecuteNonQuery();

SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand("Select ProName,ProDesc,OnDate from Products", con));
DataTable dt = new DataTable();
adapter.Fill(dt);
proDataGrid.DataSource = dt;
proDataGrid.DataBind();
adapter.Dispose();
cmd.Dispose();
con.Close();

lblInfo.Text = "Added Successfully";
txtProductDesc.Text = "";
txtProductName.Text = "";
}
}catch(Exception ex)
{
lblInfo.Text = "Error:" + ex.Message.ToString();
}
}

public void FillGrid()
{
try
{
con = new SqlConnection(connectionString);
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand("Select ProName,ProDesc,OnDate from Products", con));
DataTable dt = new DataTable();
adapter.Fill(dt);
proDataGrid.DataSource = dt;
proDataGrid.DataBind();
adapter.Dispose();
con.Close();
}
catch(Exception ex)
{

}
}
}
}

Here we will call FillGrid() method on page_load method if it was not a PostBack request i.e. the page is loaded for the very first time. And on button click we will first check if user has entered both the fields, if yes we will add data using stored procedure into our table.

For this it is important to specify the sql command type as “StoredProcedure” and also supply all the parameters defined and required into stored procedure.

cmd.CommandText = "sp_AddProducts";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProName", txtProductName.Text.ToString());
cmd.Parameters.AddWithValue("@Prodesc", txtProductDesc.Text.ToString());

That’s it.

Now run your page to test it.

Output:
DOWNLOAD SOURCE CODE

ASP.NET – Stored Procedure. How to use SQL Stored Procedures

Also see:

Share this: