Ejemplo n.º 1
0
        public static IQueryable <T> StageOnSqlServerUsingBcp <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
            where T : class
        {
            sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
            CreateDatabaseIfLocalDb(sqlServerSettings);
            var firstItem = items.FirstOrDefault();

            if (firstItem != null)
            {
                sqlServerSettings.WriteLog($"Opening db: {sqlServerSettings.ConnectionString}");
                using (var conn1 = new SqlConnection(sqlServerSettings.ConnectionString))
                {
                    conn1.Open();
                    try
                    {
                        SqlCommand command = new SqlCommand(firstItem.CreateTableScript(sqlServerSettings.DBColumnDataTypeMapper, 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());
            }
        }
Ejemplo n.º 2
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();
                }
            }
        }