SprocIt - Sql Access Utility

03.17.16

I like to use this class when I don't want the overhead of an ORM. I think using raw ADO.net is ugly and requires a lot of saramony to use I wrote this utlilty class to pretty up the code. The class uses method chaining to clean up the setup of ADO.

Use Cases:

using (var sprocIt = new SprocIt().UsingConnection(connectionString))
{
     sprocIt
        .WithSproc("sproc.name")
        .WithParams("@Name", "@Name2")
        .WithValues(value, value2)
        .Execute();
}
using (var sprocIt = new SprocIt().UsingConnection(connectionString))
{
	var reader = sprocIt
		.WithSql("Select * from table where Id = @id")
		.WithParams("@Id")
		.WithValues(1)
		.ExecuteReader();
		
	while(reader.Read())
	{
		....
	}
}

Class

using System;
using System.Data;
using System.Data.SqlClient;

namespace Runtime
{
    /// <summary>
    /// Helper class that wraps SqlCommand uses method chaining to setup an execution.
    /// e.g.
    /// using (var sprocIt = new SprocIt().UsingConnection(connectionString))
    /// {
    ///    sprocIt
    ///        .WithSproc("sproc.name")
    ///        .WithParams("@Name")
    ///        .WithValues(value)
    ///        .Execute();
    /// }
    /// </summary>
    public class SprocIt : IDisposable
    {
        private const int Timeout = 1200;
        private SqlCommand _command = new SqlCommand();
        private SqlConnection _connection = new SqlConnection();
        private string _connectionString = string.Empty;

        public virtual void Dispose()
        {
            CloseConnection();
            _connection.Dispose();
            _command.Dispose();
        }

        public virtual int Execute()
        {
            OpenConnection();
            try
            {
                return _command.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual SqlDataReader ExecuteReader()
        {
            OpenConnection();
            return _command.ExecuteReader();
        }

        public virtual object ExecuteResult()
        {
            OpenConnection();
            try
            {
                return _command.ExecuteScalar();
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual T ExecuteResult<T>()
        {
            OpenConnection();
            object result = null;
            try
            {
                result = _command.ExecuteScalar();
                return (T)result;
            }
            catch (InvalidCastException)
            {
                if (typeof(T) == typeof(int) && result != null) //fix a string value that is an int 
                {
                    int value;
                    if (int.TryParse(result.ToString(), out value))
                        return (T)((object)value);
                }
            }
            finally
            {
                CloseConnection();
            }
            return default(T);
        }

        public virtual SprocIt UsingConnection(string connection)
        {
            _connectionString = connection;
            _connection = new SqlConnection(_connectionString);
            return this;
        }

        public virtual SprocIt WithName(string sprocName)
        {
            if (string.IsNullOrWhiteSpace(sprocName)) throw new ArgumentNullException("sprocName");
            _command = new SqlCommand(sprocName, _connection)
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithParams(params string[] parameters)
        {
            for (int i = 0; i < parameters.Length; i++)
            {
                if (_command.Parameters.Count <= i)
                    _command.Parameters.Add(new SqlParameter());
                _command.Parameters[i].ParameterName = parameters[i];
            }
            return this;
        }

        public virtual SprocIt WithSproc(string sprocName)
        {
            if (string.IsNullOrWhiteSpace(sprocName)) throw new ArgumentNullException("sprocName");
            _command = new SqlCommand(sprocName, _connection)
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithSql(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentNullException("sql");
            _command = new SqlCommand(sql, _connection)
            {
                CommandType = CommandType.Text,
                CommandTimeout = Timeout
            };
            return this;
        }

        public virtual SprocIt WithValues(params object[] values)
        {
            for (int i = 0; i < values.Length; i++)
            {
                if (_command.Parameters.Count <= i)
                    _command.Parameters.Add(new SqlParameter());
                _command.Parameters[i].Value = values[i];
            }
            return this;
        }

        private void CloseConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _command.Parameters.Clear();
        }

        private void OpenConnection()
        {
            _connection.Open();
        }
    }
}