Inspecting SQL Server’s tables

A few days ago, I was working on a small project for autogenerating source code from a SQL Server database, and I bumped into the problem of obtaining from a given server the description of the tables in it. If using MySQL this task is quite simple, however, using SQL Server the problem is not that simple.

Long story short, after a few hours I came up with this “small” piece of code:

     sys.tables.object_id AS TableId,
     sys.columns.column_id AS ColumnId, AS ColumnName, AS TypeName,
     sys.columns.precision AS NumericPrecision,
     sys.columns.scale AS NumericScale,
     sys.columns.is_nullable AS IsNullable,
          TABLE_NAME = AND
               TABLE_NAME = AND
               constraint_type = 'PRIMARY KEY' AND
               COLUMN_NAME =
     ) AS IsPrimaryKey,
     sys.columns.max_length / 2 AS CharMaxLength
     sys.columns, sys.types, sys.tables
     sys.tables.object_id = sys.columns.object_id AND
     sys.types.system_type_id = sys.columns.system_type_id AND
     sys.types.user_type_id = sys.columns.user_type_id AND = '{0}'

Nice uh?

Basically the whole deal after this code is just inspecting into the sys database, which holds the information about the tables in the database. Obviously the ‘{0}’ should be replaced with the table in question. I place the ‘{0}’ because this code was meant to be running using C#, so, all I needed was a call to string.Format.

After retrieving the data with the tables, it would be nice to inspect the relationships between those tables, uh? Well, that is simpler than you might think, just we need to retrieve the relationships list, again from the same database. SQL code for doing that is this one:

SELECT AS RelationshipName,
     sys.foreign_keys.object_id AS RelationshipId,
     sys.foreign_keys.parent_object_id AS ParentTableId,
     sys.foreign_keys.referenced_object_id AS ReferencedTableId,
     sys.foreign_key_columns.parent_column_id AS ParentColumnId,
     sys.foreign_key_columns.referenced_column_id AS ReferencedColumnId
     sys.foreign_keys, sys.foreign_key_columns
     object_id = constraint_object_id; 

After having all that data, we are in conditions to generate any source code based on any database inspected from a SQL Server 2k5/2k8 database, which is a cool thing.

Helpers vs Extenders

One of the most important features we need to achieve in our life as developers is to write code which at some point can be reused. Reuse is tricky word because sometimes we find ourselves saying “it just needs a tiny modification” when it needs some major rewriting. Making reusable code is not just writing a snippet of code and then copy it and write it down again, it’s having libraries of code that don’t need any sort of modification (in the worst case scenario, some small ones).

This takes me to the point of this post, which is considering method extensions when developing applications. Let’s see the following problem, suppose we need for a certain task to retrieve the names of the properties available in an object, but we need to make this as generically as possible, because we don’t know when we could need the same feature again.

The initial approach is to do a small static class which does the job, most people tend to call these classes helpers. Helpers are there to “help” our code doing stuff that might be needed by several parts of the same application. To our problem, the helper code would look like this:

public static class ReflectionHelper
    public static List<string> GetMethods( object who )
        //Implementation of the method

This approach does work most times, but is not nice in the refactoring sense of development. We need to keep these kinds of helper objects to the smallest amount in any design we make, because by following this pattern, we can end up in one of two possible scenarios.

The first possible ending is that we might end up with a few helper classes doing just a few functionalities, sometimes providing a really horrible refactoring nightmare. The second possible scenario is that we might end up with one monster helper with a lot of helper methods, which will make the previous nightmare look like a pleasant dream.

There are two more plausible solutions to this problem. The first one applies to the case where retrieving the list of methods is part of the work of our objects; to this case we could apply the scenario of an ORM framework when it’s mapping the properties to an object and retrieves the properties using reflection. Anyways, in this case we could add a base class called ReflectableObject, which provides all the required functionalities.

If we choose to follow the ORM sample, we would have a class diagram having a Model class and to complicate things a bit, we could also have a repository class. Both would need to perform some sort of reflection on the objects they manage, so they would require it. The class diagram would be like this:

This way works pretty well for the cases where we want to include this behavior into the object, but this does pose a few problems:

  • We would need to add an extra layer of classes to our design, thus complicating the design
  • When we have only one class needing to perform the actual operation we need to create, adding another layer of complexity would feel like killing a fly with a rail gun.
  • If by any chance of fate we are working with sealed classes, inheritance is automatically discarded.

Finally, there is one last option: Using extension methods. Extension methods are there to provide a flexible and reusable way to extend objects that are there already, and we do not want to either modify or inherit.

We can think of extensions as a light inheritance, we say: “OK, we have this class and we want to add a few methods to it, but we don’t want to create a new class and inherit from it, so what do we do?”

We extend the class.

The code to extend a class is quite similar to the helper, but instead we include a reference to the class we are planning to extend in the method, like this:

public static class ReflectionHelper 
     public static List<string> GetMethods( this object who ) 
          //Implementation of the method 

Is quite simple, suppose that our class is in the ReflectionHelpers namespace, we would be able to do this then:

using ReflectionHelpers; 
using System; 

namespace MyNamespace 
    public class MyClass 
        public void Foo() 
            object o = SomeWeirdOperation(); 
            var methods = o.GetMethods(); 

All objects can call now the GetMethods method! By doing this we are successfully removing the extra layer from the design, we are getting the job done and it doesn’t requires new classes to be added, if we change the ReflectionHelper class name to Reflexive it wouldn’t matter, because all that matters is that the GetMethods method is extending the object class as specified in the object signature.

Extension methods are the base of LINQ, and perhaps one of the most powerful tools implemented in the .NET framework.

Simple class for data access using ADO.NET

