public static IQueryable <T> StageOnSqlServerUsingBcp <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
where T : class
{
sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
CreateDatabaseIfLocalDb(sqlServerSettings);
var firstItem = items.FirstOrDefault();
if (firstItem != null)
{
sqlServerSettings.WriteLog($"Opening db: {sqlServerSettings.ConnectionString}");
using (var conn1 = new SqlConnection(sqlServerSettings.ConnectionString))
{
conn1.Open();
try
{
SqlCommand command = new SqlCommand(firstItem.CreateTableScript(sqlServerSettings.DBColumnDataTypeMapper, sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SqlCommand command = new SqlCommand("TRUNCATE TABLE [{0}]".FormatString(sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch
{
try
{
SqlCommand command = new SqlCommand("DELETE FROM [{0}]".FormatString(sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch { }
}
}
using (SqlBulkCopy bcp = new SqlBulkCopy(sqlServerSettings.ConnectionString))
{
bcp.DestinationTableName = sqlServerSettings.TableName;
bcp.EnableStreaming = true;
//bcp.NotifyAfter = 10;
//bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
//{
// Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
//};
bcp.WriteToServer(new ChoEnumerableDataReader(items));
}
var ctx = new ChoETLSqlServerDbContext <T>(sqlServerSettings.ConnectionString);
var dbSet = ctx.Set <T>();
return(dbSet);
}
else
{
return(items.AsQueryable());
}
}
private static void LoadDataToDb <T>(IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings,
Dictionary <string, PropertyInfo> PIDict) where T : class
{
CreateDatabaseIfLocalDb(sqlServerSettings);
bool isFirstItem = true;
bool isFirstBatch = true;
long notifyAfter = sqlServerSettings.NotifyAfter;
long batchSize = sqlServerSettings.BatchSize;
sqlServerSettings.WriteLog($"Opening connection to `{sqlServerSettings.ConnectionString}`...");
sqlServerSettings.WriteLog($"Starting import...");
//Open sqlite connection, store the data
var conn = new SqlConnection(sqlServerSettings.ConnectionString);
SqlCommand insertCmd = null;
try
{
conn.Open();
SqlTransaction trans = sqlServerSettings.TurnOnTransaction ? conn.BeginTransaction() : null;
try
{
int index = 0;
foreach (var item in items)
{
index++;
if (isFirstItem)
{
isFirstItem = false;
if (item != null)
{
if (isFirstBatch)
{
isFirstBatch = false;
try
{
SqlCommand command = CreateCommand(item.CreateTableScript(sqlServerSettings.DBColumnDataTypeMapper, sqlServerSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SqlCommand command = CreateCommand("DELETE FROM [{0}]".FormatString(sqlServerSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
}
if (insertCmd != null)
{
insertCmd.Dispose();
}
insertCmd = CreateInsertCommand(item, sqlServerSettings.TableName, conn, trans, PIDict);
//insertCmd.Prepare();
}
}
PopulateParams(insertCmd, item, PIDict);
insertCmd.ExecuteNonQuery();
if (notifyAfter > 0 && index % notifyAfter == 0)
{
if (sqlServerSettings.RaisedRowsUploaded(index))
{
sqlServerSettings.WriteLog(sqlServerSettings.TraceSwitch.TraceVerbose, "Abort requested.");
break;
}
}
if (batchSize > 0 && index % batchSize == 0)
{
if (trans != null && trans.Connection != null)
{
trans.Commit();
trans = null;
}
if (insertCmd != null)
{
insertCmd.Dispose();
}
isFirstItem = true;
conn.Close();
conn = null;
conn = new SqlConnection(sqlServerSettings.ConnectionString);
conn.Open();
trans = sqlServerSettings.TurnOnTransaction ? conn.BeginTransaction() : null;
}
}
if (trans != null && trans.Connection != null)
{
trans.Commit();
trans = null;
}
}
catch
{
if (trans != null && trans.Connection != null)
{
trans.Rollback();
trans = null;
}
throw;
}
sqlServerSettings.WriteLog($"Import completed successfully.");
}
catch (Exception ex)
{
sqlServerSettings.WriteLog(sqlServerSettings.TraceSwitch.TraceError, $"Import failed. {ex.Message}.");
throw;
}
finally
{
if (insertCmd != null)
{
insertCmd.Dispose();
}
if (conn != null)
{
conn.Close();
}
}
}