LINQed & Layered

CoverStory

LANGUAGES:C#

ASP.NETVERSIONS: 3.5

LINQed & Layered

Implement CRUD Operations with LINQ in Three-tierASP.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 ofaccessing data that can be stored either in the database, XML files, or evenplain objects. However, when it comes to developing three-tier Webapplications, the tricks and workarounds start to show up to integrate LINQinto such applications. Many steps must be taken when developing three-tier Webapplications; by discussing the CRUD (create, read, update, and delete) operationsand how they must be implemented by LINQ, this article will show you how youcan develop such applications depending solely on LINQ.

Developing three-tier ASP.NET Web applications is usuallycomposed of the User Interface (UI) layer, Business Logic Layer (BLL), and DataAccess Layer (DAL). The UI layer usually contains the ASP.NET Web Forms and usercontrols; BLL contains all the validation code and business rules that governthe business; and, finally, DAL contains all the nasty detailed code ofaccessing the data store and applying the different functionalities.

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

Then along came LINQ technology, which helps developerssave time by generating the DAL in a matter of a few clicks. All you have to dois create a new DataContext class and drag all the tables you have in thedatabase into it, or create new ones. Relations will be automatically createdonce tables are dragged to the surface of the DataContext. Strongly typedclasses will be generated for each table in the dragged tables, and propertiesto reflect relations between the objects will be added.

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

References to LINQ

This article assumes a fair knowledge of using LINQ and itis not intended as a primer explaining LINQ. If you feel you need moreinformation on LINQ, I recommend you check the following link to a compiledversion 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 VisualStudio 2008. This ensures that LINQ is enabled by default inside the Webapplication. Once the Web site is created, add two sub-folders to the App_Codefolder, as shown in Figure 1.

Figure 1: Add two sub-folders to theApp_Code folder.

Creating Data Access Layer Classes

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

You might have different options here. One option is tohave 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 thiswill make the DataContext function properly when it wants to track the objectsand relations between them.

