Complete CRUD Operation in Asp.Net C# with SQL Using Stored Procedure

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CRUD_ASP_SP_Practice._Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">

<tr>

<td colspan="2" style="font-family: Arial, Helvetica, sans-serif; font-size: x-large; font-weight: bold; color: #333333">Complete CRUD Operation in Asp.Net C# with SQL Using Stored Procedure</td>

</tr>

<tr>

</tr>

<tr>

<td>

<asp:Label ID="Label1" runat="server" Text="Product ID" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:TextBox ID="TextBox1" runat="server" Font-Size="Medium" Width="200px"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label2" runat="server" Text="Item Name" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:TextBox ID="TextBox2" runat="server" Font-Size="Medium" Width="200px"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label3" runat="server" Text="Specification" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:TextBox ID="TextBox3" runat="server" Font-Size="Medium" Width="200px"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label4" runat="server" Text="Unit" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:DropDownList ID="DropDownList1" runat="server" Font-Size="Medium" Width="200px">

<asp:ListItem>PCS</asp:ListItem>

<asp:ListItem>KG</asp:ListItem>

<asp:ListItem>DZ</asp:ListItem>

<asp:ListItem>Ltr</asp:ListItem>

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label5" runat="server" Text="Status" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal">

<asp:ListItem>Running</asp:ListItem>

<asp:ListItem>Unused</asp:ListItem>

</asp:RadioButtonList>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label6" runat="server" Text="Creation Date" Font-Size="Medium"></asp:Label>

</td>

<td>

<asp:TextBox ID="TextBox4" runat="server" Font-Size="Medium" Width="200px"></asp:TextBox>

</td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>

<asp:Button ID="Button1" runat="server" Text="Insert" Font-Size="Large" ForeColor="White" BackColor="#6600CC" OnClick="Button1_Click"/>

  <asp:Button ID="Button2" runat="server" Text="Update" Font-Size="Large" ForeColor="White" BackColor="#6600CC" OnClick="Button2_Click"/>

  <asp:Button ID="Button3" runat="server" Text="Delete" Font-Size="Large" ForeColor="White" BackColor="#6600CC" OnClick="Button3_Click" OnClientClick="return confirm('Are you sure to delete ?')"/>

  <asp:Button ID="Button4" runat="server" Text="Search" Font-Size="Large" ForeColor="White" BackColor="#6600CC" OnClick="Button4_Click"/>

  <asp:Button ID="Button5" runat="server" Text="Load" Font-Size="Large" ForeColor="White" BackColor="#6600CC" OnClick="Button5_Click"/>

</td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<asp:GridView ID="GridView1" runat="server" Width="1219px">

</asp:GridView>

</td>

</tr>

</table>

<%--<main>

<p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS, and JavaScript.</p>

<p><a href="http://www.asp.net" class="btn btn-primary btn-md">Learn more »</a></p>

</section>

<h2 id="gettingStartedTitle">Getting started</h2>

<p>

ASP.NET Web Forms lets you build dynamic websites using a familiar drag-and-drop, event-driven model.

A design surface and hundreds of controls and components let you rapidly build sophisticated, powerful UI-driven sites with data access.

</p>

<p>

<a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301948">Learn more »</a>

</p>

</section>

<h2 id="librariesTitle">Get more libraries</h2>

<p>

NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.

</p>

<p>

<a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301949">Learn more »</a>

</p>

</section>

<h2 id="hostingTitle">Web Hosting</h2>

<p>

You can easily find a web hosting company that offers the right mix of features and price for your applications.

</p>

<p>

<a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301950">Learn more »</a>

</p>

</section>

</div>

</main>--%>

</asp:Content>

Above File is CRUD_ASP_SP_Practice\Default.aspx

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

using System.Globalization;

namespace CRUD_ASP_SP_Practice

