Enhancing the ADO Repository with CRUD functionality

September 4^th, 2015

Today, we extend the functionality of the basic ADO Repository we built by adding a Create, Update, and Delete without an Object-Relational Mapper (ORM).

A while ago, we discussed how to create a generic repository pattern without an ORM like entity framework or NHibernate.

One reader mentioned that it should have CRUD (Create, Retrieve, Update, and Delete) functionality. While this was an easy task implementing the Retrieve in the post, it was more of a challenge to implement the CUD.

If you have never used an ORM (Object-relational Mapping) library before, it does makes your life so much easier. You don't need to create the queries, know the field names, or keep track of any other entity specifics.

A company I worked for in the past had a SQL Server 2000 database and I couldn't use Entity Framework because it wasn't supported. So I had to "make-do" with some code to fix this issue. While I didn't implement this solution, it would've made life a little easier to maintain, but hindsight is always 20/20.

For this post today, I don't want to reinvent the wheel or create a brand new ORM from scratch, but I thought it would be easier to create a simple SqlQueryBuilder class to automatically generate a create, update, and delete SQL Statement to send to a table.

We need to decorate the object so we can extract the right information from the object to build a proper SQL statement.

So let's get started.

Modify our Business Object

With an ORM, the table name, keys, and field matching are automatically determined based on the table, but for our purposes, we'll just use the Northwind database and use the Categories table.

For right now, we have a simple Category POCO (Plain old CLR object). I generated it the old fashioned way...I typed it in.

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public string Description { get; set; }
}

We need to identify a couple things. One, we need to know where the categories are "housed" in the database. Luckily, we have a TableAttribute to help us out.

Two, we need to identify which property is the table's key. Again, we can use the KeyAttribute.

Now, our Category class looks like this:

[Table("Categories")]
public class Category
{
    [Key]
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public string Description { get; set; }
}

We're done with the business object. Now to focus on our AdoRepository.

AdoRepository

In our AdoRepository, I added an additional method called ExecuteCommand.

protected void ExecuteCommand(SqlCommand command)
{
    command.Connection = _connection;
    command.CommandType = CommandType.Text;
    _connection.Open();
    try
    {
        command.ExecuteNonQuery();
    }
    finally
    {
        _connection.Close();
    }
}

Simple, right?

I know we used a OldFaqRepository in the last post, but here is a CategoryRepository that inherits from the AdoRepository.

public class CategoryRepository : AdoRepository<Category>
{
    public CategoryRepository(string connectionString)
        : base(connectionString)
    {
    }
 
    #region Get
 
    public IEnumerable<Category> GetAll()
    {
        // DBAs across the country are having strokes 
        //  over this next command!
        using (var command = new SqlCommand("SELECT * FROM Categories"))
        {
            return GetRecords(command);
        }
    }
 
    public Category GetById(string id)
    {
        // PARAMETERIZED QUERIES!
        using (var command = new SqlCommand("SELECT * FROM Categories WHERE CategoryID = @id"))
        {
            command.Parameters.Add(new ObjectParameter("id", id));
            return GetRecord(command);
        }
    }
 
    #endregion
 
    public override Category PopulateRecord(SqlDataReader reader)
    {
        return new Category
        {
            CategoryID = reader.GetInt32(1),
            CategoryName = reader.GetString(2),
            Description = reader.GetString(3)
        };
    }
}

With everything updated, now we can focus on our Insert, Update, and Delete methods.

Keep in mind that this code was a rudimentary attempt at inserting, updating, and deleting records relatively quick. This is by no means full-fledged production code. I, personally, don't like string-based queries inside code and I would rather have used Sprocs (Stored Procedures).

SqlQueryBuilder of T

This new class makes things a little simpler if you don't have an ORM.

My approach is to take an object, analyze it, and build a query out of it.

The basic class structure looks like this:

public class SqlQueryBuilder<T> where T : class
{
    private readonly T _item;
 
    public SqlQueryBuilder(T item)
    {
        _item = item;
    }
}

