Last active
August 29, 2015 14:15
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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