{

public partial class _Default : Page

{

string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

GetProductList();

}

protected void Button1_Click(object sender, EventArgs e)

{

try

{

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();

SqlCommand cmd = new SqlCommand("ProductSetup_SP",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ProductID",int.Parse(TextBox1.Text));

cmd.Parameters.AddWithValue("@ItemName",TextBox2.Text);

cmd.Parameters.AddWithValue("@Specification",TextBox3.Text);

cmd.Parameters.AddWithValue("@Unit",DropDownList1.SelectedValue);

cmd.Parameters.AddWithValue("@Status",RadioButtonList1.SelectedValue);

DateTime parsedDate;

string inputDate = TextBox4.Text.Trim();

if (!DateTime.TryParseExact(inputDate, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate))

{

Response.Write("<script>alert('Invalid date format. Please use dd/MM/yyyy.')</script>");

return;

}

cmd.Parameters.AddWithValue("@CreationDate", parsedDate);

cmd.ExecuteNonQuery();

ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Inserted');", true);

GetProductList();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

void GetProductList()

{

try

{

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();

SqlCommand cmd = new SqlCommand("ProductList_SP",con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

protected void Button2_Click(object sender, EventArgs e)

{

try

{

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();

SqlCommand cmd = new SqlCommand("ProductUpdate_SP", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ProductID",int.Parse(TextBox1.Text));

cmd.Parameters.AddWithValue("@ItemName",TextBox2.Text);

cmd.Parameters.AddWithValue("@Specification",TextBox3.Text);

cmd.Parameters.AddWithValue("@Unit",DropDownList1.SelectedValue);

cmd.Parameters.AddWithValue("@Status",RadioButtonList1.SelectedValue);

cmd.Parameters.AddWithValue("@CreationDate",DateTime.Parse(TextBox4.Text));

cmd.ExecuteNonQuery();

ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Updated');", true);

GetProductList();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

protected void Button3_Click(object sender, EventArgs e)

{

try

{

using(SqlConnection con = new SqlConnection(cs))

{

con.Open();

SqlCommand cmd = new SqlCommand("ProductDelete_SP",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ProductID",int.Parse(TextBox1.Text));

cmd.ExecuteNonQuery();

ScriptManager.RegisterStartupScript(this,this.GetType(),"script","alert('Successfully Deleted');",true);

GetProductList();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

protected void Button4_Click(object sender, EventArgs e)

{

try

{

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();

SqlCommand cmd = new SqlCommand("ProductSearch_SP",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ProductID",int.Parse(TextBox1.Text));

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

protected void Button5_Click(object sender, EventArgs e)

{

try

{

GetProductList();

}

catch (Exception ex)

{

Response.Write("Exception: " + ex.ToString());

}

Above File is CRUD_ASP_SP_Practice\Default.aspx.cs

CREATE DATABASE Test_DB;

USE Test_DB;

CREATE TABLE ProductSetup_Tab(

ProductID INT NOT NULL PRIMARY KEY,

ItemName nvarchar(50) NOT NULL,

Specification nvarchar(150) NOT NULL,

Unit nvarchar(30) NOT NULL,

Status nvarchar(30) NOT NULL,

CreationDate datetime NOT NULL);

SELECT * FROM ProductSetup_Tab;

INSERT INTO ProductSetup_Tab(ProductID,ItemName,Specification,Unit,Status,CreationDate)

VALUES(2,'Laptop','Core i7','PCS','Running',TRY_CONVERT(DATETIME,'20/05/2021',103)),

(3,'Laptop','Core i5','PCS','Running',TRY_CONVERT(DATETIME,'15/06/2021',103)),

(4,'Mouse','logitech','PCS','Running',TRY_CONVERT(DATETIME,'05/02/2020',103)),

(5,'Keyboard','Zebronics','PCS','Running',TRY_CONVERT(DATETIME,'10/04/2021',103));

--CREATING STORED PROCEDURE FOR INSERTING DATA IN DATABASE TABLE

CREATE PROCEDURE ProductSetup_SP

@ProductID INT,

@ItemName nvarchar(50),

@Specification nvarchar(150),

@Unit nvarchar(30),

@Status nvarchar(30),

@CreationDate DATETIME

BEGIN

INSERT INTO ProductSetup_Tab(ProductID,ItemName,Specification,Unit,Status,CreationDate)

VALUES(@ProductID,@ItemName,@Specification,@Unit,@Status,@CreationDate);

END

EXECUTE ProductSetup_SP 6,'Laptop','Core i7','PCS','Running','15/11/2024';

--CREATING STORED PROCEDURE FOR SELECTING DATABASE TABLE DATA

CREATE PROCEDURE ProductList_SP

BEGIN

SELECT * FROM ProductSetup_Tab;

END

EXECUTE ProductList_SP;

--CREATING STORED PROCEDURE FOR UPDATING DATABASE TABLE DATA

CREATE PROCEDURE ProductUpdate_SP

@ProductID INT,

@ItemName nvarchar(50),

@Specification nvarchar(150),

@Unit nvarchar(30),

@Status nvarchar(30),

@CreationDate datetime

BEGIN

UPDATE ProductSetup_Tab SET ItemName=@ItemName,Specification=@Specification,Unit=@Unit,Status=@Status,CreationDate=@CreationDate WHERE ProductID=@ProductID;

END

DECLARE @ConvertedDate DATETIME;

SET @ConvertedDate = CONVERT(DATETIME,'24/11/2022',103);

EXECUTE ProductUpdate_SP 2,'Laptop','Core i3','PCS','Running',@ConvertedDate;

--CREATING STORED PROCEDURE FOR DELETING DATABASE TABLE DATA

CREATE PROCEDURE ProductDelete_SP

@ProductID INT

BEGIN

DELETE FROM ProductSetup_Tab WHERE ProductID=@ProductID;

END

EXECUTE ProductDelete_SP 6;

--CREATING STORED PROCEDURE FOR SEARCHING DATABASE TABLE DATA

CREATE PROCEDURE ProductSearch_SP

@ProductID INT

BEGIN

SELECT * FROM ProductSetup_Tab WHERE ProductID=@ProductID;

END

EXECUTE ProductSearch_SP 4;

Search This Blog

ADO.NET

Complete CRUD Operation in Asp.Net C# with SQL Using Stored Procedure | ProgrammingGeek

Comments

Post a Comment