Bill Blogs in C#

I normally don't post straight links to others, but this is worth mentioning.

Charlie Calvert blogged about this late last week.  Members of the C# team spent the day working for Habitat for Humanity in the Seattle area.  You can read more here: http://blogs.msdn.com/charlie/archive/2007/10/26/c-team-and-habitat-for-humanity.aspx

Remember how busy those folks are trying to put the finishing touches on VS 2008, and they still found time in their busy schedules to take the day to help others. 

Kudos to all involved.

Yesterday I discussed Linq to SQL and interoperability with existing database technology.

Today I'll look at how Linq to SQL handles stored procedures. Much of the work is done by the database designer, which creates methods that invoke your stored procedures.

At its core, all calls into stored procedures call DataContext.ExecuteMethodCall(). ExecuteMethodCall returns an IExecuteResult object. IExecuteResult provides a Result parameter to access the result object.

Attributes on each method call provide type information about the call. For example, this method call takes an NChar(15) as a parameter and returns an int:

[Function(Name="dbo.Customers Count By Region")]

[return: Parameter(DbType="Int")]

public int CustomersCountByRegion([Parameter(DbType="NVarChar(15)")] string param1)

{

    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);

    return ((int)(result.ReturnValue));

}

You call that method thusly:

int count = db.CustomersCountByRegion("WA");

That's a simple scalar method that returns an integer.

Of course, many stored procedures return a set of results. Methods that return a sequence of objects return a type of ISingleResult<T>. In this instance, you get a sequence of CustomerByCityResult objects:

ISingleResult<CustomersByCityResult> result = db.CustomersByCity("London");

CustomerByCityResult is created by the Linq to SQL designer as a partial class when you add the stored procedure to you data model. That class is an autogenerated class that contains properties and fields for each of the columns returned by that stored procedure.

Some stored procedures return different record sets depending on parameters. That's support by retrieving the result as an object that implements IMultipleResult. Then, you can use GetResult<T> to retrieve the result as one of the types returned by the stored procedure.

For example, this (rather contrived) method returns either the full customer record, or only selected columns from the customer table:

IMultipleResults result = db.WholeOrPartialCustomersSet(1);

IEnumerable<WholeCustomersSetResult> shape1 = result.GetResult<WholeCustomersSetResult>();

result = db.WholeOrPartialCustomersSet(2);

IEnumerable<PartialCustomersSetResult> shape2 = result.GetResult<PartialCustomersSetResult>();

But wait, there's more. Some Stored procedures return records of more than one type. Then, you can retrieve the different record types by calling GetResult<T> using the different type parameter:

IMultipleResults result = db.GetCustomerAndOrders("SEVES");

IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();

IEnumerable<OrdersResultSet> orders = result.GetResult<OrdersResultSet>();

Finally, you can use the Linq to SQL framework to work with stored procedures that use out parameters. In all cases, the output parameters are cast as ref parameters, even if the parameter is used only as an out parameter:

db.CustomerTotalSales(customerID, ref totalSales);

The DataContext.ExecuteMethodCall() can also be used to access User Defined Functions on the DataContext object. The return value is the same (either ISingleResult<T>, or IMultipleResults). However, the wizard generated code may generate a call to either DataContext.ExecuteMethodCall, or DataContext.CreateMethodCallQuery(), depending on the action of the user defined function.

Next, we'll come back with an overview of the DataContext methods.

Holy crud. The launch is approaching, and I haven't reviewed the features and samples in the 101 * 4 demos in the SampleQueries application.

If I want to have any hope of finishing, I need to do this quite a bit more quickly.

Next on the list of 101+ Linq to SQL samples are ones that show that you can use SQL statements in a LINQ query:

var products = db.ExecuteQuery<Product>(
"SELECT [Product List].ProductID, [Product List].ProductName " +
"FROM Products AS [Product List] " +
"WHERE [Product List].Discontinued = 0 " +
"ORDER BY [Product List].ProductName;"
);

I'm not a fan of this syntax, except as a transitional strategy for moving applications to Linq. The query is a text string. None of the parameters are typed, and if you goofed, and typed db.ExecuteQuery<Customer> instead of db.ExecuteQuery<Product>, you'd have a runtime exception on your hands. That's not fun. That's what Linq is supposed to help you avoid: you should be able to work with types the compiler understands, not strings.

You can also execute commands directly:

db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");

That has the same problem. The command is text, not code. It's a string, there's no security, no intelligence, no type checking.

There are other interesting strategies that you can use to migrate code from existing applications. The next two samples show how you can use the database connection from an existing application in Linq.

SqlConnection nwindConn = new SqlConnection(connString);
nwindConn.Open();

// ... other ADO.NET database access code ... //

// Use pre-existing ADO.NET connection to create DataContext:
Northwind interop_db = new Northwind(nwindConn) { Log = db.Log };

var orders =
from o in interop_db.Orders
where o.Freight > 500.00M
select o;

nwindConn.Close();

Notice that this sample opens a new connection, and queries against that connection. You specify the data connection as part of the data source in your query. That's all it is. If you construct the data context object using an existing connection, that's the connection you'll be working with.

The next sample shows how you can use old style (it feels funny calling .NET 2.0 old-style, but it is) database transactions to wrap your Linq enabled commands:

var q =

from p in db.Products

where p.ProductID == 3

select p;

// Create a standard ADO.NET connection:

SqlConnection nwindConn = new SqlConnection(connString);

nwindConn.Open();

