Manage Data Insertions and Retrievals with LINQ –to-SQL


Today, let's perform simple light weighted operation using LINQ-to-SQL as retrieval and data insertion.

Let's display the data retrieved values using simple grid.

Let's pass on some LINQ queries to perform an expected operation.

We are now good to go and do some practical implementations. One Main Point to Remember Here is I have choosen LINQ-To-SQL with dataContext name as : DataClasses1DataContext

Code Toolbox Requirements:

  • Button, Label, 3 Textboxes and Grid View

Simple Query Logic for retrieval looks like this:

var data = new DataClasses1DataContext();
GridView1.DataSource = (from abc in data.Students orderby abc.Age select abc);
GridView1.DataBind();


Simple Query Logic for Insertion looks like this:

var data = new DataClasses1DataContext();

 var insertintostudent = new Student();

 insertintostudent.FirstName = TextBox1.Text;
 insertintostudent.LastName = TextBox2.Text;
 insertintostudent.Age = Convert.ToInt32(TextBox3.Text);
 
 data.Students.InsertOnSubmit(insertintostudent);


Complete Code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml">
  <head id="Head1" runat="server"> <title></title>
   </head> <body> <form id="form1" runat="server">
    <div> <center>
     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" onload="PageLoad"> <AlternatingRowStyle BackColor="White" />
      <Columns> <asp:BoundField HeaderText="PersonId" DataField="PersonId" ReadOnly="true"/>
       <asp:BoundField HeaderText="FirstName" DataField="FirstName"/>
        <asp:BoundField HeaderText="LastName" DataField="LastName" />
         <asp:BoundField HeaderText="Age" DataField="Age" /> </Columns>
          <EditRowStyle BackColor="#7C6F57" />
           <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
              <RowStyle BackColor="#E3EAEB" />
               <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F8FAFA" /> <SortedAscendingHeaderStyle BackColor="#246B61" />
                 <SortedDescendingCellStyle BackColor="#D4DFE1" />
                  <SortedDescendingHeaderStyle BackColor="#15524A" />
                  </asp:GridView></center>
                   <br />
                    <br />
                     <center>
                      <table>
                       <tr>
                        <td>
                        <asp:Label ID="Label1" runat="server" Text="Please Enter FirstName" Font-Bold="true" Font-Italic="true">
                        </asp:Label>
                        </td>
                        </tr>
                         <tr>
                         <td><asp:TextBox ID="TextBox1" runat="server">
                         </asp:TextBox></td></tr>
                          <tr>
                          <td>
                          <asp:Label ID="Label2" runat="server" Text="Please Enter LastName" Font-Bold="true" Font-Italic="true">
                          </asp:Label
>

                          </td>
                          </tr>
                           <tr>
                           <td>
                           <asp:TextBox ID="TextBox2" runat="server">
                           </asp:TextBox>
                           </td>
                           </tr>
                            <tr>
                             <td>
                              <asp:Label ID="Label3" runat="server" Text="Please Enter Age" Font-Bold="true" Font-Italic="true">
                              </asp:Label></td></tr> <tr><td><asp:TextBox ID="TextBox3" runat="server">
                              </asp:TextBox></td>
                               </tr>
                                <tr>
                                 <td>
                                  <asp:Button ID="Button1" runat="server" Text="Insert Values to DB" onclick="Button1Click" />
                                  </td></tr>
                                   </table>
                                   </center>
                                    <asp:Label ID="lbl" runat="server">
                                    </asp:Label>
                                     </div>
                                      </form>
                                       </body>
                                        </html
>

 Complete Code of WebForm1.aspx.cs looks like this:

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

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void PageLoad(object sender, EventArgs e)
        {
            Bind();
        }

        protected void Button1Click(object sender, EventArgs e)
        {

            var data = new DataClasses1DataContext();

            var insertintostudent = new Student();

            insertintostudent.FirstName = TextBox1.Text;
            insertintostudent.LastName = TextBox2.Text;
            insertintostudent.Age = Convert.ToInt32(TextBox3.Text);

            data.Students.InsertOnSubmit(insertintostudent);
            data.SubmitChanges();
            Bind();
            lbl.Text = "<center><b>Records Inserted</b><center>";
            lbl.ForeColor = System.Drawing.Color.Red;
            lbl.Visible = true;

            TextBox1.Text = "";
            TextBox2.Text = "";
            TextBox3.Text = "";
        }

        public void Bind()
        {
            var data = new DataClasses1DataContext();
            GridView1.DataSource = (from abc in data.Students orderby abc.Age select abc);
            GridView1.DataBind();
        }

    }
}


The Output of this application looks like this:

LINQ Retrieve and Insert.png


I hope this article is useful for you.