This blog helped me today.

I don’t like the diff/merge tool that comes with TFS. At all. It isn’t just that it’s feature-poor; it also isn’t good at the one thing it’s supposed to do. I’ve had a quite a few merge operations go horribly wrong, resulting in data loss and syntactically incorrect merge results. Today I found two excellent articles that described how to replace the default TFS diff/merge with an alternative one:

TFS: Using Alternative Diff/Merge Tools

diff/merge configuration in Team Foundation - common Command and Argument values

There are a lot of alternatives out there. I chose to use KDiff3 because it is both free and popular. Most of the other alternatives are either one (Beyond Compare is very popular but not free) or the other (SourceGear DiffMerge is free and looks very good but is not as well-known).

When choosing an alternative, make sure that you choose one that supports 3-way merges. They are much more accurate and reliable than 2-way merges. Of the options listed in the articles above, that means to rule out WinMerge and Beyond Compare prior to version 3.

I came across an interesting technique earlier this week. Did you know that you can return an anonymous type from a method and still have strongly typed access to its members? I got this technique from Tomas Petricek’s blog. Here is a quick example of how it works; for details of why it works please see the original article.

        private static object ReturnAnon()
        {
            return new { FirstName = "Captain", MiddleName = "Jack", LastName = "Sparrow" };
        }

        private static T Cast<T>(object o, T type)
        {
            return (T)o;
        }

        static void Main(string[] args)
        {
            var anon = Cast(ReturnAnon(), new { FirstName = "", MiddleName = "", LastName = "" });
            Console.WriteLine(String.Format("FirstName: {0}", anon.FirstName));
            Console.WriteLine(String.Format("MiddleName: {0}", anon.MiddleName));
            Console.WriteLine(String.Format("LastName: {0}", anon.LastName));
            Console.ReadLine();
        }

Of course, just because you can return an anonymous type doesn’t mean that you should. As a general rule, I would say that if you are using the method’s return value in only one place, then it is OK to use this technique. However, the moment that the method is used from a second place, that anonymous type should be explicitly declared instead.

Sometimes you want to clear out a database, but you can't just drop the entire database and create a new one. Recently, a colleague needed to do just that, but couldn't because the database was hosted elsewhere. Here is a simple script that works to drop all objects in any database I've tested it against so far. If your database contains object types that mine don't, you might need to add to this script using the same patterns established here.

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + name + ']'
from sys.procedures

-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + name + ']'
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + name + ']'
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + name + ']'
from sys.types
where is_user_defined = 1

exec sp_executesql @stmt

Things You Didn't Know About Temp Tables and Table Variables

OK, so I'm a little late to the party. I've been using it for a few days now, and I'm very pleased. Browsing is even faster, and there are new features like the Quick Find address bar that performs full-text searches on every page in your browsing history. Give it a try at www.opera.com/download.

Lately I've needed to compare the schemas of two or more databases and highlight the differences. Based on internet research, I settled on two contenders: AdeptSQL Diff and Red Gate SQL Compare. Both are reasonably fast. AdeptSQL's UI looks very 16-bit and has limited reporting options. Red Gate's UI is very attractive and offers more reporting options. However, Red Gate's product currently has a bug that considers quoted and unquoted trigger identifiers to be different, even if the identifier itself is the same. This resulted in a large number of false differences, forcing me to go with AdeptSQL for now. If Red Gate can fix this problem, I would be inclined to switch to their product in the future.

This is actually right in the SQL Server Books Online, but not easily found. Given a temp table named #temp:

if ( object_id( 'tempdb..#temp' ) is null )
    -- table doesn't exist
else
    -- table exists

Here is an interesting article on a way to turn many looping row-based operations into set-based ones: http://www.sqlservercentral.com/articles/TSQL/62867/

I think the idea is a clever one, and I love the "Row By Agonizing Row" (RBAR) acronym!

I'm a big fan of the Opera browser for its speed and excellent out-of-the-box functionality. One of the few things I have been willing to concede that it was lacking was a good interactive debugger like FireBug, until now. Opera Dragonfly has arrived!

This article presents some interesting ideas on when and when not to use AJAX.

http://dev.opera.com/articles/view/stop-using-ajax/

I found the following blog post on programming.reddit.com:

http://www.atrevido.net/blog/2007/09/05/C+30+And+LINQ+Misunderstandings.aspx

I also recommend these blogs on functional programming in C# 3.0 by the same author:

http://www.atrevido.net/blog/2007/08/12/Practical+Functional+C+Part+I.aspx

http://www.atrevido.net/blog/2007/08/13/Practical+Functional+C+Part+II.aspx

http://www.atrevido.net/blog/2007/08/16/Practical+Functional+C+Part+III+Loops+Are+Evil.aspx

http://www.atrevido.net/blog/2007/08/29/Practical+Functional+C+Part+IV+Think+In+ResultSets.aspx

This is really good stuff that any C# programmer can benefit from

I stumbled upon this great series of articles that go into much more detail about functional programming in C#, the culmination of which is to write implementations of the "big three" higher-order functions: Map, Filter, and Reduce.

http://diditwith.net/PermaLink,guid,a1a76478-03d2-428f-9db6-9cf4e300ea0f.aspx

If you create a brand-new web application in ASP.NET 2.0 or later, client-side validation will automatically work in Firefox. However, if you have legacy applications originally written in ASP.NET 1.1 or earlier, client-side validation will not automatically work in Firefox. To enable this feature in legacy applications, simply open your web.config file and locate the xhtmlConformance element and change it to this:

<xhtmlConformance mode="Transitional"/>

