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);
}
/// <summary>
/// Delete all entities in the table related to the type T asynchronously using .NET 4.5 Task.
/// </summary>
/// <typeparam name="T">Type of entity</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>true if deleted, false if none found</returns>
public static async Task <bool> DeleteAllAsync <T>(this IDbConnection connection, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
var statement = "DELETE FROM " + GetTableName(type);
var deleted = await connection.ExecuteAsync(statement, null, transaction, commandTimeout).ConfigureAwait(false);
return(deleted > 0);
}
/// <summary>
/// Inserts an entity into table "Ts" asynchronously using .NET 4.5 Task and returns identity id.
/// </summary>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToInsert">Entity to insert</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>
/// <param name="sqlAdapter">The specific ISqlAdapter to use, auto-detected based on connection if null</param>
/// <returns>Identity of inserted entity</returns>
public static async Task <int> InsertAsync <T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null,
int?commandTimeout = null, ISqlAdapter sqlAdapter = null) where T : class
{
var type = typeof(T);
if (sqlAdapter == null)
{
sqlAdapter = GetFormatter(connection);
}
var isList = false;
if (type.IsArray || type.IsGenericType())
{
isList = true;
type = type.GetGenericArguments()[0];
}
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type);
var computedProperties = ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
sqlAdapter.AppendColumnName(sbColumnList, property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbColumnList.Append(", ");
}
}
var sbParameterList = new StringBuilder(null);
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
sbParameterList.AppendFormat("@{0}", property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbParameterList.Append(", ");
}
}
if (!isList) //single entity
{
return(await sqlAdapter.InsertAsync(connection, transaction, commandTimeout, name, sbColumnList.ToString(),
sbParameterList.ToString(), keyProperties, entityToInsert));
}
//insert list of entities
var cmd = $"INSERT INTO {name} ({sbColumnList}) values ({sbParameterList})";
return(await connection.ExecuteAsync(cmd, entityToInsert, transaction, commandTimeout));
}
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>
/// Delete entity in table "Ts" asynchronously using .NET 4.5 Task.
/// </summary>
/// <typeparam name="T">Type of entity</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToDelete">Entity to delete</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>true if deleted, false if not found</returns>
public static async Task <bool> DeleteAsync <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
if (entityToDelete == null)
{
throw new ArgumentException("Cannot Delete null Object", nameof(entityToDelete));
}
var type = typeof(T);
if (type.IsArray)
{
type = type.GetElementType();
}
else if (type.IsGenericType())
{
type = type.GetGenericArguments()[0];
}
var keyProperties = KeyPropertiesCache(type);
var explicitKeyProperties = ExplicitKeyPropertiesCache(type);
if (!keyProperties.Any() && !explicitKeyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var name = GetTableName(type);
keyProperties.AddRange(explicitKeyProperties);
var sb = new StringBuilder();
sb.AppendFormat("DELETE FROM {0} WHERE ", name);
for (var i = 0; i < keyProperties.Count; i++)
{
var property = keyProperties.ElementAt(i);
sb.AppendFormat("{0} = @{1}", property.Name, property.Name);
if (i < keyProperties.Count - 1)
{
sb.AppendFormat(" AND ");
}
}
var deleted = await connection.ExecuteAsync(sb.ToString(), entityToDelete, transaction, commandTimeout).ConfigureAwait(false);
return(deleted > 0);
}
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);
}
/// <summary>
/// Updates entity in table "Ts" asynchronously using .NET 4.5 Task, checks if the entity is modified if the entity is tracked by the Get() extension.
/// </summary>
/// <typeparam name="T">Type to be updated</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToUpdate">Entity to be updated</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>true if updated, false if not found or not modified (tracked entities)</returns>
public static async Task <bool> UpdateAsync <T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var proxy = entityToUpdate as IProxy;
if (proxy != null)
{
if (!proxy.IsDirty)
{
return(false);
}
}
var type = typeof(T);
if (type.IsArray)
{
type = type.GetElementType();
}
else if (type.IsGenericType())
{
type = type.GetGenericArguments()[0];
}
var keyProperties = KeyPropertiesCache(type);
var explicitKeyProperties = ExplicitKeyPropertiesCache(type);
if (!keyProperties.Any() && !explicitKeyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("update {0} set ", name);
var allProperties = TypePropertiesCache(type);
keyProperties.AddRange(explicitKeyProperties);
var computedProperties = ComputedPropertiesCache(type);
var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
var adapter = GetFormatter(connection);
for (var i = 0; i < nonIdProps.Count; i++)
{
var property = nonIdProps.ElementAt(i);
adapter.AppendColumnNameEqualsValue(sb, property.Name);
if (i < nonIdProps.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(" where ");
for (var i = 0; i < keyProperties.Count; i++)
{
var property = keyProperties.ElementAt(i);
adapter.AppendColumnNameEqualsValue(sb, property.Name);
if (i < keyProperties.Count - 1)
{
sb.AppendFormat(" and ");
}
}
var updated = await connection.ExecuteAsync(sb.ToString(), entityToUpdate, commandTimeout : commandTimeout, transaction : transaction).ConfigureAwait(false);
return(updated > 0);
}
