Extension methods to simplify database handling.

Two extension methods used to simplify database handling.

[sourcecode language=”csharp”]
public static class DbExtensionMethods
{
/// <summary>
/// Create a new database command.
/// </summary>
/// <param name="connection">Connection used as factory.</param>
/// <param name="commandText">SQL query.</param>
/// <returns>Created command</returns>
public static IDbCommand CreateCommand(this IDbConnection connection, string commandText)
{
var cmd = connection.CreateCommand();
cmd.CommandText = commandText;
return cmd;
}

/// <summary>
/// Add a new parameter to the command
/// </summary>
/// <param name="cmd">Command</param>
/// <param name="name">Parameter name, without prefix such as ‘@’.</param>
/// <param name="value">Parameter value, datatype should be the same as in the database column.</param>
public static void AddParameter(this IDbCommand cmd, string name, object value)
{
var parameter = cmd.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
cmd.Parameters.Add(parameter);
}
}
[/sourcecode]

Usage

The following code:
[sourcecode language=”csharp”]
var cmd =
new SQLiteCommand("UPDATE todo_items set title=@name, description=@description WHERE id = @id", _db);
cmd.Parameters.Add(new SQLiteParameter("name", item.Title));
cmd.Parameters.Add(new SQLiteParameter("description", item.Description));
cmd.Parameters.Add(new SQLiteParameter("id", item.Id));
[/sourcecode]

becomes
[sourcecode language=”csharp”]
var cmd = _db.CreateCommand("UPDATE todo_items set title=@name, description=@description WHERE id = @id");
cmd.AddParameter("name", item.Title);
cmd.AddParameter("description", item.Description);
cmd.AddParameter("id", item.Id);
[/sourcecode]

Best of all, the code becomes driver independent. (You can switch between SQLiteConnection, SqlClientConnection or whatever without any code modifications).

Everyone know what it is, but no one have used it?

I must confess that I’ve never used the northwind database, despite that I’ve been developing using Visual Studio for more than 10 years.

I’m going to try Entity Framework 4 due to a request from a colleague of mine. The tutorial that I found is using Northwind database. I though that the DB was included in vstudio, but I didn’t find it anywhere. Therefore I downloaded it from Microsoft.

I really hope that the POCO implementation is well done. I’ve never used EF before since it couldn’t keep its hands of my POCOs.

My name is More, Docu More!

Are you one of those people that thinks that code is self explainable? Still thinking that when you go back and try to fix code that you did a year ago? Yes? Good for you.

Documentation is a tool that is quite underrated. If used in the right way, it can improve your coding skills. Seriously!

I got a few guide lines that I try to follow when coding. (Sure, sometimes I don’t give a sh!t. We are only humans, right?)

Rule 1: There are no rules

If you do not intend to follow your rules, don’t make them. It’s quite easy to make a lot of rules that will, if you use them, improve your code quite a lot. But you need to use them, right?

If you feel that you have too many rules, simply remove those that you break most often. When you have started to follow all your rules, add another one (if necessary).

Rule 2: Describe the purpose

When documenting projects and classes, ALWAYS motivate why you created them. If you can’t write a clear and concise motivation to why a class was created, you have probably done something wrong. Refactor.

Rule 3: AND is evil

Do not write a lot of AND in your code documentation. If you do, read about single responsibility principle in a previous post.

Example:

This method will save the user, email the admin and save a blog entry into the database.

Rule 4: Explain what parameters do, not what they are

I quite often see documentation which just describes what parameters are (their type), but not what they are used for or what business rules they are validated against.

Coding contracts will be a big help here, since they are also imported into the documentation.

Rule 5: Return values are not obvious

Return values are not obvious. Can null be returned? What is no users are found, is null or an empty list returned? Always describe the return values.

If you find yourself describing more than two different type of return values, check if you should refactor the method.

Example of a good description (for the method GetUser): The requested user if found; otherwise null.

Rule 6: Exceptions

Are you always throwing/catching Exception instead of more specific exceptions? Stop. Read here and here

Ohh. We are talking about DOCUMENTING exceptions, not about how to use them. Well. Just document them….

Final words

Have you ever started to work out? You create a wonderful exercise program and decide to go the gym four times a week. It looks really good on paper, but how long do you keep it up? It’s the same with documentation. Start small. Download GhostDoc (see below) and start describing the purpose of each project/class/method. Do that for a week or two. Feels good? Do more.

Tools

GhostDoc helps you document your stuff in Visual Studio. When done, use Sandcastle to generate the docs. You might want to wrap sandcastle with a more user-friendly GUI.