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

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


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



    <table class="w-100">

        <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>

            <td>&nbsp;</td>

            <td>&nbsp;</td>

        </tr>

        <tr>

            <td>

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

            </td>

            <td>

                <asp:TextBox ID="TextBox1" Font-Size="Medium" runat="server" 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" Font-Size="Medium" runat="server" 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" Font-Size="Medium" runat="server" 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" Font-Size="Medium" Width="200px">

                    <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>

            <td>&nbsp;</td>

            <td>&nbsp;</td>

        </tr>

        <tr>

            <td>&nbsp;</td>

            <td>&nbsp;</td>

        </tr>

        <tr>

            <td>&nbsp;</td>

            <td>

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

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

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

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

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

            </td>

        </tr>

        <tr>

            <td>&nbsp;</td>

            <td>&nbsp;</td>

        </tr>

        <tr>

            <td colspan="2">

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

                    <HeaderStyle BackColor="#6600CC" ForeColor="White"/>

                </asp:GridView>

            </td>

        </tr>

    </table>





    <%--<main>

        <section class="row" aria-labelledby="aspnetTitle">

            <h1 id="aspnetTitle">ASP.NET</h1>

            <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 &raquo;</a></p>

        </section>


        <div class="row">

            <section class="col-md-4" aria-labelledby="gettingStartedTitle">

                <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 &raquo;</a>

                </p>

            </section>

            <section class="col-md-4" aria-labelledby="librariesTitle">

                <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 &raquo;</a>

                </p>

            </section>

            <section class="col-md-4" aria-labelledby="hostingTitle">

                <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 &raquo;</a>

                </p>

            </section>

        </div>

    </main>--%>


</asp:Content>

Above File is CRUD_ASP_SP\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.Data;
using System.Configuration;
using System.Drawing;

namespace CRUD_ASP_SP
{
    public partial class _Default : Page
    {
        string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GetProductList();
            }
        }
        //SqlConnection con = new SqlConnection("Data Source=HEER\\SQLEXPRESS;Initial Catalog=MyTest_DB;Integrated Security=True;Encrypt=False");
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    int productid = int.Parse(TextBox1.Text);
                    string iname = TextBox2.Text;
                    string specification = TextBox3.Text;
                    string unit = DropDownList1.SelectedValue;
                    string status = RadioButtonList1.SelectedValue;
                    con.Open();
                    DateTime cdate = DateTime.Parse(TextBox4.Text);
                    SqlCommand cmd = new SqlCommand("exec ProductSetup_SP '" + productid + "','" + iname + "','" + specification + "','" + unit + "','" + status + "','" + cdate + "'", con);
                    cmd.ExecuteNonQuery();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Inserted !!')", true);
                    GetProductList();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }
        }

        void GetProductList()
        {
            try
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = new SqlCommand("exec ProductList_SP", con);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    int productid = int.Parse(TextBox1.Text);
                    string iname = TextBox2.Text;
                    string specification = TextBox3.Text;
                    string unit = DropDownList1.SelectedValue;
                    string status = RadioButtonList1.SelectedValue;
                    con.Open();
                    DateTime cdate = DateTime.Parse(TextBox4.Text);
                    SqlCommand cmd = new SqlCommand("exec ProductUpdate_SP '" + productid + "','" + iname + "','" + specification + "','" + unit + "','" + status + "','" + cdate + "'", con);
                    cmd.ExecuteNonQuery();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Updated !!')", true);
                    GetProductList();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    int productid = int.Parse(TextBox1.Text);
                    con.Open();
                    SqlCommand cmd = new SqlCommand("exec ProductDelete_SP '" + productid + "'", con);
                    cmd.ExecuteNonQuery();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Deleted !!')", true);
                    GetProductList();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    int productid = int.Parse(TextBox1.Text);
                    con.Open();
                    SqlCommand cmd = new SqlCommand("exec ProductSearch_SP '"+productid+"'",con);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }
        }

        protected void Button5_Click(object sender, EventArgs e)
        {
            try
            {
                    GetProductList();
            }
            catch (Exception ex)
            {
                    Console.WriteLine("Exception: " + ex.ToString());
            }
        }
    }
}
Above File is CRUD_ASP_SP\Default.aspx.cs














<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.7.2" />
    <httpRuntime targetFramework="4.7.2" />
    <pages>
      <namespaces>
        <add namespace="System.Web.Optimization" />
      </namespaces>
      <controls>
        <add assembly="Microsoft.AspNet.Web.Optimization.WebForms" namespace="Microsoft.AspNet.Web.Optimization.WebForms" tagPrefix="webopt" />
      </controls>
    </pages>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" />
        <bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Web.Infrastructure" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-13.0.0.0" newVersion="13.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
<connectionStrings>
<add name="dbcs" connectionString="Data Source=HEER\SQLEXPRESS;Initial Catalog=MyTest_DB;Integrated Security=True;Encrypt=False" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Above File is CRUD_ASP_SP\Web.config
















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
AS
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
AS
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
AS
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
AS
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
AS
BEGIN
SELECT * FROM ProductSetup_Tab WHERE ProductID=@ProductID;
END

EXECUTE ProductSearch_SP 4;




























Comments

Post a Comment