CoverStory

LANGUAGES: C#

ASP.NET VERSIONS: 3.5

 

LINQed & Layered

Implement CRUD Operations with LINQ in Three-tier ASP.NET Applications

 

By Bilal Haidar

 

With the rise of LINQ (language-integrated query) technology, developers are having a lot of fun playing around with the new standard way of accessing data that can be stored either in the database, XML files, or even plain objects. However, when it comes to developing three-tier Web applications, the tricks and workarounds start to show up to integrate LINQ into such applications. Many steps must be taken when developing three-tier Web applications; by discussing the CRUD (create, read, update, and delete) operations and how they must be implemented by LINQ, this article will show you how you can develop such applications depending solely on LINQ.

 

Developing three-tier ASP.NET Web applications is usually composed of the User Interface (UI) layer, Business Logic Layer (BLL), and Data Access Layer (DAL). The UI layer usually contains the ASP.NET Web Forms and user controls; BLL contains all the validation code and business rules that govern the business; and, finally, DAL contains all the nasty detailed code of accessing the data store and applying the different functionalities.

 

In the past, developing a DAL was a hectic job for the developer because of the redundant code that must be written mainly to access the data store and perform operations. The same code would have been written several times in the same application with minor changes from one class to another. Several third-party software products were written that allow the developer to create templates to generate DAL classes to preserve the wasted time spent, but that came at the expense of learning the language used by that software, and this itself was an obstacle.

 

Then along came LINQ technology, which helps developers save time by generating the DAL in a matter of a few clicks. All you have to do is create a new DataContext class and drag all the tables you have in the database into it, or create new ones. Relations will be automatically created once tables are dragged to the surface of the DataContext. Strongly typed classes will be generated for each table in the dragged tables, and properties to reflect relations between the objects will be added.

 

Having explained briefly how LINQ will affect an application, it is clear that the focus is now moved to the BLL in three-tier architecture Web applications to make it work gently with LINQ DAL; this is what we ll do in this article, by showing you how to create a three-tier ASP.NET application to retrieve, update, delete, and add data related to customers.

 

References to LINQ

This article assumes a fair knowledge of using LINQ and it is not intended as a primer explaining LINQ. If you feel you need more information on LINQ, I recommend you check the following link to a compiled version of Scott Guthrie s articles on LINQ to SQL (in PDF book format): http://it-box.blogturk.net/2007/10/19/linq-to-sql-tutorial-series-by-scott-guthrie-pdf-book-format/.

 

Implement CRUD Operations

To start, let s create a new ASP.NET Web site using Visual Studio 2008. This ensures that LINQ is enabled by default inside the Web application. Once the Web site is created, add two sub-folders to the App_Code folder, as shown in Figure 1.

 

Figure 1: Add two sub-folders to the App_Code folder.

 

Creating Data Access Layer Classes

Assuming there is a database with a single table named Customers, we ll create a new LINQ to SQL class, then drag into it the single table present in the database. That s it; you re done with the DAL classes. At this point, all the required classes have been generated.

 

You might have different options here. One option is to have a single DataContext class to hold all the tables in your database; another option is to have a DataContext class for each related set of tables. Option one was implemented above; however, if you plan to go by the other option, make sure to completely separate the tables in each DataContext class this will make the DataContext function properly when it wants to track the objects and relations between them.

 

Creating Business Logic Layer Classes

Now that the DataContext class is created, it s time to create the BLL. We ll add a new class named CustomerManager. This class will hold all the methods required for managing a Customer, from retrieving all Customers to retrieving a single Customer, inserting a new Customer, and updating and deleting an old Customer record. This class will be based solely on the DataContext class that was generated in the section above. The first method to be discussed is GetCustomers (see Figure 2).

 

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]

 public static List<Customer> GetCustomers(int startRowIndex,

   int maximumRows)

 {

     List<Customer> customers = null;

     string key = "Customers_Customers_" + startRowIndex.ToString() +

        "_" + maximumRows.ToString();

     // If data already present in Cache, get them from there

     // else load the data from the database

     if (HttpContext.Current.Cache[key] != null)

     {

        customers = (List<Customer>)HttpContext.Current.Cache[key];

     }

     else

     {

        // Get all customers from the database

        // and by having custom paging using Skip/Take

        // Query Expressions in LINQ

        customers = (

          from

              c in BizObject.Context.Customers

          orderby

              c.CustomerID descending

          select

              c

        ).Skip(startRowIndex).Take(maximumRows).ToList();

        // Add the records retrieved to cache

        if (customers != null && customers.Count > 0)

          HttpContext.Current.Cache.Insert(key, customers, null,

             DateTime.Now.AddDays(1), TimeSpan.Zero);

     }

     return customers;

 }

Figure 2: The GetCustomers method.

 

The GetCustomers method has been designated with the DataObjectMethodAttribute with the Select type, so it will automatically be selected by the ObjectDataSource used later inside the UI layer. The method returns a list of Customer objects. A Customer object has been generated inside the DataContext class and includes all the columns present in the Customer data table.

 

As a good habit, it s worth making use of caching to improve the performance of three-tier architecture applications. Instead of accessing the database every time the customers data is to be retrieved, it s enough to retrieve the data for the first time and place it inside the Cache object. This way, the next time the UI layer asks for the same customers data, it ll be retrieved from the cache instead!

 

If the records were not in the cache, then the method executes a LINQ Query to select all customers from the data table, sort them by the CustomerID in a descending way, then apply custom paging using the Skip and Take methods added to LINQ. There is nothing special about this method except the LINQ Query.

 

The second method to be discussed is InsertCustomer (see Figure 3). The InsertCustomer method accepts as input an object of type Customer. The first step is creating a new instance of the DataContext class. If there is a need to compare this DataContext instance to the old way of developing three-tier architecture applications, it corresponds to an instance of the DAL object.

 

public static int InsertCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw new ArgumentException("Customer object is invalid");

   // Create a new instance of the DataContext

   CustomerDataContext db = BizObject.Context;

   // Create a new customer and add it to the DataContext

   // to be able to have the DataContext track changes on the object

    Customer localCustomer = new Customer {

           FirstName = customer.FirstName,

           LastName = customer.LastName,

           Email = customer.Email

   };

   // Add the item to the DataContext

   db.Customers.InsertOnSubmit(localCustomer);

   // Save the item to the database

   db.SubmitChanges();

   // Clear the cache for all cache keys that

   // start with customers_customer. This will include

   // the keys for all customers retrieved and

   // single customers too.

   BizObject.PurgeCacheItems("customers_customer");

   // Return ID of the new customer created

   return localCustomer.CustomerID;

}

Figure 3: The InsertCustomer method.

 

Notice that in the body of the method, a new instance of a Customer object is created. All the fields are mapped from the input parameter to the local instance.

 

After the local Customer instance is filled, it is added to the Customers table inside the DataContext instance object. Then a call to SubmitChanges against the DataContext instance object is done, which ensures the new customer information has been added to the database. At this stage, the DataContext internally will issue an INSERT SQL Statement to insert this new record into the database.

 

Creating a new instance of the Customer class might be misleading, and prompts a major question why wouldn t it be enough to directly add the input Customer instance into the Customers table of the DataContext class? The DataContext object, to be able to keep track of the Customer object s changes throughout the life of that object, the DataContext instance must be created before creating a new instance of the Customer class. This newly created object resides inside the context of the DataContext class. It needs to keep track of it, and that s why there is a need to create a new instance of the DataContext class first, then add the locally created Customer instance to the DataContext object. Once the object is added to the DataContext object, tracking that object starts, and, in this case, the DataContext object will notice that this is a new object and, upon hitting SubmitChanges, the new object will be inserted into the database.

 

The third method to be discussed is UpdateCustomer (see Figure 4). The UpdateCustomer method follows the same technique used by the InsertCustomer method. A new instance of the DataContext class is created, then a new instance of the Customer class is created. It might look more convincing as to why there is a need to create a local instance of the Customer class in this method when the following facts are highlighted:

• When the UI layer first requested that Customer object, it either used the GetCustomers or GetCustomerByID method to retrieve that object. Each method created its own DataContext instance in that specific HttpRequest. When updating the same Customer object, the old DataContext object is now dead.
• When it comes to updating an object that was created by a different DataContext object, the current DataContext object will not have any clue about that object, and it will not be able to notice any changes inside it, because it was not responsible for tracking it from the creation time; hence the need to create a new instance of the Customer class, set the Primary Key(s), set any property that has a role in the Concurrency Checking, then attach the new object to the new DataContext instance.
• Once the object is attached, updating all the other properties in the object can be done safely. Once all properties to be updated have been configured, a call to the DataContext s SubmitChanges is done and the changes will be reflected on the database immediately.

 

public static void UpdateCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw new ArgumentException("Customer object invalid");

   // Create a new instance of the DataContext

   CustomerDataContext db = BizObject.Context;

   // Create a new instance of the Customer object

   // Make sure to include Primary Key and the Keys

   // used by DataContext for concurrency checking

   Customer localCustomer = new Customer {

       CustomerID = customer.CustomerID,

       Email = customer.Email

   };

   // Attach the new object to the DataContext

   db.Customers.Attach(localCustomer);

   // Now update the object, after adding it to the

   // DataContext, changes will be tracked

   localCustomer.Age = customer.Age;

   // Check Concurrency Conflict

   try

   {

       // Now you can submit changes using the DataContxet

       db.SubmitChanges(ConflictMode.ContinueOnConflict);

   }

    catch (System.Data.Linq.ChangeConflictException ex)

   {

       foreach (ObjectChangeConflict occ in db.ChangeConflicts)

       {

           // All database values overwrite current values.

           occ.Resolve(RefreshMode.OverwriteCurrentValues);

       }

       db.SubmitChanges();

   }

   finally

   {

       // Dispose the DataContext object

       db.Dispose();

   }

   // Purge the Cache

   BizObject.PurgeCacheItems("customers_customer_" +

      customer.CustomerID.ToString());

   BizObject.PurgeCacheItems("customers_customers");

}