We pass in an object and store it for analysis purposes (primarily for it's contents).

Before we start writing our data modification methods, we need some helper methods.

#region Helper methods
 
protected string GetTableName()
{
    var tableAttr = Attribute.GetCustomAttribute(typeof(T),
        typeof(TableAttribute));
 
    return tableAttr != null
        ? (tableAttr as TableAttribute).Name
        : String.Empty;
}
 
private SqlString GetSqlValue(T item, PropertyInfo propertyInfo)
{
    return new SqlString(propertyInfo.GetValue(item).ToString());
}
 
private string GetKeyFieldName()
{
    var result = GetKeyField();
    return result.Name;
}
 
private string GetKeyFieldValue()
{
    var result = GetKeyField();
    return result.GetValue(_item).ToString();
}
 
private PropertyInfo GetKeyField()
{
    var keyField = _item
        .GetType()
        .GetProperties()
        .FirstOrDefault(e => Attribute.IsDefined(e, typeof(KeyAttribute)));
 
    if (keyField != null)
    {
        return keyField;
    }
 
    throw new Exception("Key on a property could not be found");
}
 
#endregion

I won't go into too much detail, but here's an overview of the methods.

GetTableName() - Extracts the name of the table attached to the class.
GetSqlValue() - Provides us with a SQL version of a property string.
GetKeyFieldName() - Returns the name of the property that has the Key attribute.
GetKeyFieldValue() - Returns the value of the property that has the Key attribute.
GetKeyField() - Finds the property that has a Key Attribute attached to it.

Create (INSERT) process

The insert process uses the Insert/Select method of adding data to a table.

#region Insert
 
public SqlCommand GetInsertCommand()
{
    var table = GetTableName();
    if (String.IsNullOrEmpty(table))
        throw new Exception("No Table attribute was found.");
 
    var query = String.Format("INSERT INTO {0} SELECT {1}",
        table,
        GetInsertFieldList());
 
    return new SqlCommand(query);
}
 
private string GetInsertFieldList()
{
    var sb = new StringBuilder();
    var properties = _item.GetType().GetProperties();
    foreach (var propertyInfo in properties)
    {
        var property = GetSqlValue(_item, propertyInfo);
        sb.Append(GetFormattedInsertField(propertyInfo, property));
    }
    var query = sb.ToString();
    return query.Remove(query.Length - 1);
}
 
private string GetFormattedInsertField(PropertyInfo propertyInfo, SqlString property)
{
    // int
    var result = String.Format("{0} as {1},", property.Value, propertyInfo.Name);
    // string
    if (propertyInfo.PropertyType == typeof(string))
    {
        result = String.Format("'{0}' as {1},", property.Value, propertyInfo.Name);
    }
    // datetime
    else if (propertyInfo.PropertyType == typeof(DateTime))
    {
        result = String.Format("'{0:u}' as {1},", property.Value, propertyInfo.Name);
    }
    return result;
}
 
#endregion

The GetInsertCommand is the main method.

The most noteworthy element of this method is the GetInsertFieldList() method. This method loops through all of the properties and create an INSERT SQL string based on the object property's name and values.

Once it's done, we have a comma attached to the end, which is why we remove it with the Length-1.

If you notice the GetFormattedInsertField(), we account for int, string, and DateTime. If you would like more, you can add more types to the routine. As I mentioned, this was a quick build to showcase how valuable ORMs can be.

Writing this code was painful for me. ;-)

UPDATE-ing Records

Updating records follows a same procedure, but uses different syntax. In this case, we require the Key field to update only one record. Once we have the key field name and value, we write out the SQL for the update statement.

#region Update
 
public SqlCommand GetUpdateCommand()
{
    var table = GetTableName();
    if (String.IsNullOrEmpty(table))
        throw new Exception("No Table attribute was found.");
 
    var query = String.Format("UPDATE {0} SET {1} WHERE {2}={3}",
        table,
        GetUpdateFieldList(),
        GetKeyFieldName(),
        GetKeyFieldValue());
 
    return new SqlCommand(query);
}
 
private string GetUpdateFieldList()
{
    var sb = new StringBuilder();
    var properties = _item.GetType().GetProperties();
    var keyField = GetKeyFieldName();
    foreach (var propertyInfo in properties)
    {
        if (keyField == propertyInfo.Name) continue;
 
        var property = GetSqlValue(_item, propertyInfo);
        sb.Append(GetFormattedUpdateField(propertyInfo, property));
    }
    var query = sb.ToString();
    return query.Remove(query.Length - 1);
}
 
