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