public static int InsertFromQuery <T>(this IQueryable <T> querable, string tableName, Expression <Func <T, object> > insertObjectExpression) { 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); SqlUtil.ToggleIdentiyInsert(true, tableName, dbConnection, dbTransaction); rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction); SqlUtil.ToggleIdentiyInsert(false, tableName, dbConnection, dbTransaction); } else { sqlQuery.Clauses.First().InputText += string.Format(" INTO {0}", tableName); rowAffected = SqlUtil.ExecuteSql(sqlQuery.Sql, dbConnection, dbTransaction); } dbTransaction.Commit(); } catch (Exception ex) { dbTransaction.Rollback(); throw ex; } finally { dbConnection.Close(); } } return(rowAffected); }
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options) { int rowsAffected = 0; 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 => options.KeepIdentity || !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, Common.Constants.Guid_ColumnName); var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true); IEnumerable <string> columnsToInsert = columnNames; List <string> columnsToOutput = new List <string>(); List <PropertyInfo> propertySetters = new List <PropertyInfo>(); Type entityType = typeof(T); foreach (var storeGeneratedColumnName in storeGeneratedColumnNames) { columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName)); propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName)); } string mergeSqlText = string.Format("INSERT INTO {0} ({1}) OUTPUT {2} SELECT {3} FROM {4};", destinationTableName, SqlUtil.ConvertToColumnString(columnsToInsert), SqlUtil.ConvertToColumnString(columnsToOutput), SqlUtil.ConvertToColumnString(columnsToInsert), stagingTableName); if (options.KeepIdentity) { SqlUtil.ToggleIdentiyInsert(true, destinationTableName, dbConnection, transaction); } var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction); if (options.KeepIdentity) { SqlUtil.ToggleIdentiyInsert(false, destinationTableName, dbConnection, transaction); } rowsAffected = bulkQueryResult.RowsAffected; if (options.AutoMapOutputIdentity) { if (rowsAffected == entities.Count()) { var entityIndex = 1; foreach (var result in bulkQueryResult.Results) { var entity = bulkInsertResult.EntityMap[entityIndex]; propertySetters[0].SetValue(entity, result[0]); entityIndex++; } } } SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction); //ClearEntityStateToUnchanged(context, entities); transaction.Commit(); return(rowsAffected); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { dbConnection.Close(); } } }