/// <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);
}
/// <summary>
/// Returns a list of entities 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>);
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out string 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));
}
/// <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, new ()
{
var type = typeof(T);
if (!GetQueries.TryGetValue(type.TypeHandle, out string sql))
{
var key = GetSingleKey <T> (nameof(Get));
var name = GetTableName(type);
sql = $"select * from {name} where {key.Name} = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
var items = connection.Get <T> (sql, dynParms, transaction, commandTimeout);
return(items?.FirstOrDefault());
}
/// <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);
}
/// <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>
/// <param name="pageSize">每页显示数</param>
/// <param name="pageIndex">页码，从0开始</param>
/// <returns>Entity of T</returns>
public static IEnumerable <T> GetAll <T> (this IDbConnection connection,
IDbTransaction transaction = null, int?commandTimeout = null, int?pageSize = null, int?pageIndex = null)
where T : class, new ()
{
var type = typeof(T);
var cacheType = typeof(List <T>);
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out string sql))
{
//GetSingleKey<T> (nameof (GetAll));
var name = GetTableName(type);
sql = $"select * from {name}";
GetQueries[cacheType.TypeHandle] = sql;
}
var sbSql = new StringBuilder();
var adapter = GetFormatter(connection);
adapter.GetPageQuerySql(sbSql, pageSize, pageIndex);
return(connection.Get <T> (sbSql.ToString(), transaction: transaction, commandTimeout: commandTimeout));
}
/// <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> > GetPagedAsync <T>(this IDbConnection connection, int pageNumber = 1, int pageSize = 100, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
if (pageNumber <= 0 || pageSize <= 0)
{
throw new ArgumentException($"pageNumber and pageSize parameters must be greater than zero.");
}
var type = typeof(T);
var cacheType = typeof(List <T>);
if (!GetQueries.TryGetValue(cacheType.TypeHandle, out string sql))
{
GetSingleKey <T>(nameof(GetPaged));
var tableName = GetTableName(type);
var adapter = GetFormatter(connection);
sql = adapter.GetPagedQuery(tableName, pageNumber, pageSize);
GetQueries[cacheType.TypeHandle] = sql;
}
if (!type.IsInterface)
{
return(connection.QueryAsync <T>(sql, null, transaction, commandTimeout));
}
return(GetPagedAsyncImpl <T>(connection, transaction, commandTimeout, sql, type));
}
/// <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);
var sb = new StringBuilder();
var adapter = GetFormatter(connection);
var key = GetSingleKey <T>(nameof(GetAsync));
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);
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);
}