Visual Studio comes with some handy (and some not-so-handy) code snippets, but for some reason there isn't one for declaring a method. It isn't that big of a deal, but after a while you might start resenting finishing your method header, then having to type Enter, {, Enter, }, Up, Enter to start the method body. Here's a snippet that will automatically put an open and closing brace after your method declaration, with the cursor ready to go in between:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>method</Title>
      <Shortcut>m</Shortcut>
      <Description>Code snippet for methods</Description>
      <Author>Adam Anderson</Author>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>method-modifiers</ID>
          <ToolTip>Method modifiers</ToolTip>
          <Default>public</Default>
        </Literal>
        <Literal>
          <ID>return-type</ID>
          <ToolTip>Return type</ToolTip>
          <Default>void</Default>
        </Literal>
        <Literal>
          <ID>member-name</ID>
          <ToolTip>Method name</ToolTip>
          <Default>Name</Default>
        </Literal>
        <Literal>
          <ID>param-list</ID>
          <ToolTip>Parameter list</ToolTip>
          </Default>
        </Literal>
      </Declarations>
      <Code Language="csharp">
        <![CDATA[$method-modifiers$ $return-type$ $member-name$( $param-list$ )
{
$end$
}]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

I recently ran into an unexpected behavior of SSRS while writing a complex report. The report required various calculations that would refer to specific previous groups and details, and I had decided to solve the problem by writing a custom report function that would evaluate on each detail row, saving the information I would need later in a dictionary. My solution worked fine, as long as my custom calculation in a group footer didn't depend on the contents of that group's details. Upon further investigation, I learned something very interesting about the order in which SSRS evaluates group headers, footers, and details...

Here is how to observe the behavior for yourself: create a stored procedure like this one, that generates some simple test data:

CREATE PROCEDURE dbo.HundredRows
AS
SET NOCOUNT ON

create table #result (
id int,
grp int
)

declare @id int
set @id = 1

while @id <= 100
begin
insert #result
values ( @id, ( @id - 1 ) / 5 )

set @id = @id + 1
end

select *
from #result

This will return a result set whose id column increments in steps of one from 1 to 100, and whose grp column increments in steps of one for every five rows. Next, create a simple report layout with a table, showing the values of each column and grouping by the grp column. In the group header and footer, include the same column references. Next, add this custom code:

Dim eval_order As Integer = 0

Public Function show_eval_order() As String
  eval_order = eval_order + 1
  Return eval_order.ToString()
End Function

Display the result of this function in the group header, footer, and detail rows. Your layout should look like this:

Once you have the report set up, click on the preview tab to see the surprising results:

I've only shown the first two groups here, but notice what has happened! The order column clearly indicates that the group header and footer are both evaluated before the details in between! By looking at the value of the id column in the footer, we can see that the the group's header and footer rows are printed while the "current" row in the dataset is still positioned on the first row in the group.

While this is interesting, it isn't very important as long as you stick to using SSRS's built-in aggregation functions. It is only when you attempt to "roll your own" by saving the dataset in your own storage for later reference that you might run into trouble. In my case, I used the following workarounds:

1. Move calculations that rely on the details having been traversed from the group footer to the following group's header. It's clunky and less intuitive, but that's what SSRS has forced upon us.
2. For the final group, move the calculation out of the table entirely, and put it into textboxes just below the table. While there is no way to tell SSRS to evaluate the textboxes after it evaluates the table, it does so at least for the 2005 version.

In summary, it sure would have been nice if SSRS would have evaluated group headers and footers after it evaluated the details that each group contains, but since we have clearly demonstrated that it doesn't, we now know what we will have to do in order to make custom report code produce the output we want: by putting those custom calculations into the following group's header, and by putting the final footer calculation outside the table entirely.

While I was writing a stored procedure that did some string manipulation and was debugging some unexpected behavior, I came across a fascinating, infurating, hidden-in-plain-sight "feature" of the LEN function: it returns the length of a string expression excluding trailing blanks. So, for example:

declare @string varchar(10)
set @string = '123456789 '

declare @len int
set @len = len( @string )

print @len
print right( @string, @len - 5 )

returns 9 and '789 ', not the expected 10 and '6789 '. Also, with this code:

declare @string varchar(10)
set @string = '123456789 '

declare @len int, @reverse_len int
set @len = len( @string )
set @reverse_len = len( reverse( @string ) )

print @len
print @reverse_len

One might expect that the length of a string would be the same forwards and backwards, but it isn't! @len = 9 and @reverse_len = 10!

A workaround for non-Unicode character data is to use the DATALENGTH function, which returns the size of an expression in bytes. If you choose to use DATALENGTH with Unicode data, you must divide by 2 to get the actual string length. Another workaround would be to append a non-blank character to the end of the string, get the length, then subtract one. Neither is very nice. If we had wanted to exclude trailing blanks, we could have called RTRIM before LEN ourselves.

I've been writing a lot of dynamic SQL that generates other SQL lately. While SQL isn't the best string manipulation language, it's got great native access to database schema information. However, my latest project was resulting in some very long scripts that coudn't be printed with PRINT, because it truncates the output after 8000 bytes, which is 8000 chars or 4000 nchars. One solution to this would have been to execute dynamic SQL to create a stored procedure containing the text, but in this case, I wanted to append multiple scripts together, so I chose to use PRINT as a simple way to output them all. The concept was simple: For each 4000 character Unicode block, find the last occurrance of CR, LF, or both, print the left part, and repeat on the right part. However, I ran into a few obstacles on the way; see my last two T-SQL Gotchas. Here is the final result, a general-purpose PRINT replacement that can handle any Unicode string, assuming that it contains at least one CR or LF every 4000 characters. The delimiter(s) could be parameterized, but I'll leave that as an exercise for you.

-- Works around the 4000/8000 character limit of the print statement
CREATE PROCEDURE dbo.LongPrint( @string nvarchar(max) )
AS
SET NOCOUNT ON

