/// <summary>
/// Execure Sp that returns a multiple records
/// </summary>
/// <param name="spName"></param>
/// <param name="parameters">
/// <remarks>do not use @ in specifying parameters, this is automatically done</remarks></param>
/// <param name="propertiesToInclude"></param>
/// <param name="useCache"></param>
/// <param name="cacheIntervalInMinutes"></param>
/// <returns></returns>
public TDto ExecuteUniqueSp(string spName, Dictionary <string, string> parameters = null, List <String> propertiesToInclude = null, bool useCache = false, int cacheIntervalInMinutes = 10)
{
try
{
var cacheKey = string.Format("{0}_{1}", DataStore, spName);
if (useCache)
{
var cachedData = MemoryCacheHelper.GetCachedData(cacheKey);
if (cachedData != null)
{
return((TDto)cachedData);
}
}
var instance = Activator.CreateInstance <TDto>();
var properties = instance.GetType().GetProperties();
var connStr = ConfigurationManager.ConnectionStrings[DataStore].ConnectionString;
using (var conn = new SqlConnection(connStr))
{
var cmd = new SqlCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
};
conn.Open();
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.AddWithValue(string.Format("@{0}", parameter.Key), parameter.Value);
}
}
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(null);
}
while (reader.Read())
{
instance = Activator.CreateInstance <TDto>();
foreach (var propertyInfo in properties)
{
try
{
if (propertyInfo.PropertyType.GetInterfaces().Contains(typeof(IDto)))
{
var attr =
propertyInfo.GetCustomAttributes(typeof(LightFootAttribute), false)
.FirstOrDefault() as LightFootAttribute;
if (attr == null)
{
continue;
}
var mapsToColumn = attr.MapsTo;
var data = SafeReader <object>(reader, mapsToColumn);
var lfoot = Type.GetType("TechQuest.Framework.Dao.LightFoot.LightFootDao`1");
var stringArg1 = propertyInfo.PropertyType;
var lFootInstanceMaker = lfoot.MakeGenericType(stringArg1);
var lFootInstance = Activator.CreateInstance(lFootInstanceMaker, DataStore,
DataStoreTypeImpl);
var method = lFootInstance.GetType().GetMethod("ExecuteUniqueSp");
var resInstance = method.Invoke(lFootInstance, new object[] { attr.MapsToSp, new Dictionary <string, string> {
{ attr.ForeignKeyId, data.ToString() }
}, null, false, 0 });
propertyInfo.SetValue(instance, resInstance, null);
}
else
{
if (propertiesToInclude != null && propertiesToInclude.Contains(propertyInfo.Name))
{
continue;
}
var attr =
propertyInfo.GetCustomAttributes(typeof(LightFootAttribute), false)
.FirstOrDefault() as LightFootAttribute;
var data = SafeReader <object>(reader,
attr != null ? attr.ColumnNameOverride : propertyInfo.Name);
var setDataVal = Convert.ChangeType(data, propertyInfo.PropertyType);
propertyInfo.SetValue(instance, setDataVal, null);
}
}
catch (Exception exception)
{
var msg = exception.Message;
}
}
if (useCache)
{
MemoryCacheHelper.SetCachedData(cacheKey, _lock, cacheIntervalInMinutes, instance);
}
return(instance);
}
return(null);
}
}
catch (Exception exception)
{
var msg = exception.Message;
}
return(null);
}
/// <summary>
/// Execure Sp that returns a single record
/// </summary>
/// <param name="spName"></param>
/// <param name="parameters"></param>
/// <param name="propertiesToInclude"></param>
/// <param name="cacheIntervalInMinutes"></param>
/// <returns></returns>
public List <TDto> ExecuteSp(string spName, Dictionary <string, string> parameters = null, List <String> propertiesToInclude = null, int cacheIntervalInMinutes = 10, bool useCache = false)
{
try
{
var cacheKey = string.Format("{0}_{1}", DataStore, spName);
if (useCache)
{
var cachedData = MemoryCacheHelper.GetCachedData(cacheKey);
if (cachedData != null)
{
return((List <TDto>)cachedData);
}
}
var result = new List <TDto>();
var instance = Activator.CreateInstance <TDto>();
var properties = instance.GetType().GetProperties();
var connStr = ConfigurationManager.ConnectionStrings[DataStore].ConnectionString;
using (var conn = new SqlConnection(connStr))
{
var cmd = new SqlCommand(spName, conn)
{
CommandType = CommandType.StoredProcedure
};
conn.Open();
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.AddWithValue(string.Format("@{0}", parameter.Key), parameter.Value);
}
}
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(null);
}
while (reader.Read())
{
instance = Activator.CreateInstance <TDto>();
foreach (var propertyInfo in properties)
{
try
{
if (propertiesToInclude != null && propertiesToInclude.Contains(propertyInfo.Name))
{
continue;
}
var attr = propertyInfo.GetCustomAttributes(typeof(LightFootAttribute), false).FirstOrDefault() as LightFootAttribute;
var data = SafeReader <object>(reader, attr != null ? attr.ColumnNameOverride : propertyInfo.Name);
var setDataVal = Convert.ChangeType(data, propertyInfo.PropertyType);
propertyInfo.SetValue(instance, setDataVal, null);
}
catch (Exception exception)
{
var msg = exception.Message;
}
}
result.Add(instance);
}
if (useCache)
{
MemoryCacheHelper.SetCachedData(cacheKey, _lock, cacheIntervalInMinutes, instance);
}
return(result);
}
}
catch (Exception exception)
{
var msg = exception.Message;
}
return(null);
}