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