santosh-asp tutorial

Insert, Update, Delete and Search by Linq with User Store Procedure

There are very simple steps for Insert, Update, Delete and Search by Linq with User Store Procedure in ASP.NET

Step 1:

We are using SQL SERVER database, so we create table tblStudent and four user store procedure

  

CREATE DATABASE dbSantoshTest

use dbSantoshTest

CREATE TABLE tblStudent
(
Name varachar(100), Rollno int Primary key, Branch varchar(50)
)

--Create User Store Procedure
---InserttblStudent---
CREATE PROC USP_InserttblStudent  
@Name varchar(100),@Rollno int, @Branch varchar(50),@Status varchar(50) output  
  
AS  
BEGIN  
 begin try  
  begin tran  
 Insert into tblStudent values(@Name,@Rollno,@Branch)  
 set @Status='Student record has been inserted!'  
 commit tran  
 end try  
   
 begin catch  
 set @Status='Student record has not been inserted!'  
 rollback  
 end catch  
  
END


----UpdateStudent----

CREATE PROC USP_UpdateStudent
@Name varchar(100),@Rollno int,@Branch varchar(50),@Status varchar(50) output

AS
BEGIN

 begin try
  begin tran
  Update tblStudent set Name=@Name,Branch=@Branch where Rollno=@Rollno
  
  set @Status='Student record has been updated!.'
  commit tran
 end try
 
 begin catch
 set @Status='Student record has not been updated!.'
 rollback
 end catch
 
END

----DeleteStudent------

CREATE PROC USP_DeleteStudent
@Rollno int,@Status varchar(50) output
AS
BEGIN
  begin try
   begin tran
   Delete from tblStudent where Rollno=@Rollno
   set @Status='Student record has been deleted!.'
   commit tran
   
  end try
  
  begin catch
   set @Status='Student record has not been deleted!.'
   rollback
  end   catch
 
 
END


-----GetStudent-----
CREATE PROC USP_GetStudent
AS
BEGIN
  Select * from tblStudent
END

Step 2:

Create application and give name LinqTestStoreProcedure as shown below:-

Step 3:

Now, Go to Solution Explorer and Right Click on LinqTestStoreProcedure -->Add-->Add New Item-->Dialog Box open and Select Webform and give name Default.aspx as shown in below:-

Step 4:

Now, Go to Solution Explorer and Right Click on LinqTestStoreProcedure -->Add-->Add New Item-->Dialog Box open and Select Linq to SQL SERVER and give name LinqTestStoreProcedureDataClasses as shown in below:-

Step 5:

Now, Go to Server Explorer-->Stored Procedures and Drag and drop four Stored Procedures to LinqTestStoreProcedureDataClasses.dbml file as shown in below:-

Step 6:

Now, Go to Default.aspx page and write the following code:-

  

<%@ 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>
        #div {
            background-color:#0094ff;
            color:white;
        }

    </style>