set @string = rtrim( @string )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @string ) > @len )
begin
   set @left = left( @string, @len )
   set @reverse = reverse( @left )
   set @cr_index = @len - charindex( @cr, @reverse ) + 1
   set @lf_index = @len - charindex( @lf, @reverse ) + 1
   set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
   set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
   print left( @string, @crlf_index - 1 )
   set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf )
end

print @string

The full text of the error reads: "Commit failed. (details follow): 'pre-commit' hook failed with error output: all was unexpected at this time." I've run into this error when trying to commit files in Subversion (with TortoiseSVN) at least twice. At first, I thought it was caused by some obscure state error. I tried doing a cleanup on the directory, and then on the parent directory, with no success. I tried deleting the entire project and downloading it afresh from the repository. Nothing worked, until my associate Noel asked me what comment I'd used. It was then I realized that my comment contained double quotes. I replaced the double quotes with single quotes, and the commit worked without a hitch. In a way, it's a relief to know that I don't have to worry about any of my projects getting into a weird state where I can't commit anything, but on the other hand, it's a little troubling to think that my comment text apparently isn't getting escaped.

First of all, that title was a mouthful, so let me describe what I meant. A custom DataSource is something that you can assign (or databind) to the DataSource property of any bindable control. In programming terms, this means that the DataSource must implement IListSource, IDataSource, or IEnumerable. Well, I don't know about you, but I don't really want to spend a lot of time implementing all the methods of any of those interfaces. That's where Iterators come in; C# 2.0 added a new feature called Iterator blocks that makes it extremely easy to implement the IEnumerator interface, which is the only thing that you must implement in order to fully implement IEnumerable. In this article, I'll solve a real-world problem using this technique.

In application development, it's very common to need to fill a list control such as a DropDownList with a list of valid items, plus a blank item which represents a null value in a data store. However, while it is very easy to simply set some properties on a DropDownList to databind its items to the contents of a DataTable, there is no simple way to add that blank value without dropping into the code-behind. This is no big deal, but it just doesn't feel very clean to fill some DropDownLists declaratively, and have to resort to imperative code for other DropDownLists. One way to solve this problem would be to create a custom control descending from each list control with properties that control the extra blank item, but first of all, you'd have to write a custom control for each list control, and secondly, if we did that, then we couldn't talk about Iterators, could we? So instead, I'm going to create a custom DataSource that returns the contents of a DataTable, plus a blank item to represent null.

Let's proceed with writing the custom DataSource. Using Iterators to implement IEnumerable is as easy as declaring a class that implements it and using the yield keyword to build the return value. Let's declare our ListSource class and let Visual Studio create the method stubs to implement the interface:

using System;
using System.Collections.Generic;
using System.Text;

namespace Falafel.ListData
{
  class ListDataSource : IEnumerable<ListData>
  {
    #region IEnumerable<ListData> Members

    IEnumerator<ListData> IEnumerable<ListData>.GetEnumerator()
    {
      throw new Exception( "The method or operation is not implemented." );
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
      throw new Exception( "The method or operation is not implemented." );
    }

    #endregion
  }
}

Assume the ListData class is a simple data class with two string properties: Text and Value. The first thing to notice is that the parameter list for GetEnumerator is empty, so the class itself will need to be initialized with properties that control the output of GetEnumerator. Let's add some private fields and a constructor to initialize them. New code is in italics:

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Falafel.ListData
{
  class ListDataSource : IEnumerable<ListData>
  {
    private DataTable _Table;
    private string _TextField;
    private string _ValueField;

    public ListDataSource( DataTable table, string textField, string valueField )
    {
      _Table = table;
      _TextField = textField;
      _ValueField = valueField;
    }

    #region IEnumerable<ListData> Members

    IEnumerator<ListData> IEnumerable<ListData>.GetEnumerator()
    {
      throw new Exception( "The method or operation is not implemented." );
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
      throw new Exception( "The method or operation is not implemented." );
    }

    #endregion
  }
}

Of course, you could add code to expose the private fields as properties, but since that's not the focus of the article, let's just assume everyone knows how to do that so we can get to the interesting part. Now that we can create an instance of the class with a reference to a DataTable and some field names, we can implement GetEnumerator:

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Falafel.ListData
{
  class ListDataSource : IEnumerable<ListData>
  {
    private DataTable _Table;
    private string _TextField;
    private string _ValueField;

    public ListDataSource( DataTable table, string textField, string valueField )
    {
      _Table = table;
      _TextField = textField;
      _ValueField = valueField;
    }

    #region IEnumerable<ListData> Members

    IEnumerator<ListData> IEnumerable<ListData>.GetEnumerator()
    {
      yield return new ListData( String.Empty, String.Empty );
      foreach ( DataRow row in _Table.Rows )
        yield return new ListData( (string) row[ _TextField ], (string) row[ _ValueField ] );
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
      return ( (IEnumerable<ListData>) this ).GetEnumerator();
    }

    #endregion
  }
}

Let's pause for a minute and contemplate the power of what those two lines have. The return type of GetEnumerator is IEnumerator<ListData>, but nowhere are we implementing any of the methods of the IEnumerator interface. Instead, we are simply iterating through a collection and returning each value with yield return. The C# 2.0 compiler does the work of implementing the IEnumerable interface for you.

Well, you could add any bells and whistles to the class that you wanted to, of course. Maybe you want to be able to specify the text and value of the null item, or maybe you want access to those private members through properties. Let's fast-forward past all that and demonstrate the final application of this class: the ability to bind to the DataSource declaratively. To do so, open any aspx page in Source View, locate a list control such as a DropDownList, and add the following attributes to the opening tag:

DataSource='<%# new ListDataSource( ds.MyTable, "MyTextField", "MyValueField" ) %>' DataTextField="Text" DataValueField="Value"

Now, when the control is databound, it will bind to our custom DataSource and fill its items using the ListData objects yielded by GetEnumerator.

