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

Safer Deletes and Updates on Live DataBase

05.21.09

Problem: You need to change production data from a database.

Solution: Use Transations.

Its always scary working with live data I found that I feel more confident when I take the following measures.

Do a Select first to check the where clause:

SELECT * FROM Foo WHERE FooID = 1000

Wrap your changes in a Transaction and roll it back if you did not get the expected result.

Begin Transaction
  DELETE FROM Foo WHERE FooID = 1000
IF @@RowCount <> 1 BEGIN
  Rollback Transaction
END
ELSE
  Commit Transaction

Visual Studio Throws an Error When Trying to Connect/Open an .mdf File

04.11.09

Problem: You try to open a .mdf file in visual studio and you get one of the following errors.

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly.  Please verify the installation of the component or download from the URL:  http://go.microsoft.com/fwlink/?LinkId=49251

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

Solution: Open Server Explorer add Data Connection. Make sure the Data Source = Microsoft SQL Server Database File (SqlClient) and browse to the .mdf file located in your source directory(There maybe on located in the bin directory but this will get copied over by what is in src on a build). Click the Advance button. In order to work I needed to switch the Data Source to .\SQLEXPRESS2005 (This will be different for each computer).

Final connection string should look something like this:

Data Source=.\SQLEXPRESS2005;AttachDbFilename=|DataDirectory|\NerdDinner.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True