/// <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); }