Many data access layers we see today are meant for medium to big projects, and building the required models, and loading the required dll’s in a small project sometimes feels like trying to kill a fly with a mortar. So, what do we do most times? Write some plain ADO.NET access code to retrieve the data and that’s it, we don’t want to mess our code with NHibernate, Entity Framework or DataSets only for retrieving data from a single (or two) tables. If we change providers, all we would do is just change the class names.

This approach is simple, but with a little tweaks we can make it reusable, ergo we don’t have to rewrite the code is it would be a fairly plain database access. To accomplish this, we will make a small mix of singletons and dictionaries, to be able to talk to different databases at the same time. I’ve found this feature useful, since I use SQLite for caching my request and MySQL to handle big stuff that I require saving on DB.

So, our singleton code would be like this one:

private static Dictionary<string, Database> _instances;

public static Database Get(string flavour)
    if ( _instances == null )
        _instances = new Dictionary<string, Database>();

    if (_instances.ContainsKey(flavour))
        return _instances[flavour];

    var dbProvider = DbProviderFactories.GetFactory(flavour);
    var connectionStrings = ConfigurationManager.ConnectionStrings;
    var cs = connectionStrings[flavour].ConnectionString
                              .Replace("|HomeDir|", Environment.CurrentDirectory)
                              .Replace("'", """);
    _instances.Add(flavour, new Database(dbProvider, cs));
    return _instances[flavour];

private DbConnection _connection = null;
private DbProviderFactory _factory;

private Database(DbProviderFactory factory, string connectionString)
    _factory = factory;
    _connection = _factory.CreateConnection();
    _connection.ConnectionString = connectionString;

As you can see, we use the DbProviderFactories class to load the required factory classes for our project. A sample app.settings file would be like this one:



The basic idea is to be able to load the factory required for our flavor from an XML file and forget about instantiation of classes. This way, we can truly reuse our code. Now we need to mimic the 3 important methods of any ADO.NET command:

  • ExecuteScalar
  • ExecuteNonQuery
  • ExecuteReader

But, we also need to support parameters, and to do so, we will just pass parameters as a list of KeyValuePair objects. With that in mind, let’s create an utility method to create the command for a given query:

private DbCommand CreateCommand(string query, 
                                params KeyValuePair<string, object>[] args)
    var cmd = _factory.CreateCommand();
    cmd.Connection = _connection;
    cmd.CommandText = query;

    foreach (var argument in args)
        var param = _factory.CreateParameter();
        param.ParameterName = argument.Key;
        param.Value = argument.Value;


    return cmd;

With this utility method, creating one of the proxies for ADO.NET is quite simple. The ExecuteScalar proxy would be like this one:

public object ExecuteScalar(string query, params KeyValuePair<string, object>[] args)
    var cmd = CreateCommand(query, args);

        object scalar = cmd.ExecuteScalar();

        return scalar;
    catch (Exception)
    { return null; }

This one is quite easy uh? Now, we wont return a DbDataReader in the ExecuteReader, because it would be simple to read the values and return a List of dictionaries, where each Dictionary object would represent a given row.
Since I don’t return a DbDataReader, I thought that ExecuteReader wouldn’t be OK, so I changed the name to ExecuteList. The basic layout of the method is like the past one, but once we have the DbDataReader, we would fill the rows, like this:

while (reader.Read())
    var row = new Dictionary<string, object>();
    int fcount = reader.FieldCount;

    for (int i = 0; i < fcount; i++)
        string fName = reader.GetName(i);
        object val = reader.GetValue(i);

        if (reader.IsDBNull(i))
            val = null;

        row.Add(fName, val);


<p>And we return the result, which is of type List&lt;Dictionary&lt;string, object&gt;&gt;. Cute uh?</p>
<p>With these three methods, we have a jumpstart for a nice (a very simple) data access class, but what would be of .NET without types, so we would expect to have a typed retrieval method, something like this:</p>
public List<t> ExecuteList<t>(string query, params KeyValuePair<string, object>[] args)

Where T is (hopefully) a POCO class, and we just want to fill it. This is a lame attempt of doing some ORM, but as we said earlier, the idea is to create a small reusable class for data access in small apps, most likely for small jobs or personal projects.
Back to the problem, we would need a method to receive a dictionary and return an object of type T, and using the wondrous Reflection, we can do this:

private T ParseClass<t>(Dictionary<string, object> hash)
    Type t = typeof (T);
    var properties = t.GetProperties();
    var instance = Activator.CreateInstance(t);

    foreach (var property in properties)
       if ( property.CanWrite && hash.ContainsKey(property.Name) )
        property.SetValue(instance, hash[property.Name], null);

    return (T)instance;

I’m making a few assumptions here.

  1. First, I use classes to access data, not DataTables, since I find Types much more convenient at development time. I think we can agree that this:
        var name = st.Name;

    Is much friendlier than this:

        var name = (string)dt.Rows[“Name”];

  2. Second, this magic method happens only for simple classes, which work as POCOs so we don’t have to worry about read only properties, or strange fields or highly complex structures, just some small properties, keep in mind, that for medium projects, using a small class like this could (most times IS) fatal to your application/design.

With that in mind, the last method for typed data retrieval would look like this:

public List<t> ExecuteList<t>(string query, params KeyValuePair<string, object>[] args)
    var dictionary = ExecuteList(query, args);
    return dictionary.Select(ParseClass<t>).ToList();

As I’ve said many times in this post, this class would be only for small projects, but it does help to have a something like that, because we might find a few personal projects with small lines of code using the same data access code and retyping it again and again is not going to make it better. Remember that any snippet of code that you type over and over in projects, no matter how small it is, can be abstracted into a bit more complex (and useful) library.

I hope that this code can help someone out there, feel free to comment!