LINQ

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.

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

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:

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:

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:

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.

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:

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.