In this article, I took two concepts: that all it takes to implement a DataSource is to implement IEnumerable, and that all it takes to implement IEnumerable is to write a little code using the yield keyword. I combined these concepts to demonstrate how to quickly and easily create a custom DataSource that could implement any custom logic desired and still integrate declaratively with all existing ASP.NET controls. If you are in need of .NET training or consulting, please contact us here at Falafel Software.

Dr. T takes you through a well-explained tour of C# 3.0's new features. http://blogs.msdn.com/madst/archive/2007/01/23/is-c-becoming-a-functional-language.aspx

This is an interesting article about the performance and scalability of temp table and table variable in SQL Server 2000 and 2005. http://www.lakesidesql.com/articles/?p=12

In my previous blog, I wrote about how to create generic utility classes that would cast or convert any object to a given type, returning a default if the value was DBNull. I quickly discovered that this code will work just fine as long as the passed type is not nullable. For example, this will work:

int i = ConvertDBNull.To<int>( value, 0 );

But this won't:

int? i = ConvertDBNull.To<int?>( value, null );

The line that fails within the To<>() method is this:

return (T) Convert.ChangeType( value, t );

The reason the call to ChangeType() fails is that there are no conversions defined for the Nullable<> struct, a fact that I find rather puzzling, since it would have been quite easy to do. All you have to do is get the underlying type and convert to it instead. We will still cast the result to the nullable version of the type, because casting a value type to its nullable counterpart is allowed. Here is how to extract that underlying type:

How To Detect Nullable Types Using Reflection

First of all, we will need to be able to detect when a nullable type is passed to the conversion method. The .NET Framework doesn't provide anything that explicitly tests for nullable types, but we can use reflection to determine whether a type is nullable or not. The first thing you need to know is that nullable types are implemented by the framework by using a generic struct called Nullable<>. For example, the following two declarations are identical:

int? i;
Nullable<int> i;

Therefore, if a given type is nullable, it is a generic type whose type is Nullable<>.

private static bool IsNullable( Type t )
{
  if ( !t.IsGenericType ) return false;
  Type g = t.GetGenericTypeDefinition();
  return ( g.Equals( typeof( Nullable<> ) ) );
}

Solving The Conversion Problem

Now that we can detect a nullable type when it's passed, we will need to extract the underlying type. The underlying type is the first and only generic argument passed to the Nullable<> struct. Therefore, the underlying type can be obtained like this:

private static Type UnderlyingTypeOf( Type t )
{
  return t.GetGenericArguments()[ 0 ];
}

We simply need to substitute the underlying type for the nullable type in our original code to get the desired results:

public static T To<T>( object value, T defaultValue )
{
  if ( value == DBNull.Value ) return defaultValue;
  Type t = typeof( T );
  if ( IsNullable( t ) )
    t = UnderlyingTypeOf( t );

  return (T) Convert.ChangeType( value, t );
}

But wait! If you use this method to attempt to convert a null value to a nullable type, the result will be an invalid conversion, because you will be trying to convert null to a value type. We'll have to return without converting if the passed value is null:

public static T To<T>( object value, T defaultValue )
{
  if ( value == DBNull.Value ) return defaultValue;
  Type t = typeof( T );
  if ( IsNullable( t ) )
  {
    if ( value == null ) return default( T );
    t = UnderlyingTypeOf( t );
  }

  return (T) Convert.ChangeType( value, t );
}

Finally, we have a method that will convert any type, including nullable types, to any other type, including nullable types. I hope that you've enjoyed today's submission. If you need training or consulting, contact us and get the Falafel team working for you!

Oh, DBNull, how you complicate my code! You throw exceptions when I try to cast or convert you, forcing me to litter my code with conditionals that check for you first. Well, your days of plaguing my code with repetitive conditional expressions and operators are at an end, because I just wrote a new utility class that can cast an object to any type, returning a user-defined default value of that same type if the object is DBNull. And thanks to the power of C# 2.0 generics, it only took 7 lines of code. And 4 of those are curly braces.

On a more serious note, this article is really about the same thing as the last one I wrote: how to reduce or eliminate repetitive code. Today's inspiration came from the annoying stock behavior of the DBNull class, which actually goes to the trouble of implementing the IConvertable interface just so it can throw exceptions if any code tries to convert it. The result is that you end up writing a lot of code like this:

// Assume dr is a DataReader
object o = dr[ "Column1" ];
int i = ( o != DBNull.Value ) ? (int) o : 0; // or whatever default value makes sense

Code like this, while innocuous and relatively harmless, just kind of gets to a man after a while. I started to write a class that would test for DBNull before casting to a given type, and it looked kind of like this:

public static class CastDBNull
{
  public static string ToString( object value, string defaultValue )
  {
    return ( value != DBNull.Value ) ? (string) value : defaultValue;
  }
  public static int ToInt32( object value, int defaultValue )
  {
    return ( value != DBNull.Value ) ? (int) value : defaultValue;
  }
  // And so on
}

It didn't take long to recognize a pattern: I was writing the same code over and over again, varying only the type. This calls for generics! The resulting code after applying generics to the problem becomes much more compact:

public static class CastDBNull
{
  public static T To<T>( object value, T defaultValue )
  {
    return ( value != DBNull.Value ) ? (T) value : defaultValue;
  }
}

The generic version takes a type specifier that determines both the return type and the type of the default value. In case generic syntax is still new and strange-looking to you, perhaps an example of how to use this class will help illustrate its power and flexibility:

// Pass string type to cast to string
string s = CastDBNull.To<string>( dr[ "Column1" ], String.Empty );
// Same class, same method, but passing int type allows casting to int
int i = CastDBNull.To<int>( dr[ "Column2" ], 0 );

