Insert Update Delete Data in MS SQL DB using ASP.NET | Search Enabled Gridview using Datatables Library
In this ASP.NET Database tutorial we will perform Select, Insert, Update & Delete operations using C# programming. We will also create a search enabled gridview in ASP.NET by applying the datatables jquery library on it.
We will insert/update/delete Author & publisher data in this tutorial.
Watch the video tutorial at the end of this video to understand more about this tutorial. Use the code given below for this page.
Download all Images for this project – CLICK HERE
Site1.Master code –
<%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Site1.master.cs" Inherits="WebApplication3.Site1" %> <!DOCTYPE html> <html> <head runat="server"> <title></title> <%--bootstrap css--%> <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" /> <%--datatables css--%> <link href="datatables/css/jquery.dataTables.min.css" rel="stylesheet" /> <%--fontawesome css--%> <link href="fontawesome/css/all.css" rel="stylesheet" /> <%--our custom css--%> <link href="css/customstylesheet.css" rel="stylesheet" /> <%--jquery--%> <script src="bootstrap/js/jquery-3.3.1.slim.min.js"></script> <%--popper js--%> <script src="bootstrap/js/popper.min.js"></script> <%--bootstrap js--%> <script src="bootstrap/js/bootstrap.min.js"></script> <%--Datatables js--%> <script src="datatables/js/jquery.dataTables.min.js"></script> <asp:ContentPlaceHolder ID="head" runat="server"> </asp:ContentPlaceHolder> </head> <body> <form id="form1" runat="server"> <div> <nav class="navbar navbar-expand-lg navbar-light"> <a class="navbar-brand" href="#"> <img src="imgs/books.png" width="30" height="30" /> E-Library </a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarSupportedContent"> <ul class="navbar-nav mr-auto"> <li class="nav-item active"> <a class="nav-link" href="homepage.aspx">Home</a> </li> <li class="nav-item active"> <a class="nav-link" href="#">About Us</a> </li> <li class="nav-item active"> <a class="nav-link" href="#">Terms</a> </li> </ul> <ul class="navbar-nav"> <li class="nav-item active"> <asp:LinkButton class="nav-link" ID="LinkButton4" runat="server" OnClick="LinkButton4_Click">View Books</asp:LinkButton> </li> <li class="nav-item active"> <asp:LinkButton class="nav-link" ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">User Login</asp:LinkButton> </li> <li class="nav-item active"> <asp:LinkButton class="nav-link" ID="LinkButton2" runat="server" OnClick="LinkButton2_Click">Sign Up</asp:LinkButton> </li> <li class="nav-item active"> <asp:LinkButton class="nav-link" ID="LinkButton3" runat="server" OnClick="LinkButton3_Click" Visible="False">Logout</asp:LinkButton> </li> <li class="nav-item active"> <asp:LinkButton class="nav-link" ID="LinkButton7" runat="server" OnClick="LinkButton7_Click" Visible="False">Hello user</asp:LinkButton> </li> </ul> </div> </nav> </div> <!-- Main Content Placeholder --> <div> <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server"> </asp:ContentPlaceHolder> </div> <!-- Main Content Placeholder --> <!-- Footer --> <footer> <div id="footer1" class="container-fluid"> <div class="row"> <div class="col-xs-12 col-sm-12 col-md-12 text-center"> <p> <asp:LinkButton class="footerlinks" ID="LinkButton6" runat="server" OnClick="LinkButton6_Click">Admin Login</asp:LinkButton> <asp:LinkButton class="footerlinks" ID="LinkButton11" runat="server" OnClick="LinkButton11_Click" Visible="False">Author Management</asp:LinkButton> <asp:LinkButton class="footerlinks" ID="LinkButton12" runat="server" OnClick="LinkButton12_Click" Visible="False">Publisher Management</asp:LinkButton> <asp:LinkButton class="footerlinks" ID="LinkButton8" runat="server" OnClick="LinkButton8_Click" Visible="False">Book Inventory</asp:LinkButton> <asp:LinkButton class="footerlinks" ID="LinkButton9" runat="server" OnClick="LinkButton9_Click" Visible="False">Book Issuing</asp:LinkButton> <asp:LinkButton class="footerlinks" ID="LinkButton10" runat="server" OnClick="LinkButton10_Click" Visible="False">Member Management</asp:LinkButton> </p> </div> </div> </div> <div id="footer2" class="container-fluid"> <div class="row"> <div class="col-xs-12 col-sm-12 col-md-12 text-center"> <p style="color:whitesmoke">© All right Reversed. <a class="footerlinks" href="#" target="_blank">Simple Snippets</a></p> </div> </div> </div> </footer> <!-- ./Footer --> </form> </body> </html>
Site1.master.cs code –
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication3 { public partial class Site1: System.Web.UI.MasterPage { protected void Page_Load(object sender, EventArgs e) { try { if (Session["role"].Equals("")) { LinkButton1.Visible = true; // user login link button LinkButton2.Visible = true; // sign up link button LinkButton3.Visible = false; // logout link button LinkButton7.Visible = false; // hello user link button LinkButton6.Visible = true; // admin login link button LinkButton11.Visible = false; // author management link button LinkButton12.Visible = false; // publisher management link button LinkButton8.Visible = false; // book inventory link button LinkButton9.Visible = false; // book issuing link button LinkButton10.Visible = false; // member management link button } else if (Session["role"].Equals("user")) { LinkButton1.Visible = false; // user login link button LinkButton2.Visible = false; // sign up link button LinkButton3.Visible = true; // logout link button LinkButton7.Visible = true; // hello user link button LinkButton7.Text = "Hello " + Session["username"].ToString(); LinkButton6.Visible = true; // admin login link button LinkButton11.Visible = false; // author management link button LinkButton12.Visible = false; // publisher management link button LinkButton8.Visible = false; // book inventory link button LinkButton9.Visible = false; // book issuing link button } else if (Session["role"].Equals("admin")) { LinkButton1.Visible = false; // user login link button LinkButton2.Visible = false; // sign up link button LinkButton3.Visible = true; // logout link button LinkButton7.Visible = true; // hello user link button LinkButton7.Text = "Hello Admin"; LinkButton6.Visible = false; // admin login link button LinkButton11.Visible = true; // author management link button LinkButton12.Visible = true; // publisher management link button LinkButton8.Visible = true; // book inventory link button LinkButton9.Visible = true; // book issuing link button } } catch (Exception ex) { } } protected void LinkButton6_Click(object sender, EventArgs e) { Response.Redirect("adminlogin.aspx"); } protected void LinkButton11_Click(object sender, EventArgs e) { Response.Redirect("adminauthormanagement.aspx"); } protected void LinkButton12_Click(object sender, EventArgs e) { Response.Redirect("adminpublishermanagement.aspx"); } protected void LinkButton8_Click(object sender, EventArgs e) { Response.Redirect("adminbookinventory.aspx"); } protected void LinkButton9_Click(object sender, EventArgs e) { Response.Redirect("adminbookissuing.aspx"); } protected void LinkButton10_Click(object sender, EventArgs e) { Response.Redirect("adminmembermanagement.aspx"); } protected void LinkButton4_Click(object sender, EventArgs e) { Response.Redirect("viewbooks.aspx"); } protected void LinkButton1_Click(object sender, EventArgs e) { Response.Redirect("userlogin.aspx"); } protected void LinkButton2_Click(object sender, EventArgs e) { Response.Redirect("usersignup.aspx"); } //logout button protected void LinkButton3_Click(object sender, EventArgs e) { Session["username"] = ""; Session["fullname"] = ""; Session["role"] = ""; Session["status"] = ""; LinkButton1.Visible = true; // user login link button LinkButton2.Visible = true; // sign up link button LinkButton3.Visible = false; // logout link button LinkButton7.Visible = false; // hello user link button LinkButton6.Visible = true; // admin login link button LinkButton11.Visible = false; // author management link button LinkButton12.Visible = false; // publisher management link button LinkButton8.Visible = false; // book inventory link button LinkButton9.Visible = false; // book issuing link button LinkButton10.Visible = false; // member management link button Response.Redirect("homepage.aspx"); } // view profile protected void LinkButton7_Click(object sender, EventArgs e) { } } }
adminauthormanagement.aspx code –
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="adminauthormanagement.aspx.cs" Inherits="WebApplication3.adminauthormanagement" %> <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server"> <script type="text/javascript"> $(document).ready(function () { //$(document).ready(function () { //$('.table').DataTable(); // }); $(".table").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable(); //$('.table1').DataTable(); }); </script> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <div class="container"> <div class="row"> <div class="col-md-5"> <div class="card"> <div class="card-body"> <div class="row"> <div class="col"> <center> <h4>Author Details</h4> </center> </div> </div> <div class="row"> <div class="col"> <center> <img width="100px" src="imgs/writer.png" /> </center> </div> </div> <div class="row"> <div class="col"> <hr> </div> </div> <div class="row"> <div class="col-md-4"> <label>Author ID</label> <div class="form-group"> <div class="input-group"> <asp:TextBox CssClass="form-control" ID="TextBox1" runat="server" placeholder="ID"></asp:TextBox> <asp:Button class="btn btn-primary" ID="Button1" runat="server" Text="Go" OnClick="Button1_Click" /> </div> </div> </div> <div class="col-md-8"> <label>Author Name</label> <div class="form-group"> <asp:TextBox CssClass="form-control" ID="TextBox2" runat="server" placeholder="Author Name"></asp:TextBox> </div> </div> </div> <div class="row"> <div class="col-4"> <asp:Button ID="Button2" class="btn btn-lg btn-block btn-success" runat="server" Text="Add" OnClick="Button2_Click" /> </div> <div class="col-4"> <asp:Button ID="Button3" class="btn btn-lg btn-block btn-warning" runat="server" Text="Update" OnClick="Button3_Click" /> </div> <div class="col-4"> <asp:Button ID="Button4" class="btn btn-lg btn-block btn-danger" runat="server" Text="Delete" OnClick="Button4_Click" /> </div> </div> </div> </div> <a href="homepage.aspx"><< Back to Home</a><br> <br> </div> <div class="col-md-7"> <div class="card"> <div class="card-body"> <div class="row"> <div class="col"> <center> <h4>Author List</h4> </center> </div> </div> <div class="row"> <div class="col"> <hr> </div> </div> <div class="row"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:elibraryDBConnectionString2 %>" SelectCommand="SELECT * FROM [author_master_tbl]"></asp:SqlDataSource> <div class="col"> <asp:GridView class="table table-striped table-bordered" ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="author_id" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="author_id" HeaderText="author_id" ReadOnly="True" SortExpression="author_id" /> <asp:BoundField DataField="author_name" HeaderText="author_name" SortExpression="author_name" /> </Columns> </asp:GridView> </div> </div> </div> </div> </div> </div> </div> </asp:Content>
adminauthormanagementt.aspx.cs code –
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication3 { public partial class adminauthormanagement: System.Web.UI.Page { string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { GridView1.DataBind(); } // add button click protected void Button2_Click(object sender, EventArgs e) { if (checkIfAuthorExists()) { Response.Write("<script>alert('Author with this ID already Exist. You cannot add another Author with the same Author ID');</script>"); } else { addNewAuthor(); } } // update button click protected void Button3_Click(object sender, EventArgs e) { if (checkIfAuthorExists()) { updateAuthor(); } else { Response.Write("<script>alert('Author does not exist');</script>"); } } // delete button click protected void Button4_Click(object sender, EventArgs e) { if (checkIfAuthorExists()) { deleteAuthor(); } else { Response.Write("<script>alert('Author does not exist');</script>"); } } // GO button click protected void Button1_Click(object sender, EventArgs e) { getAuthorByID(); } // user defined function void getAuthorByID() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("SELECT * from author_master_tbl where author_id='" + TextBox1.Text.Trim() + "';", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count >= 1) { TextBox2.Text = dt.Rows[0][1].ToString(); } else { Response.Write("<script>alert('Invalid Author ID');</script>"); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } void deleteAuthor() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("DELETE from author_master_tbl WHERE author_id='" + TextBox1.Text.Trim() + "'", con); cmd.ExecuteNonQuery(); con.Close(); Response.Write("<script>alert('Author Deleted Successfully');</script>"); clearForm(); GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } void updateAuthor() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("UPDATE author_master_tbl SET author_name=@author_name WHERE author_id='" + TextBox1.Text.Trim() + "'", con); cmd.Parameters.AddWithValue("@author_name", TextBox2.Text.Trim()); cmd.ExecuteNonQuery(); con.Close(); Response.Write("<script>alert('Author Updated Successfully');</script>"); clearForm(); GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } void addNewAuthor() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("INSERT INTO author_master_tbl(author_id,author_name) values(@author_id,@author_name)", con); cmd.Parameters.AddWithValue("@author_id", TextBox1.Text.Trim()); cmd.Parameters.AddWithValue("@author_name", TextBox2.Text.Trim()); cmd.ExecuteNonQuery(); con.Close(); Response.Write("<script>alert('Author added Successfully');</script>"); clearForm(); GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } bool checkIfAuthorExists() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("SELECT * from author_master_tbl where author_id='" + TextBox1.Text.Trim() + "';", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count >= 1) { return true; } else { return false; } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); return false; } } void clearForm() { TextBox1.Text = ""; TextBox2.Text = ""; } } }
adminpublishermanagement.aspx code –
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="adminpublishermanagement.aspx.cs" Inherits="WebApplication3.adminpublishermanagement" %> <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server"> <script type="text/javascript"> $(document).ready(function () { $(".table").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable(); }); </script> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <div class="container"> <div class="row"> <div class="col-md-5"> <div class="card"> <div class="card-body"> <div class="row"> <div class="col"> <center> <h4>Publisher Details</h4> </center> </div> </div> <div class="row"> <div class="col"> <center> <img width="100px" src="imgs/publisher.png" /> </center> </div> </div> <div class="row"> <div class="col"> <hr> </div> </div> <div class="row"> <div class="col-md-4"> <label>Publisher ID</label> <div class="form-group"> <div class="input-group"> <asp:TextBox CssClass="form-control" ID="TextBox1" runat="server" placeholder="ID"></asp:TextBox> <asp:Button class="btn btn-primary" ID="Button1" runat="server" Text="Go" OnClick="Button1_Click" /> </div> </div> </div> <div class="col-md-8"> <label>Publisher Name</label> <div class="form-group"> <asp:TextBox CssClass="form-control" ID="TextBox2" runat="server" placeholder="Publisher Name"></asp:TextBox> </div> </div> </div> <div class="row"> <div class="col-4"> <asp:Button ID="Button2" class="btn btn-lg btn-block btn-success" runat="server" Text="Add" OnClick="Button2_Click" /> </div> <div class="col-4"> <asp:Button ID="Button3" class="btn btn-lg btn-block btn-warning" runat="server" Text="Update" OnClick="Button3_Click" /> </div> <div class="col-4"> <asp:Button ID="Button4" class="btn btn-lg btn-block btn-danger" runat="server" Text="Delete" OnClick="Button4_Click" /> </div> </div> </div> </div> <a href="homepage.aspx"><< Back to Home</a><br> <br> </div> <div class="col-md-7"> <div class="card"> <div class="card-body"> <div class="row"> <div class="col"> <center> <h4>Publisher List</h4> </center> </div> </div> <div class="row"> <div class="col"> <hr> </div> </div> <div class="row"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:elibraryDBConnectionString %>" SelectCommand="SELECT * FROM [publisher_master_table]"></asp:SqlDataSource> <div class="col"> <asp:GridView class="table table-striped table-bordered" ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="publisher_id" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="publisher_id" HeaderText="publisher_id" ReadOnly="True" SortExpression="publisher_id" /> <asp:BoundField DataField="publisher_name" HeaderText="publisher_name" SortExpression="publisher_name" /> </Columns> </asp:GridView> </div> </div> </div> </div> </div> </div> </div> </asp:Content>
adminpublishermanagement.aspx.cs code –
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace WebApplication3 { public partial class adminpublishermanagement: System.Web.UI.Page { string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { GridView1.DataBind(); } // add publisher protected void Button2_Click(object sender, EventArgs e) { if (checkPublisherExists()) { Response.Write("<script>alert('Publisher Already Exist with this ID.');</script>"); } else { addNewPublisher(); } } // update publisher protected void Button3_Click(object sender, EventArgs e) { if (checkPublisherExists()) { updatePublisherByID(); } else { Response.Write("<script>alert('Publisher with this ID does not exist');</script>"); } } // delete publisher protected void Button4_Click(object sender, EventArgs e) { if (checkPublisherExists()) { deletePublisherByID(); } else { Response.Write("<script>alert('Publisher with this ID does not exist');</script>"); } } protected void Button1_Click(object sender, EventArgs e) { getPublisherByID(); } // user defined functions void getPublisherByID() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("SELECT * from publisher_master_table where publisher_id='" + TextBox1.Text.Trim() + "';", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count >= 1) { TextBox2.Text = dt.Rows[0][1].ToString(); } else { Response.Write("<script>alert('Publisher with this ID does not exist.');</script>"); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } bool checkPublisherExists() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("SELECT * from publisher_master_table where publisher_id='" + TextBox1.Text.Trim() + "';", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count >= 1) { return true; } else { return false; } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); return false; } } void addNewPublisher() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("INSERT INTO publisher_master_table(publisher_id,publisher_name) values(@publisher_id,@publisher_name)", con); cmd.Parameters.AddWithValue("@publisher_id", TextBox1.Text.Trim()); cmd.Parameters.AddWithValue("@publisher_name", TextBox2.Text.Trim()); cmd.ExecuteNonQuery(); con.Close(); Response.Write("<script>alert('Publisher added successfully.');</script>"); GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } public void updatePublisherByID() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("update publisher_master_table set publisher_name=@publisher_name WHERE publisher_id='" + TextBox1.Text.Trim() + "'", con); cmd.Parameters.AddWithValue("@publisher_name", TextBox2.Text.Trim()); int result = cmd.ExecuteNonQuery(); con.Close(); if (result > 0) { Response.Write("<script>alert('Publisher Updated Successfully');</script>"); GridView1.DataBind(); } else { Response.Write("<script>alert('Publisher ID does not Exist');</script>"); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } public void deletePublisherByID() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("Delete from publisher_master_table WHERE publisher_id='" + TextBox1.Text.Trim() + "'", con); int result = cmd.ExecuteNonQuery(); con.Close(); if (result > 0) { Response.Write("<script>alert('Publisher Deleted Successfully');</script>"); GridView1.DataBind(); } else { Response.Write("<script>alert('Publisher ID does not Exist');</script>"); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } } }