Building the Data and Business Layers Using .NET 3.5

The data and business layers of the Dropthings portal use two of the hottest features of the .NET 3.0 and 3.5 frameworks: Windows Workflow Foundation (WF) and LINQ. The data layer makes good use of LINQ to SQL, a feature of .NET 3.5. The business layer is built largely with the WF released with .NET 3.0, using new language extensions in C# 3.0.

LINQ, or Language integrated query, is a set of C# 3.0 language and .NET 3.5 framework features for writing structured queries over local object collections and remote data sources. With LINQ, you can query any collection that implements IEnumerable<>, including tables in a database.

LINQ to SQL is a lot of fun to work with and makes the task of writing a data access layer that generates highly optimized SQL amazingly simple. If you haven’t used LINQ to SQL before, brace yourself.

With LINQ to SQL, you design the database that the application will use and then use the Visual Studio 2008 Object/Relational Designer (sqlmetal.exe in LINQ Preview) to generate a class that represents the database with an appropriate object model. This is a giant step beyond having to handcode the entity and data access classes. Formerly, whenever the database design changed, you had to modify the entity classes and modify the insert, update, delete, and get methods in the data access layer. Of course, you could use third-party object-relational mapping (ORM) tools or some kind of code generator that generates entity classes from database schema and data access layer code. But now, LINQ to SQL does it all for you!

A great thing about LINQ to SQL is that it can generate objects known as projections that contain only the fields you want to receive from a specific query, not the entire row. There’s no ORM tool or object-oriented database library that can do this today because the operation requires a custom compiler to support it. The benefit of projection is pure performance. You select only fields that you need, and you don’t have to build a jumbo-sized object with every field from the tables you query. LINQ to SQL selects only the required fields and creates objects that contain only the selected fields. Let’s take a look at some example queries used in the business layer. Example 4-1 shows how easy it is to create a newPageobject in a database.

Example 4-1. Inserting a new Page object in a database using LINQ to SQL

var db = new DashboardData(ConnectionString);

var newPage = new Page();
newPage.UserId = UserId;
newPage.Title = Title;
newPage.CreatedDate = DateTime.Now; newPage.LastUpdate = DateTime.Now;

db.Pages.Add(newPage);
db.SubmitChanges();
NewPageId = newPage.ID;

HereDashboardDatais theDataContextgenerated by the Visual Studio 2008 Object Relational Designer. It contains all the database access methods and entities for tables in the database.DataContext takes care of generating queries for objects that are requested from the database, executing the queries, and populating objects from the database. It also keeps track of changes made to the objects and when they are updated, and knows exactly which fields to update in the tables.DataContext completely encapsulates database access and provides a nice, clean, object-oriented way of working with data that is persisted in a database. Moreover,DataContextallows you to run arbitrary queries as well; you can use regular stored procedures to read and write rows in database tables.

Example 4-2 shows how to get aPageand change its name. You can use lambda expressions similar to those you have seen in Chapter 3 to define the condition for the where clause.

Example 4-2. Get an object by primary key and updating

var page = db.Pages.Single( p => p.ID == PageId );
page.Title = PageName;
db.SubmitChanges();

Another option is to select only a scalar value from the database. Reading scalar values directly from a database is faster than reading a row and then converting it to an object repeatedly. Example 4-3 shows how to do it.

Example 4-3. Read scalar values

var UserGuid = (from u in db.AspnetUsers where u.LoweredUserName == UserName
select u.UserId).Single();

You can also read specific fields and create an object on the fly that contains only the specific fields. This is called projection and is shown in Example 4-4.

Example 4-4. Create projection

var users = from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName };

foreach( var user in users )
{
Debug.WriteLine( user.UserName );
}

In Example 4-4, only two fields from theAspnet_Userstable are selected. LINQ to SQL returns an object that has only these two fields, and not all the fields in the table.

Database paging is very easy in LINQ to SQL. For example, if you want to select 20 rows starting at the 100th row, just use theSkipandTakefunctions as shown in Example 4-5.