Calls to this class take a little less typing than the conditional expressions and operators they encapsulate, but more importantly to my fingers, I find them easier to type. I find that writing lots of little utility classes like this makes programming both faster and more enjoyable. Thanks to a flexible method in the Convert class, the same kind of logic can be applied to convert values generically rather than simply casting them:

public static class ConvertDBNull
{
  public static T To<T>( object value, T defaultValue )
  {
    if ( value == DBNull.Value ) return defaultValue;
    return (T) Convert.ChangeType( value, typeof( T ) );
  }
}

I hope that you've enjoyed today's submission. If you need training or consulting, contact us and get the Falafel team working for you!

I hate writing the same code over and over again. Whenever I do, ennui begins to set in and even writing the same five lines of code again suddenly becomes unbearable. That’s usually a good time to refactor the duplicate code into a method or utility class. I recently found myself writing some code like this that defied the usual Extract Method refactoring: code that created and opened a connection, created a command, executed a reader, iterated through the reader’s results, and did something on every record. Read on to see how to refactor this code into its own reusable class by applying the Template Method Design Pattern...

First let’s take a look at the shape of the code I was writing to examine why Extract Method wasn’t an appropriate refactoring. The code looked something like this:

using ( SqlConnection connection = new SqlConnection() )
{
  using ( SqlCommand command = new SqlCommand() )
  {
    using ( SqlDataReader reader = command.ExecuteReader() )
    {
      while ( reader.Read() )
      {
        // Do something
      }
    }
  }
}

All I really wanted to do was get at that SqlDataReader and process its results. However, I couldn’t just define a method that returned the SqlDataReader from the innermost using statement, because once the using statements are exited, the objects are disposed, so I would be left with a reference to a SqlDataReader with no open SqlConnection. However, there is a design pattern that will solve this problem: the Template Method pattern.

Strictly defined, the Template Method is a pattern whose intent is “Define the skeleton of an algorithm in an operation, deferring some steps to subclasses.” (Gamma, Erich, Richard Helm, Ralph Johnson, and John Vlissides. Design Patterns: Elements of Reusable Object-Oriented Software.) Let’s take that basic idea of defining the skeleton of an algorithm and deferring some steps, and apply it to this problem.

First of all, when I execute code like this, it’s usually a one-time kind of thing, so I don’t really want to have to define a new subclass every time I want to use my new Template Method implementation. Instead, I’m going to define a class that offers events that can be handled to provide the same kind of functionality.

The heart of my new Template Method implementation is the method that defines the skeleton that I keep finding myself writing, and all other design decisions will flow from it. In order for the method to be flexible, I’ll need to be able to pass a ConnectionString, some CommandText, a CommandType, a flexible number of parameters, and maybe a CommandBehavior. That’s a mouthful, and yet I want to keep the actual method signature as small as possible. Since it’s more common to execute multiple commands against the same connection than it is to execute the same command against multiple connections, I decided to make ConnectionString a property of the class rather than a parameter of the method. I’ve observed that the CommandBehavior parameter of SqlCommand.ExecuteReader() is often either omitted or fairly constant; I almost always use CommandBehavior.SingleResult. At any rate, I think that this is another parameter that is better left as a property of the class rather than part of the method signature. That leaves me with CommandText, CommandType, and SqlParameters as my method parameters.

The whole point of this Template Method was to provide a simple way to inject specific logic into the skeleton of some standard data-access logic. I chose to do this with events so I wouldn’t have to define subclasses every time I wanted different logic. In my experience, the two most common places that I do stuff with a SqlDataReader is on each record (processing data), and after the DataReader is closed (reading output parameters). Since I want this new class to be flexible, I will also provide an event that is raised when the DataReader moves from one result to the next. I’ll define a catch-all event type that provides access to the SqlDataReader (for access to the data) and the SqlCommand (for access to the output parameters), and then define three events all of this type that get raised on Read, on NextResult, and on Close. Let’s take a look at the final result of all these decisions:

public int Execute( string commandText, CommandType commandType, params SqlParameter[] parameters )
{
  using ( SqlConnection connection = new SqlConnection( _ConnectionString ) )
  {
    using ( SqlCommand command = new SqlCommand( commandText, connection ) )
    {
      command.CommandType = commandType;
      AddParameters( command, parameters );
      connection.Open();
      using ( SqlDataReader reader = command.ExecuteReader( _Behavior ) )
      {
        ReaderCommandEventArgs args = new ReaderCommandEventArgs( command, reader );
        do
        {
          RaiseEvent( NextResult, args );
          while ( reader.Read() )
            RaiseEvent( Read, args );
        } while ( reader.NextResult() );
        reader.Close();
        RaiseEvent( Close, args );
        return reader.RecordsAffected;
      }
    }
  }
}

AddParameters() is a helper method that adds the parameters to the command, but doesn’t throw an exception if the array is null. RaiseEvent() is another helper method that raises assigned event handlers while skipping unassigned ones.

My new Template Method class is ready for use! I've decided to call it a ReaderCommand, since it invokes the ExecuteReader method of a SqlCommand. Let’s compare the old code with the equivalent code using my new class. Here’s the old code:

using ( SqlConnection connection = new SqlConnection( connectionString ) )
{
  using ( SqlCommand command = new SqlCommand( commandText, connection ) )
  {
    command.Parameters.AddWithValue( "@Param1", "abc" );
    command.Parameters.AddWithValue( "@Param2", 123 );
    connection.Open();
    using ( SqlDataReader reader = command.ExecuteReader( CommandBehavior.SingleResult ) )
    {
      while ( reader.Read() )
        Console.WriteLine( reader[ 0 ] );
    }
  }
}

It’s not that this code is so bad, but there is a lot of boilerplate code in there, with the important varying bits scattered throughout, and no less than four levels of indentation, with the most important logic at the center of it all. Here’s the new code:

