CRUD Operations using stored procedures with Validation ASP.NET

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


<!DOCTYPE html>


<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <style type="text/css">

        .auto-style1 {

            width: 100%;

        }

        .auto-style2 {

            width: 165px;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <table class="auto-style1">

            <tr>

                <td colspan="2" style="font-family:Arial,Helvetica,sans-serif;font-size:x-large;font-weight:bold;color:#333333">Employee CRUD Operations using Stored Procedures</td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td class="auto-style2">Employee Id</td>

                <td>

                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Please Enter Employee Id" ForeColor="Red" Display="Dynamic" Text="*" ControlToValidate="TextBox1" SetFocusOnError="true"></asp:RequiredFieldValidator>

                    <asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="Please Enter Product Id" ForeColor="Red" Display="Dynamic" Text="*" ControlToValidate="TextBox1" SetFocusOnError="true"></asp:CustomValidator>

                </td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td class="auto-style2">Name of Employee</td>

                <td>

                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Please Enter Employee Name" ForeColor="Red" Display="Dynamic" Text="*" ControlToValidate="TextBox2" SetFocusOnError="true"></asp:RequiredFieldValidator>

                    <asp:CustomValidator ID="CustomValidator2" runat="server" ErrorMessage="Please Enter Employee Name" ForeColor="Red" Display="Dynamic" Text="*" ControlToValidate="TextBox2" SetFocusOnError="true"></asp:CustomValidator>

                </td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td class="auto-style2">Salary</td>

                <td>

                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Please Enter Salary" ForeColor="Red" Text="*" Display="Dynamic" ControlToValidate="TextBox3" SetFocusOnError="true"></asp:RequiredFieldValidator>

                    <asp:CustomValidator ID="CustomValidator3" runat="server" ErrorMessage="Please Enter Salary" ForeColor="Red" Display="Dynamic" Text="*" ControlToValidate="TextBox3" SetFocusOnError="true" ></asp:CustomValidator>

                </td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>

                    <asp:Button ID="btnInsert" runat="server" Font-Size="Large" ForeColor="White" BackColor="#6600CC" Text="Insert" Height="45px" Width="96px" OnClick="btnInsert_Click"/>

                    &nbsp;&nbsp;&nbsp;<asp:Button ID="btnUpdate" runat="server" Font-Size="Large" ForeColor="White" BackColor="#6600CC" Text="Update" Height="45px" Width="96px" OnClick="btnUpdate_Click"/>

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

                    &nbsp;&nbsp;&nbsp;<asp:Button ID="btnSearch" runat="server" Font-Size="Large" ForeColor="White" BackColor="#6600CC" Text="Search" Height="45px" Width="96px" CausesValidation="false" OnClick="btnSearch_Click"/>

                    &nbsp;&nbsp;&nbsp;<asp:Button ID="btnLoad" runat="server" Font-Size="Large" ForeColor="White" BackColor="#6600CC" Text="Load" Height="45px" Width="96px" CausesValidation="false" OnClick="btnLoad_Click"/>

                    &nbsp;&nbsp;&nbsp;<asp:Button ID="btnGet" runat="server" Font-Size="Large" ForeColor="White" BackColor="#6600CC" Text="Get" Height="45px" Width="96px" CausesValidation="false" OnClick="btnGet_Click"/>


                </td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>

                    <asp:Label ID="lblMessage" runat="server"></asp:Label>

                </td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td class="auto-style2">&nbsp;</td>

                <td>

                    <asp:GridView ID="GridView1" runat="server" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2" ForeColor="Black">

                        <FooterStyle BackColor="#CCCCCC" />

                        <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />

                        <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />

                        <RowStyle BackColor="White" />

                        <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />

                        <SortedAscendingCellStyle BackColor="#F1F1F1" />

                        <SortedAscendingHeaderStyle BackColor="#808080" />

                        <SortedDescendingCellStyle BackColor="#CAC9C9" />

                        <SortedDescendingHeaderStyle BackColor="#383838" />

                    </asp:GridView>

                </td>

            </tr>

            </table>

        </div>

    </form>

</body>

</html>

Above File is ASP_CRUD_WithSP\WebForm1.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;

namespace ASP_CRUD_WithSP
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        string cs = ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString;

        private void ShowEmployeeData()
        {
            try
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_ShowEmpData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    //Below is the usage of "SqlDataAdapter"
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();

                    //Below is the usage of "SqlDataReader"
                    //SqlDataReader dr = cmd.ExecuteReader();
                    //GridView1.DataSource = dr;
                    //GridView1.DataBind();
                }
            }
            catch (Exception ex)
            {
                Response.Write("Exception: " + ex.Message);
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            ShowEmployeeData();
        }

        private void InsertEmployeeData()
        {
            try
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_InsertEmpData",con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empid", int.Parse(TextBox1.Text));
                    cmd.Parameters.AddWithValue("@ename", TextBox2.Text);
                    cmd.Parameters.AddWithValue("@salary", TextBox3.Text);
                    int result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        lblMessage.Text = "Record Inserted Successfully";
                    }
                }
            }
            catch (Exception ex)
            {
                Response.Write("Exception: " + ex.Message);
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            InsertEmployeeData();
            ShowEmployeeData();
        }

        private void UpdateEmployeeData()
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_UpdateEmpData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empid",int.Parse(TextBox1.Text));
                    cmd.Parameters.AddWithValue("@ename",TextBox2.Text);
                    cmd.Parameters.AddWithValue("@salary",TextBox3.Text);
                    int result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        lblMessage.Text = "Record Updated Successfully";
                    }
                    else
                    {
                        lblMessage.Text = "Record Not Updated";
                    }
                }
            }
            catch (Exception ex)
            {
                //Response.Write("Exception: " + ex.Message);
                lblMessage.Text = ex.Message;
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            UpdateEmployeeData();
            ShowEmployeeData();
        }

        private void DeleteEmployeeData()
        {
            try
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_DeleteEmpData",con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empid",int.Parse(TextBox1.Text));
                    int result = cmd.ExecuteNonQuery();
                    if (result == 1)
                    {
                        lblMessage.Text = "Record Deleted Successfully";
                    }
                    else
                    {
                        lblMessage.Text = "Record not deleted";
                    }
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            DeleteEmployeeData();
            ShowEmployeeData();
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_SearchEmpData", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empid",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.Message);
            }
        }

        protected void btnLoad_Click(object sender, EventArgs e)
        {
            try
            {
                ShowEmployeeData();
            }
            catch (Exception ex)
            {
                Response.Write("Exception: " + ex.Message);
            }
        }

        protected void btnGet_Click(object sender, EventArgs e)
        {
            try
            {
                using(SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("prc_SearchEmpData",con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@empid",int.Parse(TextBox1.Text));
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        TextBox1.Text = dr["empid"].ToString();
                        TextBox2.Text = dr["ename"].ToString();
                        TextBox3.Text = dr["salary"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                Response.Write("Exception: " + ex.Message);
            }
        }
    }
}
Above File is ASP_CRUD_WithSP\WebForm1.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>
<connectionStrings>
<add name="MyCon" connectionString="Data Source=DESKTOP-77M6N4G\SQLEXPRESS;Initial Catalog=Winsoft;Integrated Security=True;Encrypt=False" providerName="System.Data.SqlClient"/>
</connectionStrings>
  <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>
</configuration>
Above File is ASP_CRUD_WithSP\Web.config




















CREATE DATABASE Winsoft;

USE Winsoft;

CREATE TABLE Employee(empid INT NOT NULL PRIMARY KEY,ename nvarchar(50) NOT NULL,salary DECIMAL(18,2) NOT NULL);

INSERT INTO Employee(empid,ename,salary)
VALUES(1,'Ajay',56000),
(2,'Parth',80000),
(3,'Yatrik',120000),
(4,'Aaditya',420000),
(5,'Pooja',85000),
(6,'Naman',90000);

SELECT * FROM Employee;




--CREATING A STORED PROCEDURE FOR SELECTING DATA FROM DATABASE TABLE
CREATE PROCEDURE prc_ShowEmpData
AS
BEGIN
SELECT * FROM Employee;
END

EXECUTE prc_ShowEmpData;


--CREATING A STORED PROCEDURE FOR INSERTING DATA IN DATABASE TABLE
CREATE PROCEDURE prc_InsertEmpData
@empid INT,
@ename nvarchar(50),
@salary DECIMAL(18,2)
AS
BEGIN
INSERT INTO Employee(empid,ename,salary) VALUES(@empid,@ename,@salary);
END

EXECUTE prc_InsertEmpData 7,'Yogesh','520000';


--CREATING A STORED PROCEDURE FOR UPDATING DATA FROM DATABASE TABLE
CREATE PROCEDURE prc_UpdateEmpData
@empid INT,
@ename nvarchar(50),
@salary DECIMAL(18,2)
AS
BEGIN
UPDATE Employee SET ename=@ename,salary=@salary WHERE empid=@empid;
END

EXECUTE prc_UpdateEmpData 1,'Ajay Patel','550000';


--CREATING A STORED PROCEDURE FOR DELETING DATA FROM DATABASE TABLE
CREATE PROCEDURE prc_DeleteEmpData
@empid INT
AS
BEGIN
DELETE FROM Employee WHERE empid=@empid;
END


--CREATING A STORED PROCEDURE FOR SEARCHING DATA FROM DATABASE TABLE
CREATE PROCEDURE prc_SearchEmpData
@empid INT
AS
BEGIN
SELECT * FROM Employee WHERE empid=@empid;
END

EXECUTE prc_SearchEmpData 4;




Comments

Post a Comment

Popular posts from this blog

Create a User Registration Form in ASP.NET using SQL Server, Visual Studio 2022 & Bootstrap

Image
 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="userreg.aspx.cs" Inherits="Registration_Form.userreg" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title>User Registration</title>     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous"/>     <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script> </head> <body>     <div class="container">         <form class="row g-3 bg-light" runat="server" meth...
Read more

Create a Simple Login Form in ASP.NET using Visual Studio 2022

Image
 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="login.aspx.cs" Inherits="LoginFormASP.login" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <h1 align="center">Login Form</h1>     <form id="form1" runat="server">         <table align="center">             <tr>                 <td>Username</td>                 <td>                     <asp:TextBox ID="txtUser" runat="server"></asp:TextBox>                 </td>             </tr>             <tr>       ...
Read more

SqlCommand Class ADO.Net | ExecuteNonQuery | ExecuteReader | ExecuteScalar

Image
 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace SQLConnectionADO {     internal class Program     {         static void Main(string[] args)         {             Program.Connection();             Console.ReadLine();         }         static void Connection()         {             string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;             SqlConnection con = null;             try             {                 using (con = new SqlConnection(cs))       ...
Read more