public static class DatabaseExtensions
    {
        public static void WithConnection(this MSDB.Database thisObj, Action action)
        {
            using (var connection = thisObj.CreateConnection())
            {
                connection.Open();
                action(connection);
            }
        }
        public static T WithConnection(this MSDB.Database db, Func action)
        {
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                return action(conn);
            }
        }

        public static void WithinTransaction(this MSDB.Database thisObj, Action action)
        {
            thisObj.WithConnection(conn => conn.WithinTransaction(action));
        }
        public static T WithinTransaction(this MSDB.Database db, Func action)
        {
            return db.WithConnection(
                conn =>
                {
                    using (var transaction = conn.BeginTransaction())
                    {
                        var result = action(transaction);
                        transaction.Commit();
                        return result;
                    }
                });
        }

        public static void WithTextCommand(this MSDB.Database db, string sql, Action action)
        {
            db.WithConnection(conn =>
            {
                using (var cmd = conn.CreateTextCommand(sql))
                {
                    action(cmd);
                }
            });
        }
        public static T WithTextCommand(this MSDB.Database db, string sql, Func action)
        {
            return db.WithConnection(conn =>
            {
                using (var cmd = conn.CreateTextCommand(sql))
                {
                    return action(cmd);
                }
            });
        }

        public static T ExecuteScalar(this MSDB.Database db, string sql, Action action)
        {
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                return conn.ExecuteScalar(sql, action); 
            }
        }
        public static T ExecuteScalar(this MSDB.Database db, string sql)
        {
            return db.ExecuteScalar(sql, cmd => { });
        }
        public static T ExecuteScalar(this MSDB.Database db, Action action)
        {
            return db.ExecuteScalar(string.Empty, action);
        }

        public static int ExecuteNonQuery(this MSDB.Database db, string sql, Action action)
        {
            using (var cmd = db.GetSqlStringCommand(sql))
            {
                action(cmd);
                return db.ExecuteNonQuery(cmd);
            }
        }
        public static int ExecuteNonQuery(this MSDB.Database db, Action action)
        {
            return db.ExecuteNonQuery(string.Empty, action);
        }
    }