files

Code Snippets

Title: IDbCommand extensions
Description: Extension methods for IDbCommand for a more fluent interface to executing sql commands
Category: IDbCommand Extensions
Visibility:  Public
Added by: Patrick Goldbach on 5/6/2012

 Currently rated 4.67 by 3 user(s)

Get link for this code snippet
Collapse code snippet
Expand code snippet
Tweet this snippet
Tweet about this snippet
Delete code snippet
Edit code snippet
public static class DbCommandExtensions
    {
        public static void AddInParam(this IDbCommand thisObj, string name, DbType type)
        {
            thisObj.AddInParam(name, type, null);
        }

        public static void AddInParam(this IDbCommand thisObj, string name, DbType type, object value)
        {
            IDataParameter param = thisObj.CreateDbParam(name, type, value);
            thisObj.Parameters.Add(param); //Do not add until after all values have been set, in case of exception
        }

        public static void AddInParam(this IDbCommand thisObj, string name, DbType type, object value, int size)
        {
            IDbDataParameter param = thisObj.CreateDbParam(name, type, ParameterDirection.Input, value);
            param.Size = size;
            thisObj.Parameters.Add(param); //Do not add until after all values have been set, in case of exception
        }

        public static void AddOutParam(this IDbCommand thisObj, string name, DbType type)
        {
            thisObj.Parameters.Add(CreateDbParam(thisObj, name, type, ParameterDirection.Output, DBNull.Value));
        }

        public static void AddOutParam(this IDbCommand thisObj, String name, DbType type, int size)
        {
            IDbDataParameter param = CreateDbParam(thisObj, name, type, ParameterDirection.Output, DBNull.Value);
            param.Size = size;
            thisObj.Parameters.Add(param);
        }

        public static IDbDataParameter CreateDbParam(this IDbCommand thisObj, string name, DbType type)
        {
            return CreateDbParam(thisObj, name, type, ParameterDirection.Input, null);
        }

        public static IDbDataParameter CreateDbParam(this IDbCommand thisObj, string name, DbType type, Object value)
        {
            return CreateDbParam(thisObj, name, type, ParameterDirection.Input, value);
        }

        public static IDbDataParameter CreateDbParam(this IDbCommand thisObj, string name, DbType type, ParameterDirection direction, Object value)
        {
            IDbDataParameter param = thisObj.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            param.Value = value ?? DBNull.Value;
            param.Direction = direction;
            return param;
        }

        public static T ExecuteScalar<T>(this IDbCommand thisObj)
        {
            var result = thisObj.ExecuteScalar();
            return (T)Convert.ChangeType(result, typeof(T));
        }

        public static T ExecuteScalarOrDefault<T>(this IDbCommand thisObj, T defaultValue)
        {
            var result = thisObj.ExecuteScalar();
            return (Convert.IsDBNull(result) || result == null) ? defaultValue : (T)Convert.ChangeType(result, typeof(T));
        }

        public static int ExecuteNonQuery(this IDbCommand thisObj, int retryCount)
        {
            if (thisObj.Transaction != null)
                return ExecuteNonQuery(thisObj, thisObj.Transaction.IsolationLevel, retryCount);

            return ExecuteNonQuery(thisObj, IsolationLevel.Unspecified, retryCount);
        }

        public static int ExecuteNonQuery(this IDbCommand thisObj, IsolationLevel isolationLevel, int retryCount)
        {
            return ExecuteNonQuery(thisObj, isolationLevel, retryCount, 3000);
        }

        public static int ExecuteNonQuery(this IDbCommand thisObj, int retryCount, int delayInMilliseconds)
        {
            return ExecuteNonQuery(thisObj, thisObj.Transaction != null ? thisObj.Transaction.IsolationLevel : IsolationLevel.Unspecified, retryCount, delayInMilliseconds);
        }

        public static int ExecuteNonQuery(this IDbCommand thisObj, IsolationLevel isolationLevel, int retryCount, int delayInMilliseconds)
        {
            if (thisObj.Transaction != null)
                throw new InvalidOperationException("Command should not be associated with an existing transaction");

            const int DEADLOCK_ERROR_NUMBER = 1205;
            bool wasSuccessful = false;
            int retryCounter = 0, result = 0;
            while (!wasSuccessful && retryCounter < retryCount)
            {
                thisObj.Transaction = GetTransaction(thisObj, isolationLevel);

                try
                {
                    result = thisObj.ExecuteNonQuery();

                    if (thisObj.Transaction != null)
                        thisObj.Transaction.Commit();

                    wasSuccessful = true;
                }
                catch (SqlException ex)
                {
                    if (ex.Number != DEADLOCK_ERROR_NUMBER || retryCounter >= retryCount)
                    {
                        if (thisObj.Transaction != null)
                            thisObj.Transaction.Rollback();
                        throw;

                    }
                    thisObj.Cancel();

                    Thread.Sleep(delayInMilliseconds); ;
                    wasSuccessful = false;
                    retryCounter += 1;
                }
            }
            return result;
        }

        private static IDbTransaction GetTransaction(IDbCommand command, IsolationLevel isolationLevel)
        {
            return isolationLevel != IsolationLevel.Unspecified
                       ? command.Connection.BeginTransaction(isolationLevel)
                       : null;
        }
    }