SqlServicesRespository

SqlServicesServiceRepository.cs

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



namespace MichaelKappel.Net.Repositories.Interfaces
{
    public interface IRepositoryCrud<T, TKey>
    {
        T Create(T item);
        T Get(TKey internalUrl);
        System.Collections.Generic.List<T> GetItems();
        T Remove(T item);
        T Set(T item);
    }
}


namespace MichaelKappel.Net.Repositories.SqlServicesRepository
{
    public abstract class SqlServicesRepository<T, TKey>
    {
        internal String defaultConnectionName = "ApplicationServices";
        public SqlServicesRepository()
        {

        }
        protected SqlConnection GetSqlConnection()
        {
            return new SqlConnection(getConnectionString());
        }
        protected SqlConnection GetSqlConnection(String name)
        {
            return new SqlConnection(getConnectionString(name));
        }
        private String getConnectionString()
        {
            return getConnectionString(defaultConnectionName);
        }
        private String getConnectionString(String name)
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }
        protected T GetInfo(String storedProcedure, params SqlParameter[] parameters)
        {
            T result = default(T);
            var results = GetInfos(storedProcedure, parameters);
            if (results != default(List<T>))
            {
                if (results.Count == 1)
                    result = results[0];
                else if (results.Count > 0)
                    throw new Exception(storedProcedure + " result count greater than 0");
            }
            return result;
        }
        protected List<T> GetInfos(String storedProcedure, params SqlParameter[] parameters)
        {
            var results = default(List<T>);
            using (SqlConnection connection = GetSqlConnection())
            {
                using (SqlCommand command = new SqlCommand(storedProcedure, connection))
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    if (parameters != null && parameters.Count() > 0)
                        foreach (var parameter in parameters)
                            command.Parameters.Add(parameter);

                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        try
                        {
                            if (reader.HasRows)
                            {
                                results = new List<T>();
                                while (reader.Read())
                                    results.Add(CreateInfoFromReader(reader));
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
            }
            return results;
        }
        protected DT ReadAs<DT>(IDataReader reader, string feildName)
        {
            try
            {
                var rawFieldValue = reader[feildName];
                if (rawFieldValue == DBNull.Value)
                    return default(DT);

                return (DT)rawFieldValue;
            }
            catch (Exception e)
            {
                throw;
            }
        }
        protected abstract T CreateInfoFromReader(SqlDataReader reader);
    }

}