Figure 4: The UpdateCustomer method.

 

The key point here is to notice that the input parameter was created by another DataContext object, and, to make the current DataContext instance of this current HttpRequest aware of that object, it s a must to first attach the object to the new DataContext object, then update the required properties.

 

The fourth method to be discussed is DeleteCustomer (see Figure 5), which is similar to the UpdateCustomer method explained previously. The same concept applies here, with some changes.

 

public static void DeleteCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw new ArgumentException("Customer object invalid");

   // Create a new instance of the DataContext

   CustomerDataContext db = BizObject.Context;

   // Get the Customer from the database

   Customer localCustomer = (

       from

           c in db.Customers

       where

           c.CustomerID == customer.CustomerID

       select

           c

       ).FirstOrDefault();

   // Delete customer taken the CustomerID

   // of the customer retrieved, this way

   // changes will be tracked on

   db.Customers.DeleteOnSubmit(localCustomer);

   db.SubmitChanges();

   // Purge the Cache

   BizObject.PurgeCacheItems("customers_customer");

}

Figure 5: The DeleteCustomer method.

 

A new instance of the Customer class was retrieved from the database corresponding to the input parameter Customer object. The new instance could have been easily created from the input Customer object but this way, a check is done to make sure the record really exists in the database before deleting it!

 

Once the object is retrieved by the current DataContext instance, removing it from the Customers table using the Remove method would signal to the DataContext object that an object has to be deleted. This would not have been true if the input Customer object was directly removed from the Customers table; the reason is, again, the input Customer object was retrieved by another DataContext object, and there is no way for the current DataContext object to keep track of it but to create a local instance of the same record!

 

Creating User Interface Web Forms

Up till now, we ve detailed creating the BLL and the DAL based on LINQ. What s still missing is creating a UI layer to complete the three-tier architecture.

 

The UI layer we ll create is a very simple UI. The main goal is to show how the different layers in the application are interacting with each other in the presence of LINQ. That interaction has been shown between the BLL and DAL; now the interaction between the UI layer and BLL will be shown.

 

Create a simple ASPX page that shows a GridView listing all the customers in the database. The GridView is configured with an ObjectDataSource:

 

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"

   DataObjectTypeName="bhaidar.DAL.Customer"

   SelectMethod="GetCustomers"

   TypeName="bhaidar.BLL.CustomerManager" />

 

The DataObjectTypeName is bhaidar.DAL.Customer. This property could have been pointed to an object created in the BLL that represents a wrapper over the Customer classes auto-generated inside the DataContext class. However, for the sake of this article, the UI layer will be satisfied by the DAL Customer object.

 

Configuring the ObjectDataSource is the same as it used to be before the days of LINQ; nothing changes at all!

 

A FormView is also included to display a single customer, edit it, delete it, or create a new one. Figure 6 shows a snapshot of that Web Form when a customer record is selected in the GridView and details about it are shown in the FormView to be edited or deleted.

 

Figure 6: ASP.NET Web Form customer management.

 

Clicking the Edit link would cause the FormView to be changed to Edit Mode. If a new customer is to be added, clicking the New link would put the FormView in Insert Mode. And finally, if there s a need to delete an existing customer, clicking the Delete link will do so!

 

Conclusion

This article opens with a brief explanation of the new LINQ technology, three-tier ASP.NET applications, and references to important links on getting started with LINQ. A detailed explanation then illustrated how to create a new DAL based on LINQ, as well as how to create a BLL and integrate it with the LINQ DAL classes. Finally, we saw how to create a UI layer, with Web Forms and user controls that interact with the BLL.

 

Source code accompanying this article is available for download.

 

Bilal Haidar is a Microsoft MVP in ASP/ASP.NET (since 2004). He is an MCP, MCTS, MCPD, and MCT holder. He is a Telerik MVP and the Webmaster for the LebDev user group. Bilal is one of the top posters on the ASP.NET official forums, as well as a moderator. He is a senior software developer at CCC, a multinational construction company based in Athens, Greece. You can check his published articles on http://www.aspalliance.com and http://www.code-magazine.com. He runs his own blog at http://www.bhaidar.net. Contact him with any questions or for help at mailto:bhaidar@gmail.com.