int MinCost = 9;
int MaxCost = 10;
List<SpParameter> parameters = new List<SpParameter>();
parameters.Add(new SpParameter("MinCost", System.Data.ParameterDirection.Input, MinCost));
parameters.Add(new SpParameter("MaxCost", System.Data.ParameterDirection.Input, MaxCost));
Mapper call = new Mapper();
List<Product> products = call.ExecuteList<Product>("GetProducts", parameters);
PRODUCT.CS
namespace DTO.Entity
{
public partial class Product
{
[Mapping(ColumnName = "InventoryId")]
public int InventoryId { get; private set; }
[Mapping(ColumnName = "ProductId")]
public int ProductId { get; private set; }
[Mapping(ColumnName = "Stock")]
public decimal Stock { get; private set; }
[Mapping(ColumnName = "MinimumStock")]
public decimal MinimumStock { get; private set; }
[Mapping(ColumnName = "ExpiryDate")]
public DateTime ExpiryDate { get; private set; }
[Mapping(ColumnName = "Barcode")]
public decimal Barcode { get; private set; }
[Mapping(ColumnName = "Description")]
public string Description { get; private set; }
[Mapping(ColumnName = "UM")]
public string UM { get; private set; }
[Mapping(ColumnName = "CostPrice")]
public decimal CostPrice { get; private set; }
[Mapping(ColumnName = "SalePrice")]
public decimal SalePrice { get; private set; }
[Mapping(ColumnName = "WholePrice")]
public Nullable<decimal> WholePrice { get; private set; }
[Mapping(ColumnName = "AmountWholePrice")]
public Nullable<short> AmountWholePrice { get; private set; }
[Mapping(ColumnName = "Deleted")]
public Nullable<bool> Deleted { get; private set; }
}
}
MAPPER.CS
namespace DTO.Entity
{
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public class Mapper
{
const int START_POINT = 0;
const int SP_PARAM_INDICATOR = 64;
private static string ConnectionString
{
get
{
string connString = ConfigurationManager.ConnectionStrings["POSEntities"].ToString();
connString = connString.Substring(connString.IndexOf("data source"), connString.Length - connString.IndexOf("data source"));
connString = connString.Substring(0, connString.IndexOf("MultipleActiveResultSets"));
return connString;
}
}
private SqlConnection Connection { get; set; }
private SqlCommand Command { get; set; }
public List<SpParameter> OutParameters { get; private set; }
private void Open()
{
try
{
Connection = new SqlConnection(ConnectionString);
Connection.Open();
}
catch (Exception ex)
{
//email notification
}
}
private void Close()
{
if (Connection.State == System.Data.ConnectionState.Open)
{
Connection.Close();
}
}
/// <summary>
/// executes stored procedure with DB parameteres if they are passed
/// </summary>
/// <param name="procedureName"></param>
/// <param name="executeType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private object ExecuteProcedure(string procedureName, ExecuteType executeType, List<SpParameter> parameters)
{
object returnObject = null;
if (!Connection.Equals(null))
{
if (Connection.State.Equals(ConnectionState.Open))
{
Command = new SqlCommand(procedureName, Connection);
Command.CommandType = CommandType.StoredProcedure;
if (!parameters.Equals(null))
{
Command.Parameters.Clear();
foreach (SpParameter dbParameter in parameters)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = ((char)SP_PARAM_INDICATOR).ToString() + dbParameter.Name;
parameter.Direction = dbParameter.Direction;
parameter.Value = dbParameter.Value;
Command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case ExecuteType.ExecuteReader:
returnObject = Command.ExecuteReader();
break;
case ExecuteType.ExecuteNonQuery:
returnObject = Command.ExecuteNonQuery();
break;
case ExecuteType.ExecuteScalar:
returnObject = Command.ExecuteScalar();
break;
default:
break;
}
}
}
return returnObject;
}
/// <summary>
/// updates output parameters from stored procedure
/// </summary>
private void UpdateOutParameters()
{
if (Command.Parameters.Count > 0)
{
OutParameters = new List<SpParameter>();
OutParameters.Clear();
for (int i = START_POINT; i < Command.Parameters.Count; i++)
{
if (Command.Parameters[i].Direction.Equals(ParameterDirection.Output))
{
OutParameters.Add(new SpParameter(Command.Parameters[i].ParameterName,
ParameterDirection.Output,
Command.Parameters[i].Value));
}
}
}
}
/// <summary>
/// executes scalar query stored procedure without parameters
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public T ExecuteSingle<T>(string procedureName) where T : new()
{
return ExecuteSingle<T>(procedureName, null);
}
/// <summary>
/// executes scalar query stored procedure and maps result to single object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteSingle<T>(string procedureName, List<SpParameter> parameters) where T : new()
{
Open();
IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
T tempObject = new T();
if (reader.Read())
{
for (int i = START_POINT; i < reader.FieldCount; i++)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
reader.Close();
UpdateOutParameters();
Close();
return tempObject;
}
/// <summary>
/// executes list query stored procedure without parameters
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public List<T> ExecuteList<T>(string procedureName) where T : new()
{
return ExecuteList<T>(procedureName, null);
}
/// <summary>
/// executes list query stored procedure and maps result generic list of objects
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public List<T> ExecuteList<T>(string procedureName, List<SpParameter> parameters) where T : new()
{
List<T> objects = new List<T>();
Open();
IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
while (reader.Read())
{
T tempObject = new T();
for (int i = START_POINT; i < reader.FieldCount; i++)
{
if (!reader.GetValue(i).Equals(DBNull.Value))
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
objects.Add(tempObject);
}
reader.Close();
UpdateOutParameters();
Close();
return objects;
}
/// <summary>
/// executes non query stored procedure with parameters
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string procedureName, List<SpParameter> parameters)
{
int returnValue;
Open();
returnValue = (int)ExecuteProcedure(procedureName, ExecuteType.ExecuteNonQuery, parameters);
UpdateOutParameters();
Close();
return returnValue;
}
}
public enum ExecuteType
{
ExecuteReader,
ExecuteNonQuery,
ExecuteScalar
};
public class SpParameter
{
public string Name { get; set; }
public ParameterDirection Direction { get; set; }
public object Value { get; set; }
public SpParameter(string paramName, ParameterDirection paramDirection, object paramValue)
{
Name = paramName;
Direction = paramDirection;
Value = paramValue;
}
}
}