</head>
<body>
    <form id="form1" runat="server">
        <center> 
    <div id="div">
        <h1>Insert, Update, Delete using stored procedures in LINQ to SQL</h1>
    <table style="height: 311px; background-color: #FFCC66; color: #000000;">
        <tr> <td colspan="2"> <asp:Label ID="lblMessage" runat="server" Font-Bold="True" ForeColor="Black"></asp:Label></td></tr>
        <tr> <td>Name </td> <td> <asp:TextBox ID="txtName" runat="server"></asp:TextBox> </td> </tr>
        <tr> <td>Roll Number </td> <td> <asp:TextBox ID="txtRollno" runat="server"></asp:TextBox> </td> </tr>
        <tr> <td>Branch </td> <td> <asp:TextBox ID="txtBranch" runat="server"></asp:TextBox> </td> </tr>
        <tr> <td> <asp:Button ID="btnInsert" runat="server" Text="Insert"  Width="150px" Height="30px" OnClick="btnInsert_Click"/> </td> <td>  <asp:Button ID="btnUpdate" runat="server" Text="Update"  Width="150px" Height="30px" OnClick="btnUpdate_Click"/> </td></tr>
        <tr> <td> <asp:Button ID="btnDelete" runat="server" Text="Delete"  Width="150px" Height="30px" OnClick="btnDelete_Click"/> </td> <td>  <asp:Button ID="btnSearch" runat="server" Text="Search"  Width="150px" Height="30px" OnClick="btnSearch_Click"/> </td></tr>
        <tr> <td colspan="2"><asp:Button ID="btnClear" runat="server" Text="Clear"  Width="316px" Height="30px" OnClick="btnClear_Click"/> </td></tr>
         </table>
         <br /> <br />
            <asp:GridView ID="gvDetail" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                <SortedAscendingCellStyle BackColor="#FDF5AC" />
                <SortedAscendingHeaderStyle BackColor="#4D0000" />
                <SortedDescendingCellStyle BackColor="#FCF6C0" />
                <SortedDescendingHeaderStyle BackColor="#820000" />
        </asp:GridView>
         <br /> <br />
    </div>
           

            </center>
    </form>
</body>
</html>

Step 6:

Now, Go to Default.aspx.cs page and write the following code:-

  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;


public partial class _Default : System.Web.UI.Page
{
    string Status;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    //check text box field is empty code
    public bool validate()
    {
        lblMessage.ForeColor = Color.Red;
        if (txtName.Text == "")
        {
            lblMessage.Text = "Name field should not be blank.";
            return false;
        }

        if (txtRollno.Text == "")
        {
            lblMessage.Text = "Roll number field should not be blank.";
            return false;
        }

        if (txtBranch.Text == "")
        {
            lblMessage.Text = "Branch field should not be blank.";
            return false;
        }

        return true;
    }


    //Insert  code
    private void Insert()
    {
        try
        {
            LinqTestStoreProcedureDataClassesDataContext objDC = new LinqTestStoreProcedureDataClassesDataContext();

            objDC.USP_InserttblStudent(txtName.Text, Convert.ToInt32(txtRollno.Text), txtBranch.Text, ref Status);
            lblMessage.Text = Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = ex.Message;
        }
    }

    //Update  code
    private void Update()
    {
        try
        {
            LinqTestStoreProcedureDataClassesDataContext objDC = new LinqTestStoreProcedureDataClassesDataContext();

            objDC.USP_UpdateStudent(txtName.Text, Convert.ToInt32(txtRollno.Text), txtBranch.Text, ref Status);
            lblMessage.Text = Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = ex.Message;
        }
    }

    //Delete  code
    private void Delete()
    {
        try
        {
            LinqTestStoreProcedureDataClassesDataContext objDC = new LinqTestStoreProcedureDataClassesDataContext();

            objDC.USP_DeleteStudent(Convert.ToInt32(txtRollno.Text), ref Status);
            lblMessage.Text = Status;
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = ex.Message;
        }
    }

    //GetStudent  code
    private void GetStudentRecord()
    {
        LinqTestStoreProcedureDataClassesDataContext objDC = new LinqTestStoreProcedureDataClassesDataContext();
        gvDetail.DataSource = objDC.USP_GetStudent();
        gvDetail.DataBind();

        
    }

    //Clear textbox   code
    protected void Clear()
    {
        txtName.Text = "";
        txtRollno.Text = "";
        txtBranch.Text = "";
    }


    protected void btnInsert_Click(object sender, EventArgs e)
    {
        if (validate() == false)
        {
            return;
        }
        Insert();
        GetStudentRecord();
        Clear();
    }


    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        if (validate() == false)
        {
            return;
        }
        Update();
        GetStudentRecord();
        Clear();
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (txtRollno.Text == "")
        {
            lblMessage.ForeColor = Color.Red;
            lblMessage.Text = "Roll number field should not be blank.";
            return;
        }
        Delete();
        GetStudentRecord();
        Clear();
    }

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

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

Now, run the application and perform operations.