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