Example 4-5. Paging using Skip and Take

var users = (from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName }).Skip(100).Take(20);

foreach( var user in users )
{
Debug.WriteLine( user.UserName );
}

It’s easy to provide transaction support in LINQ to SQL. You just write code inside ausing block, and the code inside it falls into a transaction scope (see Example 4-6).

Example 4-6. Using transaction

using( var ts = new TransactionScope() )
{
List<Page>pages = db.Pages.Where( p => p.UserId == oldGuid ).ToList();
foreach( Page page in pages )
page.UserId = newGuid;

// Change setting ownership
UserSetting setting = db.UserSettings.Single( u => u.UserId == oldGuid ); db.UserSettings.Remove(setting);

setting.UserId = newGuid; db.UserSettings.Add(setting); db.SubmitChanges();

ts.Complete();
}

When there’s any exception, theusing block will call theDisposefunction onts, and the transaction will abort unless it is already completed. But if the code reaches the end of the block, it callsts.complete()and the transaction commits.

The first step to using LINQ to SQL is to build a DataContext, which contains all entity classes and their data access methods. You will generate oneEntityclass per table, e.g., thePageEntity class for thePagetable in the database. Visual Studio 2008’s ORM designer allows you to easily drag and drop tables onto a designer surface and generate entity classes. The next step will be to create a simple helper class that makes working with LINQ to SQL even easier. Let’s start with designing the ORM in Visual Studio 2008.

Generating a Data Model Using the Visual Studio 2008 Designer

Visual Studio 2008 comes with an object relational mapping designer, which allows you to create a LINQ to SQL classes file and then drag and drop tables from the server explorer to the designer surface. Visual Studio will then automatically generate classes from those tables. You can further modify the associations, turn on or off lazy loading of properties, add validation, and much more. Nothing special was done to generate Figure 4-1 in the data model, besides putting all the tables from the database onto the designer surface.

After you create the designer and build the project, the data access and entity classes will be ready for use.DashboardDatais the data context class that’s included in the project.

Example 4-7 shows the code for a DatabaseHelper that makes working with LINQ to SQL a lot easier.

Example 4-7. DatabaseHelper, part 1

public static class DatabaseHelper
{
  public const string ConnectionStringName = "DashboardConnectionString";
  public const string ApplicationID = "fd639154-299a-4a9d-b273-69dc28eb6388";
  public readonly static Guid ApplicationGuid = new Guid(ApplicationID);