ReaderCommand rc = new ReaderCommand( connectionString, CommandBehavior.SingleResult );
rc.Read += new ReaderCommandEventHandler( rc_Read );
rc.Execute( commandText, CommandType.Text, new SqlParameter( "@Param1", "abc" ), new SqlParameter( "@Param2", 123 ) );

static void rc_Read( object sender, ReaderCommandEventArgs e )
{
  Console.WriteLine( e.Reader[ 0 ] );
}

The old code was 14 lines long, with data access and program logic all jumbled together. The new code is 7 lines long (and 3 of those line were auto-generated by the IDE), with the program logic cleanly separated from the data access. The result is code that is easier to read and easier to write.

Today I’ve demonstrated how the Template Method design pattern can reduce redundant code and result in code that is easier to read and write. I hope that this example will inspire you to refactor your own redundant code into reusable classes. For more information on this topic, I highly recommend Head First Design Patterns by Freeman & Freeman as an excellent way to get started with design patterns, and Design Patterns by Erich Gamma, et al. as the definitive initial catalog.

As an application developer, I often need to generate scripts for mutiple database objects, each in its own file, with both a DROP and a CREATE statement. In SQL Server 2000, there was a robust set of scripting options to choose from which allowed exactly this kind of use. However, in SQL Server 2005, these options have vanished. All selected objects get put into a single script file, and there is no way to generate DROP and CREATE in a single script. Some options can be accessed by choosing the Tasks | Generate Scripts... menu item at the database level, but they don't provide the desired functionality, and the wizard itself is cumbersome to navigate.

In response to this loss of functionality, Bill Graziano wrote a utility that mimics these commonly-used options of SQL scripting. The original blog, which contains links to an installer as well as source code, can be found here.

A lot has been written about the merits of multiple narrow indexes versus single larger compound indexes. This blog by Grant Fritchey yields juicy food for thought on that very topic, backed by some very thorough testing with real-world data.

While browsing the web today, I came across a recommendation for Foxit Reader as an alternative to the more well-known Adobe Acrobat Reader. After downloading it and trying it out, I am happy to report that my first impressions of the product are very positive. Some of my biggest causes for discontent with Adobe Acrobat were the sluggish loading times and persistent nagging to upgrade, and both of these are a thing of the past with Foxit. Now when I double-click a PDF file, it opens in the blink of an eye! Farewell, Acrobat. Your slowness and nagging won't be missed.

I'm still playing around with that MakeRegion macro that Philip first posted. Today I added a regular expression to parse the first line of code and extract only the member name instead of the entire line.

For instructions on how to install, see The Blog That Started It All.

Imports EnvDTE
Imports EnvDTE80
Imports System.Diagnostics
Imports System.Text.RegularExpressions

Public Module Regions

  Sub MakeRegion()
    Regions.MakeRegion()
  End Sub

  Public Class Regions
    ' MakeRegion inserts #region and #endregion tags
    ' around selected text in the VS editor.
    Shared Sub MakeRegion()
      Dim rName As String = ""
      Dim pad As String = ""
      Dim junk As String
      Dim count, i As Integer
      Dim startpoint, endpoint, tmppoint As EditPoint

      With DTE.ActiveDocument.Selection
        startpoint = .TopPoint.CreateEditPoint()
        endpoint = .BottomPoint.CreateEditPoint
      End With

      If startpoint.EqualTo(endpoint) Then
        Exit Sub
      End If

      'ELR: ADDED THIS, to move the startpoint to the start of the line()
      'so that the Pad function works correctly
      If Not startpoint.AtStartOfLine Then
        startpoint.StartOfLine()
      End If

      Dim DefaultResponse = GetDesc(DTE.ActiveDocument.Selection.TopPoint.CreateEditPoint())
      Dim re As Regex = New Regex("\s(\w+)\s*(?:\(|\:|$)")
      DefaultResponse = re.Match(DefaultResponse).Groups(1).Value

      'IV 2004-12-13: rName = InputBox("Region Name:")
      rName = InputBox("Type the name you want to give to the region, which will appear in the Visual Studio Code Editor.", "Make Region", _
      DefaultResponse)

      rName = rName.Trim()

      If rName.Length = 0 Then
        Exit Sub
      End If

      DTE.UndoContext.Open("Insert A Region")
      Try
        junk = startpoint.GetText(startpoint.LineLength)

        pad = String.Empty
        For count = 0 To junk.Length - 1
          If junk.Substring(count, 1).Equals(" ") _
          Or junk.Substring(count, 1).Equals(vbTab) Then
            pad += junk.Substring(count, 1)
          Else
            Exit For
          End If
        Next

        'ELR: ADDED Test for Languages
        If DTE.ActiveDocument.Language = "CSharp" Then
          ' C Sharp Code
          startpoint.Insert(String.Format("{0}#region {1}{2}", _
           pad, rName, vbCrLf))
          If endpoint.LineLength = 0 Then
            endpoint.Insert(String.Format("{0}#endregion {1}{2}", _
             pad, rName, vbCrLf))
          Else
            endpoint.Insert(String.Format("{0}#endregion {1}", _
             vbCrLf & pad, rName))
          End If
        Else
          ' VB Code
          startpoint.Insert(String.Format("{0}#Region {1}{2}", _
           pad, rName, vbCrLf))
          If endpoint.LineLength = 0 Then
            endpoint.Insert(String.Format("{0}#End Region '{1}{2}", _
             pad, rName, vbCrLf))
          Else
            endpoint.Insert(String.Format("{0}#End Region '{1}{2}", _
             vbCrLf & pad, rName, vbCrLf))
          End If
        End If
      Finally
        DTE.UndoContext.Close()
      End Try
    End Sub

    ' IV: Get the description from the 1st line of code in the region
    ' i.e. ignore c# comment tags (///) or take 1st line of the comments (//)
    ' Requires adjustments for VB and other langs
    Private Shared Function GetDesc(ByVal startpoint As EditPoint) As String
      Dim line As String = ""
      Dim tmppoint As EditPoint

      line = startpoint.GetText(startpoint.LineLength)
      If (line.Length > 0) Then
        line = line.TrimStart(" ", vbTab)
        If DTE.ActiveDocument.Language = "CSharp" Then
          If (line.StartsWith("///")) Or (line.StartsWith("[")) Then
            tmppoint = startpoint
            tmppoint.LineDown()
            line = GetDesc(tmppoint)
          ElseIf (line.StartsWith("//")) Then
            line = line.TrimStart("//", " ")
          End If
          line = line.Replace("{", String.Empty)
        End If
        line = line.TrimEnd(" ", vbTab)
      End If
      Return line
    End Function
  End Class

End Module

Here's a little code snippet that will search a collection of Controls for the first control of a specified type.

private object FindControlByType( Control root, Type t )
{
  if ( root != null && root.GetType().Equals( t ) )
    return root;
  foreach( Control c in root.Controls )
  {
    object node = FindControlByType( c, t );
    if ( node != null && node.GetType().Equals( t ) )
      return node;
  }
  return null;
}

Example: the following example returns the first instance of an HtmlForm in a Page's Control collection:

HtmlForm form = (HtmlForm) FindControlByType( Page, typeof( HtmlForm ) );

Danc of Lost Garden has written many interesting and thought-provoking articles, mostly about game design. Today's article, however, is wider in scope, addressing the evolution of product design, and is well worth the read.

Is Courier New just not doing it for you anymore? This page summarizes and rates different fixed-width fonts based on their suitability for use in a programming environment. The reviewer evaluates several factors including how easily the characters 'l', '1', and 'i' are distinguised, as well as the characters '0', 'o', and 'O'. After browsing several of the handy font previews, simply follow the associated link to download your new favorite programming font. The top-rated font, Bitstream Sans Vera Mono, looks great without ClearType and awful with it, so if you have font smoothing enabled, I recommend you look further down the list for something that plays better with ClearType

I love working with MS SQL Server. Oh sure, Oracle is supposedly faster and all that jazz, but when it comes to rapid development, MSSQL's ease of use just can't be beat. However, there are still some things to watch out for. Today I want to warn you all off of using UNION.

Conceptually, there's nothing wrong with UNION. It takes two result sets with congruent columns and returns them as a single result set. There are two potential performance issues, however, one of which can be quite serious.

The first problem is that the UNION operator automatically performs a DISTINCT operation on the combined result set. If it's unnecessary, or worse, if it is necessary, but you've also used SELECT DISTINCT in one or both of the UNIONed queries, then UNION is causing a loss of performance for nothing. The workaround to this is simple; use UNION ALL instead.

The second problem can be far worse. I've run into situations many times where the UNION (or UNION ALL) operator caused inexplicably poor performance. In one recent scenario, I used UNION ALL to merge the results of two queries, each of which ran in about 7 seconds. When merged using UNION ALL, query execution time leapt up to 1:20. Neither the estimated execution plan nor the actual one reflected this; the total subtree cost was exactly the same as the sum of the two queries' costs when run separately. The fix is a little more complex, but extremely effective.

My solution to the problem is to use a table variable. In general, I recommend the use of table variables over temp tables unless you absolutely need a feature that temp tables support but table variables do not.

Example

The example below is not intended to demonstrate good database design; it merely serves to illustrate the solution. Given the original query:

SELECT FirstName, LastName, Salary
FROM Employees
UNION ALL
SELECT FirstName, LastName, Salary
FROM Managers

First declare a table variable with columns that match that of the result set:

DECLARE @Result TABLE (
  FirstName varchar(50),
  LastName varchar(50),
  Salary money,
  -- Example of how to declare a PK within a table variable
  PRIMARY KEY ( LastName, FirstName )
)

Next, insert each query into the result table:

INSERT @Result
SELECT FirstName, LastName, Salary
FROM Employee

INSERT @Result
SELECT FirstName, LastName, Salary
FROM Manager

Finally, select the results:

SELECT *
FROM @Result

This workaround will not only avoid potential performance problems with UNION, but will actually allow you to improve join performance through the definition of a good primary key, if the UNION result is joined to other data.

Build events are handy ways to perform tasks that need to be done every time a project is rebuilt. The Visual Studio IDE exposes these events through the project properties dialog for windows applications, but not for web applications. However, the IDE can be coerced into supporting build events for web applications, and it's surprisingly easy to do. Simply open your web project's .csproj file and look under the settings element for the following elements:

<VISUALSTUDIOPROJECT>
  <CSHARP>
    <BUILD>
      <SETTINGS>
        <!-- Your Build Events Here -->
        PreBuildEvent = ""
        PostBuildEvent = ""
        <!-- RunPostBuildEvent = "Always" | "OnBuildSuccess" | "OnOutputUpdated" -->
        RunPostBuildEvent = "OnBuildSuccess"
      </SETTINGS>
    </BUILD>
  </CSHARP>
</VISUALSTUDIOPROJECT>

Simply enter your command-line actions to PreBuildEvent and/or PostBuildEvent and enter the string describing the desired condition in RunPostBuildEvent. Save the file, and you're good to go.

Expression columns are a useful feature of ADO.NET that allows developers to define calculated columns that are maintained on the client side using local data. This is a significant improvement over performing calculations within a SQL query, because expression columns can recalculate based on changes to local data without requiring a round trip to the database server, and less data needs to be transferred from server to client. Besides being able to perform simple arithmetic on values within the same row, expressions can also retrieve values from parent and child rows in a relational dataset, thus providing lookup and aggregation functionality as well. For detailed information on expression column syntax, see the DataColumn.Expression property help topic.

As useful as expression columns can potentially be, however, they come with a substantial limitation: they cannot be used in conjunction with an insert or update query that automatically refreshes row values from the database server. This is because of the way that the .NET framework applies the retrieved values during an update operation, which is to iterate through all columns in the table, expression columns included, and update their values, temporarily setting the ReadOnly property to false. When this operation is attempted on an expression column, the following exception message will result: “Cannot change ReadOnly property of the expression column.” This has been a known bug since 2002, and Microsoft has supposedly promised a fix for the next version of .NET, although it is unclear whether they mean the next version of the framework or of Visual Studio. Since the bug still exists in Visual Studio 2003 and .NET framework 1.1, one can only hope that this bug will be fixed in Visual Studio 2005 and .NET framework 2.0. Until then, there are several workarounds possible, which will be explained in the following paragraphs.

          Since this bug occurs when expression columns are mixed with insert or update queries that refresh the dataset, the only solution is to remove one of the two factors. The first and simplest solution is to remove the select query from the end of the insert or update query. However, this solution has a drawback in that the row will not be refreshed, which can be especially vexing if the database is generating the primary key field.

          The next solution takes the previous one a step further by writing custom code in the RowUpdated event. The RowUpdated event fires after changes have been applied to the database. Custom code could be added to this event to more selectively and intelligently update only the fields in the dataset that correspond to fields in the database. For example:

private void sqlDataAdapter1_RowUpdated(object sender,

  System.Data.SqlClient.SqlRowUpdatedEventArgs e)

{

  DataColumn dc = e.Row.Table.Columns[ "RowID" ];

  bool readOnly = dc.ReadOnly;

  dc.ReadOnly = false;

  try

  {

    SqlCommand cmd = new SqlCommand( "select @@IDENTITY", conn );

    e.Row[ "RowID" ] = cmd.ExecuteScalar();

    e.Row.AcceptChanges();

  }

  finally

  {

    dc.ReadOnly = readOnly;

  }

}

          Another possibility is to remove the expression columns from the bug-causing equation, rather than removing the automatically supported dataset refresh. In this approach, each call to DataAdapter.Update() would be bracketed by deletion and recreation of all expression fields in the DataSet. For example:

ArrayList al = new ArrayList();

// Save expression columns

foreach ( DataColumn dc in ds.Table1.Columns )

  if ( dc.Expression != "" )

    al.Add( dc );

// Delete saved columns from collection

foreach ( object o in al )

  ds.Table1.Columns.Remove( (DataColumn) o );

try

{

  da.Update( ds.Table1 );

}

finally

{

  // Restore saved columns

  foreach ( object o in al )

    ds.Table1.Columns.Add( (DataColumn) o );

}

          Lastly, some expression column functionality can be duplicated through custom data binding expressions. To do this, it is first necessary to explain the syntax data bindings created at design time. When creating a simple data binding through the Data Bindings dialog, Visual Studio creates a DataBinder.Eval() method call. The role of the DataBinder class is to simplify writing data binding expressions by eliminating two tasks required by data binding expressions that do not use DataBinder. These two tasks are: casting the object containing the data, and converting the data to the correct data type, which is typically a string. To see these advantages at work, consider the following line of code, which is a typical example of the kind of expression required to bind non-string data in a repeating control such as a DataList:

lblDisplay.Text =

  ((DataRowView) container.DataItem)[ "IntField" ].ToString();

If special formatting was needed, then the code would expand to the following:

lblDisplay.Text =

  String.Format("{0:d10}",

    ((DataRowView) container.DataItem)[ "IntField" ] );

This same task can be accomplished by using DataBinder.Eval(), as shown in the following example:

lblDisplay.Text =

  DataBinder.Eval( container.DataItem, "IntField", "{0:d10}" );

The benefit is that explicit casting isn’t necessary and the format string may be accessed as an extra parameter of the Eval() method rather than requiring a call to the static String.Format() method. However, the savings in typing have a cost in runtime speed. When writing your own custom data binding expressions, consider using the longer form as a way to improve application performance.

          What is important to understand when writing custom data binding expressions is that there isn’t anything magic about using DataBinder.Eval(). Any expression written in the page language that returns a value can be used as a custom data binding expression. Thus, to perform a simple lookup in a data binding expression, enter the following expression as a custom data binding expression. Assume the dataset ds contains two tables named Table and Lookup, with a relationship defined between them with the default name LookupTable. The example code returns the value of the field LookupValue in the table Lookup.

// Typed dataset syntax

ds.Table[0].GetLookupRow.LookupValue

// Untyped dataset syntax

ds.Tables["Table"].Rows[0].GetParentRow("LookupTable")["LookupValue"]

Likewise, aggregate expression columns can be replaced with equivalent DataTable.Compute() method calls. To extend the above example, assume that the table called Table has a child table called Child containing a field called Amount and related by the field TableID. The relationship in the typed dataset is called TableChild. The following expression column (defined in Table) and DataTable.Compute() calls will yield the same results:

// Expression column expression

Sum( Child( TableChild ).Amount )

// Equivalent DataTable.Compute() for the current TableRow

Child.Compute( "Sum( Amount )", "TableID=" + TableRow.TableID );

Armed with this knowledge, it may be possible to entirely avoid the need for creating any persistent expression columns in the dataset.

          Expression columns provide useful and powerful tools to create columns that can recalculate themselves in response to data changes without making a round trip to the database server while keeping the size of the data transmitted as compact as possible. However, in the current version of .NET, they come with significant limitations that will require extra effort on the developer’s part. If you need both the functionality of expression columns and to refresh row values from the database on insert or update, the approaches described above will provide you with several options with which to work around this bug. Here’s hoping it really gets fixed in the next version!