Skip to content

Instantly share code, notes, and snippets.

@dejanstojanovic
Last active August 29, 2015 14:15
Show Gist options
  • Save dejanstojanovic/bfbb75815532c450fcdc to your computer and use it in GitHub Desktop.
Save dejanstojanovic/bfbb75815532c450fcdc to your computer and use it in GitHub Desktop.
Simple DataAccess layer base class which maps value from database to generic type POCO class instance
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using log4net;
namespace SimpleData.DataAccessLayer
{
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class DataColumn : Attribute
{
#region Fields
private string columnName;
#endregion
#region Properties
public string ColumnName
{
get
{
return this.columnName;
}
set
{
this.columnName = value;
}
}
#endregion
#region Constructors
public DataColumn(string columnName)
{
this.columnName = columnName;
}
#endregion
}
public abstract class Base : IDisposable
{
private static ILog Log = LogManager.GetLogger(typeof(Base));
#region Fields
private SqlConnection connection = null;
#endregion
#region Properties
public SqlConnection Connection
{
get
{
return this.connection;
}
set
{
this.connection = value;
}
}
#endregion
#region Constructors
public Base()
{
}
public Base(string connectionString)
{
this.connection = new SqlConnection(connectionString);
}
public Base(SqlConnection connection)
{
this.connection = connection;
}
#endregion
#region Data operations
/// <summary>
/// Returns SqlDataReader for stored procedure with optional paramets list
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string procedureName, Dictionary<string, IConvertible> parameters = null)
{
return this.GetCommand(procedureName, parameters).ExecuteReader();
}
/// <summary>
/// Returns DataTable for stored procedure with optional paramets list
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string procedureName, Dictionary<string, IConvertible> parameters = null)
{
DataTable dataTable = new DataTable();
this.GetAdapter(procedureName, parameters).Fill(dataTable);
return dataTable;
}
/// <summary>
/// Returns DataSet for stored procedure with optional paramets list
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string procedureName, Dictionary<string, IConvertible> parameters = null)
{
DataSet dataSet = new DataSet();
this.GetAdapter(procedureName, parameters).Fill(dataSet);
return dataSet;
}
/// <summary>
/// Creates single instance of POCO class of type T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteProcedureSingleModel<T>(string procedureName, Dictionary<string, IConvertible> parameters = null) where T : class, new()
{
T item = null;
IDataRecord record = this.GetCommand(procedureName, parameters).ExecuteScalar() as IDataRecord;
if (record != null)
{
var objectType = typeof(T);
item = new T();
for (int columnIndex = 0; columnIndex < record.FieldCount; columnIndex++)
{
var objectProperty = objectType.GetProperties()
.Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
.Where(a => ((DataColumn)a).ColumnName == record.GetName(columnIndex))
.Any()
).FirstOrDefault();
if (objectProperty != null)
{
var dataValue = record.GetValue(columnIndex);
objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
}
}
}
return item;
}
/// <summary>
/// Return IEnumerable of POCO class instances of type T for the mutiple results from database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IEnumerable<T> ExecuteProcedureMultipleModel<T>(string procedureName, Dictionary<string, IConvertible> parameters = null) where T : class, new()
{
var reader = this.ExecuteReader(procedureName, parameters);
if (reader.HasRows)
{
var objectType = typeof(T);
while (reader.Read())
{
T item = new T();
for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
{
var objectProperty = objectType.GetProperties()
.Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
.Where(a => ((DataColumn)a).ColumnName == reader.GetName(columnIndex))
.Any()
).FirstOrDefault();
if (objectProperty != null)
{
var dataValue = reader.GetValue(columnIndex);
objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
}
}
yield return item;
}
}
reader.Close();
}
#endregion
#region Command preparing methods
/// <summary>
/// Creates SqlAdapter instance for the stored procedure with optional paramets
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private SqlDataAdapter GetAdapter(string procedureName, Dictionary<string, IConvertible> parameters = null)
{
return new SqlDataAdapter(this.GetCommand(procedureName, parameters));
}
/// <summary>
/// Creates SqlAdapter instance for the command
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private SqlDataAdapter GetAdapter(SqlCommand command)
{
return new SqlDataAdapter(command);
}
/// <summary>
/// Prepares SqlCommand for stored procedure with oprional parameters list
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private SqlCommand GetCommand(string procedureName, Dictionary<string, IConvertible> parameters = null)
{
SqlCommand command = new SqlCommand(procedureName);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = this.connection;
if (parameters != null && parameters.Any())
{
foreach (var param in parameters)
{
command.Parameters.Add(new SqlParameter(param.Key, param.Value));
}
}
return command;
}
#endregion
public void Dispose()
{
if (this.connection != null)
{
this.connection.Close();
this.connection.Dispose();
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment