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