A code snippet to connect to SQL Server's Northwind sample database

As I'm often running demos at seminars and customer meetings, I find myself writing the same and same snippets of code all over again. Although I've moved to ADO.NET Entities quite extensively for accessing SQL data, I still need quite often to demonstrate a quick data access to for example SQL Server, and just fetch a records or two from a sample database.

Most often, I'm using the old Northwind sample database (which is more or less replaced by AdventureWorks, but I find Northwind to be simpler and easier to grasp for quick demos), which contains a basic sales model with customers and orders that everybody understands.

Now, I quite often write code to take a customer ID, and fetch the company name based on the ID. This is trivial with SQLConnection and SQLCommand classes, but the work gets repetitive at best. In case you find yourself in a similar situation, then feel free to copy and paste the following C# code (tested with Visual Studio 2008):

using System.Data.SqlClient;
...
public static string GetCustomerDetails(
  string customerId)
{
  string connStr = "Data Source=myserver;"+
    "Initial Catalog=Northwind;"+
    "Integrated Security=True";
  SqlConnection conn = new SqlConnection(
      connStr);
  try
  {
    conn.Open();
    string sql = "SELECT * " +
        "FROM [customers] " +
        "WHERE [customerid] = @custid";
    SqlCommand cmd = new SqlCommand(
        sql, conn);
    cmd.Parameters.AddWithValue(
        "@custid", customerId);
    try
    {
      SqlDataReader reader =
          cmd.ExecuteReader();
      try
      {
        if (reader.Read())
        {
          string company =
              reader.GetString(1);
          return company;
        }
      }
      finally
      {
          reader.Dispose();
      }
    }
    finally
    {
        cmd.Dispose();
    }
  }
  finally
  {
      conn.Dispose();
  }
  return null;
}

As you can see, the code is very easy to follow, and basically all you need to do is change the connection string and make sure you have the Northwind sample database installed (I mostly create it using a custom SQL script that I've created by extracting it from an existing Northwind database). And true, the code could be made shorted for instance using C#'s using statements, but the point here is to create quick code and not necessarily show the best practices.

Hope this helps!