Last week I re-discovered code snippets in Visual Studio 2005. Well, at least the part about how easy it is to make your own code snippets. Don't get me wrong, I haven't typed a single property definition by hand since I installed VS2005 and the "prop" snippet (and before that I used third party plug-ins and macros to do properties). In C# 3.0 and VS2008 writing properties is even easier with the new compact property syntax. But that's a different story (which I'm going to tell in my C# 3.0 and LINQ session at MSDN Live fall 2007). This post is about code snippets.

In my last post about re-throwing exceptions I briefly described the project I've been working the last 6 months, so I'm not going to spend too much time recapping on this. The project is a v2.0 of an internal application written by the IT-department of the health region. Since this is a v2.0 much of the grunt work is all ready done, and much of the plumbing is in place. The core architecture and coding style is defined, and most of the project structure is in place. Last week we (me and Mats) where adding some new features involving some new tables in the database. Following the architecture of the application we had to add code and classes all the way through all the layers (data access, business layer, service layer, UI process layer, UI layer). The data access layer is using the Data Access Application Block from the Patterns and Patterns team, but we still did lots of copy-and-paste re-use of other pieces of "create db command, add parameters, execute stored procedure, parse result set and return entity object"-code. A typical piece of code looked something like this: 

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
{
    SqlCommand command = new SqlCommand("GetItems", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@author", "Jonas Folleso");

    using (command)
    {
        command.Connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            List<Item> items = new List<Item>();
            while (reader.Read())
            {
                Item item = new Item();
                item.ID = reader["id"] == null ? -1 : Convert.ToInt32(reader["id"]);
                item.Title = reader["title"] == null ? string.Empty : reader["title"] as string;
                items.Add(item);
            }
            reader.Close();
        }
        command.Connection.Close();
    }
}

Writhing code like this was getting boring (and inefficient) so it was time to be a true programmer and find a cleaner, easier way to solve this problem. Since most of the project structure and style was all ready set we didn't want to make to dramatic changes, and introducing fancy things like ORM's, LINQ, new base classes and whatnot was not an option at this point of the project. Instead I remembered how easy it is to create custom code snippets in VS2005. You simply create an XML file containing a snippet definition, and store the file with a .snippet extension in your "Documents\Visual Studio 2005\Code Snippets\C#"-folder.

For the code sample above we created a bunch of code snippets following the coding style all ready established in the project. We created snippets like "reader" for the core structure of executing a stored procedure and parsing the reader. We created snippets like "readint", "readstring", "readbool" and "readdate" to read columns from the result set, and we used snippets like "addint", "addstring" etc. to add columns to the command object. I've included the entire snippet definition of the "reader" snippet to give you some impression of what a custom code snippet looks like. The snippet format is well documented, and you can find a bunch of other samples online if you want to learn more about it.

<CodeSnippets
    xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Execute Sql Data Reader</Title>
      <Author>Jonas Follesø</Author>
      <Description>A standard way to execute a stored procedure returning a reader</Description>
      <HelpUrl>http://jonas.follesoe.no</HelpUrl>
      <Shortcut>reader</Shortcut>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>spname</ID>
          <ToolTip>Name of stored procedure</ToolTip>
          <Default>mySproc</Default>
        </Literal>
        <Literal>
          <ID>connectionName</ID>
          <ToolTip>Name of the SQL connection in the configuration file</ToolTip>
          <Default>myConnection</Default>
        </Literal>
      </Declarations>
      <Code Language="csharp">
        <![CDATA[using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["$connectionName$"].ConnectionString))
            {
                SqlCommand command = new SqlCommand("$spname$", connection);
                command.CommandType = CommandType.StoredProcedure;                
                
                using (command)
                {
                    $end$
                    command.Connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {                         
                        }
                        reader.Close();
                    }
                    command.Connection.Close();
                }
            }]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

I've updated the snippets to make them useful for any C# programmer writing data access code by hand, using ADO.NET (the one we use at work depends on the data access application block). The snippets are packed into one ZIP file you can download and extract into your local snippet folder. If you want to squeeze some extra productivity out of Visual Studio Microsoft have uploaded a C# snippet pack (containing some of the stuff the VB guys got out of the box, but we missed out on…).