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

ASP.NET – Use SQL Stored Procedure on WebForms

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
ASP.NET – Stored Procedure. How to use SQL Stored Procedures

 

Also see:

    1. ASP.NET GridView CSS
    2. ASP.NET GridView CSS Designing using Bootstrap
    3. ASP.NET GridView Binding from SQL Database
    4. ASP.NET GridView Fill from SQL Database
    5. ASP.NET GridView Fill from SQL DataSet
    6. ASP.NET GridView Export to CSV Excel
    7. ASP.NET GridView Export to Excel
    8. How to show images in ASP.NET GridView from Image Path
    9. ASP.NET GridView with Buttons
    10. ASP.NET GridView with Delete Button
    11. ASP.NET GridView with UpdatePanel
    12. ASP.NET GridView – Create Awesome CSS Style & Design
    13. ASP.NET GridView – How to fill Data from Database
    14. ASP.NET – Importing Excel CSV to MS SQL Database Server
    15. ASP.NET Button Style and Design with CSS
    16. Styling Buttons using CSS
    17. How to use CSS in ASP.NET
    18. ASP.NET How to host Website in windows IIS
    19. ASP.NET Android – Storing Images in Base64 String value using Android App and .NET webservice
    20. ASP.NET Android – Storing Images in Byte[] value using Android App and .NET webservice
    21. ASP.NET Android – Uploading Images with Image Path from Android and .NET webservice