ASP.NET – Insert data using SQL Stored Procedures

  • by
Post Views: 1,019

SQL stored procedures are prepared SQL queries that we can use multiple times in our applications. With combination of “if and else” statements it can be used for inserting, deleting, updating data in SQL database. It can be with or without parameters. In this post we will learn how we can use Stored procedures on ASP.NET web forms. We will create a web form in .NET and add data and display data from SQL table using Stored procedures. Let’s start.

DOWNLOAD SOURCE CODE.

Create a database with name “ASPNET” in SQL, and run below script to create table and procedures:

Create table:

CREATE TABLE [dbo].[tblUsersInfo](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL
)

Create Stored procedure:

create proc [dbo].[sp_AddUsers]
(
@Name nvarchar(50)=null,@Gender nvarchar(50)=null,@Email nvarchar(50)=null,
@Password nvarchar(50)=null,
@StmtType nvarchar(50)
)
as
begin

if @StmtType='get'
begin
Select Name,Email,Gender from tblUsersInfo order by UserId desc;
end;

if @StmtType='add'
begin
Insert into tblUsersInfo (Name,Email,Gender,Password) values (@Name,@Email,@Gender,@Password);
Select Name,Email,Gender from tblUsersInfo order by UserId desc;
end;
end;
GO

Now in your ASP.NET web project, create a web form with name AddUsers.aspx and edit it as below:

AddUsers.aspx:

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>
Add Users
</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css"
integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous"/>

<style>
body
{
padding:10px;
}

.form-control
{
width:250px !important;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<h1>Add Users form:</h1>
<hr />
<div class="row">
<div class="col-lg-12">
<span>Name:</span>
<asp:TextBox runat="server" ID="txtName" CssClass="form-control" />

<br />
<span>Email:</span>
<asp:TextBox runat="server" ID="txtEmail" CssClass="form-control" />

<br />
<span>Gender:</span>
<asp:DropDownList runat="server" CssClass="form-control" ID="ddGender">
<asp:ListItem Text="Male" Value="Male" />
<asp:ListItem Text="Female" Value="Female" />
<asp:ListItem Text="Others" Value="Others" />
</asp:DropDownList>

<br />
<span>Password:</span>
<asp:TextBox runat="server" ID="txtPassword" CssClass="form-control" TextMode="Password"/>
<br />

<asp:Button runat="server" ID="btnSave" CssClass="btn btn-success" Text="Save"
OnClick="btnSave_Click"/>
<br />
</div>

<div class="col-lg-12">
<asp:GridView runat="server" ID="mydataGrid" CssClass="table" UseAccessibleHeader="true"/>
</div>
</div>
</form>
</body>
</html>

This web form has a ASP.NET GridView control to display records from database and few textboxes and a dropdownlist to add data into our table.

ASP.NET using SQL stored procedures

ASP.NET using SQL stored procedures

Edit the code file as below:

AddUsers.aspx.cs:

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

namespace LoginApp
{
public partial class AddUsers : System.Web.UI.Page
{
static string connectionString = @"Data Source=VIBES;Initial Catalog=ASPNET;User ID=sa;Password=789";

SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
DataTable dt;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
FillGrid();
}

protected void btnSave_Click(object sender, EventArgs e)
{
try
{
con = new SqlConnection(connectionString);
con.Open();
cmd = new SqlCommand();
cmd.CommandText = "sp_AddUsers";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
//@Name,@Gender,@Email,@Password
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Gender", ddGender.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Email", txtEmail.Text.ToString());
cmd.Parameters.AddWithValue("@Password", txtPassword.Text.ToString());
cmd.Parameters.AddWithValue("@StmtType", "add");

adapter = new SqlDataAdapter(cmd);

dt = new DataTable();

adapter.Fill(dt);

cmd.Dispose();
con.Close();

if(dt!=null && dt.Rows.Count>0)
{
mydataGrid.DataSource = dt;
mydataGrid.DataBind();
}

}
catch(Exception ex)
{

}
}

public void FillGrid()
{
try
{
con = new SqlConnection(connectionString);
con.Open();
cmd = new SqlCommand();
cmd.CommandText = "sp_AddUsers";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@StmtType", "get");

adapter = new SqlDataAdapter(cmd);

dt = new DataTable();

adapter.Fill(dt);

cmd.Dispose();
con.Close();

if (dt != null && dt.Rows.Count > 0)
{
mydataGrid.DataSource = dt;
mydataGrid.DataBind();
}

}
catch (Exception ex)
{

}
}
}
}

On this form we are calling FillGrid() method on our Page load method and on button click method we are adding data to the database using SQL connection and command. Notice here, we are using the same SQL stored procedure to conduct different database operations just by changing a single SQL parameter.

  1. For adding data we are calling sql procedure sp_AddUsers with its @StmtType='get'.
  2. For fetching data we are calling same procedure sp_AddUsers with its @StmtType='add'.

So this way we can use SQL stored procedures on ASP.NET web forms. You can download source code using below link.

DOWNLOAD SOURCE CODE.

 

ASP.NET using SQL stored procedures

ASP.NET using SQL stored procedures


Tags: