/// <summary>
/// Executes the storedProcedure against the connString and returns a IEnumerable<TEntity>
/// It will also load the partialParam with the current row's non-TEntity values.
///
/// If you need the return value, access it dynamically from partialParam
/// Note: @RETURN_VALUE is returned as ReturnValue
///
/// NOTE: Shouldn't forget to initialize the partialParam as an ExpandoObject
/// </summary>
/// <typeparam name="TEntity">The [TableName]VD associated with the table entity coming from the stored procedure.</typeparam>
/// <param name="connString">The connection string that identifies the database server</param>
/// <param name="storedProcedure">The stored procedure to execute</param>
/// <param name="partialParam">Populated with row/record values that ExecuteReader can't find a corresponding property in your TEntity</param>
/// <param name="entity">An entity that contain values which should be used as parameters for storedProcedure</param>
/// <param name="includeReturnValue">Specify if the Return Value from the storedProcedure should be included</param>
/// <param name="implicitCast">Support of implicit or explict cast</param>
/// <returns></returns>
public static TEntity ExecuteReaderSingle <TEntity>(IDbConnection connection, string storedProcedure, dynamic partialParam, TEntity entity = null, bool includeReturnValue = true, bool implicitCast = true) where TEntity : class, new()
{
//db value don't exist in entity
if (partialParam == null)
{
partialParam = new ExpandoObject();
}
if (entity == null)
{
entity = new TEntity();
}
//if (partialParam == null) throw new Exception("Initialize the 'partialParam' parameter to an ExpandoObject!");
if (partialParam.GetType() != typeof(ExpandoObject))
{
throw new Exception("Initialize the 'partialParam' parameter to an ExpandoObject!");
}
var param = partialParam as IDictionary <string, object>;
//NOTE: partialParam take presedence over the entity properties. Basically to give you control
//NOTE: Output Parameters will return no value. Use ExecuteNonQuery or ExecuteScalar instead
Type entityType = typeof(TEntity);
IList <TEntity> entites = new List <TEntity>();
using (connection) {
using (IDbCommand command = connection.CreateCommand()) {
command.CommandText = storedProcedure;
command.CommandType = CommandType.StoredProcedure;
if (connection.State != ConnectionState.Open)
{
connection.Open(); //new List<IDataParameter>(); //
}
List <IDataParameter> paramList = (List <IDataParameter>)SqlHelperParameterCache.GetSpParameterSet(connection, storedProcedure, includeReturnValue);
/*
* For now, use the foreach
* string _key = storedProcedure + entityType.Name + paramList.GetHashCode().ToString();
* AttachParamToCommand _genMethod;
* TryGetQueryCache(_key, out _genMethod);
* if (_genMethod == null) {
* _genMethod = GenerateAttachParamToCommand<TEntity>(command, paramList, ref partialParam);
* SetQueryCache(_key, _genMethod);
* }
* _genMethod(command, paramList, entity, ref partialParam);*/
//Initialize || Set param values
foreach (IDataParameter p in paramList)
{
if (p.Direction != ParameterDirection.ReturnValue)
{
bool hasSetParamValue = false;
string paramName = p.ParameterName.TrimStart('@');
if (HasPartialProperty(partialParam, paramName))
{
p.Value = param[paramName]; hasSetParamValue = true;
}
else
{
//Find the parameter in the entity
PropertyInfo prop = typeof(TEntity).GetProperty(paramName);
if (prop != null)
{
p.Value = prop.GetValue(entity, null) ?? DBNull.Value; hasSetParamValue = true;
}
}
if (!hasSetParamValue && p.Direction == ParameterDirection.Input)
{
throw new ArgumentException("The required stored procedure parameter (" + paramName + ") does not exist in entity or was not specified!", paramName);
}
}
command.Parameters.Add(p);
}
using (IDataReader dr = command.ExecuteReader()) {
if (typeof(TEntity).IsClass)
{
int rowIndex = 0;
string _drClassDeserializerKey = storedProcedure + entityType.Name;
EntityDeserializer _drClassDeserializer;
TryGetQueryCache(_drClassDeserializerKey, out _drClassDeserializer);
if (_drClassDeserializer == null)
{
_drClassDeserializer = DeserializeEntity <TEntity>(dr,
++rowIndex, command.Parameters, entites, entityType, partialParam, implicitCast);
SetQueryCache(_drClassDeserializerKey, _drClassDeserializer);
}
//TODO: Get table columns and cache it
if (dr.Read()) //true => implicitCast
{
return((TEntity)_drClassDeserializer(dr, true, partialParam) ?? new TEntity());
}
}
else
{
throw new Exception("Non-Class entity types are not supported for ExecuteReader");
}
}
}
}
return(new TEntity());
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the
/// resultset returned by the query. Extra columns or rows are ignored.
/// It will also load the partialParam with the current row's non-TEntity values.
///
/// If you need the return value, access it dynamically from partialParam
/// Note: @RETURN_VALUE is returned as ReturnValue
/// </summary>
/// <typeparam name="TEntity">The [TableName]VD associated with the table entity coming from the stored procedure.</typeparam>
/// <typeparam name="TReturnValue">The type for the firs column of the first row in the resultset</typeparam>
/// <param name="connString">The connection string that identifies the database server</param>
/// <param name="storedProcedure">The stored procedure to execute</param>
/// <param name="partialParam">Populated with row/record values that ExecuteReader can't find a corresponding property in your TEntity</param>
/// <param name="entity">Populated with row/record values that ExecuteReader can't find a corresponding property in your TEntity</param>
/// <returns>The first column of the first row in the resultset</returns>
public static TReturnValue ExecuteScalar <TEntity, TReturnValue>(IDbConnection connection, string storedProcedure, ref dynamic partialParam, TEntity entity = null) where TEntity : class, new()
{
TReturnValue returnValue;
//db value don't exist in entity
if (entity == null)
{
entity = new TEntity();
}
if (partialParam == null)
{
partialParam = new ExpandoObject();
}
if (partialParam.GetType() != typeof(ExpandoObject))
{
throw new Exception("Initialize the 'partialParam' parameter to an ExpandoObject!");
}
var param = partialParam as IDictionary <string, object>;
//TODO: partialParam take presedence over the entity properties. Basically to give you control
//TODO: Output Parameters will return no value. Use ExecuteNonQuery or ExecuteScalar instead
IList <TEntity> list = new List <TEntity>();
using (connection) {
using (IDbCommand command = connection.CreateCommand()) {
command.CommandText = storedProcedure;
command.CommandType = CommandType.StoredProcedure;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
IList <IDataParameter> paramList = SqlHelperParameterCache.GetSpParameterSet(connection, storedProcedure, true);
//Initialize || Set param values
foreach (IDataParameter p in paramList)
{
if (p.Direction != ParameterDirection.ReturnValue)
{
bool hasSetParamValue = false;
string paramName = p.ParameterName.TrimStart('@');
//Check in the partial pram
//param = partialParam.Get(paramName);
if (HasPartialProperty(partialParam, paramName))
{
p.Value = param[paramName]; hasSetParamValue = true;
}
else
{
//Find the parameter in the entity
PropertyInfo prop = typeof(TEntity).GetProperty(paramName);
if (prop != null)
{
p.Value = prop.GetValue(entity, null) ?? DBNull.Value; hasSetParamValue = true;
}
}
if (!hasSetParamValue && p.Direction == ParameterDirection.Input)
{
throw new ArgumentException("The required stored procedure parameter (" + paramName + ") does not exist in entity or was not specified!", paramName);
}
}
command.Parameters.Add(p);
}
var o = command.ExecuteScalar();
returnValue = (o == null) ? default(TReturnValue) : (TReturnValue)o;
foreach (SqlParameter p in command.Parameters)
{
string _paramName = p.ParameterName.TrimStart('@');
if (_paramName == "RETURN_VALUE")
{
param["ReturnValue"] = p.Value;
}
else
{
param[p.ParameterName.TrimStart('@')] = p.Value;
}
}
command.Parameters.Clear();
}
}
return(returnValue);
}