  public static DashboardData GetDashboardData()
  {
    var db = new DashboardData(ConfigurationManager.
   ConnectionStrings[ConnectionStringName].ConnectionString);
    return db;
  }

Figure 4-1.  Database model that shows LINQ to SQL classes

DatabaseHelper also takes care of configuration management and initialization of theDataContentclass. It has aGetDashboardDatafunction that returns a reference to theDashboardData instance already configured with the connection string.Insert,Update, andDeletemethods offer shortcuts for performing common database operations.

DatabaseHelperreads the connection string from the<connectionString>block in the web.config or app.config file. It also stores theApplicationIdfor the ASP.NET membership provider.

Although it would be sensible to place theApplicationID in web.config, I’ve placed it inDatabaseHelperjust to simplify this discussion.

Whenever you create a new application using an ASP.NET membership, ASP.NET creates an entry in theAspnet_Applicationstable. ThisApplicationIDis also used in theaspnet_userstable to identify which application a user belongs to.

Building the Data Access Layer Continued

The membership provider is built for hosting multiple applications in the same database. So, all the root objects such as user, personalization, etc. belong to an application.

Here’s a problem with LINQ to SQL. If an entity travels through multiple tiers, then it gets detached from theDataContextthat created it. This means that as soon as an entity is out of the data access layer and into the business or web layer, it is detached from theDataContextbecause theDataContextis only available within the data access layer. So, when you try to update entities again using a differentDataContext, you first need to attach the entity instance to the new data context, then make the changes and callSubmitChanges. Now the problem is that from the business layer you do not have access to theDataContext, which is created by the data access layer when the entity object is being updated. The business layer will just send the entity object to the data access component, and then the component will update by creating a newDataContext.

LINQ to SQL requires that you attach the entity object before making changes to it so that it can track which properties are being changed and determine what kind ofUPDATEorINSERT statement to generate. However, a typical business layer will make the object modifications first and then send them to the data access component. So, a traditional attempt like Example 4-8 will fail.

Example 4-8. Common way of updating objects in the database

Page p = DashboardData.GetSomePage();
...
...

// Long time later may be after a page postback
p.Title = "New Title"; DashboardData.UpdatePage( p );

Somehow you need to do what is shown in Example 4-9.

Example 4-9. Proper way of updating objects in the database using LINQ to SQL

Page p = DashboardData.GetSomePage();
...
...
// Long time later may be after a page postback
DashboardData.AttachPage( p );
p.Title = "New Title"; DashboardData.UpdatePage( p );

However, Example 4-9 is not possible because you can’t makeDashboardData stateless. You need to createDataContext inside methods and then store the reference toDataContextbetween function calls. This will be fine for a single-user scenario, but not an acceptable solution for multiuser web sites. So, I made a workaround (see Example 4-10).

Example 4-10. Workaround for stateless data persistence

// Load the object from database
Page p = DashboardData.GetSomePage();
...
...
// Long time later may be after a page
postback
DashboardData.Update<Page>( p, delegate( Page p1 )
{
  p1.Title = "New Title";
});

Here, theUpdate<>method first attaches the page object toDataContextand then calls the delegate passing the reference to the attached object. You can now modify the passed object as if you were modifying the original object inside the delegate. Once the delegate completes, the object will be updated usingDataContext.SubmitChanges();.

The implementation of theUpdate<>method is shown in Example 4-11.

Example 4-11. The DashboardData.Update<T>updates an object in the database

public static void Update<T>(T obj, Action<T>update)
{
  var db = GetDashboardData();
  db.GetTable<T>().Attach(obj);
  update(obj);
  db.SubmitChanges();
}

The widget container usesDatabaseHelper to update objects in the database as shown in Example 4-12.

Example 4-12. The widget container uses DatabaseHelper to update objects

WidgetInstance widgetInstance = DatabaseHelper.GetDashboardData(). 
              WidgetInstances.Single( wi => wi.Id == WidgetInstanceId );

DatabaseHelper.Update<WidgetInstance>( widgetInstance,
               delegate( WidgetInstance wi )
{
        wi.ColumnNo = ColumnNo;
        wi.OrderNo = RowNo;
});

The delegate inUpdate<T>allows you to be in the context of the business layer or the caller. So, you can access the UI elements or other functions/properties that you need to update the entity’s properties.

Similarly, there’s anUpdateAll<>function that updates a list of objects in the database (see Example 4-13).

Example 4-13. DashboardData.UpdateAll<T>updates multiple objects

public static void UpdateAll<T>(List<T>items, Action<T>update)
{
  var db = GetDashboardData();

  foreach( T item in items )
  {
    db.GetTable<T>().Attach(item);
    update(item);
  }

  db.SubmitChanges();
}

For convenience, I have madeInsert<>anDelete<>also. But they are not required because they do not have an “Attach first, modify later” requirement (see Example 4-14).

Example 4-14. Insert<> and Delete<> functions in DashboardData

public static void Delete<T>(Action<T>makeTemplate) where T:new()
{
  var db = GetDashboardData();
  T template = new T();
  makeTemplate(template);
  db.GetTable<T>().Remove(template);
  db.SubmitChanges();
}
public static void Insert<T>(T obj)
{
  var db = GetDashboardData();
  db.GetTable<T>().Add(obj);
  db.SubmitChanges();
}

TheDelete<>method is a tricky one. First you need to attach the object to theTableand then call the table’sRemovefunction. This means you need to first get the object before you can callDelete, which adds a read overhead while deleting an object (see Example 4-15).

Example 4-15. Delete<T>takes the object to delete and then removes it from the database

public static void Delete<T>(T entity) where T : class,new()
{
     using (var db = GetDashboardData())
     {
          db.GetTable<T>().Attach(entity);
          db.GetTable<T>().Remove(entity);
          db.SubmitChanges();
     }
}

Now that you have learned how to build the data access layer, let’s address some of the challenges you’ll face while running the portal project in a production environment.

An Ajax web portal has a unique challenge when it comes to cleaning up unused data that is generated by anonymous users who never return. Every first visit creates one anonymous user, a page setup, widgets, etc. If the user doesn’t come back, that information remains in the database permanently. It is possible that the user might come back within a day, or a week or a month, but there’s no guarantee. Generally, sticky users—users who return to your site frequently—make up 30 to 50 percent of the total users who come to an Ajax web portal. So, you end up with 50 to 70 percent unused data. Dropthings requires daily data cleanup to keep the database size down—user accounts expire, RSS feeds get old, anonymous sessions expire, and users never come back.

This is a huge cleanup operation once a web portal becomes popular and starts receiving thousands of users every day. Think about deleting millions of rows from 20 or 30 tables, one after another, while maintaining foreign key constraints. Also, the cleanup operation needs to run while the site is running, without hampering its overall performance. The whole operation results in heavily fragmented index and space in the MDF file. The log file also becomes enormous to keep track of the large transactions. Hard drives get really hot and sweat furiously. Although the CPU keeps going, it’s really painful to watch SQL Server go through this every day. But there is no alternative to keep up with SQL Server’s RAM and disk I/O requirements. Most importantly, this avoids counting users in monthly reports that are not valid users.

When a user visits the site, the ASP.NET membership provider updates theLastActivityDateof theaspnet_userstable. From this field, you can find out how long the user has been idle. TheIsAnonymousbit field shows whether the user account is anonymous or registered. If it is registered, then there is no need to worry. But if it is anonymous and more than 30 days old, you can be sure that the user will never come back because the cookie has already expired. However, we can’t avoid creating an anonymous user because the user might want a fresh start (see the “Implementing Authentication and Authorization” section in Chapter 3). Another scenario is a user logging out on a shared computer (e.g., a cyber café) and the next person using it as an anonymous user.

Here’s how the whole cleanup process works:

1. Find out the users that are old enough to be discarded and are anonymous
   
2. Find out the pages the user has 
    
3. Delete all of the widget instances on those pages 
    
4. Delete those pages 
   
5. Remove rows from child tables related toaspnet_userslikeaspnet_profile,aspnet_UsersInRoles, andaspnet_PersonalizationPerUser
   
6. Remove rows for users to be deleted
   
7. Remove the users fromaspnet_users

Example 4-16 is the giant DB script that does it all. I have added inline comments to explain what the script is doing.

Example 4-16. Cleaning up old anonymous users and their related data

-- Number of days after which we give users the 'bye bye'
DECLARE @Days int
SET @Days = 29

-- Number of users to delete per run. If it's too high, the database will get stuck
-- for a long time. If it's too low, you will end up having more trash than
-- you can clean up. Decide this number based on how many anonymous users are
–- created per day and how frequently you run this query. The correct formula
-- for this number is: @NoOfUsersToDelete > AnonUsersPerDay / FrequencyOfRun
DECLARE @NoOfUsersToDelete int
SET @NoOfUsersToDelete = 1000

-- To find other tables, create temporary tables that hold users and pages to delete
-- as the user and page are used.
-- Having them in a temp table is better than repeatedly running SELECT ID FORM ... IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [PagesToDelete]') AND type in (N'U'))
DROP TABLE [dbo].[PagesToDelete]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [aspnetUsersToDelete]') AND type in (N'U')) DROP TABLE [dbo].[AspnetUsersToDelete]

create table PagesToDelete (PageID int NOT NULL PRIMARY KEY)
create table AspnetUsersToDelete (UserID uniqueidentifier NOT NULL PRIMARY KEY)

-- Find inactive anonymous users and store the UserID in the temporary
-- table
insert into AspnetUsersToDelete
select top(@NoOfUsersToDelete) UserID from aspnet_Users where
(isAnonymous = 1) and (LastActivityDate < (getDate()-@Days))
order by UserID -- Saves SQL Server from sorting in clustered index again

print 'Users to delete: ' + convert(varchar(255),@@ROWCOUNT)
GO

-- Get the user pages that will be deleted insert into PagesToDelete
select ID from Page where UserID in
(
select UserID from AspnetUsersToDelete
)

print 'Pages to delete: ' + convert(varchar(255),@@ROWCOUNT)
GO

-- Delete all widget instances on the pages to be deleted
delete from WidgetInstance where PageID IN ( SELECT PageID FROM PagesToDelete )

print 'Widget Instances deleted: ' + convert(varchar(255), @@ROWCOUNT)
GO

-- Delete the pages
delete from Page where ID IN
( SELECT PageID FROM PagesToDelete )
GO

-- Delete UserSetting
delete from UserSetting WHERE UserID IN ( SELECT UserID FROm AspnetUsersToDelete ) GO

-- Delete profile of users
delete from aspnet_Profile WHERE UserID IN ( SELECT UserID FROm AspnetUsersToDelete ) GO

-- Delete from aspnet_UsersInRoles
delete from aspnet_UsersInRoles WHERE UserID IN
( SELECT UserID FROm AspnetUsersToDelete ) GO

-- Delete from aspnet_PersonalizationPerUser delete from aspnet_PersonalizationPerUser WHERE UserID IN
( SELECT UserID FROm AspnetUsersToDelete ) GO

-- Delete the users
delete from aspnet_users where userID IN ( SELECT UserID FROm AspnetUsersToDelete )

PRINT 'Users deleted: ' + convert(varchar(255), @@ROWCOUNT)
GO

drop table PagesToDelete
drop table AspnetUsersToDelete
GO

When to Run the Script

Now the question is, when can I run this script? The answer depends on several factors:

1. The period of lowest traffic on your site. For example, in the U.S., most users are asleep at midnight. Of course, that works only if the majority of your users are from the U.S. 
    
2. Other maintenance tasks, such as index defrag or database backup, are the least likely to be running. If by any chance any other maintenance task conflicts with this enormous delete operation, SQL Server is dead. 
    
3. The time it takes to run the script. The operation will take anywhere from 10 minutes to a number of hours depending on the volume of trash to clean up. So, consider the amount of time it will take to run this script and plan other
   maintenance jobs accordingly. 
    
4. When you typically run index defrag. It’s best to run the script 30 minutes before the index defrag jobs run, because after the script completes, the tables will be heavily fragmented. So, you need to defrag the indexes.

Before running this script, first:

1. Turn off auto shrink from database property. Database size will automatically reduce after the cleanup. Shrinking a database requires a lot of disk I/O activity and it slows the database down. Turn off auto shrink because the database will eventually grow again. 
    
2. Ensure that the log file’s initial size is big enough to hold such enormous transactions. You can specify one-third of the MDF size as LDF’s initial size. Also make sure the log file did not shrink—let it occupy HD space. It saves SQL Server from expanding and shrinking the file. Both of these require high disk I/O.

Once the cleanup job is done and the index defrag runs, the performance of your database will improve significantly. Because the tables are now smaller, the indexes are smaller, and SQL Server doesn’t have to run through large indexes anymore. Future index defrags take less time because there’s not much data left to optimize. SQL Server also takes less RAM because it has to work with much less amount of data. Database backup size is also reduced because the MDF size does not keep increasing indefinitely. As a result, the significant overhead of this cleanup operation is quite acceptable when compared to all the benefits.

Please check back tomorrow for the continuation of this article.