/// <summary>
/// Updates entity in table "Ts", 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="entitiesToUpsert">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 async static Task <int> UpsertBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToUpsert,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
var typeMeta = TypeMeta.Get <T>();
return(await connection.MergeBulkAsync <T>(
entitiesToUpsert,
transaction,
commandTimeout,
sqlBulkCopy : sqlBulkCopy,
matched =>
{
matched.Action = MergeAction.Update;
matched.Condition = typeMeta.PropertiesExceptKeyAndComputed.ColumnListDifferenceCheck();
},
notMatchedByTarget =>
{
notMatchedByTarget.Action = MergeAction.Insert;
}
));
}
/// <summary>
/// Inserts an entity into table "Ts" and returns identity id or number of inserted rows if inserting a list.
/// </summary>
/// <typeparam name="T">The type to insert.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entitiesToInsert">Entity to insert, can be list of entities</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>Identity of inserted entity, or number of inserted rows if inserting a list</returns>
public async static Task <int> InsertBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToInsert,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
var meta = TypeMeta.Get <T>();
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var insertedTableName = await connection.TransferBulkAsync(entitiesToInsert, transaction : transaction, sqlBulkCopy : sqlBulkCopy);
var columnList = meta.PropertiesExceptKeyAndComputed.ColumnList();
var query = $@"
INSERT INTO {meta.TableName} ({columnList})
SELECT {columnList} FROM {insertedTableName};
" ;
var inserted = await connection.ExecuteAsync(query, commandTimeout, transaction);
if (wasClosed)
{
connection.Close();
}
return(inserted);
}
/// <summary>
/// Updates entity in table "Ts", 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="entitiesToDelete">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 async static Task <int> DeleteBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToDelete,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
var entityCount = entitiesToDelete.Count();
if (entityCount == 0)
{
return(0);
}
if (entityCount == 1)
{
return(await connection.DeleteAsync(entitiesToDelete.First(), transaction : transaction, commandTimeout : commandTimeout) ? 1 : 0);
}
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var insertedTableName = await connection.TransferBulkAsync(
entitiesToDelete,
typeMeta.TableName,
typeMeta.PropertiesKeyAndExplicit,
transaction : transaction,
sqlBulkCopy : sqlBulkCopy
);
var query = $@"
DELETE Target
FROM
{ insertedTableName } AS Source
INNER JOIN { typeMeta.TableName } AS Target
ON { typeMeta.PropertiesKeyAndExplicit.ColumnListEquals(" AND ") };
" ;
var deleted = await connection.ExecuteAsync(query, commandTimeout : commandTimeout, transaction : transaction);
if (wasClosed)
{
connection.Close();
}
return(deleted);
}
public ProxyTypeDescriptor(Type type)
{
Meta = TypeMeta.Get(type);
ProxyType = typeof(PlatformProxy <>).MakeGenericType(type);
_properties = Meta.Members.Select(m => new ProxyPropertyDescriptor(this, m)).ToArray();
_propertyCollection = new PropertyDescriptorCollection(_properties);
_events = new EventDescriptorCollection(type.GetEvents().Select(e => new ProxyEventDescriptor(e)).ToArray());
}
public MergeActionOptions <T> Insert()
{
var typeMeta = TypeMeta.Get <T>();
Action = MergeAction.Insert;
ColumnsByPropertyInfo(typeMeta.PropertiesExceptKeyAndComputed);
return(this);
}
/// <summary>
/// Merges entity in table "Ts", 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="entitiesToMerge">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 async static Task <int> MergeBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToMerge,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null,
Action <MergeKeyOptions> key = null,
Action <MergeActionOptions <T> > matched = null,
Action <MergeActionOptions <T> > notMatchedByTarget = null,
Action <MergeActionOptions <T> > notMatchedBySource = null
) where T : class
{
if (entitiesToMerge == null)
{
throw new ArgumentNullException(nameof(entitiesToMerge));
}
var entityCount = entitiesToMerge.Count();
if (entityCount == 0)
{
return(0);
}
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var result = await connection.Execute(
entitiesToMerge,
typeMeta.PropertiesExceptComputed,
async (connection, transaction, source, parameters, properties) =>
{
var sb = new StringBuilder($@"
MERGE INTO {typeMeta.TableName} AS Target
USING {source} AS Source
ON ({OnColumns(typeMeta, keyAction: key).ColumnListEquals(" AND ")})"
);
sb.AppendLine();
MergeMatchResult.Matched.Format(typeMeta, matched, sb);
MergeMatchResult.NotMatchedBySource.Format(typeMeta, notMatchedBySource, sb);
MergeMatchResult.NotMatchedByTarget.Format(typeMeta, notMatchedByTarget, sb);
//MergeOutputFormat(typeMeta.PropertiesKey.Union(typeMeta.PropertiesComputed).ToList(), sb);
sb.Append(";");
return(await connection.ExecuteAsync(sb.ToString(), param: parameters, commandTimeout: commandTimeout, transaction: transaction));
},
transaction : transaction
);
return(result.Sum());
}
public MergeActionOptions <T> Update()
{
var typeMeta = TypeMeta.Get <T>();
Action = MergeAction.Update;
ColumnsByPropertyInfo(typeMeta.PropertiesExceptKeyAndComputed);
CheckConditionOnColumns();
return(this);
}
/// <summary>
/// Merges entity in table "Ts", 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="entitiesToMerge">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 async static Task <int> MergeBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToMerge,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null,
Action <MergeActionOptions> matched = null,
Action <MergeActionOptions> notMatchedByTarget = null,
Action <MergeActionOptions> notMatchedBySource = null
) where T : class
{
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var wasClosed = connection.State == System.Data.ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var insertedTableName = await connection.TransferBulkAsync(
entitiesToMerge,
typeMeta.TableName,
typeMeta.Properties,
transaction : transaction,
sqlBulkCopy : sqlBulkCopy
);
var sb = new StringBuilder($@"
MERGE INTO {typeMeta.TableName} AS Target
USING {insertedTableName} AS Source
ON ({typeMeta.PropertiesKeyAndExplicit.ColumnListEquals(" AND ")})"
);
MergeMatchResult.Matched.Format(typeMeta, matched, sb);
MergeMatchResult.NotMatchedBySource.Format(typeMeta, notMatchedBySource, sb);
MergeMatchResult.NotMatchedByTarget.Format(typeMeta, notMatchedByTarget, sb);
sb.Append(";");
var merged = await connection.ExecuteAsync(sb.ToString(), commandTimeout : commandTimeout, transaction : transaction);
if (wasClosed)
{
connection.Close();
}
return(merged);
}
/// <summary>
/// Updates entity in table "Ts", 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="entitiesToUpdate">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 async static Task <int> UpdateBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToUpdate,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
entitiesToUpdate = entitiesToUpdate.Where(x => (x is SqlMapperExtensions.IProxy proxy && !proxy.IsDirty) || !(x is SqlMapperExtensions.IProxy));
var entityCount = entitiesToUpdate.Count();
if (entityCount == 0)
{
return(0);
}
if (entityCount == 1)
{
return(await connection.UpdateAsync(entitiesToUpdate.First(), transaction : transaction, commandTimeout : commandTimeout) ? 1 : 0);
}
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var result = await connection.Execute(
entitiesToUpdate,
typeMeta.PropertiesExceptComputed,
async (connection, transaction, source, parameters, properties) =>
{
var query = $@"
UPDATE Target
SET
{ typeMeta.PropertiesExceptKeyAndComputed.ColumnListEquals(", ") }
FROM
{ source } AS Source
INNER JOIN { typeMeta.TableName } AS Target
ON { typeMeta.PropertiesKeyAndExplicit.ColumnListEquals(" AND ") };
" ;
return(await connection.ExecuteAsync(query, param: parameters, commandTimeout: commandTimeout, transaction: transaction));
},
transaction : transaction
);
return(result.Sum());
}
public static ProxyXamlType Get(Type type)
{
var meta = TypeMeta.Get(type);
lock (_cache)
{
ProxyXamlType result;
if (!_cache.TryGetValue(type, out result))
{
_cache[type] = result = new ProxyXamlType(meta);
}
return(result);
}
}
/// <summary>
/// Updates entity in table "Ts", 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="entitiesToUpdate">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 async static Task <bool> UpdateBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToUpdate,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
entitiesToUpdate = entitiesToUpdate.Where(x => x is SqlMapperExtensions.IProxy proxy && !proxy.IsDirty);
if (!entitiesToUpdate.Any())
{
return(false);
}
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var insertedTableName = await connection.TransferBulkAsync(entitiesToUpdate, transaction : transaction, sqlBulkCopy : sqlBulkCopy);
var query = $@"
UPDATE Target
SET
{ typeMeta.PropertiesExceptKeyAndComputed.ColumnListEquals(", ") }
FROM
{ insertedTableName } AS Source
INNER JOIN { typeMeta.TableName } AS Target
ON { typeMeta.PropertiesKeyAndExplicit.ColumnListEquals(" AND ") };
" ;
var updated = await connection.ExecuteAsync(query, commandTimeout : commandTimeout, transaction : transaction);
if (wasClosed)
{
connection.Close();
}
return(updated > 0);
}
public async static Task <IEnumerable <T> > GetBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToGet,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
)
{
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var insertedTableName = await connection.TransferBulkAsync(
entitiesToGet,
typeMeta.TableName,
typeMeta.PropertiesKeyAndExplicit,
transaction : transaction,
sqlBulkCopy : sqlBulkCopy
);
var query = $@"
SELECT *
FROM
{ insertedTableName } AS Source
INNER JOIN { typeMeta.TableName } AS Target
ON { typeMeta.PropertiesKeyAndExplicit.ColumnListEquals(" AND ") };
" ;
var enities = await connection.QueryAsync <T>(query, commandTimeout : commandTimeout, transaction : transaction);
if (wasClosed)
{
connection.Close();
}
return(enities);
}
public static async Task <string> TransferBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToInsert,
SqlTransaction transaction = null,
Action <SqlBulkCopy> sqlBulkCopy = null
)
{
var meta = TypeMeta.Get <T>();
return(await connection.TransferBulkAsync(
entitiesToInsert,
meta.TableName,
meta.Properties,
transaction : transaction,
sqlBulkCopy : sqlBulkCopy
));
}
/// <summary>
/// Inserts an entity into table "Ts" and returns identity id or number of inserted rows if inserting a list.
/// </summary>
/// <typeparam name="T">The type to insert.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entitiesToInsert">Entity to insert, can be list of entities</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>Identity of inserted entity, or number of inserted rows if inserting a list</returns>
public async static Task <int> InsertBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToInsert,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
) where T : class
{
var entityCount = entitiesToInsert.Count();
if (entityCount == 0)
{
return(0);
}
if (entityCount == 1)
{
return(await connection.InsertAsync(entitiesToInsert, transaction : transaction, commandTimeout : commandTimeout));
}
var typeMeta = TypeMeta.Get <T>();
var result = await connection.Execute(
entitiesToInsert,
typeMeta.PropertiesExceptKeyAndComputed,
async (connection, transaction, source, parameters, properties) =>
{
var columnList = properties.ColumnList();
var query = $@"
INSERT INTO {typeMeta.TableName} ({columnList})
SELECT {columnList} FROM {source} AS Source;
" ;
return(await connection.ExecuteAsync(query, param: parameters, commandTimeout: commandTimeout, transaction: transaction));
},
transaction : transaction
);
return(result.Sum());
}
/// <summary>
/// Updates entity in table "Ts", 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="entitiesToUpsert">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 async static Task <int> UpsertBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToUpsert,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null,
Action <MergeKeyOptions> key = null
) where T : class
{
var typeMeta = TypeMeta.Get <T>();
return(await connection.MergeBulkAsync <T>(
entitiesToUpsert,
transaction,
commandTimeout,
sqlBulkCopy : sqlBulkCopy,
key : key,
matched : options => options.Update(),
notMatchedByTarget : options => options.Insert()
));
}
public async static Task <IEnumerable <T> > GetBulkAsync <T>(
this SqlConnection connection,
IEnumerable <T> entitiesToGet,
SqlTransaction transaction = null,
int?commandTimeout = null,
Action <SqlBulkCopy> sqlBulkCopy = null
)
{
if (!entitiesToGet.Any())
{
return(new List <T>());
}
var typeMeta = TypeMeta.Get <T>();
if (typeMeta.PropertiesKey.Count == 0 && typeMeta.PropertiesExplicit.Count == 0)
{
throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");
}
var result = await connection.Execute(
entitiesToGet,
typeMeta.PropertiesKeyAndExplicit,
async (connection, transaction, source, parameters, properties) =>
{
var query = $@"
SELECT *
FROM
{ source } AS Source
INNER JOIN { typeMeta.TableName } AS Target
ON { properties.ColumnListEquals(" AND ") };
" ;
return(await connection.QueryAsync <T>(query, param: parameters, commandTimeout: commandTimeout, transaction: transaction));
},
transaction : transaction
);
return(result.SelectMany(x => x.Select(y => y)));
}
public static async Task <int> TruncateAsync <T>(
this SqlConnection connection
)
{
var typeMeta = TypeMeta.Get <T>();
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
{
connection.Open();
}
var result = await connection.ExecuteAsync($"TRUNCATE TABLE {typeMeta.TableName}");
if (wasClosed)
{
connection.Close();
}
return(result);
}
