LINQ

• Download code - 130.58 KB
• Download Database - 202.87 KB

Introduction

Language INtegrated Query or LINQ changes the way you write your data driven applications. Previously developers used to think and code differently to access different data stores such as SQL server.The new LINQ based programming can take away the hassles involved while developing such applications. In this multi part series I am going to explain how LINQ capabilities can be used ASP.NET applications.

Using the code

1- Create an entity class for the table that you want to edit.

Assemblies and Namespaces

In this example you will be using LINQ to SQL features and hence you need to refer System.Data.LINQ assembly in your web site. This assembly provides two important namespaces:

System.Data.Linq

System.Data.Linq.Mapping

The former namespace provides classes that allow you to interact with database whereas the later namespace contains classes and attributes that are used to generate entity classes representing the tables.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;

/// <summary>
/// This class is used to deal with users table. 
/// </summary>

[Table(Name = "users")]
public class users
{
    [Column(Name = "ID",IsDbGenerated=true,IsPrimaryKey=true)]
    public int ID { get; set; }

    [Column(Name = "UserName", DbType = "nvarchar(50)")]
    public string UserName { get; set; }
    [Column(Name = "Password", DbType = "nvarchar(50)")]
    public string Password { get; set; }

    public users()
    {

    }
}

The public class users consists of three public properties . ID, UserName and Password . What makes this class special is the [Table] and the [Column] attributes. The [Table] attribute marked on the users class indicates that the underlying class represents an RDBMS table. The Name property of the [Table] attribute specifies the name of the database table. If your class name and table name are same you can very well skip the Name property. The property level [Column] attribute specifies that the underlying property is a table column. The [Column] attribute has several properties. Some of the important ones are listed below:

Name: Name of the table column

DbType: Data type of the table column (not .NET data type!)

IsPrimaryKey: Whether the column represents the primary key of the table

IsDbGenerated: Whether the column values are generated by the RDBMS automatically (e.g. Identity columns)

CanBeNull: Whether the column can be null

Storage: The name of the class field that stores the column data

Notice how in our example the ID is marked with IsDbGenerated and IsPrimaryKey properties. Also, observe how the Name and DbType properties are used.

This completes our users class. We used only three columns from the users table. You can add extra columns as per the requirement.

2-Create a strongly typed data context to expose the underlying tables

Any data driven application has some data source from which data is fed into the system. Your application needs a way to talk to this data source. In LINQ to SQL terminology a Data Context does the job of this communication. Programmatically a data context is a class that derives from DataContext base class. In order to create a strongly typed data context you need to derive a class from DataContext base class as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;
using System.Data.Linq;

/// <summary>
/// Summary description for trydb
/// </summary>
public class trydb : DataContext

{
    public trydb(string connectionstring)
        : base(connectionstring)
    {
    
    
    }
    public Table<users> users;
}

The trydb class inherits from DataContext base class. The custom data context class must provide a constructor with one parameter. The constructor accepts a database connection string and simply passes it to the base class. More interesting is, however, the Table of users objects. This is how you expose the previously mapped LINQ to SQL objects to the external world.

Develpoing Web Form

3- Design the default web form as shown below:

The web form consists of a textbox to filter records based on Username. The GridView displays the selected users records. Upon selecting an user record its details are populated in a DetailsView for editing. Once you design the web form switch to its code view. We will now write two helper methods - BindGridView() and BindDetailsView(). The BindGridView() method binds the GridView with the required records and is shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;
using System.Data.Linq;

    public void BindGridView(string criteria)
    {
        string connstr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        trydb tr = new trydb(connstr);
        IEnumerable<users> results;

        if (criteria == string.Empty)
        {
            results = tr.users.ToArray();
        }
        else
        { 
        results=(from c in tr.users where c.UserName.Contains(criteria)select c);

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

    }		

Notice The code creates an instance of trydb class (our strongly typed data context) and passes the database connection string to it. In order to select all the records from the users table you simply use the users Table<> from the trydb class. To fetch records matching some criteria you use C# language keywords (from - where - select) and find all the users whose username contains certain text. In both the results are collected in a generic IEnumerable collection. The BindGridView() method is called from Page_Load event and Button1_Click event as shown below:

protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         BindGridView(string.Empty);
     }
 }

protected void Button1_Click(object sender, EventArgs e)
  {
      BindGridView(TextBox1.Text);
  }

When you select a row from the GridView, you need to display the selected record in DetailsView for editing. This is done in another helper method - BindDetailsView()

 private void BindDetailedView()
{
    int ID =(int) GridView1.SelectedValue;
    string connstr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    trydb tr = new trydb(connstr);
    var result=from emp in tr.users
               where emp.ID == ID
               select emp;
    DetailsView1.DataSource = result;
    DetailsView1.DataBind();

}

The BindDetailsView() method is called from the SelectedIndexChanged event handler of the GridView.

 protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    int ID = (int)GridView1.SelectedValue;
    string connstr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    BindDetailedView();

}

Inserting, Updating and Deleting data

Now let's see how data manipulation works in LINQ to SQL. First of all we will discuss data insertion.

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
    string connstr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    trydb tr = new trydb(connstr);
    users emp = new users();
    emp.UserName = ((TextBox)DetailsView1.Rows[1].Cells[1].Controls[0]).Text;
    emp.Password = ((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text;
    tr.users.InsertOnSubmit(emp);
    tr.SubmitChanges();
    BindGridView(string.Empty);
    BindDetailedView();

}

Notice We created an instance of users class and set its username and password properties from the new values entered in the DetailsView. We then call InsertOnSubmit() method on the users Table to add a new element to it. Calling InsertOnSubmit() method simply inserts a new element to the Table. To actually add that row to the database we call SubmitChanges() method. The Update operation is similar to Insert with little change.

  protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
    string connstr=ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    trydb tr = new trydb(connstr);
     var result=from tt in tr.users where tt.ID == (int)DetailsView1.SelectedValue select tt;
     result.First().UserName =((TextBox) DetailsView1.Rows[1].Cells[1].Controls[0]).Text;
     result.First().UserName = ((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text;
     tr.SubmitChanges();
     BindGridView(string.Empty);
     BindDetailedView();

}

Here, we first find the row matching the ID to be updated. We then set its userName and password properties. Notice the use of First() method to return just the first element of the results. Finally, SubmitChanges() method is called as before. The Delete operation takes the same lines as that of insert and is illustrated below:

protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{
    string strconn = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    trydb tr = new trydb(strconn);
    var result = tr.users.Single(emp => emp.ID
        == (int)DetailsView1.SelectedValue);
    tr.users.DeleteOnSubmit(result);
    tr.SubmitChanges();
    BindGridView(string.Empty);
    BindDetailedView();

}

Here, we first find the user record to be deleted. Notice the use of Single() method to fetch a single element. The DeleteOnSubmit() method then deletes the user object from the Table. Finally, SubmitChanges() method propagates the changes to the database.

We should hanlde DetailesView modechanging event as its mode changes according to editing states.

protected void DetailsView1_ModeChanging1(object sender, DetailsViewModeEventArgs e)
  {
      DetailsView1.ChangeMode(e.NewMode);
      BindDetailedView();
  }

That's it! Run the web form and test its functionality.