CRUD Operation In ASP.NET Web Form with Stored Procedure in C# ASP.NET

 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.Globalization;


namespace CRUD_StoredProcedure

{

    public partial class StudentInfo : System.Web.UI.Page

    {

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

        SqlConnection con;

        SqlCommand cmd;

        SqlDataAdapter adapter;

        DataTable dt;


        public void DataLoad()

        {

            if (Page.IsPostBack)

            {

                dgViewStudents.DataBind();

            }

        }


        public void ClearAllData()

        {

            txtName.Text = "";

            txtEmail.Text = "";

            txtDOB.Text = DateTime.Today.Date.ToString();

            ddlGender.SelectedValue = ddlGender.Items[0].ToString();

            chkBoxAgree.Checked = false;

            lblMessage.Text = "";

        }

        protected void Page_Load(object sender, EventArgs e)

        {


        }


        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

        {

            lblSID.Text = dgViewStudents.SelectedRow.Cells[1].Text;

            txtName.Text = dgViewStudents.SelectedRow.Cells[2].Text;

            txtEmail.Text = dgViewStudents.SelectedRow.Cells[3].Text;

            ddlGender.Text = dgViewStudents.SelectedRow.Cells[4].Text;

            txtDOB.Text = dgViewStudents.SelectedRow.Cells[5].Text;


        }


        protected void btnAdd_Click(object sender, EventArgs e)

        {

            try

            {

                if (txtName.Text != "" && txtEmail.Text != "" && chkBoxAgree.Checked)

                {

                    using (con = new SqlConnection(cs))

                    {

                        string name = txtName.Text;

                        string email = txtEmail.Text;

                        string gender = ddlGender.SelectedValue;

                        DateTime dt = DateTime.Parse(txtDOB.Text).Date;

                        string dateOnly = dt.ToString("MM-dd-yyyy");


                        con.Open();

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

                        cmd.CommandType = CommandType.StoredProcedure;

                        //cmd = new SqlCommand("INSERT INTO Students(Name,Email,Gender,BirthDate) VALUES(@Name,@Email,@Gender,@BirthDate)", con);


                        cmd.Parameters.AddWithValue("@Name", txtName.Text);

                        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);

                        cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);

                        cmd.Parameters.AddWithValue("@BirthDate", dateOnly);

                        cmd.ExecuteNonQuery();

                        DataLoad();

                        ClearAllData();

                    }

                }

                else

                {

                    lblMessage.Text = "Fill All Information";

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine("Exception: " + ex.ToString());

            }

        }


        protected void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

                if (txtName.Text != "" && txtEmail.Text != "" && chkBoxAgree.Checked)

                {

                    using (con = new SqlConnection(cs))

                    {

                        con.Open();

                        //cmd = new SqlCommand("UPDATE Students SET Name=@Name,Email=@Email,Gender=@Gender,BirthDate=@BirthDate WHERE StudentID=@StudentID", con);

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

                        cmd.CommandType = CommandType.StoredProcedure;


                        cmd.Parameters.AddWithValue("@Name", txtName.Text);

                        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);

                        cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);

                        cmd.Parameters.AddWithValue("@BirthDate", txtDOB.Text);

                        cmd.Parameters.AddWithValue("@StudentID", lblSID.Text);

                        cmd.ExecuteNonQuery();

                        DataLoad();

                        ClearAllData();

                    }

                }

                else

                {

                    lblMessage.Text = "Fill All Information";

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine("Exception: " + ex.ToString());

            }

        }


        protected void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

                using (con = new SqlConnection(cs))

                {

                    con.Open();

                    //cmd = new SqlCommand("DELETE FROM Students WHERE StudentID=@StudentID",con);

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

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@StudentID",lblSID.Text);

                    cmd.ExecuteNonQuery();

                    DataLoad();

                    ClearAllData();

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine("Exception: " + ex.ToString());

            }

        }


        protected void btnCancel_Click(object sender, EventArgs e)

        {

            ClearAllData();

        }

    }

}

Above File is CRUD_StoredProcedure\StudentInfo.aspx.cs











