ASP.NET

Use LINQ To SQL On Web Form

Here are the steps,

  1. Create an Empty Web Form Application with C#.

  2. Switch ON server explorer: By default Its ON and Visible at toolbox side on, if not then CTRL+W, L.

    Config

  3. Select Add connection to database or create a new SQL Server database from Server Explorer.

  4. Create a DBML file that is LINQ to SQL File.

    LINQ to SQL File

  5. Double click on DBML. I have given the following file name: MemberCDAC_DataClasses.DBML

    DBML

  6. Drag and Drop tblMembers on MemberCDAC_Database.DBML from Server Explorer

    Properties

  7. Click on MemberID column of tblMember and check property window for detail.
    Access: Public and Auto Generated Value : True.

    You can set setting in property window. If any changes in table, then again drag n drop or update manually. Personally, I suggest delete table link from DBML and again drag and drop.

    Table

  8. When you configure server connection, drag and drop table into DBML canvas then system will automatically add connection string in WEB.CONFIG file.

  9. Web.Config file contains the following: I have used WINDOWS authentication connectionstrings.
    1. <configuration>  
    2.     <connectionStrings>  
    3.         <add name="MemberCDACConnectionString" connectionString="Data Source=SAIBABA-PC\SAIBABA;Initial Catalog=MemberCDAC;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>  
    4.     <system.web>  
    5.         <compilation debug="true" targetFramework="4.5" />  
    6.         <httpRuntime targetFramework="4.5" /> </system.web>  
    7. </configuration>  
    Code

  10. For explaining LINQ 2 SQL, I have used a webform with the following controls  as in the following:

    Here's the aspx page.
    1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Linq2SQL_WebApp.WebForm1" %>  
    2.     <!DOCTYPE html>  
    3.     <html xmlns="http://www.w3.org/1999/xhtml">  
    4.   
    5.     <head runat="server">  
    6.         <title>Member Entry</title>  
    7.         <style type="text/css">  
    8. .auto-style1 {}  
    9.   
    10. .auto-style2 {  
    11.     width: 141px;  
    12.     height: 23px;  
    13. }  
    14.   
    15. .auto-style3 {  
    16.     height: 23px;  
    17. }  
    18.         </style>  
    19.     </head>  
    20.   
    21.     <body>  
    22.         <form id="form1" runat="server">  
    23.             <div>  
    24.                 <table style="width: 100%;">  
    25.                     <tr>  
    26.                         <td class="auto-style1">Member ID</td>  
    27.                         <td>  
    28.                             <asp:Label ID="lblMemberID" runat="server" Text="[MemberID]"></asp:Label>  
    29.                         </td>  
    30.                         <td> </td>  
    31.                     </tr>  
    32.                     <tr>  
    33.                         <td class="auto-style1">Name</td>  
    34.                         <td>  
    35.                             <asp:TextBox ID="txtName" runat="server" Width="265px"></asp:TextBox>  
    36.                         </td>  
    37.                         <td> </td>  
    38.                     </tr>  
    39.                     <tr>  
    40.                         <td class="auto-style1">Address</td>  
    41.                         <td>  
    42.                             <asp:TextBox ID="txtAddress" runat="server" Height="167px" TextMode="MultiLine" Width="265px"></asp:TextBox>  
    43.                         </td>  
    44.                         <td> </td>  
    45.                     </tr>  
    46.                     <tr>  
    47.                         <td class="auto-style1">Place</td>  
    48.                         <td>  
    49.                             <asp:TextBox ID="txtPlace" runat="server"></asp:TextBox>  
    50.                         </td>  
    51.                         <td> </td>  
    52.                     </tr>  
    53.                     <tr>  
    54.                         <td class="auto-style1">Join Date</td>  
    55.                         <td>  
    56.                             <asp:TextBox ID="txtJoinDate" runat="server"></asp:TextBox> [Format : dd/mm/yyyy] </td>  
    57.                         <td> </td>  
    58.                     </tr>  
    59.                     <tr>  
    60.                         <td class="auto-style2"></td>  
    61.                         <td class="auto-style3"></td>  
    62.                         <td class="auto-style3"></td>  
    63.                     </tr>  
    64.                     <tr>  
    65.                         <td class="auto-style1" colspan="3" style="text-align: left; padding-left: 150px">  
    66.                             <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" Width="150px" /> </td>  
    67.                     </tr>  
    68.                     <tr>  
    69.                         <td class="auto-style1"> </td>  
    70.                         <td> </td>  
    71.                         <td> </td>  
    72.                     </tr>  
    73.                     <tr>  
    74.                         <td class="auto-style1"> </td>  
    75.                         <td> </td>  
    76.                         <td> </td>  
    77.                     </tr>  
    78.                     <tr>  
    79.                         <td class="auto-style1" colspan="3">  
    80.                             <asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateSelectButton="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" AutoGenerateColumns="False" OnRowCommand="GridView1_RowCommand" OnRowDataBound="GridView1_RowDataBound" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">  
    81.                                 <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />  
    82.                                 <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />  
    83.                                 <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />  
    84.                                 <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />  
    85.                                 <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />  
    86.                                 <SortedAscendingCellStyle BackColor="#FFF1D4" />  
    87.                                 <SortedAscendingHeaderStyle BackColor="#B95C30" />  
    88.                                 <SortedDescendingCellStyle BackColor="#F1E5CE" />  
    89.                                 <SortedDescendingHeaderStyle BackColor="#93451F" />  
    90.                                 <Columns>  
    91.                                     <asp:TemplateField ShowHeader="False" ItemStyle-Width="50px">  
    92.                                         <ItemTemplate>  
    93.                                             <asp:LinkButton ID="btnSelect" runat="server" CausesValidation="false" CommandName="Select" Text="Select" /> </ItemTemplate>  
    94.                                     </asp:TemplateField>  
    95.                                     <asp:BoundField DataField="MemberID" HeaderText="Member ID" ItemStyle-Width="20px">  
    96.                                         <ItemStyle Width="20px"></ItemStyle>  
    97.                                     </asp:BoundField>  
    98.                                     <asp:BoundField DataField="Name" HeaderText="Member Name" ItemStyle-Width="300px">  
    99.                                         <ItemStyle Width="200px"></ItemStyle>  
    100.                                     </asp:BoundField>  
    101.                                     <asp:BoundField DataField="Address" HeaderText="Address" ItemStyle-Width="500px">  
    102.                                         <ItemStyle Width="350px"></ItemStyle>  
    103.                                     </asp:BoundField>  
    104.                                     <asp:BoundField DataField="Place" HeaderText="Place" ItemStyle-Width="100px">  
    105.                                         <ItemStyle Width="100px"></ItemStyle>  
    106.                                     </asp:BoundField>  
    107.                                     <asp:BoundField DataField="JoinDate" DataFormatString="{0:dd/MM/yyyy}" HeaderText="Join Date" ItemStyle-Width="100px">  
    108.                                         <ItemStyle Width="100px"></ItemStyle>  
    109.                                     </asp:BoundField>  
    110.                                     <asp:TemplateField ShowHeader="False" ItemStyle-Width="50px">  
    111.                                         <ItemTemplate>  
    112.                                             <asp:LinkButton ID="btnNew" runat="server" CausesValidation="false" CommandName="NewMember" Text="New" /> </ItemTemplate>  
    113.                                     </asp:TemplateField>  
    114.                                     <asp:TemplateField ShowHeader="False" ItemStyle-Width="50px">  
    115.                                         <ItemTemplate>  
    116.                                             <asp:LinkButton ID="btnDelete" runat="server" CausesValidation="false" CommandName="DeleteMember" Text="Delete" CommandArgument='<%# Container.DataItemIndex %>' /> </ItemTemplate>  
    117.                                     </asp:TemplateField>  
    118.                                 </Columns>  
    119.                             </asp:GridView>  
    120.                         </td>  
    121.                     </tr>  
    122.                 </table>  
    123.             </div>  
    124.         </form>  
    125.     </body>  
    126.   
    127.     </html>  
    Controls I have used:

    Control Type Control Used Description
    Label lblMemberID Member ID display from table.
    TextBox txtName Get and Set Member Name.
    TextBox txtAddress Get and Set Member Address.
    Change Following Property:
    TextMode : MultiLine
    TextBox txtPlace Get and Set Member Place.
    TextBox txtJoinDate Get and Set Member Join Date.
    Button btnSave Dynamically used and rename this button for
    Save : An new entry saved in table.
    Update : For update existing entry.
    GridView GridView1 GridView to display records and functional button for Select, New and Delete activities.
  11. The following LINQ 2 SQL queries used:
    1. var db = new MemberCDAC_DataClassesDataContext();  
    db is instance created of MemberCDAC_DataClassesDataContext.

    db take care of Select, Insert and Update and Delete activities.

    TYPE OF QUERY Query Text USED IN EVENT DESCRIPTION
    Select Query – Style 1 from a in db.tblMembers select a).ToList(); ( Created a BindGridView()
    method, which used for loand and refresh data from table.    		 GridView1.DataSource = (from a in db.tblMembers select a).ToList();
    GridView1.DataBind();
    GridView used this query directly as datasource.
    Select Query – Style 2 db.tblMembers.ToList(); Created a BindGridView()
    method, which used for loand and refresh data from table.     		GridView1.DataSource = db.tblMembers.ToList();
    GridView1.DataBind();
    Save Query //Insert new record in tblmembers
    db.tblMembers.InsertOnSubmit(NewMember);
    //Update table
    db.SubmitChanges();     		btnSave_Click Syntax:
    db.TableName.InsertOnSubmit(Class Object)

    Example:
    db.tblMembers.InsertOnSubmit(NewMember);
    above command insert a new row in table.

    db.SubmitChanges();
    This will save and update table.
    Update Query int SelectMemberID = Convert.ToInt16(lblMemberID.Text);
    var NewMember = (from a in db.tblMembers where a.MemberID == Convert.ToInt16(lblMemberID.Text) select a ).FirstOrDefault();

    NewMember.Name = txtName.Text;
    NewMember.address =txtAddress.Text;
    NewMember.place = txtPlace.Text;
    NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
    //Update table

    db.SubmitChanges();    		 btnSave_Click Syntax:
    For update command , first we have to create filled instance of Member object , then update current value from control like TextBox etc...

    var NewMember = (from a in db.tblMembers where a.MemberID == Convert.ToInt16(lblMemberID.Text) select a ).FirstOrDefault();

    NewMember.Name = txtName.Text;
    db.SubmitChanges();
    This will delete record from table.
    Delete Query db.tblMembers.DeleteOnSubmit(MemberDetail);
    db.SubmitChanges();     		GridView1_RowCommand Syntax:
    db.TableName.DeleteOnSubmit(Class Object)

    Example:
    db.tblMembers.DeleteOnSubmit(NewMember);
    above command will mark a delete row in table.

    db.SubmitChanges();
    This will delete record from table.

    db.SubmitChanges(): This command required to execute on after activity like Insert, Update, Delete. 

Recap Linq To SQL queries:

  • Fetching All Members Records
    1. (from a in db.tblMembers select a).ToList();  
  • Fetching a Particular Member Record
    1. (from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();  
  • Insert Query
    1. //Insert Blank Row in Table  
    2. db.tblMembers.InsertOnSubmit(NewMember);  
    3.   
    4. //Update table  
    5. db.SubmitChanges();  
    Note: NewMember : Filled Class object with Datas.

  • Update Query

    Firstly, you have to create object of member.
    1. Var NewMember =(from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();  
    2. NewMember.Name = txtName.Text;  
    3. NewMember.address = txtAddress.Text;  
    4. NewMember.place = txtPlace.Text;  
    5. NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);  
    6.   
    7. //Update table  
    8. db.SubmitChanges();  
  • Delete Query
    1. db.tblMembers.DeleteOnSubmit(MemberDetail);  
    2. db.SubmitChanges();  

Code Behind File for Your Reference

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Globalization;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.UI;  
  7. using System.Web.UI.WebControls;  
  8. namespace Linq2SQL_WebApp  
  9. {  
  10.     public partial class WebForm1: System.Web.UI.Page  
  11.     {  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {  
  14.             BindGridView();  
  15.         }  
  16.         protected void BindGridView()  
  17.         {  
  18.             var db = new MemberCDAC_DataClassesDataContext();  
  19.             //Using Linq query  
  20.             GridView1.DataSource = (from a in db.tblMembers select a).ToList();  
  21.             GridView1.DataBind();  
  22.             // OR  
  23.             //Direct Using ToList   
  24.             GridView1.DataSource = db.tblMembers.ToList();  
  25.             GridView1.DataBind();  
  26.         }  
  27.         protected void btnSave_Click(object sender, EventArgs e)  
  28.         {  
  29.             var db = new MemberCDAC_DataClassesDataContext();  
  30.             //Save for add new member record in table.  
  31.             if (btnSave.Text == "Save")  
  32.             {  
  33.                 //create tblmember instance.  
  34.                 tblMember NewMember = new tblMember();  
  35.                 NewMember.Name = txtName.Text;  
  36.                 NewMember.address = txtAddress.Text;  
  37.                 NewMember.place = txtPlace.Text;  
  38.                 NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);  
  39.                 //Insert new record in tblmembers  
  40.                 db.tblMembers.InsertOnSubmit(NewMember);  
  41.                 //Update table  
  42.                 db.SubmitChanges();  
  43.                 //empty text box value.  
  44.                 ClearTextBoxValue();  
  45.             }  
  46.             //Update for update existing member record in table.  
  47.             if (btnSave.Text == "Update")  
  48.             {  
  49.                 int SelectMemberID = Convert.ToInt16(lblMemberID.Text);  
  50.                 var NewMember = (from a in db.tblMembers where a.MemberID == Convert.ToInt16(lblMemberID.Text) select a).FirstOrDefault();  
  51.                 NewMember.Name = txtName.Text;  
  52.                 NewMember.address = txtAddress.Text;  
  53.                 NewMember.place = txtPlace.Text;  
  54.                 NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);  
  55.                 //Update table  
  56.                 db.SubmitChanges();  
  57.                 btnSave.Text = "Save";  
  58.                 ClearTextBoxValue();  
  59.             }  
  60.             //Refresh gridview after new or update member.  
  61.             BindGridView();  
  62.         }  
  63.         private void ClearTextBoxValue()  
  64.         {  
  65.             lblMemberID.Text = "[MemberID]";  
  66.             txtName.Text = string.Empty;  
  67.             txtAddress.Text = string.Empty;  
  68.             txtPlace.Text = string.Empty;  
  69.             txtJoinDate.Text = string.Empty;  
  70.         }  
  71.         protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)  
  72.         {}  
  73.         protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)  
  74.         {  
  75.             btnSave.Text = "Update";  
  76.             var db = new MemberCDAC_DataClassesDataContext();  
  77.             //Get MemberID from gridview   
  78.             int TMemberID = Convert.ToInt32(GridView1.SelectedRow.Cells[1].Text);  
  79.             //Fetch particular member record from table directly with MemberID.  
  80.             var MemberDetail = (from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();  
  81.             lblMemberID.Text = Convert.ToString(MemberDetail.MemberID);  
  82.             txtName.Text = MemberDetail.Name;  
  83.             txtAddress.Text = MemberDetail.address;  
  84.             txtPlace.Text = MemberDetail.place;  
  85.             txtJoinDate.Text = Convert.ToDateTime(MemberDetail.joindate).ToString("dd/MM/yyyy");  
  86.         }  
  87.         protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)  
  88.         {  
  89.             ClearTextBoxValue();  
  90.             if (e.CommandName == "NewMember")  
  91.             {  
  92.                 btnSave.Text = "Save";  
  93.                 ClearTextBoxValue();  
  94.                 txtName.Focus();  
  95.             }  
  96.             if (e.CommandName == "DeleteMember")  
  97.             {  
  98.                 int CurrentRowIndex = Convert.ToInt32(e.CommandArgument);  
  99.                 GridViewRow gvRow = GridView1.Rows[CurrentRowIndex];  
  100.                 var db = new MemberCDAC_DataClassesDataContext();  
  101.                 //Get MemberID from gridview   
  102.                 int TMemberID = Convert.ToInt32(gvRow.Cells[1].Text);  
  103.                 //Fetch particular member record from table directly with MemberID.  
  104.                 var MemberDetail = (from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();  
  105.                 db.tblMembers.DeleteOnSubmit(MemberDetail);  
  106.                 db.SubmitChanges();  
  107.                 txtName.Focus();  
  108.                 BindGridView();  
  109.             }  
  110.         }  
  111.     }  
  112. }  
SQL Table Script for Your Reference
  1. USE [MemberCDAC]  
  2. GO  
  3. /****** Object:  Table [dbo].[tblMembers]    Script Date: 11/29/2015 20:38:53 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[tblMembers](  
  9.     [MemberID] [int] IDENTITY(1,1) NOT NULL,  
  10.     [Name] [nvarchar](50) NULL,  
  11.     [address] [nvarchar](500) NULL,  
  12.     [place] [nvarchar](50) NULL,  
  13.     [joindate] [datetime] NULL,  
  14.  CONSTRAINT [PK_tblMembers] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [MemberID] ASC  
  17. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  18. ) ON [PRIMARY]  
WebApplication View

Output

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs