BasicDataAccessGateway.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DataAccess.Helpers
{
    public class BasicDataAccessGateway : IBasicDataAccessGateway, IOverrideableBasicGateway
    {
        private string connectionString;

        public BasicDataAccessGateway()
        {
            this.connectionString = ConfigurationManager.ConnectionStrings["Sql Connection String"].ToString();
        }

        private IEnumerable<DataTable> BoilerQuery(string name, Func<SqlDataAdapter, SqlCommand, IEnumerable<DataTable>> action, int timeOut = 45)
        {
            using (var sqlConnection = new SqlConnection(this.connectionString))
            {
                sqlConnection.Open();

                using (var command = new SqlCommand(name, sqlConnection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.CommandTimeout = timeOut;

                    using (var sqlAdapter = new SqlDataAdapter(command))
                    {
                        return action(sqlAdapter, command);
                    }
                }
            }
        }

        public DataTable Query(string name, IDictionary<string, Tuple<object, SqlDbType>> parameters = null, int timeOut = 45)
        {
            return BoilerQuery(name, (sqlAdapter, cmd) =>
            {

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        var param = new SqlParameter(parameter.Key, parameter.Value.Item2)
                        {
                            Value = parameter.Value.Item1
                        };

                        cmd.Parameters.Add(param);
                    }
                }

                using (var dataSet = new DataSet())
                {
                    sqlAdapter.Fill(dataSet);

                    return dataSet.Tables.Cast<DataTable>().ToList();
                }

            }, timeOut).FirstOrDefault();
        }

        public IEnumerable<DataTable> MultiQuery(string name, IDictionary<string, Tuple<object, DbType>> parameters, int timeOut = 45)
        {
            return BoilerQuery(name, (sqlAdapter, cmd) =>
                {

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value.Item1);
                        }
                    }

                    using (var dataSet = new DataSet())
                    {
                        sqlAdapter.Fill(dataSet);

                        return dataSet.Tables.Cast<DataTable>().ToList();
                    }
                }, timeOut);
        }

        public DataTable Query(string name, IDictionary<string, Tuple<object, DbType>> parameters = null, int timeOut = 45)
        {
            return MultiQuery(name, parameters, timeOut).FirstOrDefault();
        }

        public int NonQuery(string name, IDictionary<string, Tuple<object, DbType>> parameters)
        {
            var numberOfRowsAffected = 0;

            BoilerQuery(name, (sqlAdapter, cmd) =>
            {
                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.AddWithValue(parameter.Key, parameter.Value.Item1);
                    }
                }

                numberOfRowsAffected = cmd.ExecuteNonQuery();

                return null;
            });

            return numberOfRowsAffected;
        }

        public T QueryScalar<T>(string name, IDictionary<string, Tuple<object, DbType>> parameters = null, int timeOut = 45)
        {
            var dataTable = this.Query(name, parameters);

            if (dataTable.Rows.Count > 1)
            {
                throw new BasicDataAccessGatewayException("The query returned more than one row");
            }

            if (dataTable.Columns.Count > 1)
            {
                throw new BasicDataAccessGatewayException("The query returned more than one column");
            }

            var row = dataTable.Rows[0];

            return (T)row[0];
        }

        void IOverrideableBasicGateway.OverrideConnectionString(string connectionString)
        {
            this.connectionString = connectionString;
        }
    }
}