richardod's blog

Sample Photo with Nikon 105mm F/2.8 Lens Micro

2009-08-25T18:53:00.001+01:00

Here’s a photo I’ve uploaded taken with this lens to Flickr (Click on the image to see it on Flickr):

Returning Method Input Parameters as the result with Moq

2009-06-14T21:21:00.001+01:00

Today I discovered how I can return the input on a Moq Mock object as the output. Let’s imagine we have an abstract class called (oddly) EchoInput:

public abstract class EchoInput

{

public abstract string SayIt(string input);

}

We wish to create a Mock object for this abstract class that returns the value of the input as the return value for the method SayIt. To do that we simply use an overload of the Moq Return’s method that allows us to specify a Func<> delegate that simply matches the signature of the method:

var testDouble = new Mock<EchoInput>();

testDouble.Setup(mock => (mock.SayIt(Moq.It.IsAny<string>()))).Returns((string input) => input);

If you don’t match up the parameters in the call to return you will simply get a “System.Reflection.TargetParameterCountException” at runtime. This would happen if you used the example below (deliberately incorrect code):

testDouble.Setup(mock => (mock.SayIt(Moq.It.IsAny<string>())))
.Returns((string input, int x) => input);

It is worth noting that the number of parameters need to match the method’s signature. It doesn’t matter what you call the parameters but for readability it is worth giving them the same names as the original parameters.

Installing Windows 7 RC1 on IBM Thinkpad Z60m

2009-05-25T21:34:00.001+01:00

After reading lots about Windows 7 and how good it is supposed to be on laptops, I decided to take the plunge and upgrade my Thinkpad Z60m to Windows 7. Previously I had been running Windows XP on the laptop, as I don’t think it is that suitable for running Vista. Though Windows 7 has yet to be released, I had a copy of Windows 7 RC Ultimate and decided my laptop would be a good candidate for installing Microsoft’s latest desktop operating system.

As I don’t do anything overly important on my laptop I decided the upgrade wouldn’t be that much of a risk (I can always go back to XP if needed). To start off I decided to upgrade the machine from the base specification, by upgrading the memory to 2 GB of RAM from Crucial (the maximum RAM allowed in a Z60m) and upgrading the standard 100GB 5400 RPM hard drive to a better spec 7200 RPM, 320GB Seagate drive.

After installing the new RAM and hard drive, I proceeded to install Windows 7 from DVD. The installation was very quick and painless. Once installed I discovered the wireless wasn’t working out of the box and the screen resolution wasn’t right. After plugging in a CAT-5 cable, I downloaded the latest Microsoft updates, installed them and rebooted.

Once I had installed the updates the screen was displaying the correct resolution, and I could now use the wireless. I then decided to benchmark the system to get a performance score:

My overall score was 3.7, not bad considering the Z60m is now an old laptop (bear in mind these scores need to taken with a pinch of salt). It seems the new hard drive has a good performance score of 5.9, with the lowest scores being achieved from the Processor (Pentium M 2.0 GHz) and the graphics (ATI Mobility Radeon X600)- both with a score of 3.7.

Since installing Windows 7 it seems to be very stable for a RC. The only other software I have installed so far is MSN Messenger and Windows Live Writer. This blog post has been written using Windows Live Writer in Windows 7. With the ease of installation and strong hardware support following Windows Update, my first impressions of Windows 7 are resoundingly positive- I now just need to install some more software on the laptop.

Started reading Pro ASP.NET MVC Framework

2009-05-20T21:48:00.001+01:00

Today my copy of Pro ASP.NET MVC Framework arrived from Amazon. I’m hoping this book will teach me how to become an expert at ASP.NET MVC. So far I’ve read the first introductory chapter and I must say I am very impressed.

It looks like you can view a sample chapter in PDF format for free from the Apress Web site.

Microsoft 70-568 exam study guide

2009-05-17T18:30:00.000+01:00

Several years ago (maybe 5 now) when I was studying for my MCSD.NET, I stumbled upon a site for MS 70-316, which contains lots of links to help study for the Microsoft 70-316 exam. Back then you had a lot of excellent books for these exams, like Amit Kalani’s study guides, which I thought were the best around at the time.

