/// <summary>
/// Returns a list of entites from table "Ts".
/// Id of T must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var cacheType = typeof(List <T>);
string sql;
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out sql))
{
GetSingleKey <T>(nameof(GetAll));
var name = GetTableName(type);
sql = "select * from " + name;
GetQueries[cacheType.TypeHandle] = sql;
}
if (!type.IsInterface())
{
return(connection.Query <T>(sql, null, transaction, commandTimeout: commandTimeout));
}
var result = connection.Query(sql);
var list = new List <T>();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
list.Add(obj);
}
return(list);
}
public void TestProxyIsDirtyBehavior()
{
var test = ProxyGenerator.GetInterfaceProxy <IUser>();
var proxy = test as ProxyGenerator.IProxy;
Assert.False(proxy.IsDirty);
test.Age = 1;
Assert.True(proxy.IsDirty);
Assert.Single(proxy.DirtyFields);
test.Age = 1;
Assert.Single(proxy.DirtyFields);
test.Age = 2;
Assert.Single(proxy.DirtyFields);
test.Name = "a";
Assert.True(proxy.IsDirty);
Assert.Equal(2, proxy.DirtyFields.Count());
proxy.MarkAsClean();
Assert.False(proxy.IsDirty);
Assert.Empty(proxy.DirtyFields);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var adapter = GetFormatter(connection);
var idParamName = adapter.GetParameterNameWithPrefix("Id");
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var keys = KeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [Key] property");
}
if (!keys.Any())
{
throw new DataException("Get<T> only supports en entity with a [Key] property");
}
var onlyKey = keys.First();
var name = GetTableName(type);
// TODO: query information schema and only select fields that are both in information schema and underlying class / interface
sql = "select * from " + name + " where " + onlyKey.Name + " = " + idParamName;
GetQueries[type.TypeHandle] = sql;
}
T obj;
if (type.IsInterface)
{
var res = connection.Query(sql, new IdParameter {
Id = id
}).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, val, null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, new IdParameter {
Id = id
}, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts". T must be of interface type.
/// Id must be marked with [Key] attribute.
/// Created entity is tracked/intercepted for changes and used by the Update() extension.
/// </summary>
/// <typeparam name="T">Interface type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
#if DEBUG
if (type.IsAbstract)
{
throw new Exception("It's really weird to insert an abstract class");
}
#endif
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var keys = KeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [Key] property");
}
if (keys.Count() == 0)
{
keys = ManualKeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [ManualKey] property");
}
if (keys.Count() == 0)
{
throw new DataException("Get<T> only supports an entity with a [Key] or [ManualKey] property");
}
}
var onlyKey = keys.First();
var tableName = GetTableName(type);
if (TypeIsTablePerType(type))
{
tableName += " JOIN " + GetTableName(type.BaseType) + " USING (" + onlyKey.Name + ")";
}
// TODO: pluralizer
// TODO: query information schema and only select fields that are both in information schema and underlying class / interface
sql = "select * from " + tableName + " where " + onlyKey.Name + " = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
T obj = null;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return((T)((object)null));
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, val, null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a single entity by multi key from table "T".
/// keyValues is list of columns with values that must be marked with [Key] attribute or [ExplicitKey] attribute.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="keyValues">Columns of the entity to get, must be marked with [Key] attribute or [ExplicitKey] attribute</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, object[] keyValues, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var keys = KeyAndExplicitKeyPropertiesCache(type);
var keyCount = keys.Count;
if (keyValues.Length != keyCount)
{
throw new ArgumentException("Incorrect number of keys passed to Get method");
}
string sql;
var dynParms = new DynamicParameters();
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
sql = $"select * from {name} where ";
var myList = keyValues as System.Collections.IEnumerable;
if (myList != null)
{
foreach (var element in myList)
{
var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(element);
var dict = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize <Dictionary <string, object> >(json);
foreach (var key1 in dict.Keys)
{
if (keys.All(x => x.Name != key1))
{
throw new ArgumentException("Incorrect key name of keys passed to Get method");
}
sql += key1 + " = @" + key1 + " and ";
dynParms.Add("@" + key1, dict[key1]);
}
}
}
sql = sql.Substring(0, sql.Length - 4);
GetQueries[type.TypeHandle] = sql;
}
T obj;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var connectionName = GetDatabaseType?.Invoke(connection).ToLower()
?? connection.GetType().Name.ToLower();
if (!GetQueries.TryGetValue(type.TypeHandle, out string sql))
{
var key = GetSingleKey <T>(nameof(Get));
var name = GetTableName(type);
if (connectionName == "hanaconnection")
{
sql = $"select * from \"{name}\" where \"{key.Name}\" = :id";
}
else
{
sql = $"select * from \"{name}\" where \"{key.Name}\" = :id";
}
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
if (connectionName == "hanaconnection")
{
dynParms.Add(":id", id);
}
else
{
dynParms.Add("@id", id);
}
T obj;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
if (val == null)
{
continue;
}
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var genericType = Nullable.GetUnderlyingType(property.PropertyType);
if (genericType != null)
{
property.SetValue(obj, Convert.ChangeType(val, genericType), null);
}
}
else
{
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a list of entites from table "Ts".
/// Id of T must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static IEnumerable <T> GetAll <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var cacheType = typeof(List <T>);
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out string sql))
{
GetSingleKey <T>(nameof(GetAll));
var adapter = GetFormatter(connection);
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
for (var i = 0; i < allProperties.Count; i++)
{
var property = allProperties[i];
adapter.AppendColumnName(sbColumnList, GetColumnName(property), property.Name);
if (i < allProperties.Count - 1)
{
sbColumnList.Append(", ");
}
}
sql = $"select {sbColumnList.ToString()} from {name}";
GetQueries[cacheType.TypeHandle] = sql;
}
if (!type.IsInterface())
{
return(connection.Query <T>(sql, null, transaction, commandTimeout: commandTimeout));
}
var result = connection.Query(sql);
var list = new List <T>();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
if (val == null)
{
continue;
}
if (property.PropertyType.IsGenericType() && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var genericType = Nullable.GetUnderlyingType(property.PropertyType);
if (genericType != null)
{
property.SetValue(obj, Convert.ChangeType(val, genericType), null);
}
}
else
{
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
list.Add(obj);
}
return(list);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var sb = new StringBuilder();
var adapter = GetFormatter(connection);
var key = GetSingleKey <T>(nameof(Get));
adapter.AppendParametr(sb, key.Name);
if (!GetQueries.TryGetValue(type.TypeHandle, out string sql))
{
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
for (var i = 0; i < allProperties.Count; i++)
{
var property = allProperties[i];
adapter.AppendColumnName(sbColumnList, GetColumnName(property), property.Name);
if (i < allProperties.Count - 1)
{
sbColumnList.Append(", ");
}
}
sql = $"select {sbColumnList.ToString()} from {name} where {GetColumnName(key)} = {sb.ToString()}";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add(sb.ToString(), id);
T obj;
if (type.IsInterface())
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
if (val == null)
{
continue;
}
if (property.PropertyType.IsGenericType() && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var genericType = Nullable.GetUnderlyingType(property.PropertyType);
if (genericType != null)
{
property.SetValue(obj, Convert.ChangeType(val, genericType), null);
}
}
else
{
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// </summary>
/// <typeparam name="T">Interface type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns>Entity of T</returns>
public static IEnumerable <T> GetBy <T>(this IDbConnection connection, object where = null, object order = null, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var isUseWhere = where != null;
var isUseOrder = order != null;
if (!isUseWhere && !isUseOrder)
{
return(GetAll <T>(connection: connection, transaction: transaction, commandTimeout: commandTimeout));
}
var whereType = isUseWhere ? where.GetType() : null;
var orderType = isUseOrder ? order.GetType() : null;
SqlWhereOrderCache cache;
var key = GetKeyTypeWhereOrder(type, whereType, orderType);
if (!GetQueriesWhereOrder.TryGetValue(key, out cache))
{
cache = new SqlWhereOrderCache();
if (isUseWhere)
{
cache.Where = GetListOfNames(whereType.GetProperties());
}
if (isUseOrder)
{
cache.Order = GetListOfNames(orderType.GetProperties());
}
var name = GetTableName(type);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from {0}", name);
int cnt, last, i;
if (isUseWhere)
{
sb.Append(" where ");
cnt = cache.Where.Count();
last = cnt - 1;
for (i = 0; i < cnt; i++)
{
var prop = cache.Where.ElementAt(i);
sb.AppendFormat("[{0}]=@{1}", prop, prop);
if (i != last)
{
sb.Append(" and ");
}
}
}
if (isUseOrder)
{
sb.Append(" order by ");
cnt = cache.Order.Count();
last = cnt - 1;
for (i = 0; i < cnt; i++)
{
var prop = cache.Order.ElementAt(i);
sb.AppendFormat("[{0}] #{1}", prop, prop);
if (i != last)
{
sb.Append(", ");
}
}
}
// TODO: pluralizer
// TODO: query information schema and only select fields that are both in information schema and underlying class / interface
cache.Sql = sb.ToString();
GetQueriesWhereOrder[key] = cache;
}
IEnumerable <T> obj = null;
var dynParms = new DynamicParameters();
if (isUseWhere)
{
foreach (string name in cache.Where)
{
dynParms.Add(name, whereType.GetProperty(name).GetValue(where, null));
}
}
if (isUseOrder)
{
foreach (string name in cache.Order)
{
SortAs enumVal = (SortAs)orderType.GetProperty(name).GetValue(order, null);
switch (enumVal)
{
case SortAs.Asc:
cache.Sql = cache.Sql.Replace("#" + name, "ASC");
break;
case SortAs.Desc:
cache.Sql = cache.Sql.Replace("#" + name, "DESC");
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
if (type.IsInterface)
{
var res = connection.Query(cache.Sql);
if (!res.Any())
{
return((IEnumerable <T>)((object)null));
}
var objList = new List <T>();
foreach (var item in res)
{
T objItem = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = item[property.Name];
property.SetValue(objItem, val, null);
}
((IProxy)objItem).IsDirty = false; //reset change tracking and return
objList.Add(objItem);
}
obj = objList.AsEnumerable();
}
else
{
obj = connection.Query <T>(cache.Sql, dynParms, transaction: transaction, commandTimeout: commandTimeout);
}
return(obj);
}
/// <summary>
/// Returns a paging list of entities from table "T".
/// Id of T must be marked with [Key] attribute.
/// T must have an Order attribute
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static IEnumerable <T> GetPaginated <T> (this IDbConnection connection, ref int total, int currentPage, int itemsPerPage, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var cacheName = nameof(T) + nameof(GetPaginated);
var countCache = nameof(GetPaginated);
if (!GetSqlQueries.TryGetValue(cacheName, out string sql))
{
var key = GetSingleKey <T> (nameof(GetPaginated));
var name = GetTableName(type);
var orderByProp = OrderByPropertiesCache(type).FirstOrDefault();
bool isDesc = false;
if (orderByProp == null)
{
orderByProp = key;
}
else
{
isDesc = IsOrderByDesc(orderByProp);
}
sql = GetSqlAdapter(connection).GetPaginatedCmd(name, orderByProp.Name, currentPage, itemsPerPage, isDesc);
GetSqlQueries[cacheName] = sql;
}
if (!GetSqlQueries.TryGetValue(countCache, out string sqlTotal))
{
GetSingleKey <T> (nameof(GetPaginated));
var name = GetTableName(type);
sqlTotal = "SELECT COUNT(*) FROM " + name;
GetSqlQueries[countCache] = sqlTotal;
}
total = connection.QueryFirst <int> (sqlTotal, null, transaction, commandTimeout: commandTimeout);
if (!type.IsInterface)
{
return(connection.Query <T> (sql, new { Page = currentPage, ItemsPerPage = itemsPerPage, }, transaction, commandTimeout: commandTimeout));
}
var result = connection.Query <T> (sql, new { Page = currentPage, ItemsPerPage = itemsPerPage, }, transaction, commandTimeout: commandTimeout);
var list = new List <T> ();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T> ();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
if (val == null)
{
continue;
}
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var genericType = Nullable.GetUnderlyingType(property.PropertyType);
if (genericType != null)
{
property.SetValue(obj, Convert.ChangeType(val, genericType), null);
}
}
else
{
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
list.Add(obj);
}
return(list);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entity"></param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static async Task <T> GetSigleAsync <T>(this IDbConnection connection, object entity = null, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql = "";
var name = GetTableName(type);
StringBuilder sb = new StringBuilder();
if (entity == null)
{
if (!GetListQueries.TryGetValue(type.TypeHandle, out sql))
{
sb.Append($"select * from {name}");
sql = sb.ToString();
GetListQueries[type.TypeHandle] = sql;
}
else
{
sb.Append(sql);
}
}
else
{
var entityType = entity.GetType();
if (!GetListQueries.TryGetValue(entityType.TypeHandle, out sql))
{
sb.Append($"select * from {name}");
var param = GetParameters(entity);
if (param != null && param.ParameterNames.Count() > 0)
{
sb.Append(" where ");
var adapter = GetFormatter(connection);
int length = param.ParameterNames.Count();
int index = 1;
foreach (var item in param.ParameterNames)
{
adapter.AppendColumnNameEqualsValue(sb, item);
if (index < length)
{
sb.Append(" and ");
}
index++;
}
}
GetListQueries[type.TypeHandle] = sb.ToString();
}
else
{
sb.Append(sql);
}
}
sql = sb.ToString();
if (!type.IsInterface())
{
return(await connection.QueryFirstOrDefaultAsync <T>(sql, entity, transaction, commandTimeout : commandTimeout));
}
var res = (await connection.QueryFirstOrDefaultAsync <dynamic>(sql, entity).ConfigureAwait(false)).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entity">param</param>
/// <param name="isasc">isasc</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static async Task <IEnumerable <T> > GetListAsync <T>(this IDbConnection connection, object entity = null, bool isasc = true, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql = "";
var name = GetTableName(type);
StringBuilder sb = new StringBuilder();
if (entity == null)
{
if (!GetListQueries.TryGetValue(type.TypeHandle, out sql))
{
sb.Append($"select * from {name}");
sql = sb.ToString();
GetListQueries[type.TypeHandle] = sql;
}
else
{
sb.Append(sql);
}
}
else
{
var entityType = entity.GetType();
if (!GetListQueries.TryGetValue(entityType.TypeHandle, out sql))
{
sb.Append($"select * from {name}");
var param = GetParameters(entity);
if (param != null && param.ParameterNames.Count() > 0)
{
sb.Append(" where ");
var adapter = GetFormatter(connection);
int length = param.ParameterNames.Count();
int index = 1;
foreach (var item in param.ParameterNames)
{
adapter.AppendColumnNameEqualsValue(sb, item);
if (index < length)
{
sb.Append(" and ");
}
index++;
}
}
GetListQueries[type.TypeHandle] = sb.ToString();
}
else
{
sb.Append(sql);
}
}
AppendAsc(sb, type, isasc);
sql = sb.ToString();
if (!type.IsInterface())
{
return(await connection.QueryAsync <T>(sql, entity, transaction, commandTimeout : commandTimeout));
}
var result = await connection.QueryAsync(sql, entity, transaction, commandTimeout : commandTimeout);
var list = new List <T>();
foreach (IDictionary <string, object> res in result)
{
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
((IProxy)obj).IsDirty = false;
list.Add(obj);
}
return(list);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts".
/// Id must be marked with [Key] attribute.
/// Entities created from interfaces are tracked/intercepted for changes and used by the Update() extension
/// for optimal performance.
/// </summary>
/// <typeparam name="T">Interface or type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, bool isTrimKey = false, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var keys = KeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [Key] property");
}
if (!keys.Any())
{
throw new DataException("Get<T> only supports en entity with a [Key] property");
}
var onlyKey = keys.First();
var name = GetTableName(type);
if (isTrimKey)
{
sql = "select * from " + name + " where trim(" + onlyKey.Name + ") = :id";
}
else
{
sql = "select * from " + name + " where " + onlyKey.Name + " = :id";
}
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add(":id", id);
T obj;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, val, null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts". T must be of interface type.
/// Id must be marked with [Key] attribute.
/// Created entity is tracked/intercepted for changes and used by the Update() extension.
/// </summary>
/// <typeparam name="T">Interface type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var keys = KeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [Key] property");
}
if (keys.Count() == 0)
{
throw new DataException("Get<T> only supports en entity with a [Key] property");
}
var onlyKey = keys.First();
var name = GetTableName(type);
int value = findUpperLetter(name);
if (value != -1)
{
var temp = name.Substring(0, value);
temp = temp + "_" + name.Substring(value, name.Length - value);
name = temp;
}
// TODO: pluralizer
// TODO: query information schema and only select fields that are both in information schema and underlying class / interface
sql = "select * from " + name.ToUpper(new CultureInfo("en-US", false)) + " where " + onlyKey.Name + " = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
T obj = null;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return((T)((object)null));
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, val, null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
/// <summary>
/// Returns a single entity by a single id from table "Ts" asynchronously using .NET 4.5 Task. T must be of interface type.
/// Id must be marked with [Key] attribute.
/// Created entity is tracked/intercepted for changes and used by the Update() extension.
/// </summary>
/// <typeparam name="T">Interface type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <returns>Entity of T</returns>
public static async Task <T> GetAsync <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
if (!GetQueries.TryGetValue(type.TypeHandle, out string sql))
{
var key = GetSingleKey <T>(nameof(GetAsync));
var name = GetTableName(type);
var adapter = GetFormatter(connection);
var sb = new StringBuilder();
sb.AppendFormat("select * from {0} where ", name);
adapter.AppendColumnNameEqualsValue(sb, key.Name);
sql = sb.ToString();
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@Id", id);
if (!type.IsInterface())
{
return((await connection.QueryAsync <T>(sql, dynParms, transaction, commandTimeout).ConfigureAwait(false)).FirstOrDefault());
}
var res = (await connection.QueryAsync <dynamic>(sql, dynParms).ConfigureAwait(false)).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return(null);
}
var obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
if (val == null)
{
continue;
}
if (property.PropertyType.IsGenericType() && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var genericType = Nullable.GetUnderlyingType(property.PropertyType);
if (genericType != null)
{
property.SetValue(obj, Convert.ChangeType(val, genericType), null);
}
}
else
{
property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null);
}
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
return(obj);
}