private string GetFormattedUpdateField(PropertyInfo propertyInfo, SqlString property)
{
    // int
    var result = String.Format("{0}={1},", propertyInfo.Name, property.Value);
    // string
    if (propertyInfo.PropertyType == typeof(string))
    {
        result = String.Format("{0}='{1}',", propertyInfo.Name, property.Value);
    }
    // datetime
    else if (propertyInfo.PropertyType == typeof(DateTime))
    {
        result = String.Format("{0}='{1:u}',", propertyInfo.Name, property.Value);
    }
    return result;
}
 
#endregion

Our UPDATE code looks similar, but there are a few differences.

We don't want to update the Key. We just want to update the other fields. As you can see, we grab the key field name before we enter the property loop. Once we are in the loop, if the PropertyInfo.Name is not the same, continue building the Update Field List.

DELETE Statement

Finally, we come to our DELETE statement. After everything we've been through, we now have a way to get a key field.

Our DELETE statement is the simplest method yet.

#region Delete
 
public SqlCommand GetDeleteCommand()
{
    var table = GetTableName();
    if (String.IsNullOrEmpty(table))
        throw new Exception("No Table attribute was found.");
 
    var query = String.Format("DELETE FROM {0} WHERE {1}={2}",
        table,
        GetKeyFieldName(),
        GetKeyFieldValue());
 
    return new SqlCommand(query);
}
 
#endregion

Now that we have all of our commands set up, we can add the commands to our CategoryRepository class.

CategoryRepository.cs

public class CategoryRepository : AdoRepository<Category>
{
    public CategoryRepository(string connectionString)
        : base(connectionString)
    {
    }
 
    public void Add(Category category)
    {
        var builder = new SqlQueryBuilder<Category>(category);
        ExecuteCommand(builder.GetInsertCommand());
    }
 
    public void Update(Category category)
    {
        var builder = new SqlQueryBuilder<Category>(category);
        ExecuteCommand(builder.GetUpdateCommand());
    }
 
    public void Delete(Category category)
    {
        var builder = new SqlQueryBuilder<Category>(category);
        ExecuteCommand(builder.GetDeleteCommand());
    }
 
    #region Get
 
    public IEnumerable<Category> GetAll()
    {
        // DBAs across the country are having strokes 
        //  over this next command!
        using (var command = new SqlCommand("SELECT * FROM Categories"))
        {
            return GetRecords(command);
        }
    }
 
    public Category GetById(string id)
    {
        // PARAMETERIZED QUERIES!
        using (var command = new SqlCommand("SELECT * FROM Categories WHERE CategoryID = @id"))
        {
            command.Parameters.Add(new ObjectParameter("id", id));
            return GetRecord(command);
        }
    }
 
    #endregion
 
    public override Category PopulateRecord(SqlDataReader reader)
    {
        return new Category
        {
            CategoryID = reader.GetInt32(1),
            CategoryName = reader.GetString(2),
            Description = reader.GetString(3)
        };
    }
}

SqlQueryBuilder.cs

public class SqlQueryBuilder<T> where T : class
{
    private readonly T _item;
 
    public SqlQueryBuilder(T item)
    {
        _item = item;
    }
 
    #region Insert
 
    public SqlCommand GetInsertCommand()
    {
        var table = GetTableName();
        if (String.IsNullOrEmpty(table))
            throw new Exception("No Table attribute was found.");
 
        var query = String.Format("INSERT INTO {0} SELECT {1}",
            table,
            GetInsertFieldList());
 
        return new SqlCommand(query);
    }
 
    private string GetInsertFieldList()
    {
        var sb = new StringBuilder();
        var properties = _item.GetType().GetProperties();
        foreach (var propertyInfo in properties)
        {
            var property = GetSqlValue(_item, propertyInfo);
            sb.Append(GetFormattedInsertField(propertyInfo, property));
        }
        var query = sb.ToString();
        return query.Remove(query.Length - 1);
    }
 
