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