Creating Business Logic Layer Classes

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

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]

 public static ListGetCustomers(int startRowIndex,

   int maximumRows)

 {

     Listcustomers = null;

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

        "_" + maximumRows.ToString();

     // If data alreadypresent in Cache, get them from there

     // else load the datafrom the database

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

     {

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

     }

     else

     {

        // Get allcustomers from the database

        // and by havingcustom paging using Skip/Take

        // QueryExpressions in LINQ

        customers = (

          from

              c inBizObject.Context.Customers

          orderby

              c.CustomerIDdescending

          select

              c

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

        // Add the recordsretrieved 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: TheGetCustomers method.

The GetCustomers method has been designated with theDataObjectMethodAttribute with the Select type, so it will automatically be selectedby the ObjectDataSource used later inside the UI layer. The method returns alist of Customer objects. A Customer object has been generated inside theDataContext class and includes all the columns present in the Customer datatable.

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

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

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

public static int InsertCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw newArgumentException("Customer object is invalid");

   // Create a newinstance of the DataContext

   CustomerDataContext db= BizObject.Context;

   // Create a new customerand add it to the DataContext

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

    Customer localCustomer = new Customer {

           FirstName =customer.FirstName,

           LastName =customer.LastName,

           Email =customer.Email

   };

   // Add the item to theDataContext

   db.Customers.InsertOnSubmit(localCustomer);

   // Save the item to thedatabase

   db.SubmitChanges();

   // Clear the cache forall cache keys that

   // start withcustomers_customer. This will include

   // the keys for allcustomers retrieved and

   // single customerstoo.

   BizObject.PurgeCacheItems("customers_customer");

   // Return ID of the newcustomer created

   returnlocalCustomer.CustomerID;

}

Figure 3: The InsertCustomermethod.

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

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

Creating a new instance of the Customer class might bemisleading, and prompts a major question why wouldn t it be enough todirectly add the input Customer instance into the Customers table of theDataContext class? The DataContext object, to be able to keep track of theCustomer object s changes throughout the life of that object, the DataContextinstance 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 newinstance of the DataContext class first, then add the locally created Customerinstance to the DataContext object. Once the object is added to the DataContextobject, tracking that object starts, and, in this case, the DataContext objectwill notice that this is a new object and, upon hitting SubmitChanges, the newobject will be inserted into the database.

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

public static void UpdateCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw newArgumentException("Customer object invalid");

   // Create a newinstance of the DataContext

   CustomerDataContext db= BizObject.Context;

   // Create a newinstance of the Customer object

   // Make sure to includePrimary Key and the Keys

   // used by DataContextfor concurrency checking

   Customer localCustomer= new Customer {

       CustomerID =customer.CustomerID,

       Email =customer.Email

   };

   // Attach the newobject to the DataContext

   db.Customers.Attach(localCustomer);

   // Now update theobject, after adding it to the

   // DataContext, changeswill be tracked

   localCustomer.Age =customer.Age;

   // Check ConcurrencyConflict

   try

   {

       // Now you cansubmit changes using the DataContxet

       db.SubmitChanges(ConflictMode.ContinueOnConflict);

   }

    catch(System.Data.Linq.ChangeConflictException ex)

   {

       foreach(ObjectChangeConflict occ in db.ChangeConflicts)

       {

           // All databasevalues overwrite current values.

           occ.Resolve(RefreshMode.OverwriteCurrentValues);

       }

       db.SubmitChanges();

   }

   finally

   {

       // Dispose theDataContext object

       db.Dispose();

   }

   // Purge the Cache

   BizObject.PurgeCacheItems("customers_customer_"+

      customer.CustomerID.ToString());

   BizObject.PurgeCacheItems("customers_customers");

}

Figure 4: TheUpdateCustomer method.

The key point here is to notice that the input parameterwas created by another DataContext object, and, to make the current DataContextinstance of this current HttpRequest aware of that object, it s a must to firstattach the object to the new DataContext object, then update the requiredproperties.

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

public static void DeleteCustomer(Customer customer)

{

   // Validate the object

   if (customer == null)

       throw newArgumentException("Customer object invalid");

   // Create a newinstance of the DataContext

   CustomerDataContext db= BizObject.Context;

   // Get the Customerfrom the database

   Customer localCustomer= (

       from

           c indb.Customers

       where

           c.CustomerID ==customer.CustomerID

       select

           c

       ).FirstOrDefault();

   // Delete customertaken the CustomerID

   // of the customerretrieved, this way

   // changes will betracked on

   db.Customers.DeleteOnSubmit(localCustomer);

   db.SubmitChanges();

   // Purge the Cache

   BizObject.PurgeCacheItems("customers_customer");

}

Figure 5: The DeleteCustomermethod.

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

Once the object is retrieved by the current DataContextinstance, removing it from the Customers table using the Remove method wouldsignal to the DataContext object that an object has to be deleted. This wouldnot have been true if the input Customer object was directly removed from theCustomers table; the reason is, again, the input Customer object was retrievedby another DataContext object, and there is no way for the current DataContextobject 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 DALbased on LINQ. What s still missing is creating a UI layer to complete the three-tierarchitecture.

The UI layer we ll create is a very simple UI. The maingoal is to show how the different layers in the application are interactingwith each other in the presence of LINQ. That interaction has been shownbetween the BLL and DAL; now the interaction between the UI layer and BLL willbe shown.

Create a simple ASPX page that shows a GridView listingall the customers in the database. TheGridView is configured with an ObjectDataSource:

   DataObjectTypeName="bhaidar.DAL.Customer"    SelectMethod="GetCustomers"    TypeName="bhaidar.BLL.CustomerManager" />   The DataObjectTypeName is bhaidar.DAL.Customer. Thisproperty could have been pointed to an object created in the BLL thatrepresents a wrapper over the Customer classes auto-generated inside theDataContext class. However, for the sake of this article, the UI layer will besatisfied by the DAL Customer object.   Configuring the ObjectDataSource is the same as it used tobe 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 WebForm when a customer record is selected in the GridView and details about itare shown in the FormView to be edited or deleted.  
Figure 6: ASP.NETWeb Form customer management.   Clicking the Edit link would cause the FormView to bechanged to Edit Mode. If a new customer is to be added, clicking the New linkwould put the FormView in Insert Mode. And finally, if there s a need to deletean existing customer, clicking the Delete link will do so!  Conclusion This article opens with a brief explanation of the newLINQ technology, three-tier ASP.NET applications, and references to importantlinks on getting started with LINQ. A detailed explanation then illustrated howto create a new DAL based on LINQ, as well as how to create a BLL and integrateit with the LINQ DAL classes. Finally, we saw how to create a UI layer, withWeb Forms and user controls that interact with the BLL.   Source codeaccompanying this article is available for download.  Bilal Haidar is aMicrosoft MVP in ASP/ASP.NET (since 2004).He is an MCP, MCTS, MCPD, and MCT holder. He is a Telerik MVP and the Webmasterfor the LebDev user group. Bilal is one of the top posters on the ASP.NETofficial 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 hispublished articles on http://www.aspalliance.comand http://www.code-magazine.com. Heruns his own blog at http://www.bhaidar.net.Contact him with any questions or for help at mailto:[email protected].