Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A workaround Sql Bulk Insert for Entity Framework 6.
namespace Data.Commands
{
using System;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Diagnostics;
public class CreateConfigurationCommand
{
public int Execute(ISession session, Configuration configuration)
{
// remove the Funds as they create too large an object graph
// with EF 6 and we run out of memory.
var funds = config.Funds;
config.Funds = null;
var context = session.DbContext;
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
using (var transaction = context.Database.BeginTransaction())
{
try
{
// commit everything else in the config to the database.
context.Set<Configuration>().Add(config);
context.SaveChanges();
// let the funds know which config they belong to.
foreach (var fund in funds)
{
fund.ConfigurationId = config.Id;
}
// 1st table - Fund.
foreach (var fund in funds)
{
var FundId = InsertFund(context, transaction, fund);
// create a new DataTable for the Percentages
// for each sheet.
var percents = PercentsDataTable();
// 2nd table - Year.
foreach (var year in fund.Years)
{
var YearId =
InsertYear(context, transaction, year, FundId);
// 3rd table - Percent - use SqlBulkCopy.
foreach (var percent in year.Percents)
{
AddPercentRow(percents, percent, YearId);
}
percents.AcceptChanges();
}
using (
var sqlBulkCopy = new SqlBulkCopy(
(SqlConnection)context.Database.Connection,
SqlBulkCopyOptions.Default,
(SqlTransaction)transaction.UnderlyingTransaction))
{
WriteToServer(sqlBulkCopy, percents);
}
}
transaction.Commit();
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
transaction.Rollback();
context.Set<Configuration>().Remove(config);
context.SaveChanges();
throw;
}
}
return config.Id;
}
private static void AddPercentRow(
DataTable percents, Percent percent, object YearIdentity)
{
var row = percents.NewRow();
row["Percent"] = percent.Percent;
row["FundName"] = percent.FundName;
row["YearId"] = Convert.ToInt32(YearIdentity);
percents.Rows.Add(row);
}
private static object InsertFund(
DbContext context, DbContextTransaction transaction, Fund fund)
{
const string InsertIntoFund =
"INSERT INTO [model].[Fund] (Name, ConfigurationId) "
+ "VALUES (@name, @fk_id) SELECT scope_identity()";
var command = context.Database.Connection.CreateCommand();
command.Transaction = transaction.UnderlyingTransaction;
command.CommandText = InsertIntoFund;
command.Parameters.Add(new SqlParameter("name", fund.Name));
command.Parameters.Add(new SqlParameter("fk_id", fund.ConfigurationId));
var FundId = command.ExecuteScalar();
return FundId;
}
private static object InsertYear(
DbContext context,
DbContextTransaction transaction,
Year year,
object FundId)
{
const string InsertIntoYear =
"INSERT INTO [model].[Year] (Year, FundId) "
+ "VALUES (@year, @fk_id) "
+ "SELECT scope_identity()";
var command = context.Database.Connection.CreateCommand();
command.Transaction = transaction.UnderlyingTransaction;
command.CommandText = InsertIntoYear;
command.Parameters.Add(new SqlParameter("year", year.Year));
command.Parameters.Add(new SqlParameter("fk_id", FundId));
var YearIdentity = command.ExecuteScalar();
return YearIdentity;
}
private static DataTable FundPercentsDataTable()
{
// for the final table, which has a perhaps 750,000 inserts,
// set up a data table for a sql bulk copy per sheet
// (out of memory possible if tried as a single copy).
var percents = new DataTable("Percentages");
percents.Columns.Add("Percent", typeof(decimal));
percents.Columns.Add("FundName", typeof(string));
percents.Columns.Add("YearId", typeof(int));
return percents;
}
private static void WriteToServer(
SqlBulkCopy sqlBulkCopy, DataTable percents)
{
sqlBulkCopy.DestinationTableName = "[model].[Percent]";
sqlBulkCopy.EnableStreaming = true;
// sql bulk copy needs to be explicitly told the column mappings.
sqlBulkCopy.ColumnMappings.Add("Percent", "Percent");
sqlBulkCopy.ColumnMappings.Add("FundName", "FundName");
sqlBulkCopy.ColumnMappings.Add("YearId", "YearId");
sqlBulkCopy.WriteToServer(percents.CreateDataReader());
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.