public static int DeleteFromQuery <T>(this IQueryable <T> querable, int?commandTimeout)
{
int rowAffected = 0;
var dbQuery = querable as DbQuery <T>;
var dbConnection = GetSqlConnectionFromIQuerable(querable);
//Open datbase connection
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var dbTransaction = dbConnection.BeginTransaction())
{
try
{
var sqlQuery = SqlBuilder.Parse(dbQuery.Sql);
sqlQuery.ChangeToDelete("[Extent1]");
rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction, commandTimeout);
dbTransaction.Commit();
}
catch (Exception)
{
dbTransaction.Rollback();
throw;
}
finally
{
dbConnection.Close();
}
}
return(rowAffected);
}
public static int UpdateFromQuery <T>(this IQueryable <T> querable, Expression <Func <T, T> > updateExpression)
{
int rowAffected = 0;
var dbQuery = querable as DbQuery <T>;
var dbConnection = GetSqlConnectionFromIQuerable(querable);
//Open datbase connection
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var dbTransaction = dbConnection.BeginTransaction())
{
try
{
var sqlQuery = SqlQuery.Parse(dbQuery.Sql);
string setSqlExpression = updateExpression.ToSqlUpdateSetExpression("Extent1");
sqlQuery.ChangeToUpdate("[Extent1]", setSqlExpression);
rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction);
dbTransaction.Commit();
}
catch (Exception ex)
{
dbTransaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
}
return(rowAffected);
}
public static int BulkUpdate <T>(this DbContext context, IEnumerable <T> entities, BulkUpdateOptions <T> options)
{
int rowsUpdated = 0;
var outputRows = new List <BulkMergeOutputRow <T> >();
var tableMapping = context.GetTableMapping(typeof(T));
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.Columns.Where(o => !o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
string[] storeGeneratedColumnNames = tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction);
BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity);
IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.IgnoreColumnsOnUpdate.GetObjectProperties().Contains(o));
string updateSetExpression = string.Join(",", columnstoUpdate.Select(o => string.Format("t.{0}=s.{0}", o)));
string updateOnExpression = string.Join(" AND ", storeGeneratedColumnNames.Select(o => string.Format("s.{0}=t.{0}", o)));
string updateSql = string.Format("UPDATE t SET {0} FROM {1} AS s JOIN {2} AS t ON {3}; SELECT @@RowCount;",
updateSetExpression, stagingTableName, destinationTableName, updateOnExpression);
rowsUpdated = SqlUtil.ExecuteSql(updateSql, dbConnection, transaction);
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
//ClearEntityStateToUnchanged(context, entities);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
return(rowsUpdated);
}
}
public static int InsertFromQuery <T>(this IQueryable <T> querable, string tableName, Expression <Func <T, object> > insertObjectExpression, BulkInsertOptions <T> options)
{
int rowAffected = 0;
var dbQuery = querable as DbQuery <T>;
var dbConnection = GetSqlConnectionFromIQuerable(querable);
//Open datbase connection
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var dbTransaction = dbConnection.BeginTransaction())
{
try
{
var sqlQuery = SqlQuery.Parse(dbQuery.Sql);
if (SqlUtil.TableExists(tableName, dbConnection, dbTransaction))
{
sqlQuery.ChangeToInsert(tableName, insertObjectExpression);
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentityInsert(true, tableName, dbConnection, dbTransaction);
}
rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction, options);
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentityInsert(false, tableName, dbConnection, dbTransaction);
}
}
else
{
sqlQuery.Clauses.First().InputText += string.Format(" INTO {0}", tableName);
rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction, options);
}
dbTransaction.Commit();
}
catch (Exception ex)
{
dbTransaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
}
return(rowAffected);
}
public static int BulkDelete <T>(this DbContext context, IEnumerable <T> entities, BulkDeleteOptions <T> options)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
Validate(tableMapping);
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] keyColumnNames = options.DeleteOnCondition != null ? CommonUtil <T> .GetColumns(options.DeleteOnCondition, new[] { "s" })
: tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, keyColumnNames, dbConnection, transaction);
BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, keyColumnNames, SqlBulkCopyOptions.KeepIdentity);
string deleteSql = string.Format("DELETE t FROM {0} s JOIN {1} t ON {2}", stagingTableName, destinationTableName,
CommonUtil <T> .GetJoinConditionSql(options.DeleteOnCondition, keyColumnNames));
rowsAffected = SqlUtil.ExecuteSql(deleteSql, dbConnection, transaction, options.CommandTimeout);
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
dbConnection.Close();
}
return(rowsAffected);
}
}
public int ExecuteNonQuery()
{
return(SqlUtil.ExecuteSql(this.SqlText, this.Connection, null, this.Parameters));
}
internal static int ExecuteSql(string query, SqlConnection connection, SqlTransaction transaction, int?commandTimeout = null)
{
return(SqlUtil.ExecuteSql(query, connection, transaction, null, commandTimeout));
}
