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