Database.cs

Database.cs

public class Database : IDisposable, IDatabase
{
	private string _connectionString;
	private string _connectionStringProviderName;
	private int _timeout = 30;
	private IDbConnection _connection;
	private bool _dependencyStarted = false;

	public static string GetConnectionString(string connectionStringName)
	{
		return ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
	}

	public Database(string connectionStringName) : this(connectionStringName, 30) { }

	public Database(string connectionStringName, int timeout)
	{
		if (connectionStringName == null)
			throw new ArgumentNullException("connectionStringName");

		if (timeout <= 0)
			throw new ArgumentException(string.Format("The timeout value cannot be zero or negative value"), "timeout");

		var connectionString = ConfigurationManager.ConnectionStrings[connectionStringName];
		if (connectionString == null)
			throw new NullReferenceException(string.Format("Connection string {0} not found in the configuration", connectionStringName));

		_connectionString = connectionString.ConnectionString;
		_connectionStringProviderName = connectionString.ProviderName;
		_timeout = timeout;
	}

	public IEnumerable<T> Query<T>(string sql, Func<IDataReader, T> factory, params object[] parameters)
	{
		OpenConnection();
		using (var cmd = CreateCommand(sql, parameters))
		{
			using (var reader = cmd.ExecuteReader())
			{
				while (reader.Read())
				{
					var o = factory(reader);
					yield return o;
				}
			}
		}
	}

	public T Scalar<T>(string sql, params object[] parameters)
	{
		T result;
		OpenConnection();
		using (var cmd = CreateCommand(sql, parameters))
		{
			result = (T)cmd.ExecuteScalar();
		}
		return result;
	}

	public T Record<T>(string sql, Func<IDataReader, T> factory, params object[] parameters)
	{
		T result = default(T);
		OpenConnection();
		using (var cmd = CreateCommand(sql, parameters))
		{
			using (var reader = cmd.ExecuteReader())
			{
				if (reader.Read())
				{
					result =  factory(reader);
				}
			}
		}
		return result;
	}

	public void StartDependency(string sql, OnChangeEventHandler changeHandler)
	{
		if (!_dependencyStarted)
		{
			var perm = new SqlClientPermission(PermissionState.Unrestricted);
			perm.Demand();

			_dependencyStarted = true;
			SqlDependency.Start(_connectionString);
		}

		OpenConnection();

		using (var cmd = CreateCommand(sql))
		{
			cmd.CommandType = CommandType.Text;
			((SqlCommand)cmd).Notification = null;

			var dependency = new SqlDependency((SqlCommand)cmd);
			dependency.OnChange += changeHandler;
			cmd.ExecuteNonQuery();
		}
		
	}

	public void StopDependency()
	{
		if (_dependencyStarted) SqlDependency.Stop(_connectionString);
	}

	public void Dispose()
	{
		if (_connection != null && _connection.State == ConnectionState.Open)
		{
			_connection.Close();
			_connection.Dispose();
			_connection = null;
		}
	}

	private void OpenConnection()
	{
		if (_connection == null)
		{
			if (string.IsNullOrEmpty(_connectionStringProviderName))
				throw new NullReferenceException(string.Format("Open connection failed. Connection string provider name {0} not found in the configuration", _connectionStringProviderName));

			var factory = DbProviderFactories.GetFactory(_connectionStringProviderName);
			if (factory == null)
				throw new NullReferenceException(string.Format("Open connection failed. Cannot create instance of the connection factory. Provider name {0} in the connectin string could be incorrect", _connectionStringProviderName));

			_connection = factory.CreateConnection();
			if (_connection == null)
				throw new NullReferenceException(string.Format("Open connection failed. Cannot create instance of the connection using the factory. Provider name {0} in the connectin string could be incorrect", _connectionStringProviderName));

			_connection.ConnectionString = _connectionString;
			_connection.Open();
		}
	}

	private IDbCommand CreateCommand(string sql, params object[] parameters)
	{
		if (string.IsNullOrEmpty(sql))
			throw new ArgumentNullException(sql);

		var cmd = _connection.CreateCommand();
		cmd.CommandText = sql;
		cmd.CommandType = CommandType.Text;
		cmd.CommandTimeout = _timeout;
		if (parameters != null && parameters.Length > 0)
		{
			foreach (var parameter in parameters)
			{
				cmd.Parameters.Add(parameter);
			}
		}

		return cmd;
	}
}