private static void LoadDataToDb <T>(IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings, Dictionary <string, PropertyInfo> PIDict) where T : class
{
bool isFirstItem = true;
CreateDatabaseIfLocalDb(sqlServerSettings);
//Open SqlServer connection, store the data
using (var conn = new SqlConnection(sqlServerSettings.ConnectionString))
{
SqlCommand insertCmd = null;
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var item in items)
{
if (isFirstItem)
{
isFirstItem = false;
if (item != null)
{
//Create table if not exists
try
{
SqlCommand command = new SqlCommand(item.CreateTableScript(sqlServerSettings.ColumnDataMapper, sqlServerSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SqlCommand command = new SqlCommand("TRUNCATE TABLE [{0}]".FormatString(sqlServerSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch
{
try
{
SqlCommand command = new SqlCommand("DELETE FROM [{0}]".FormatString(sqlServerSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
}
insertCmd = CreateInsertCommand(item, sqlServerSettings.TableName, conn, trans, PIDict);
}
}
PopulateParams(insertCmd, item, PIDict);
insertCmd.ExecuteNonQuery();
}
try
{
trans.Commit();
}
catch { }
}
}
}
private static void CreateDatabaseIfLocalDb(ChoETLSqlServerSettings sqlServerSettings)
{
if (!sqlServerSettings.IsLocalDb)
{
return;
}
string dbFilePath = sqlServerSettings.DbFilePath;
if (dbFilePath.IsNullOrWhiteSpace())
{
return;
}
if (File.Exists(dbFilePath))
{
if (String.Compare(Path.GetFileNameWithoutExtension(dbFilePath), "localdb", true) == 0)
{
foreach (string dbFileName in Directory.GetFiles(Path.GetDirectoryName(dbFilePath), "localdb*.*"))
{
try
{
File.Delete(dbFileName);
}
catch { }
}
}
}
CreateDatabase(Path.GetFileNameWithoutExtension(dbFilePath), dbFilePath, sqlServerSettings.MasterDbConnectionString);
}
private static ChoETLSqlServerSettings ValidateSettings <T>(ChoETLSqlServerSettings SqlServerSettings) where T : class
{
if (SqlServerSettings == null)
{
SqlServerSettings = ChoETLSqlServerSettings.Instance;
}
else
{
SqlServerSettings.Validate();
}
if (typeof(T) == typeof(ExpandoObject))
{
SqlServerSettings.TableName = SqlServerSettings.TableName.IsNullOrWhiteSpace() ? "Table" : SqlServerSettings.TableName;
}
else
{
SqlServerSettings.TableName = typeof(T).Name;
}
return(SqlServerSettings);
}
public static IQueryable <T> StageOnSqlServer <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
where T : class
{
if (typeof(T) == typeof(ExpandoObject) || typeof(T) == typeof(object))
{
throw new NotSupportedException();
}
Dictionary <string, PropertyInfo> PIDict = ChoType.GetProperties(typeof(T)).ToDictionary(p => p.Name);
sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
LoadDataToDb(items, sqlServerSettings, PIDict);
var ctx = new ChoETLSqlServerDbContext <T>(sqlServerSettings.ConnectionString);
var dbSet = ctx.Set <T>();
return(dbSet);
}
public static IQueryable <T> StageOnSqlServerUsingBcp <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
where T : class
{
if (typeof(T) == typeof(ExpandoObject) || typeof(T) == typeof(object))
{
throw new NotSupportedException();
}
Dictionary <string, PropertyInfo> PIDict = ChoType.GetProperties(typeof(T)).ToDictionary(p => p.Name);
sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
CreateDatabaseIfLocalDb(sqlServerSettings);
var firstItem = items.FirstOrDefault();
if (firstItem != null)
{
using (var conn1 = new SqlConnection(sqlServerSettings.ConnectionString))
{
conn1.Open();
try
{
SqlCommand command = new SqlCommand(firstItem.CreateTableScript(sqlServerSettings.ColumnDataMapper, 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());
}
}
//public static IEnumerable<ExpandoObject> StageOnSqlServerUsingBcp(this IEnumerable<ExpandoObject> items, string conditions = null, ChoETLSqlServerSettings sqlServerSettings = null)
//{
// sqlServerSettings = ValidateSettings<ExpandoObject>(sqlServerSettings);
// CreateDatabaseIfLocalDb(sqlServerSettings);
// var firstItem = items.FirstOrDefault();
// if (firstItem != null)
// {
// using (var conn1 = new SqlConnection(sqlServerSettings.ConnectionString))
// {
// conn1.Open();
// try
// {
// SqlCommand command = new SqlCommand(firstItem.CreateTableScript(sqlServerSettings.ColumnDataMapper, 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 { }
// }
// }
// }
// else
// throw new ApplicationException("Empty items found.");
// 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));
// }
// string sql = "SELECT * FROM {0}".FormatString(sqlServerSettings.TableName);
// if (!conditions.IsNullOrWhiteSpace())
// sql += " {0}".FormatString(conditions);
// SqlConnection conn = new SqlConnection(sqlServerSettings.ConnectionString);
// conn.Open();
// SqlCommand command2 = new SqlCommand(sql, conn);
// return command2.ExecuteReader(CommandBehavior.CloseConnection).ToEnumerable<ExpandoObject>();
//}
public static IEnumerable <ExpandoObject> StageOnSqlServer(this IEnumerable <ExpandoObject> items, string conditions = null, ChoETLSqlServerSettings sqlServerSettings = null)
{
sqlServerSettings = ValidateSettings <ExpandoObject>(sqlServerSettings);
LoadDataToDb(items, sqlServerSettings, null);
string sql = "SELECT * FROM {0}".FormatString(sqlServerSettings.TableName);
if (!conditions.IsNullOrWhiteSpace())
{
sql += " {0}".FormatString(conditions);
}
SqlConnection conn = new SqlConnection(sqlServerSettings.ConnectionString);
conn.Open();
SqlCommand command2 = new SqlCommand(sql, conn);
return(command2.ExecuteReader(CommandBehavior.CloseConnection).ToEnumerable <ExpandoObject>());
}
public static IQueryable <T> StageOnSqlServer <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
where T : class
{
Dictionary <string, PropertyInfo> PIDict = null;
if (!typeof(T).IsDynamicType() && typeof(T) != typeof(object))
{
PIDict = ChoType.GetProperties(typeof(T)).ToDictionary(p => p.Name);
}
sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
LoadDataToDb(items, sqlServerSettings, PIDict);
var ctx = new ChoETLSqlServerDbContext <T>(sqlServerSettings.ConnectionString);
var dbSet = ctx.Set <T>();
return(dbSet);
}
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();
}
}
}