// Use pre-existing ADO.NET connection to create DataContext:

Northwind interop_db = new Northwind(nwindConn) { Log = db.Log };

SqlTransaction nwindTxn = nwindConn.BeginTransaction();

try {

SqlCommand cmd = new SqlCommand("UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3");

cmd.Connection = nwindConn;

cmd.Transaction = nwindTxn;

cmd.ExecuteNonQuery();

// Share pre-existing ADO.NET transaction:

//interop_db.LocalTransaction = nwindTxn;

interop_db.Transaction = nwindTxn;

Product prod1 = interop_db.Products.First(p => p.ProductID == 4);

prod1.UnitsInStock -= 3;

interop_db.SubmitChanges();

nwindTxn.Commit();

}

catch (Exception e) {

// If there is a transaction error, all changes are rolled back,

// including any changes made directly through the ADO.NET connection

Console.WriteLine(e.Message);

Console.WriteLine("Error submitting changes... all changes rolled back.");

}

nwindConn.Close();

It's just a matter of executing the commands you want to execute within the confines of a SqlTransaction. What I think is really cool is that this code demonstrates that you can mix Linq commands with old style commands.

This post shows you some of the ways that you can mix existing DB access code with Linq queries. That will make it easier for you to migrate your existing application to Linq and support the codebase during the transition. That's going to be important for many customers.

We interrupt this blog for an important public service announcement.

Microsoft has been telling folks that the Visual Studio 2008 Beta 2 VPC images are going to expire on November 1, 2007. The VS 2008 install doesn't expire, but the base OS does. That's even more serious. If you are using the VPC image for your VS 2008 testing, you should back up all projects, VSTS database, etc. before this coming Thursday. (Of course, you should probably be running backups on anything important, but that's another story.)

You can learn more here: http://blogs.msdn.com/jeffbe/archive/2007/10/25/vs2008-beta2-vpcs-expiring-prematurely.aspx and here http://blogs.msdn.com/jeffbe/archive/2007/10/27/update-on-expiring-vs2008-beta2-vpcs.aspx The second one is particularly useful. Jeff has done some testing by simulating the timeout on one of his machines. He discusses several workarounds to get your data, etc. off the image.

Also note: None of this affects you if you have been running the installer for VS 2008 yourself (either on real hardware, or a VPC you built yourself.) It's only an issue if you've been using the VPC images from the MSDN download center.

Charlie Calvert has added another new author to the C# Develper Center.  Thomas Lebrun has added an article about extension methods. He gives a good solid overview of what extension metods are, how to use them, how to define them, and how to prevent collisions between different extension methods with the same signature.

If you aren't sure how you would use extension methods in your own work, it's well worth the read.

And that's why I write automated tests.

I recently inherited some truly bad code. I'd post some examples, but the codebase is owned by a client, and the code is covered by an NDA. But, to give you an idea how bad it is, here are a few interesting statistics from when I first examined the code:

Longest method: 1779 lines (that's not a typo. The same method has a cyclomatic complexity of 204).
Number of public fields: 527
And, most significantly: Number of unit tests: 0.

This project had a bad code smell that rivaled an unchecked diaper pail. It reeked.

So, I set about making changes.

As I said above there were no automated tests. So I'm still doing a reasonable set of hand testing with each change. That slows me down. But, my first step as I refactor each class is to create a series of unit tests that help me understand what the code does. As I refactor, I clean up the interface. Some tests go away (because some methods go away). The body of some tests are moved around. (While methods are removed, the functionality is needed, so it moves into other methods. The same test logic exercises those new methods).

It's a slow process, but it does show in the quality of the code. I've now got more than 250 unit tests. I can press one button and a few minutes later, a reasonable subset of the code is tested. My unit tests currently hit 94% of the code I've refactored. I'd like to keep it that high as I continue refactoring. I still need to exercise too much by hand, because I'm not done refactoring. (I still haven't tackled the class with the 1700 line method).

The point of this little rant is that hand testing is boring. It's also error prone. And, it's hard to tell if you've touched everything. It's also time consuming. And, it's a recurring time sink. When I write automated tests, I pay a one time cost, and I get to reuse that work to validate the code quality with every change.

That's one of the biggest gains from automated tests: you trade a recurring cost (hand testing) for a one time cost (automated testing). Yes, there are many other benefits: Code quality, isolating errors, writing testable code, etc. But yet, the one silver bullet for management is that you trade this recurring cost (a constant drain on productivity) for a one time cost (writing tests).

Charlie Calvert has found another MVP to add community content to the C# Developer center. Tomas Petricek has written a great article on how to use some of the new features in C# 3.0 and the .NET 3.5 Framework to create a generic class that manages lazy evaluations. Check it out.

I'm glad to share space with such a skilled set of authors: Mark Michaelis, Peter Richie, Greg Young, and now Tomas. I met Tomas at the last MVP Summit in March. He's a very sharp young man from Czechoslovakia. He's creating some interesting innovations using .NET and C#. It's well worth the read. Check out his blog and the C# developer center.

I read this on Scott Guthrie's blog early this afternoon.

This is absolutely fantastic. Instead of all that time in Reflector, wondering what something does, where the method works, or why it was implemented, we'll have actual source code.

If you look at Scott's blog post (especially the screen shots), you'll see what I mean. There are real comments in that code. You'll be able to understand why different methods work the way they do.

Also, I especially like the integrated debugger support, including the 'download on demand' setting.

It's just goodness.