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).