public static int InsertFromQuery <T>(this IQueryable <T> querable, string tableName, Expression <Func <T, object> > insertObjectExpression) where T : class
{
int rowAffected = 0;
var dbContext = GetDbContextFromIQuerable(querable);
var dbConnection = dbContext.GetSqlConnection();
//Open datbase connection
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var dbTransaction = dbConnection.BeginTransaction())
{
try
{
var sqlQuery = SqlQuery.Parse(querable.ToQueryString());
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.GetColumns(options.KeepIdentity);
string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().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();
}
}
}
