/// <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); string sql; if (!GetQueries.TryGetValue(type.TypeHandle, out sql)) { var key = GetSingleKey <T>(nameof(GetAsync)); var name = GetTableName(type); sql = $"SELECT * FROM {name} WHERE {key.Name} = @id"; 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]; property.SetValue(obj, Convert.ChangeType(val, property.PropertyType), null); } ((IProxy)obj).IsDirty = false; //reset change tracking and return return(obj); }
private async Task LiteralReplacement(IDbConnection conn) { try { await conn.ExecuteAsync("drop table literal1"); } catch { } await conn.ExecuteAsync("create table literal1 (id int not null, foo int not null)"); await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", new { id = 123, foo = 456 }); var rows = new[] { new { id = 1, foo = 2 }, new { id = 3, foo = 4 } }; await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", rows); var count = (await conn.QueryAsync <int>("select count(1) from literal1 where id={=foo}", new { foo = 123 })).Single(); count.IsEqualTo(1); int sum = (await conn.QueryAsync <int>("select sum(id) + sum(foo) from literal1")).Single(); sum.IsEqualTo(123 + 456 + 1 + 2 + 3 + 4); }
/// <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 defualt) 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> > GetAllAsync <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(await connection.QueryAsync <T>(sql, null, transaction, commandTimeout)); } var result = await connection.QueryAsync(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); }
private async Task LiteralReplacementDynamic(IDbConnection conn) { var args = new DynamicParameters(); args.Add("id", 123); try { await conn.ExecuteAsync("drop table literal2"); } catch { } await conn.ExecuteAsync("create table literal2 (id int not null)"); await conn.ExecuteAsync("insert literal2 (id) values ({=id})", args); args = new DynamicParameters(); args.Add("foo", 123); var count = (await conn.QueryAsync <int>("select count(1) from literal2 where id={=foo}", args)).Single(); count.IsEqualTo(1); }
private static async Task <IEnumerable <T> > GetAllAsyncImpl <T>(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, string sql, Type type) where T : class { var result = await connection.QueryAsync(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 static async System.Threading.Tasks.Task Test() { SqlFactory fac = new SqlClientFactory(); // DbConnection.ProviderFactory => DbProviderFactory; using (System.Data.Common.DbConnection con = fac.Connection) { string sql = "SELECT * FROM T_BlogPost"; string sql_paged = sql += fac.PagingTemplate(3, 2); string sql_limited = sql += fac.PagingTemplate(1); IEnumerable <T_BlogPost> a = con.Query <T_BlogPost>(sql); IEnumerable <T_BlogPost> aa = await con.QueryAsync <T_BlogPost>(sql_paged); T_BlogPost b = con.QuerySingle <T_BlogPost>(sql_limited); T_BlogPost ba = await con.QuerySingleAsync <T_BlogPost>(sql_limited); } // End Using con } // End Sub Test
/// <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 Task <IEnumerable <T> > GetAllAsync <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.QueryAsync <T>(sql, null, transaction, commandTimeout)); } return(GetAllAsyncImpl <T>(connection, transaction, commandTimeout, sql, type)); }
private async Task LiteralReplacementDynamic(IDbConnection conn) { var args = new DynamicParameters(); args.Add("id", 123); try { await conn.ExecuteAsync("drop table literal2"); } catch { } await conn.ExecuteAsync("create table literal2 (id int not null)"); await conn.ExecuteAsync("insert literal2 (id) values ({=id})", args); args = new DynamicParameters(); args.Add("foo", 123); var count = (await conn.QueryAsync<int>("select count(1) from literal2 where id={=foo}", args)).Single(); count.IsEqualTo(1); }
private async Task LiteralReplacement(IDbConnection conn) { try { await conn.ExecuteAsync("drop table literal1"); } catch { } await conn.ExecuteAsync("create table literal1 (id int not null, foo int not null)"); await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", new { id = 123, foo = 456 }); var rows = new[] { new { id = 1, foo = 2 }, new { id = 3, foo = 4 } }; await conn.ExecuteAsync("insert literal1 (id,foo) values ({=id}, @foo)", rows); var count = (await conn.QueryAsync<int>("select count(1) from literal1 where id={=foo}", new { foo = 123 })).Single(); count.IsEqualTo(1); int sum = (await conn.QueryAsync<int>("select sum(id) + sum(foo) from literal1")).Single(); sum.IsEqualTo(123 + 456 + 1 + 2 + 3 + 4); }