Complete CRUD Operation in Asp.Net C# With SQL Server Step by Step

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


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


    <div>

        <div style="font-size:x-large" align="center">Student Info Manage Form<br />

            <br />

        </div>

        <table class="w-100">

            <tr>

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

                <td style="width: 198px">Student ID</td>

                <td>

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

                </td>

            </tr>

            <tr>

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

                <td style="width: 198px">Student Name</td>

                <td>

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

                </td>

            </tr>

            <tr>

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

                <td style="width: 198px">Address</td>

                <td>

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

                        <asp:ListItem>USA</asp:ListItem>

                        <asp:ListItem>Canada</asp:ListItem>

                        <asp:ListItem>UK</asp:ListItem>

                    </asp:DropDownList>

                </td>

            </tr>

            <tr>

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

                <td style="width: 198px">Age</td>

                <td>

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

                </td>

            </tr>

            <tr>

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

                <td style="width: 198px">Contact</td>

                <td>

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

                </td>

            </tr>

            <tr>

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

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

                <td></td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td></td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td>

                    <asp:Button ID="Button1" runat="server" Text="Insert" ForeColor="White" Font-Bold="true" Font-Size="Medium" BorderColor="#9933FF" BackColor="#9933FF" Width="100px" OnClick="Button1_Click"/>

                    &nbsp;&nbsp;<asp:Button ID="Button2" runat="server" Text="Update" ForeColor="White" Font-Bold="true" Font-Size="Medium" BorderColor="#9933FF" BackColor="#9933FF" Width="100px" OnClick="Button2_Click"/>

                    &nbsp;&nbsp;<asp:Button ID="Button3" runat="server" Text="Delete" ForeColor="White" Font-Bold="true" Font-Size="Medium" BorderColor="#9933FF" BackColor="#9933FF" Width="100px" OnClick="Button3_Click" OnClientClick="return confirm('Are you sure to delete ?')"/>

                    &nbsp;&nbsp;<asp:Button ID="Button4" runat="server" Text="Search" ForeColor="White" Font-Bold="true" Font-Size="Medium" BorderColor="#9933FF" BackColor="#9933FF" Width="100px" OnClick="Button4_Click"/>

                    &nbsp;&nbsp;<asp:Button ID="Button5" runat="server" Text="Get" ForeColor="White" Font-Bold="true" Font-Size="Medium" BorderColor="#9933FF" BackColor="#9933FF" Width="100px" OnClick="Button5_Click"/>

                </td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td></td>

            </tr>

            <tr>

                <td></td>

                <td></td>

                <td>

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

                </td>

            </tr>

        </table>

    </div>



    <%--<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 CRUDTutorial\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;


namespace CRUDTutorial

{

    public partial class _Default : Page

    {

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

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                LoadRecord();

            }

        }


        protected void Button1_Click(object sender, EventArgs e)

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

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

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

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

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

                cmd.Parameters.AddWithValue("@Contact", TextBox4.Text);

                cmd.ExecuteNonQuery();

                con.Close();

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

                LoadRecord();

            }

            catch (Exception ex)

            {

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

            }

        }


        void LoadRecord()

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

                //SqlDataReader

                SqlDataReader dr = cmd.ExecuteReader();

                GridView1.DataSource = dr;

                GridView1.DataBind();

                con.Close();




                //SqlDataAdapter

                //SqlDataAdapter sda = new SqlDataAdapter(cmd);

                //DataTable dt = new DataTable();

                //sda.Fill(dt);

                //GridView1.DataSource = dt;

                //GridView1.DataBind();

                //con.Close();

            }

            catch (Exception ex)

            {

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

            }

        }


        protected void Button2_Click(object sender, EventArgs e)

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

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

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

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

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

                cmd.Parameters.AddWithValue("@Contact",TextBox4.Text);

                cmd.ExecuteNonQuery();

                con.Close();

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

                LoadRecord();

            }

            catch (Exception ex)

            {

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

            }

        }


        protected void Button3_Click(object sender, EventArgs e)

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

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

                cmd.ExecuteNonQuery();

                con.Close();

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

                LoadRecord();

            }

            catch (Exception ex)

            {

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

            }

        }


        protected void Button4_Click(object sender, EventArgs e)

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

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

                //SqlDataReader

                SqlDataReader dr = cmd.ExecuteReader();

                GridView1.DataSource = dr;

                GridView1.DataBind();

                con.Close();



                //SqlDataAdapter

                //SqlDataAdapter sda = new SqlDataAdapter(cmd);

                //DataTable dt = new DataTable();

                //sda.Fill(dt);

                //GridView1.DataSource = dt;

                //GridView1.DataBind();

                //con.Close();

            }

            catch (Exception ex)

            {

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

            }

        }


        protected void Button5_Click(object sender, EventArgs e)

        {

            try

            {

                SqlConnection con = new SqlConnection(cs);

                con.Open();

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

                cmd.CommandType = CommandType.StoredProcedure;

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

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    TextBox2.Text = dr.GetValue(1).ToString();

                    DropDownList1.SelectedValue = dr.GetValue(2).ToString();

                    TextBox3.Text = dr.GetValue(3).ToString();

                    TextBox4.Text = dr.GetValue(4).ToString();

                }

            }

            catch (Exception ex)

            {

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

            }

        }

    }

}

