Posts tagged with: database

Extension methods to simplify database handling.

Two extension methods used to simplify database handling.

    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);
        }
    }

Usage

The following code:

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

becomes

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

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