Building the Data and Business Layers Using .NET 3.5
(Page 1 of 5 )
If you'd like to learn how to use Windows Workflow Foundation and LINQ, keep reading. This three-part article explains how to use those technologies in the context of a portal. It is excerpted from chapter four of the book
Building a Web 2.0 Portal with ASP.NET 3.5, written by Omar Al Zabir (O'Reilly, 2008; ISBN: 0596510500). Copyright © 2008 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
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.
Introducing LINQ to SQL 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.
Next: Building the Data Access Layer Using LINQ to SQL >>
More .NET Articles
More By O'Reilly Media
