santosh-asp tutorial

Insert, Update, Delete and Search by Linq

There are some steps and code for insert, update, delete and search by LINQ in SQL SERVER database in ASP.NET:-

Step 1:- We are using SQL SERVER database. So, we need to create database and table in SQL SERVER as following code:-

  

CREATE DATABASE dbSantoshTest

USE dbSantoshTest

CREATE TABLE tblLinqTest
(
Name varchar(200),Rollno int Primary key,Contactno bigint,Address nvarchar(max)
)

Step 2:-

First of all we create an application and give name it LinqTest as shown in below image:-

Step 3:-

Now, Go to Solution Explorer and Right click on LinqTest and go to add new item and select Webform and give name as Default.aspx page as shown in below image:-

Step 4:-

Now, add following code in Default.aspx page:-

  

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style1 {
            width: 128px;
        }
        #div {
            background-color:#0094ff;
            color:white;
            font-family:Verdana;
            font-size:15px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div id="div">
        <center> 
        <h3>Insert, Update, Delete and Search Operation in SQL Server By LINQ:-</h3>
    <table style="height: 246px">
        <tr> <td colspan="2"> <asp:Label ID="lblMessage" runat="server" Font-Bold="true"></asp:Label> </td></tr>
        <tr> <td>Name </td> <td class="auto-style1"><asp:TextBox ID="txtName" runat="server"></asp:TextBox> </td></tr>
        <tr> <td>Roll Number </td> <td class="auto-style1"><asp:TextBox ID="txtRollno" runat="server" MaxLength="10"></asp:TextBox> </td></tr>
        <tr> <td>Contact Number </td> <td class="auto-style1"><asp:TextBox ID="txtContactNo" runat="server" MaxLength="10"></asp:TextBox> </td></tr>
        <tr> <td>Address </td> <td class="auto-style1"> <asp:TextBox ID="txtAddress" runat="server" TextMode="MultiLine"></asp:TextBox></td></tr>
        <tr> <td><asp:Button ID="btnInsert" runat="server" Text="Insert" Width="150px" Font-Bold="true" Height="30px" OnClick="btnInsert_Click" />  </td> <td class="auto-style1"><asp:Button ID="btnUpdate" runat="server" Text="Update" Width="150px" Font-Bold="true" Height="30px" OnClick="btnUpdate_Click"/> </td></tr>
        <tr> <td><asp:Button ID="btnDelete" runat="server" Text="Delete" Width="150px" Font-Bold="true" Height="30px" OnClick="btnDelete_Click" />  </td> <td class="auto-style1"><asp:Button ID="btnSearch" runat="server" Text="Search" Width="150px" Font-Bold="true" Height="30px" OnClick="btnSearch_Click"/> </td></tr>
        <tr> <td colspan="2"><asp:Button ID="btnClear" runat="server" Text="Clear" Width="300px" Height="30px" Font-Bold="true" /> </td></tr>
    </table>
            <br /> <br />
            <asp:GridView ID="gvDetail" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
                <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
                <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
                <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
                <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#FFF1D4" />
                <SortedAscendingHeaderStyle BackColor="#B95C30" />
                <SortedDescendingCellStyle BackColor="#F1E5CE" />
                <SortedDescendingHeaderStyle BackColor="#93451F" />
            </asp:GridView>
            </center>
    </div>
    </form>
</body>
</html>

Step 5:-

Now, Go to Solution Explorer and Right click on LinqTest and go to add new item and select LINQ to SQL Classes and give name as LinqTestDataClasses.dbml as shown in below image:-

Step 6:-

Now, Go to Server Explorer and drag and drop tblLinqTest table on LinqTestDataClasses.dbml as shown in below image.

Now, our application look like this:-

and connection string in Web.config file is as shown in below:-

  

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="dbSantoshTestConnectionString" connectionString="Data Source=PS-PC\SANTOSH;Initial Catalog=dbSantoshTest;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
      </assemblies>
    </compilation>
  </system.web>
</configuration>

Step 7:-

Now, Go to Default.aspx.cs page and add following code in our project:-

  

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

public partial class _Default : System.Web.UI.Page
{
    //Connection String called from Wec.config 
    string CS = ConfigurationManager.ConnectionStrings["dbSantoshTestConnectionString"].ConnectionString;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadRecord();
        }

    }

    //Bind the Gridview code
    private void LoadRecord()
    {

        LinqTestDataClassesDataContext objDC = new LinqTestDataClassesDataContext();
        var query = from data in objDC.GetTable<tblLinqTest>() select data;
        gvDetail.DataSource = query;
        gvDetail.DataBind();
    }

    //Insert method code
    public void Insert()
    {
        try
        {
            LinqTestDataClassesDataContext objDC = new LinqTestDataClassesDataContext();
            tblLinqTest objtblLinqTest = new tblLinqTest();
            objtblLinqTest.Name = txtName.Text;
            objtblLinqTest.Rollno = Convert.ToInt32(txtRollno.Text);
            objtblLinqTest.Contactno = Convert.ToInt64(txtContactNo.Text);
            objtblLinqTest.Address = txtAddress.Text;
            objDC.tblLinqTests.InsertOnSubmit(objtblLinqTest);
            objDC.SubmitChanges();
            LoadRecord();
            Clear();
            lblMessage.ForeColor = Color.Black;
            lblMessage.Text = "Record Is Inserted!";

        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
            LoadRecord();
            Clear();
        }
    }


    //Update method code
    private void Update()
    {
        try
        {
            LinqTestDataClassesDataContext objDC = new LinqTestDataClassesDataContext();
            tblLinqTest objtblLinqTest = objDC.tblLinqTests.Single(tblLinqTest => tblLinqTest.Rollno == Convert.ToInt32(txtRollno.Text));
            objtblLinqTest.Contactno = Convert.ToInt32(txtContactNo.Text);
            objtblLinqTest.Name = txtName.Text;
            objtblLinqTest.Address = txtAddress.Text;
            lblMessage.ForeColor = Color.Black;
            lblMessage.Text = "Record is updated!";
           
            LoadRecord();

        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }


    //Delete method code
    private void Delete()
    {
        try
        {
            LinqTestDataClassesDataContext objDC = new LinqTestDataClassesDataContext();
            tblLinqTest objtblLinqTest = objDC.tblLinqTests.Single(tblLinqTest => tblLinqTest.Rollno == Convert.ToInt32(txtRollno.Text));
            objDC.tblLinqTests.DeleteOnSubmit(objtblLinqTest);
            objDC.SubmitChanges();
            lblMessage.ForeColor = Color.Black;
            lblMessage.Text = "Record is deleted!";
            LoadRecord();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }
       
    //Clear textbox fields code
    private void Clear()
    {
        txtName.Text = "";
        txtRollno.Text = "";
        txtContactNo.Text = "";
        txtAddress.Text = "";
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        Insert();
        LoadRecord();
        Clear();
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        Update();
        LoadRecord();
        Clear();
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        Delete();
        LoadRecord();
        Clear();
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        LoadRecord();
        Clear();
    }
}

Now, run the application and Insert, Update, Delete and Search operation perform as shown in below:-