Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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());
            }
        }
Esempio n. 3
0
        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();
                }
            }
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        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>());
        }
Esempio n. 7
0
        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();
            }
        }
Esempio n. 8
0
        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>());
        }