Roll on several years later I find myself studying for Microsoft 70-568. Currently there are no specific books for this exam. I’m not a fan of doing courses- I prefer reading and experimenting. So this post is my own lists of links and recommended books that a test candidate may find useful.

It turns out that the upgrade exams are a combination of 3 separate MCTS/MCPD exams (Scroll down to Gerry's comment on the prep guides).

Background Reading

Before starting your study for the upgrade exam, I recommend you do some background reading on what is new in .NET 3.5 for ADO.NET, Windows Forms and ASP.NET. Here are some links to get you started:

Skills measured

This roughly ties up with the skills measured page (as of 17th May 2009) on the MS site (the tabs don’t seem to work in the version of Firefox I use, so it is probably best to use IE to visit this page). The majority of the links point to MSDN, with a few linking to some excellent articles that I found. Of course this information is not 100% complete and subject to my own interpretation of the requirements. You should supplement the missing sections with your own Google search/book reading.

Configuring and Deploying Web Applications
- Providers (I love the provider model, hence why there are so many links):
  1. Provider Model Design Pattern and Specification, Part 1 (old but detailed).
  2. The ASP.NET 2.0 Provider Model. 136 pages- a tomb of Provider Model information.
  3. ProviderBase- the base class for all provider implementations.
  4. Don’t forget to add <clear/> when adding providers.
  5. Configuring Providers.
  6. Using the ASP.NET provider functionality in Windows based apps.
  7. Implementing a Membership Provider.
  8. Implementing a Role Provider.
  9. Examining Membership, Roles and Profile.
  10. SiteMap- can use the Site-Map Provider.
  11. Creating a custom site map provider.
  12. Profiles in ASP.NET.
  13. Implementing a Profile Provider.
  14. Creating a custom DB profile provider.
  15. Session State Providers.
  16. Implementing a Session-State Store Provider.
- Authentication etc
  - I just read books for this section
- Session State and Session-State modes.
Consuming and Creating Server Controls
- A great general comparison of controls.
- QuickStarts- data (.NET 2.0 but still handy as a refresher).
- QuickStarts- standard. (.NET 2.0 but still handy as a refresher).
- ListView- my personal favourite ASP.NET control.
  - http://aspnet.4guysfromrolla.com/articles/122607-1.aspx
  - http://wiki.asp.net/page.aspx/475/comparing-listview-with-gridviewdatalist-and-repeater/
  - http://aspnet.4guysfromrolla.com/articles/010208-1.aspx
- DataList- https://aspalliance.com/1007_Building_and_Using_a_LINQ_for_SQL_Class_Library_with_ASPNET_20.6
- TreeView- http://msdn.microsoft.com/en-us/library/e8z5184w.aspx
- @Register- http://msdn.microsoft.com/en-us/library/c76dd5k1.aspx
- Templated ASP.NET user control
  - http://msdn.microsoft.com/en-us/library/36574bf6.aspx
  - http://msdn.microsoft.com/en-us/library/system.web.ui.itemplate.aspx
  - http://msdn.microsoft.com/en-us/library/system.web.ui.itemplate.aspx
- RequiredFieldValidator
- CompareValidator and here.
- RegularExpressionValidator.
- CustomValidator.
- Button.
- Textbox.
- DropdownList.
- Radio Button.
- CheckBox.
- HyperLink.
- Wizard.
- MultiView and here.
Working with data and services.
- No links for ADO.NET- I suggest you read a good book on the subject.
- Add Web Reference vs. Add Service Reference.
- Data Binding Syntax
  - Expressions overview.
  - Quick overview- the old way of doing things.
Troubleshooting and debugging Web Applications.
Working with ASP.NET AJAX and Client-Side Scripting
- The ScriptManager control.
  - ScriptManager.EnablePartialRendering
  - Services- referencing.
- ScriptManagerProxy.
- Triggers
- Web Services in ASP.NET AJAX.
- Client side Web service calls with AJAX extensions.
- Consuming Web Services from Client Script.
- Page methods and here.
- UpdateProgress and here.
- Timer Control.
- AJAX Client lifecycle events.
- JSON.
- Sys.Serialization.JavaScriptSerializer.
Targeting mobile devices (odd choice considering VS.NET 2008 doesn’t have templates installed for it).
Programming Web Applications
- Request- HttpRequest class
- HttpRequest Members
- Server- HttpServerUtility
- Application- HttpApplicationState
- Session- HttpSessionState
- Response- HttpResponse
- HttpContext
- RegionInfo
- Accessibility improvements in ASP.NET 2.0
  - http://www.15seconds.com/Issue/040727.htm
  - http://www.15seconds.com/issue/040804.htm
- TabIndex
- AlternateText
- AccessKey
- GenerateEmptyAlternateText
- Label.AssociatedControlID
- Session State
- ViewState
- Cookies
- Caching
- Application State
- Page Events
- Control Events
- Application Events:
  - http://msdn.microsoft.com/en-us/library/ms178473.aspx
  - http://msdn.microsoft.com/en-us/library/bb470252.aspx
- Session Events
- Cross Page Posting
- Response.Redirect
- Server.Transfer
- Page.IsPostBack
- AutoEventWireup
Creating a UI for a Windows Forms Application by Using Standard Controls
Integrating Data in a Windows Forms Application
Implementing Printing and Reporting Functionality in a Windows Forms Application
Enhancing Usability
- Localization
- Globalization
- Globalizing Windows Forms
- CultureInfo class
- CultureInfo.GetCultures
- CultureTypes
- MDI applications and menu merging for MDI applications
- Help systems in Windows Forms applications
- HelpProvider Class
- Tooltip component
- Popup Help
- PropertyGrid Class
- ProgressBar
- StatusStrip
- ErrorProvider
- Which timer class to use
- Threading Timer
- System.Timers.Timer
- Windows Forms Timer- single threaded, accuracy of 55ms
Implementing Asynchronous Programming Techniques
- Background Worker
- Request Status
Deploying Windows Forms Controls
Configuring and Deploying Applications
Selecting and Querying Data

The official books

You might want to get the official MS Press books to help you study for this exam. I didn’t, as I choose to read non MS books instead- for that reason I don’t have an opinion of them. For completeness, these are what I believe to be the official MS Press books for the components that make up the exam:

Unfortunately I can’t find any sample chapters for these books- from the reviews it looks like they may be useful for exam, particularly for candidates with little experience.

The books I used/think are useful

I read a lot of tech books in order to try and stay up-to-date with technology, so I didn’t read all of these books just to pass the exam. Also if like me you have access to O’Reilly Safari, then you can read some of these books on there.

ASP.NET books

ASP.NET AJAX in action (this is a .NET 2.0 book but still mostly relevant). A very good book, but unfortunately out of date. I still found it very useful both for the exam and using ASP.NET AJAX. You also might want to consider Professional ASP.NET 3.5 AJAX, as this targets ASP.NET 3.5- I didn’t buy it, but if I was looking for a book now I would probably favour this one over the “in action” range, simple because it is out of date.
Professional ASP.NET 3.5. This is a nice easy to read book on ASP.NET 3.5. It would probably be most useful for people who haven’t done much ASP.NET, but have some .NET experience.
Essential ASP.NET 2.0. Brilliant chapter on Health Monitoring and Web events- the chapter is called Diagnostics. This book looks like it aimed at more experienced ASP.NET developers. I only read the chapter on diagnostics, but the rest of it looks interesting.
Programming Microsoft ASP.NET 3.5. Good coverage of authorization and authentication.
Programming ASP.NET 3.5, 4th edition. Another book with good coverage of authentication and impersonation.

LINQ

Pro LINQ: Language Integrated Query in C# 2008. This book got me up-to-speed with LINQ and was very useful for both the exam and my daily development tasks.

Windows Forms

I didn’t use a Windows forms book to study for the exam, but I can recommend this one:

Windows Forms 2.0 Programming (2nd Edition). There doesn’t seem to be any alternative to the MS-Press study books for Windows Forms 3.5- maybe authors are targeting WPF instead.

ADO.NET

I didn’t buy any specific books on ADO.NET. However I know this framework exceptionally well, as it use it on virtually every project I work on. A book I found useful when upgrading to .NET 2.0 was:

Programming Microsoft ADO.NET Core Reference.

A book that looks like it could be very good is “Professional ADO.NET 3.5”, though I’ve not read it myself.

General .NET

CLR via C#. This is the one book I recommend to any .NET programmer. For the exam it has an excellent chapter on performing asynchronous operations.

Of course you don’t have to use books at all (or you might not have the budget to buy books), in that case the next section is the most useful.

Extra stuff

You don’t need to this stuff for the exam, but I stumbled across it whilst revising.

Completely off topic, but I found it interesting- distributed caching:

MemCached. See Memcached Session State and Cache Providers for ASP.NET- I haven’t it but it looks interesting.
Microsoft Velocity and Microsoft Project Code Name “Velocity”.

Other interesting stuff:

Using health monitoring with log4net.
Session wrapper- makes it more testable- designed for ASP.NET MVC:
- http://msdn.microsoft.com/en-us/library/system.web.httpsessionstatewrapper.aspx
- http://msdn.microsoft.com/en-us/library/system.web.httpsessionstatebase.aspx

Finally there is lots of useful programming information on Stackoverflow.

Top Gear Filming in Canary Wharf

2009-04-21T18:57:00.002+01:00

Sorry to disappoint- a non technical post!

Today the Top Gear team were in Canary Wharf and I managed to take some poor quality photos on my mobile- if only I had my SLR! Luckily I managed to make them a little better by using Photoshop Elements.

Clarkson inspecting a car:

Hammond and May inspecting a car:

The trio looking at the assembled crowd:

Clarkson asking the crowd if anyone is a banker:

It was amazing how many people “worked” for the FSA when Clarkson asked them this question!

James May talking to people in the crowd:

Looks like someone else beat me to it putting pictures on the Internet.

The Obsolete Attribute

2009-04-20T20:55:00.001+01:00

Today a former co-worker asked me “what was that attribute we used to mark something as deprecated?” After a little pause for thought I remembered it was the ObsoleteAttribute- It’s a shame Microsoft hadn’t named this DeprecatedAttribute instead- this is what the annotation is called in Java.

I had previously used this attribute whilst in the middle of a large refactoring exercise on a project, where other developers were working on existing classes. It served as a useful reminder to start calling the new method, without breaking the build/their code/unit tests (we had a team culture where we would address warnings as soon as we noticed them). After all the consumer classes had been modified to use the new code, I searched for “Obsolete” in the code and discovered that the previous development team (the project was initially outsourced) had been using comments to mark code as deprecated- so perhaps not many people know about this useful attributes existence?

By default the attribute generates a compiler warning when a program element is referred to in a consumer class. This is the way I had used the attribute previously with a textual description describing how to correct the warning. Indeed a first glance at the documentation for the class (see here in the C# language specification, and here in the MSDN library) suggests this is all this attribute can do:

        static void Main(string[] args)
        {
            ObsoleteOne();
        }
 
        [Obsolete("Use the BetterMethod instead")]
        public static void ObsoleteOne()
        {
            Console.WriteLine("This is a bad method");
        }
 
        public static void BetterMethod()
        {
            Console.WriteLine("This is a better method");
        }
 
 

Used in this manner, the attribute will caused a warning to be generated like so:

In our discussion on using the attribute my former co-worker pointed out that the attribute can also be used to create a compiler error, simply by changing the code to (notice the 2nd boolean parameter):

        [Obsolete("Use the BetterMethod instead", true)]
        public static void ObsoleteOne()
        {
            Console.WriteLine("This is a bad method");
        }

This will result in a compiler error as follows:

In my project I didn’t use this- when references to the old code were removed, I simply removed the obsolete methods. In an API which you distribute to multiple consumers, you obviously don’t have that luxury, and I can see this overload being useful. It is important to remember that this attribute is useful for classes, structs, enums, constructors, properties, fields, events, interfaces or delegates as well as methods.

Sun’s (or should I say Oracle’s) Java documentation provides some useful insight as to when you should deprecate code.

When deprecating it is important to suggest how developers using your code should fix the issue. For example if you use the deprecated class System.Web.Mail.MailMessage, you will receive the following useful compiler warning:

This tells the consumer that the method is obsolete, what the recommended alternative is and where to go to find further help- which unfortunately in this case seems to redirect to a generic .NET Framework page (woops)! I’m sure any competent developer can quickly work out that they should use System.Net.Mail.MailMessage instead though!

Excellent Slides on unit testing

2009-04-17T21:16:00.001+01:00

Today I stumbled across some excellent slides on unit testing. Unfortunately they only appear to be viewable in Firefox, but they are certainly worth viewing. They cover a lot of ground including the reason for mock objects and an excellent explanation on the differences between unit testing and integration testing.

I’m also enjoying looking at stackoverflow- this site is an excellent resource for programmers, it is also an excellent example of what can be achieved with ASP.NET MVC.

Break execution on all exceptions

2009-04-08T21:03:00.001+01:00

A colleague of mine informed me that she had been working on a hard to track down bug in an application written by another developer. After some investigation she discovered the bug was caused by invalid logic created by an exception that the original developer had decided to swallow.

This scenario is quite common and is documented as a common source of bugs in the excellent book Debugging Microsoft .NET 2.0 Applications by John Robbins. It would probably be better if the debugger breaks on all exceptions by default with Just My Code, that way those hidden bugs would become more apparent during development.

In order to get the debugger to break on all CLR exceptions all you need to do is the following:

From the Debug Menu choose Exceptions or CTRL-ALT-E (screenshot from VS.NET 2008):

Select Thrown for all CLR exceptions or expand the tree view to only break on specific exceptions. You can then debug your app and hopefully (if it is well written), the debugger shouldn’t normally hit anything that isn’t a breakpoint!

This Visual Studio debugger feature is useful for a number of scenarios, notably:

Ensuring that the application is not catching overly general exceptions.
Locating difficult to detect bugs (these can also be caused by 1).
Optimizing performance.

Of these 3 scenarios optimizing performance is worth discussing. When I first learnt about this feature, I tried it out on a large application that I was assigned the task of maintaining (disclaimer- I wasn’t in the original programming team :-).

The application was a .NET 1.1 application (I was in the process of upgrading to 2.0- as I always strive to upgrade to the latest available framework/toolset where possible/feasible) that conceptually did something along the lines of:

string hopefullyANumber;
// Assign the string from somewhere...
 
int result = 0;
try
{
    result = int.Parse(hopefullyANumber);
}
catch (Exception)
{
    // Swallow    
}
return result;

This code would not of caused any real performance issues, were it not for the fact that it was being called over and over thousands of times!

Admittedly the original developers didn’t have access to Int32.TryParse (but they could of used other techniques such as regular expressions), however after modifying the code so that it used TryParse I managed to reduce the CPU usage of the application by over 20% (I profiled it before and after with dotTRACE)!

It is good practise to see if the TryParse or Tester-Doer pattern can be of benefit to the performance of your application.

In summary having the debugger break on all exceptions in applications is a useful technique to catch bugs early in development, to check for over generalized exception handling or to enhance performance.

Visual Studio GridView Designer Fix

2009-03-24T21:23:00.001Z

In a previous post, I blogged about a bug with VS.NET 2008 designer. I’m pleased to discover this problem has now been fixed.

Using LINQ Dynamic Query Library with the ObjectDataSource

2009-03-15T13:27:00.001Z

Recently I stumbled across the Dynamic Query Library for LINQ, which comes as part of the Visual C# 2008 examples. Scott Gu has blogged about using the Dynamic Query Library with LINQ to SQL.

In this post, I show an example of using it with the ObjectDataSource Web Server Control in ASP.NET.

The requirement is to display a grid in ASP.NET that allows sorting via the middle tier. Here is an example of the GUI (clicking on the headers sorts the data).

For our example, we will bind to the following business object (Made as simple as possible, using automatic properties):

public class Person
    {
        public int Age { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

We then need to write a class to to get the data and apply the sorting (in a proper application this might be done across two tiers). To make the code as short as possible, I have used C# 3.0 Object Initialisers and Collection Initialisers.

public static class FakeDb
    {
        public static List<Person> GetPeople(string sortExpression)
        {
            List<Person> persons = new List<Person>(3) 
            { 
                new Person { Age = 10, FirstName = "Richard", LastName = "Smith" },
                new Person { Age = 34, FirstName = "Bob", LastName = "Jones" },
                new Person { Age = 50, FirstName = "Lisa", LastName = "Jones" }
            };
 
            if (!string.IsNullOrEmpty(sortExpression))
            {
                persons = persons.AsQueryable().OrderBy(sortExpression).ToList();
            }
 
            return persons;
        }
    }

The line to note is the line using the dynamic query extension methods:

people = people.AsQueryable().OrderBy(sortExpression).ToList();

To allow the List to use this extension method, we first need to convert the List to IQueryable, which is achieved using the AsQueryable extension method. We then apply the Dynamic Query Extension OrderBy, which takes the name of the property to sort on and the sort direction- for example “Age DESC”. We then convert the IQueryable result back to a List and return it.

We then just need the following in our ASPX (formatting attributes have been removed):

<asp:GridView ID="grdViewPersons" runat="server" AllowSorting="True"
            AutoGenerateColumns="False" DataSourceID="objSrcFakeData" >
            <Columns>
                <asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName"
                    SortExpression="LastName" />
            </Columns>
        </asp:GridView>
        <asp:ObjectDataSource ID="objSrcFakeData" runat="server"
            SelectMethod="GetPeople" TypeName="DynamicQuerySorting.FakeDb" SortParameterName="sortExpression">
        </asp:ObjectDataSource>

This code makes sorting a breeze! You can also use Dynamic Query sorting if you are using databinding, by hooking into the GridView.Sorting Event.

Exam 70-568 and 70-569 prep links

2009-03-14T15:45:00.003Z

Update- I've now created a "study guide for 70-568".

I’ve been waiting for these two upgrade exams to become available for what seems like an eternity. Links are now available for the exams on the Microsoft Website (though they don’t yet appear to be indexed by Google).

I hope to add any links to useful Websites I discover whilst studying for these upgrade exams.

Visual Studio 2008 ASP.NET designer bug

2009-03-14T11:22:00.000Z

Update: This has now been fixed. Please see my new post.

The ASP.NET designer in Visual Studio 2008 has a bug where the aspx code might not get updated when making changes in the design view- this only happens under certain conditions. Currently (14th March 2009), there is no fix for this known issue. When a fix is made available it will be posted to the Visual Web Developer Team Blog.

In the meantime as a workaround, I am performing any designer required work on a new page in the project, and copying the generated aspx code manually.

Speeding up SQL Server Inserts by using transactions

2009-03-01T15:51:00.000Z

When ADO.NET 2.0 was released, one of the new features being touted was the ability for SqlDataAdapter to submit updates/inserts in batches. Having spent time recently optimizing an Oracle batch solution, I decided to investigate the batch functionality provided by the SqlDataAdapter (The SQL Server specific DataAdapter).

Firing up Reflector I discovered this functionality is encapsulated in the internal class SqlCommandSet, which resides in the System.Data DLL. If you are not using the SqlDataAdapter/Datasets in your project then you are out of luck (or so I thought).

A quick Google for "SqlCommandSet" allowed me to find two posts by Oren Eini, who had exposed the functionality of this internal class via delegates (see here and here). I hadn't considered leveraging the functionality of internal types using delegates before (I have done similar things using reflection, but not delegates).

It turns out Oren's code has been included in Rhino Commons, in a wrapper class called SqlCommandSet.

Oren fails to mention the performance increase gained by using transactions in a batch solution. This post discusses the increases that can be gained by using a transaction across the whole insert operation.

Not wanting to have a dependency on Rhino Commons (I am sure it an excellent framework), and wanting to leverage the inbuilt-generic delegates of Func<> and Action<> (this is now best practise). I decided to write my own version:

#region license
// Modified by Richard OD to exploit .NET 3.5 08 March 2009
// Copyright (c) 2005 - 2007 Ayende Rahien (ayende@ayende.com)
// All rights reserved.
// 
// Redistribution and use in source and binary forms, with or without modification,
// are permitted provided that the following conditions are met:
// 
//     * Redistributions of source code must retain the above copyright notice,
//     this list of conditions and the following disclaimer.
//     * Redistributions in binary form must reproduce the above copyright notice,
//     this list of conditions and the following disclaimer in the documentation
//     and/or other materials provided with the distribution.
//     * Neither the name of Ayende Rahien nor the names of its
//     contributors may be used to endorse or promote products derived from this
//     software without specific prior written permission.
// 
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
// ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
// WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
// DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
// FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
// DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
// SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
// CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
// OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
// THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#endregion
 
 
using System;
using System.Data.SqlClient;
using System.Reflection;
 
namespace BatchUpdater
{
    public sealed class SqlCommandSetWrapper : IDisposable
    {
        private static readonly Type commandSetType;
        private readonly object commandSet;
        private readonly Action<SqlCommand> appenderDel;
        private readonly Action disposeDel;
        private readonly Func<int> executeNonQueryDel;
        private readonly Func<SqlConnection> connectionGetDel;
        private readonly Action<SqlConnection> connectionSetDel;
        private readonly Action<SqlTransaction> transactionSetDel;
 
        private int commandCount;
 
        static SqlCommandSetWrapper()
        {
            Assembly systemData = Assembly.Load("System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");
            commandSetType = systemData.GetType("System.Data.SqlClient.SqlCommandSet");
        }
 
        public SqlCommandSetWrapper()
        {
            commandSet = Activator.CreateInstance(commandSetType, true);
            appenderDel = (Action<SqlCommand>)Delegate.CreateDelegate(typeof(Action<SqlCommand>), commandSet, "Append");
            disposeDel = (Action)Delegate.CreateDelegate(typeof(Action), commandSet, "Dispose");
            executeNonQueryDel = (Func<int>)Delegate.CreateDelegate(typeof(Func<int>), commandSet, "ExecuteNonQuery");
            connectionGetDel = (Func<SqlConnection>)Delegate.CreateDelegate(typeof(Func<SqlConnection>), commandSet, "get_Connection");
            connectionSetDel = (Action<SqlConnection>)Delegate.CreateDelegate(typeof(Action<SqlConnection>), commandSet, "set_Connection");
            transactionSetDel = (Action<SqlTransaction>)Delegate.CreateDelegate(typeof(Action<SqlTransaction>), commandSet, "set_Transaction");
        }
 
        public void Append(SqlCommand command)
        {
            commandCount++;
            appenderDel.Invoke(command);
        }
 
        public int ExecuteNonQuery()
        {
            return executeNonQueryDel.Invoke();
        }
 
        public SqlConnection Connection
        {
            get
            {
                return connectionGetDel.Invoke();
            }
            set
            {
                connectionSetDel.Invoke(value);
            }
        }
 
        public SqlTransaction Transaction
        {
            set
            {
                transactionSetDel.Invoke(value);
            }
        }
 
        public int CommandCount
        {
            get
            {
                return commandCount;
            }
        }
 
        public void Dispose()
        {
            disposeDel.Invoke();
        }
    }
}

Of course this class is by its very nature risky- you should only use it in your own solutions after performing adequate testing. Also note that any changes to future versions of the .NET framework could make this class fail at runtime.

I wanted to see the performance difference between batching SQL statements (via the SqlCommandSet class) and executing multiple commands. I also wanted to see what difference using transactions would make, and also the performance impact of using SQLBulkCopy.

I then wrote a test program to compare non batched inserts against SqlCommandSet and the SqlBulkCopy class.

Here are the results (note in my case the DB is on the same machine as the C# app- not the usual separate DB server):

By far the fastest was the SqlBulkCopy. This is unsurprising as this class is very similar to SQL Server's BCP program allowing it to bypass normal integrity checks and perform minimal logging. The next fastest technique is using the SqlCommandSet with a transaction open throughout the operation, followed by a transaction open throughout the operation using seperate SqlCommand objects.

The reason why the batched updates increases performance is due to every interaction with SQL Server requiring two-way communication with the database (handshaking). However sending too many statements in one batch can also hinder performance. In Programming Microsoft® ADO.NET 2.0 Core Reference David Sceppa recommends using a batch size of between 100 and 1000 for optimum performance.

Whilst it can be important to batch multiple updates to SQL Server, it is more important for performance to wrap them in a transaction. By default without specifying an explicit transaction, SQL Server will execute each statement in a separate transaction, regardless of whether or not the query is sent as a batch or each statement. Each transaction requires the log to be written to disk- writing this out in one go is going to be a lot more efficient than doing it for each individual statement.

Here is the client test code (it could do with a bit of a cleanup):

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
 
namespace BatchUpdater
{
    class Program
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["sqlServerDB"].ConnectionString;
        private const string INSERT_STATEMENT = "INSERT INTO SomeTable(SomeColumn) VALUES ('This is nice')";
 
        static void Main(string[] args)
        {
            // Truncate just to be sure
            TruncateTable();
            Stopwatch sw = new Stopwatch();
            int batchSize = 1000;
 
            sw.Start();
            PerformInsertsWithCommandSetWrapper(batchSize, true);
            sw.Stop();
            Console.WriteLine("Time with command set wrapper and transactions {0}", sw.Elapsed);
            sw.Reset();
 
            TruncateTable();
 
            sw.Start();
            PerformInsertsUsingSingleCommands(true);
            sw.Stop();
            Console.WriteLine("Time with single commands and transactions {0}", sw.Elapsed);
            sw.Reset();
 
            TruncateTable();
 
            sw.Start();
            PerformInsertsWithCommandSetWrapper(batchSize, false);
            sw.Stop();
            Console.WriteLine("Time with command set wrapper without transactions {0}", sw.Elapsed);
            sw.Reset();
 
            TruncateTable();
 
            sw.Start();
            PerformInsertsUsingSingleCommands(false);
            sw.Stop();
            Console.WriteLine("Time with single commands without transactions {0}", sw.Elapsed);
            sw.Reset();
 
            TruncateTable();
 
            DataTable testData = GetTestData();
            sw.Start();
            UseBcp(testData);
            sw.Stop();
            Console.WriteLine("Time with BCP {0}", sw.Elapsed);
 
            Console.ReadLine();
        }
 
        private static void PerformInsertsUsingSingleCommands(bool useTransactions)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlTransaction tran = null;
                if(useTransactions) tran = con.BeginTransaction();
                for (int i = 0; i < 50000; i++)
                {
 
                    SqlCommand cmd2 = new SqlCommand(INSERT_STATEMENT, con);
                    if(useTransactions) cmd2.Transaction = tran;
                    cmd2.ExecuteNonQuery();
 
                }
                if (useTransactions)
                {
                    tran.Commit();
                    tran.Dispose();
                }
            }
        }
 
        private static void PerformInsertsWithCommandSetWrapper(int batchSize, bool useTransactions)
        {
            for (int i = 0; i < 50000; i = i + batchSize)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                using (SqlCommandSetWrapper wrapper = new SqlCommandSetWrapper())
                {
                    for (int j = 0; j < batchSize; j++)
                    {
                        SqlCommand cmd = new SqlCommand(INSERT_STATEMENT);
                        wrapper.Append(cmd);
                    }
 
                    wrapper.Connection = con;
                    con.Open();
                    SqlTransaction tran = null;
                    if(useTransactions)
                    {
                        tran = con.BeginTransaction();
                        wrapper.Transaction = tran;
                    }
                    wrapper.ExecuteNonQuery();
                    if (useTransactions)
                    {
                        tran.Commit();
                        tran.Dispose();
                    }
                }
            }
        }
 
        private static void TruncateTable()
        {
            using (SqlConnection cona = new SqlConnection(connectionString))
            {
                cona.Open();
                SqlCommand cmd = new SqlCommand("TRUNCATE TABLE SomeTable", cona);
                cmd.ExecuteNonQuery();
            }
        }
 
        private static DataTable GetTestData()
        {
            DataTable dt = new DataTable();
            dt.BeginLoadData();
            dt.Columns.Add("SomeColumn");
            for (int i = 0; i < 50000; i++)
            {
                dt.Rows.Add("This is nice");
            }
            dt.EndLoadData();
 
            return dt;
        }
 
        private static void UseBcp(DataTable dt)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(connectionString);
            bcp.DestinationTableName = "dbo.SomeTable";
 
            bcp.WriteToServer(dt);
        }
    }
}

Of course it would be interesting to look at solutions when using LINQ to SQL or LINQ to Entities.