    private string GetFormattedInsertField(PropertyInfo propertyInfo, SqlString property)
    {
        // int
        var result = String.Format("{0} as {1},", property.Value, propertyInfo.Name);
        // string
        if (propertyInfo.PropertyType == typeof(string))
        {
            result = String.Format("'{0}' as {1},", property.Value, propertyInfo.Name);
        }
        // datetime
        else if (propertyInfo.PropertyType == typeof(DateTime))
        {
            result = String.Format("'{0:u}' as {1},", property.Value, propertyInfo.Name);
        }
        return result;
    }
 
    #endregion
 
    #region Update
 
    public SqlCommand GetUpdateCommand()
    {
        var table = GetTableName();
        if (String.IsNullOrEmpty(table))
            throw new Exception("No Table attribute was found.");
 
        var query = String.Format("UPDATE {0} SET {1} WHERE {2}={3}",
            table,
            GetUpdateFieldList(),
            GetKeyFieldName(),
            GetKeyFieldValue());
 
        return new SqlCommand(query);
    }
 
    private string GetUpdateFieldList()
    {
        var sb = new StringBuilder();
        var properties = _item.GetType().GetProperties();
        var keyField = GetKeyFieldName();
        foreach (var propertyInfo in properties)
        {
            if (keyField == propertyInfo.Name) continue;
 
            var property = GetSqlValue(_item, propertyInfo);
            sb.Append(GetFormattedUpdateField(propertyInfo, property));
        }
        var query = sb.ToString();
        return query.Remove(query.Length - 1);
    }
 
    private string GetFormattedUpdateField(PropertyInfo propertyInfo, SqlString property)
    {
        // int
        var result = String.Format("{0}={1},", propertyInfo.Name, property.Value);
        // string
        if (propertyInfo.PropertyType == typeof(string))
        {
            result = String.Format("{0}='{1}',", propertyInfo.Name, property.Value);
        }
        // datetime
        else if (propertyInfo.PropertyType == typeof(DateTime))
        {
            result = String.Format("{0}='{1:u}',", propertyInfo.Name, property.Value);
        }
        return result;
    }
 
    #endregion
 
    #region Delete
 
    public SqlCommand GetDeleteCommand()
    {
        var table = GetTableName();
        if (String.IsNullOrEmpty(table))
            throw new Exception("No Table attribute was found.");
 
        var query = String.Format("DELETE FROM {0} WHERE {1}={2}",
            table,
            GetKeyFieldName(),
            GetKeyFieldValue());
 
        return new SqlCommand(query);
    }
 
    #endregion
    
    #region Helper methods
 
    protected string GetTableName()
    {
        var tableAttr = Attribute.GetCustomAttribute(typeof(T),
            typeof(TableAttribute));
 
        return tableAttr != null
            ? (tableAttr as TableAttribute).Name
            : String.Empty;
    }
 
    private SqlString GetSqlValue(T item, PropertyInfo propertyInfo)
    {
        return new SqlString(propertyInfo.GetValue(item).ToString());
    }
 
    private string GetKeyFieldName()
    {
        var result = GetKeyField();
        return result.Name;
    }
 
    private string GetKeyFieldValue()
    {
        var result = GetKeyField();
        return result.GetValue(_item).ToString();
    }
 
    private PropertyInfo GetKeyField()
    {
        var keyField = _item
            .GetType()
            .GetProperties()
            .FirstOrDefault(e => Attribute.IsDefined(e, typeof(KeyAttribute)));
 
        if (keyField != null)
        {
            return keyField;
        }
 
        throw new Exception("Key on a property could not be found");
    }
 
    #endregion
}

A couple of notes:

As mentioned before, while this is hardly a complete implementation, it should give you an idea of all of the things required to make an ORM.
I decided to abstract the SqlQueryBuilder out from everything else because while this does it's job, it doesn't perform it efficiently. When you get a new ORM, you may remove it in the future (which I would recommend) ;-)
We could probably refactor the SqlQueryBuilder into the base AdoRepository class, but I leave that exercise for the reader (or I may update it in a future post).
This code is also susceptible to SQL Injection attacks.

Conclusion

After going through this little exercise, I really appreciate the amount of effort that Entity Framework buffers from me when working with databases.

Building an ORM from scratch is something that I leave to the experts...obviously. ;-)

If you have any questions regarding this approach, by all means, please post a comment below.