Sunday, 24 March 2013

How to implement Linq using asp.net c# with examples

Chapter - 1

What is LINQ?

Language Integrated Query (LINQ) is a common query programming model and syntax that can be used across all types of data i.e. databases, XML files and in-memory objects.


LINQ Providers:
  • LINQ To Objects
  • LINQ To ADO.Net
  • LINQ To Dataset
  • LINQ To SQL
  • LINQ To Entities
  • LINQ To XML
Steps to implement LINQ to SQL:


Step 1: Create Database "StudentDb"

Step 2: Create Table "Student"

In the student table set "StudentId" as the primary key and set the identity increment to 1 and also set the identity seed to 1; see:




Step 3

Start >> All Programs >> Microsoft Visual Studio 2010 >> Microsoft Visual Studio 2010

start- Microsoft-Visual-studio-2010.jpg

Step 4

File >> New >> Website

File-New-Website.jpg

Step 5

Select .Net framework 3.5 from the dropdown list and select "ASP.NET Empty Web Site" from the templates.

Select the location and give a proper name to your website.

open-asp.net-empty-website.jpg

Step 6

Right-click on the project. Inside the "Add ASP.NET Folder" select App_Code.

This will add an App_Code folder in your root directory.

Add-ASP.NET-Folder.jpg

Step 7

Right-click on the "App_Code" folder and select "Add New Item..."

Select "LINQ to SQL Classes" and give the name "JitendraDB.dbml".

Click on the "Add" button; see:

Select -LINQ -to-Sql-Classes.jpg

This will add a "JitendraDB.dbml" file under the "App_Code" folder and opens the "Object Relational Designer". The Object Relational Designer allows us to visualize data classes in our code.

Step 8

In the Server Explorer, right-click on "Data Connections" and select "Add Connection...". 



Step 9

Select the Server name and enter the username and password for SQL Server authentication.

Select "JitendraDB" for the database name.

Press the "OK" button; see:



Step 10

Now we can explore our database "JitendraDB". From the Tables select the "Student" table and drag & drop it to "Object Relational Designer", as in:



This will add a connection string to your web.config file.

add-connection-string.jpg

Step 11

Right-click on the project file and select "Add New Item...".

Select "Web Form" from the templates.

Give the name as "CRUD-Operation-In-LINQ.aspx".

Press the "Add" button.

This will add a CRUD-Operation-In-LINQ.aspx page to your website's root directory.

Step 12

Now add the following code in your Default.aspx page under the "form" control:


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CRUD_OPERATION_LINQ.aspx.cs" Inherits="WebApplication1.LINQ.CRUD_OPERATION_LINQ" %>

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" DataKeyNames="CountryID" 
            Width="100%" AutoGenerateColumns="false" 
            GridLines="None" 
            AutoGenerateSelectButton="True" 
            onselectedindexchanging="GridView1_SelectedIndexChanging" 
            AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" onrowdeleting="GridView1_RowDeleting" 
           >
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="CountryName" HeaderText="CointryName" 
                    SortExpression="CountryName" />
                <asp:BoundField DataField="IsEnable" HeaderText="IsEnable" 
                    SortExpression="IsEnable" />
            </Columns>
            <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" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <SortedAscendingCellStyle BackColor="#FDF5AC" />
            <SortedAscendingHeaderStyle BackColor="#4D0000" />
            <SortedDescendingCellStyle BackColor="#FCF6C0" />
            <SortedDescendingHeaderStyle BackColor="#820000" />
        </asp:GridView>
    
    </div>
    <table class="style1">
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
                <asp:HiddenField ID="hdnId" runat="server" />
            </td>
        </tr>
        <tr>
            <td class="style2">
                Country Name</td>
            <td>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
            </td>
            <td>
                <asp:CheckBox ID="CheckBox1" runat="server" Text="IsEnable" />
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
                <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="ADD" />
                <asp:Button ID="Button2" runat="server" Text="Update" onclick="Button2_Click" />
                <asp:Button ID="Button3" runat="server" Text="Delete" />
                <asp:Button ID="Button4" runat="server" Text="Button" />
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
                <asp:Label ID="statusLabel" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
    </table>
    </form>
</body>
</html>



Step 13

Now add the following code to your Default.aspx.cs file:
 



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

namespace WebApplication1.LINQ
{
    public partial class CRUD_OPERATION_LINQ : System.Web.UI.Page
    {
        DataClasses1DataContext DD = new DataClasses1DataContext();
        int id;
        protected void Page_Load(object sender, EventArgs e)
        {
            Bind_GridView();
        }
        public void Bind_GridView()
        {
            var students = from student in DD.JP_Countries
                           select new
                           {
                               student.CountryID,
                               student.CountryName,
                               student.IsEnable,

                           };
            GridView1.DataSource = students;
            GridView1.DataBind();

        }

        protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {
            string id = GridView1.DataKeys[e.NewSelectedIndex].Value.ToString();
            hdnId.Value = id;
            DD = new DataClasses1DataContext();
            var singleStudent = DD.JP_Countries.Single(student => student.CountryID == Convert.ToInt32(id));
            TextBox1.Text = singleStudent.CountryName;
            CheckBox1.Checked = Convert.ToBoolean(singleStudent.IsEnable);
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                DD = new DataClasses1DataContext();
                JP_Country student = new JP_Country();
                //Student  = new Student();

                student.CountryName = TextBox1.Text;
                student.IsEnable = true;
                DD.JP_Countries.InsertOnSubmit(student);
                DD.SubmitChanges();






                statusLabel.Text = "Record Inserted Successfully.";
            }
            catch (Exception Err)
            {
                statusLabel.Text = Err.Message;
            }

            this.Bind_GridView();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            try
            {
            
                DD = new DataClasses1DataContext();
                var singleStudent = DD.JP_Countries.Single(student => student.CountryID == Convert.ToInt32(hdnId.Value));              
                 singleStudent.CountryName = TextBox1.Text;             
                DD.SubmitChanges();              
                statusLabel.Text = "Record Inserted Successfully.";
            }
            catch (Exception Err)
            {
                statusLabel.Text = Err.Message;
            }

            this.Bind_GridView();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            string id = GridView1.DataKeys[e.RowIndex].Value.ToString();
            DataClasses1DataContext DD = new DataClasses1DataContext();
           // StudentDbDataContext SDC = new StudentDbDataContext();
            var singleStudent = DD.JP_Countries.Single(student => student.CountryID == Convert.ToInt64(id));

         

            DD.JP_Countries.DeleteOnSubmit(singleStudent);
            DD.SubmitChanges();

            this.Bind_GridView();
        }
    }
}




Step 14

Now run your website.

No comments: