private static ChoETLSqliteSettings ValidateSettings <T>(ChoETLSqliteSettings sqliteSettings)
{
if (sqliteSettings == null)
{
sqliteSettings = ChoETLSqliteSettings.Instance;
}
else
{
sqliteSettings.Validate();
}
if (sqliteSettings.TableName.IsNullOrWhiteSpace())
{
if (typeof(T).IsDynamicType())
{
sqliteSettings.TableName = sqliteSettings.TableName.IsNullOrWhiteSpace() ? "TmpTable" : sqliteSettings.TableName;
}
else
{
sqliteSettings.TableName = sqliteSettings.TableName.IsNullOrWhiteSpace() ? typeof(T).Name : sqliteSettings.TableName;
}
}
return(sqliteSettings);
}
public static IQueryable <T> StageOnSQLite <T>(this IEnumerable <T> items, ChoETLSqliteSettings sqliteSettings = 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);
}
sqliteSettings = ValidateSettings <T>(sqliteSettings);
LoadDataToDb(items, sqliteSettings, PIDict);
if (!typeof(T).IsDynamicType() && typeof(T) != typeof(object))
{
var ctx = new ChoETLSQLiteDbContext <T>(sqliteSettings.GetConnectionString());
ctx.Log = sqliteSettings.Log;
var dbSet = ctx.Set <T>();
return(dbSet);
}
else
{
return(Enumerable.Empty <T>().AsQueryable());
}
}
private static void LoadDataToDb <T>(IEnumerable <T> items, ChoETLSqliteSettings sqliteSettings, Dictionary <string, PropertyInfo> PIDict = null) where T : class
{
sqliteSettings.TableName = typeof(T).Name;
if (File.Exists(sqliteSettings.DatabaseFilePath))
{
File.Delete(sqliteSettings.DatabaseFilePath);
}
SQLiteConnection.CreateFile(sqliteSettings.DatabaseFilePath);
bool isFirstItem = true;
//Open sqlite connection, store the data
using (var conn = new SQLiteConnection(@"DataSource={0}".FormatString(sqliteSettings.DatabaseFilePath)))
{
SQLiteCommand insertCmd = null;
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var item in items)
{
if (isFirstItem)
{
isFirstItem = false;
if (item != null)
{
try
{
SQLiteCommand command = new SQLiteCommand(item.CreateTableScript(sqliteSettings.ColumnDataMapper, sqliteSettings.TableName), conn);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SQLiteCommand command = new SQLiteCommand("DELETE FROM [{0}]".FormatString(sqliteSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
insertCmd = CreateInsertCommand(item, sqliteSettings.TableName, conn, PIDict);
}
}
PopulateParams(insertCmd, item, PIDict);
insertCmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}
public static IQueryable <T> StageOnSQLite <T>(this IEnumerable <T> items, ChoETLSqliteSettings sqliteSettings = 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);
sqliteSettings = ValidateSettings <T>(sqliteSettings);
LoadDataToDb(items, sqliteSettings, PIDict);
var ctx = new ChoETLSQLiteDbContext <T>(sqliteSettings.DatabaseFilePath);
var dbSet = ctx.Set <T>();
return(dbSet);
}
private static ChoETLSqliteSettings ValidateSettings <T>(ChoETLSqliteSettings sqliteSettings) where T : class
{
if (sqliteSettings == null)
{
sqliteSettings = ChoETLSqliteSettings.Instance;
}
else
{
sqliteSettings.Validate();
}
if (typeof(T) == typeof(ExpandoObject))
{
sqliteSettings.TableName = sqliteSettings.TableName.IsNullOrWhiteSpace() ? "Table" : sqliteSettings.TableName;
}
else
{
sqliteSettings.TableName = typeof(T).Name;
}
return(sqliteSettings);
}
public static IEnumerable <ExpandoObject> StageOnSQLite(this IEnumerable <ExpandoObject> items, string conditions = null, ChoETLSqliteSettings sqliteSettings = null)
{
sqliteSettings = ValidateSettings <ExpandoObject>(sqliteSettings);
LoadDataToDb(items, sqliteSettings, null);
string sql = "SELECT * FROM {0}".FormatString(sqliteSettings.TableName);
if (!conditions.IsNullOrWhiteSpace())
{
sql += " {0}".FormatString(conditions);
}
SQLiteConnection conn = new SQLiteConnection(@"DataSource={0}".FormatString(sqliteSettings.DatabaseFilePath));
conn.Open();
SQLiteCommand command2 = new SQLiteCommand(sql, conn);
return(command2.ExecuteReader(CommandBehavior.CloseConnection).ToEnumerable <ExpandoObject>());
}
private static void LoadDataToDb <T>(IEnumerable <T> items, ChoETLSqliteSettings sqliteSettings, Dictionary <string, PropertyInfo> PIDict = null)
{
ChoGuard.ArgumentNotNull(items, nameof(items));
if (File.Exists(sqliteSettings.GetDatabaseFilePath()))
{
File.Delete(sqliteSettings.GetDatabaseFilePath());
}
SQLiteConnection.CreateFile(sqliteSettings.GetDatabaseFilePath());
bool isFirstItem = true;
bool isFirstBatch = true;
long notifyAfter = sqliteSettings.NotifyAfter;
long batchSize = sqliteSettings.BatchSize;
sqliteSettings.WriteLog($"Opening connection to `{sqliteSettings.GetConnectionString()}`...");
sqliteSettings.WriteLog($"Starting import...");
//Open sqlite connection, store the data
var conn = new SQLiteConnection(sqliteSettings.GetConnectionString());
SQLiteCommand insertCmd = null;
try
{
conn.Open();
SQLiteTransaction trans = sqliteSettings.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
{
SQLiteCommand command = new SQLiteCommand(item.CreateTableScript(sqliteSettings.DBColumnDataTypeMapper, sqliteSettings.TableName), conn);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SQLiteCommand command = new SQLiteCommand("DELETE FROM [{0}]".FormatString(sqliteSettings.TableName), conn, trans);
command.ExecuteNonQuery();
}
catch { }
}
if (insertCmd != null)
{
insertCmd.Dispose();
}
insertCmd = CreateInsertCommand(item, sqliteSettings.TableName, conn, trans, PIDict);
insertCmd.Prepare();
}
}
PopulateParams(insertCmd, item, PIDict);
insertCmd.ExecuteNonQuery();
if (notifyAfter > 0 && index % notifyAfter == 0)
{
if (sqliteSettings.RaisedRowsUploaded(index))
{
sqliteSettings.WriteLog(sqliteSettings.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 SQLiteConnection(sqliteSettings.GetConnectionString());
conn.Open();
trans = sqliteSettings.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;
}
sqliteSettings.WriteLog($"Import completed successfully.");
}
catch (Exception ex)
{
sqliteSettings.WriteLog(sqliteSettings.TraceSwitch.TraceError, $"Import failed. {ex.Message}.");
throw;
}
finally
{
if (insertCmd != null)
{
insertCmd.Dispose();
}
if (conn != null)
{
conn.Close();
}
System.Data.SQLite.SQLiteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public static IEnumerable <T> StageOnSQLite <T>(this IEnumerable <T> items, string conditions, ChoETLSqliteSettings sqliteSettings = null)
{
sqliteSettings = ValidateSettings <dynamic>(sqliteSettings);
LoadDataToDb(items, sqliteSettings, null);
string sql = "SELECT * FROM {0}".FormatString(sqliteSettings.TableName);
if (!conditions.IsNullOrWhiteSpace())
{
sql += " {0}".FormatString(conditions);
}
SQLiteConnection conn = new SQLiteConnection(sqliteSettings.GetConnectionString());
conn.Open();
SQLiteCommand command2 = new SQLiteCommand(sql, conn);
return(command2.ExecuteReader(CommandBehavior.CloseConnection).ToEnumerable <T>());
}