Above File is CRUDTutorial\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=ProgrammingDB;Integrated Security=True;Encrypt=False" providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

Above File is CRUDTutorial\Web.config













CREATE DATABASE ProgrammingDB;


USE ProgrammingDB;


CREATE TABLE StudentInfo_Tab(

StudentID INT NOT NULL PRIMARY KEY,

StudentName nvarchar(50) NOT NULL,

Address nvarchar(50) NOT NULL,

Age INT NOT NULL,

Contact nvarchar(50) NOT NULL

);


SELECT * FROM StudentInfo_Tab;


INSERT INTO StudentInfo_Tab(StudentID,StudentName,Address,Age,Contact)

VALUES(1,'Ajay','Ahmedabad',24,'8599632240'),

(2,'Parth','Ahmedabad',25,'5877463320'),

(3,'Ritesh','Ahmedabad',24,'5477965523'),

(4,'Prakash','Ahmedabad',26,'8599640012'),

(5,'Lalit','Ahmedabad',25,'9633201588');



--CREATING STORED PROCEDURE FOR SELECTING DATABASE TABLE DATA

CREATE PROCEDURE StudentInfo_SP

AS

BEGIN

SELECT * FROM StudentInfo_Tab;

END


EXECUTE StudentInfo_SP;



--CREATING STORED PROCEDURE FOR INSERTING DATA INTO DATABASE TABLE

CREATE PROCEDURE StudentInfoInsert_SP

@StudentID INT,

@StudentName nvarchar(50),

@Address nvarchar(50),

@Age INT,

@Contact nvarchar(50)

AS

BEGIN

INSERT INTO StudentInfo_Tab(StudentID,StudentName,Address,Age,Contact) VALUES(@StudentID,@StudentName,@Address,@Age,@Contact);

END


EXECUTE StudentInfoInsert_SP 6,'Ramesh','Ahmedabad',25,'8566947723';



--CREATING STORED PROCEDURE FOR UPDATING DATA FROM DATABASE TABLE

CREATE PROCEDURE StudentInfoUpdate_SP

@StudentID INT,

@StudentName nvarchar(50),

@Address nvarchar(50),

@Age INT,

@Contact nvarchar(50)

AS

BEGIN

UPDATE StudentInfo_Tab SET StudentName=@StudentName,Address=@Address,Age=@Age,Contact=@Contact WHERE StudentID=@StudentID;

END


EXECUTE StudentInfoUpdate_SP 4,'Prakash Patel','USA',32,'8566321104';



--CREATING STORED PROCEDURE FOR DELETING DATA FROM DATABASE TABLE

CREATE PROCEDURE StudentInfoDelete_SP

@StudentID INT

AS

BEGIN

DELETE FROM StudentInfo_Tab WHERE StudentID=@StudentID;

END


EXECUTE StudentInfoDelete_SP 10;



--CREATING STORED PROCEDURE FOR SEARCHING DATA FROM DATABASE TABLE

CREATE PROCEDURE StudentInfoSearch_SP

@StudentID INT

AS

BEGIN

SELECT * FROM StudentInfo_Tab WHERE StudentID=@StudentID;

END


EXECUTE StudentInfoSearch_SP 5;





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