<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="StudentInfo.aspx.cs" Inherits="CRUD_StoredProcedure.StudentInfo" %>

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


    <br/><br/><br/>

    <table class="w-100">

        <tr>

            <td colspan="2">

                <asp:Label ID="IbITitle" runat="server" Text="Student Information"></asp:Label>

            </td>

            <td></td>

            <td></td>

        </tr>

        <tr>

            <td style="width: 245px">

                <asp:Label ID="IbIName" runat="server" Text="Name"></asp:Label>

            </td>

            <td>

                <asp:TextBox ID="txtName" runat="server" Width="223px"></asp:TextBox>

            </td>

            <td colspan="2" rowspan="8">

                <asp:GridView ID="dgViewStudents" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="StudentID" DataSourceID="SqlDataSource1" Height="218px" Width="419px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">

                    <Columns>

                        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />

                        <asp:BoundField DataField="StudentID" HeaderText="StudentID" InsertVisible="False" ReadOnly="True" SortExpression="StudentID" />

                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

                        <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />

                        <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />

                        <asp:BoundField DataField="BirthDate" HeaderText="BirthDate" SortExpression="BirthDate" DataFormatString="{0:MM/dd/yyyy}"/>

                    </Columns>

                </asp:GridView>

                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:StudentInformationConnectionString %>" DeleteCommand="DELETE FROM [Students] WHERE [StudentID] = @StudentID" InsertCommand="INSERT INTO [Students] ([Name], [Email], [Gender], [BirthDate]) VALUES (@Name, @Email, @Gender, @BirthDate)" ProviderName="<%$ ConnectionStrings:StudentInformationConnectionString.ProviderName %>" SelectCommand="SELECT * FROM [Students]" UpdateCommand="UPDATE [Students] SET [Name] = @Name, [Email] = @Email, [Gender] = @Gender, [BirthDate] = @BirthDate WHERE [StudentID] = @StudentID">

                    <DeleteParameters>

                        <asp:Parameter Name="StudentID" Type="Int32" />

                    </DeleteParameters>

                    <InsertParameters>

                        <asp:Parameter Name="Name" Type="String" />

                        <asp:Parameter Name="Email" Type="String" />

                        <asp:Parameter Name="Gender" Type="String" />

                        <asp:Parameter DbType="Date" Name="BirthDate" />

                    </InsertParameters>

                    <UpdateParameters>

                        <asp:Parameter Name="Name" Type="String" />

                        <asp:Parameter Name="Email" Type="String" />

                        <asp:Parameter Name="Gender" Type="String" />

                        <asp:Parameter DbType="Date" Name="BirthDate" />

                        <asp:Parameter Name="StudentID" Type="Int32" />

                    </UpdateParameters>

                </asp:SqlDataSource>

            </td>

        </tr>

        <tr>

            <td style="width: 245px">

                <asp:Label ID="IbIEmail" runat="server" Text="Email"></asp:Label>

            </td>

            <td>

                <asp:TextBox ID="txtEmail" runat="server" Width="223px"></asp:TextBox>

            </td>

        </tr>

        <tr>

            <td style="width: 245px">

                <asp:Label ID="IbIGender" runat="server" Text="Gender"></asp:Label>

            </td>

            <td>

                <asp:DropDownList ID="ddlGender" runat="server">

                    <asp:ListItem>Male</asp:ListItem>

                    <asp:ListItem>Female</asp:ListItem>

                    <asp:ListItem>Others</asp:ListItem>

                </asp:DropDownList>

            </td>

        </tr>

        <tr>

            <td style="width: 245px">

                <asp:Label ID="IbIDOB" runat="server" Text="Date Of Birth"></asp:Label>

            </td>

            <td>

                <asp:TextBox ID="txtDOB" runat="server" Width="223px" TextMode="Date"></asp:TextBox>

            </td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td>

                <asp:CheckBox ID="chkBoxAgree" runat="server" Text="Yes, I Agree!"/>

                &nbsp;<asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>

            </td>

        </tr>

        <tr>

            <td style="width: 245px">

                <asp:Label ID="lblSID" runat="server" Text=""></asp:Label>

            </td>

            <td></td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td>

                <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click"/>

                &nbsp;<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click"/>

                &nbsp;<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click"/>

                &nbsp;<asp:Button ID="btnCancel" runat="server" Text="Cancel Operation" OnClick="btnCancel_Click"/>

            </td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td></td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td></td>

            <td></td>

            <td></td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td></td>

            <td></td>

            <td></td>

        </tr>

        <tr>

            <td style="width: 245px"></td>

            <td></td>

            <td></td>

            <td></td>

        </tr>

    </table>

</asp:Content>

Above File is CRUD_StoredProcedure\StudentInfo.aspx


















<?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="dbCon" connectionString="Data Source=HEER\SQLEXPRESS;Initial Catalog=StudentInformation;Integrated Security=True;Encrypt=False"
    providerName="System.Data.SqlClient" />
  <add name="StudentInformationConnectionString" connectionString="Data Source=HEER\SQLEXPRESS;Initial Catalog=StudentInformation;Integrated Security=True;Encrypt=False"
    providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Above File is CRUD_StoredProcedure\Web.config
























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