public void WriteRecordNoHeaderTest()
        {
            var cont = new PipelineContext()
            {
                FirstLineContainsHeaders = false,
                SourceFilePath           = @"..\..\..\D2S.LibraryTests\TestDataFiveColumns.txt",
                Delimiter = "||",
                TotalObjectsInSequentialPipe = 10,
                DestinationTableName         = "dbo.SimpleSqlTableLoaderTest"
            };
            DestinationTableCreator table = new DestinationTableCreator(cont);

            table.CreateTable();
            SimpleSqlTableLoader loader = new SimpleSqlTableLoader(cont);

            for (int i = 0; i < 5; i++)
            {
                var row = loader.GetEmptyRow();
                for (int j = 0; j < cont.ColumnNamesSelection.Count(); j++)
                {
                    row[j] = $"col{j}";
                }
                loader.PostRecord(row);
            }
            try
            {
                loader.WriteRecords();
            }
            finally
            {
                int rowCount = DropTableAndReturnRows(cont.DestinationTableName);
                Assert.IsTrue(rowCount == 5);
            }
        }
Exemple #2
0
        public void InserterTest()
        {
            PipeLineContext c = new PipeLineContext();

            c.PathToSourceFile      = @"C:\Users\C51188\Documents\CSAHC_Tradedetails_20180228\blabla.txt";
            c.ConnectionString      = @"Data Source=NLGSPIDCS34019\S0QRMSN;Initial Catalog=QRM_TDM_DMT_LIQ_DEV_DWH;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
            c.FieldDelimiter        = '|';
            c.DestinationTableName  = "MurexTradeDetails";
            c.IsSuggestingDataTypes = false;
            c.LinesToScan           = 2000;
            ConcurrentQueue <List <string> > RowsCollection = new ConcurrentQueue <List <string> >();
            SourceFileReader        Reader     = new SourceFileReader(c, RowsCollection);
            Inserter                Writer     = new Inserter(c, RowsCollection);
            DestinationTableCreator TableMaker = new DestinationTableCreator(c);

            Reader.ReadFinished += Writer.StopWriting;

            Writer.FinishedWriting += IsDone;

            TableMaker.CreateTable();
            Reader.StartReading();
            Writer.CreateConcurrentWriter();
            Writer.CreateConcurrentWriter();

            while (!Done)
            {
                Thread.Sleep(2000);
            }
        }
        public void WriteDbNullValuesTest()
        {
            var cont = new PipelineContext()
            {
                FirstLineContainsHeaders = false,
                SourceFilePath           = @"..\..\..\D2S.LibraryTests\TestDataFiveColumns.txt",
                Delimiter = "||",
                TotalObjectsInSequentialPipe = 10,
                DestinationTableName         = "dbo.WriteDbNullValuesTest",
                DbNullStringValue            = "nullerino"
            };
            DestinationTableCreator table = new DestinationTableCreator(cont);

            table.CreateTable();
            SimpleSqlTableLoader loader = new SimpleSqlTableLoader(cont);
            var row = loader.GetEmptyRow();

            for (int j = 0; j < cont.ColumnNamesSelection.Count(); j++)
            {
                row[j] = "nullerino";
            }
            loader.PostRecord(row);
            try
            {
                loader.WriteRecords();
            }
            finally
            {
                var val = ReadScalar(1, 1, "dbo.WriteDbNullValuesTest");
                Assert.AreEqual(expected: DBNull.Value, actual: val);
                DropTableAndReturnRows("dbo.WriteDbNullValuesTest");
            }
        }
Exemple #4
0
        public void CreateTableTest()
        {
            PipeLineContext c = new PipeLineContext();

            c.PathToSourceFile = @"C:\Users\C51188\Documents\Axiom ultimo Jan 2018.txt";
            c.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Liquidity;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
            c.FieldDelimiter   = '\t';
            DestinationTableCreator creator = new DestinationTableCreator(c);

            creator.CreateTable();
        }
        public void CreateTableWithContextTest()
        {
            /* result should look like this without spaces:
             *   string	167	6
             *  integer	56	4
             *  decimal	106	5
             *  char	175	1
             */


            DestinationTableCreator creater = new DestinationTableCreator(pipelineContext);

            try
            {
                creater.CreateTable();

                string[] nameTypeDataType = new string[4];
                using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
                {
                    con.Open();
                    string commtext = "select name, CONVERT(nvarchar,system_type_id),CONVERT(nvarchar, max_length) from sys.columns where object_id = (select object_id from sys.tables where name = 'CreationTest')";
                    using (SqlCommand comm = new SqlCommand(commtext, con))
                    {
                        SqlDataReader result = comm.ExecuteReader();
                        for (int i = 0; i < 4; i++)
                        {
                            result.Read();
                            nameTypeDataType[i] = result.GetString(0) + result.GetString(1) + result.GetString(2);
                        }
                    }
                }


                Assert.IsTrue(nameTypeDataType[0] == "string23112");
                Assert.IsTrue(nameTypeDataType[1] == "integer564");
                Assert.IsTrue(nameTypeDataType[2] == "decimal10617");
                Assert.IsTrue(nameTypeDataType[3] == "char1751");
            }

            finally
            {
                using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
                {
                    con.Open();
                    string commtext = "drop table dbo.CreationTest";
                    using (SqlCommand comm = new SqlCommand(commtext, con))
                    {
                        comm.ExecuteNonQuery();
                    }
                }
            }
        }
        public void TestInit()
        {
            DestinationTableCreator d = new DestinationTableCreator(pipelineContext);

            d.CreateTable();
            using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
            {
                using (SqlCommand com = new SqlCommand())
                {
                    com.Connection = con;
                    con.Open();
                    com.CommandText = "insert into dbo.TruncatorTest ([string], [integer], [decimal], [char]) values ('s', 1, 1, 's')";
                    com.ExecuteNonQuery();
                }
            }
        }
Exemple #7
0
        private static void CreateOrRecreateTargetTable(DataColumnCollection header, string connectionString, string schemaAndTable)
        {
            //drop (if exists) and recreate
            string[] cols      = new string[header.Count];
            string[] datatypes = new string[header.Count];
            for (int i = 0; i < header.Count; i++)
            {
                cols[i]      = header[i].ColumnName;
                datatypes[i] = DataTypeMapping.SystemToSql[header[i].DataType];
            }
            DestinationTableCreator dtc = new DestinationTableCreator(schemaAndTable, cols, datatypes);

            //using d2s class for this
            ConfigVariables.Instance.ConfiguredConnection = connectionString; //this is where the d2s class get their connstring from
            DestinationTableDropper dtd = new DestinationTableDropper(schemaAndTable);

            dtd.DropTable();
            dtc.CreateTable();
        }
Exemple #8
0
#pragma warning disable S3241 // Methods should not return values that are never used
        private async Task StartFileTransfer()
#pragma warning restore S3241 // Methods should not return values that are never used
        {
            //build staging table
            if (!Context.IsAppendingDataToExistingTable)
            {
                DestinationTableCreator TableMaker = new DestinationTableCreator(Context);
                TableMaker.CreateTable();
            }

            //create buffer
            ConcurrentQueue <List <string> > Queu = new ConcurrentQueue <List <string> >();
            //create threads to transfer file
            SourceFileReader Reader = new SourceFileReader(Context, Queu);
            Inserter         Writer = new Inserter(Context, Queu);

            Writer.done             = false;
            Reader.ReadFinished    += Writer.StopWriting;
            Writer.FinishedWriting += OnWriterFinishing;

            // start everything up and monitor for finish
            Reader.StartReading();
            for (int i = 0; i < NumberOfWriterThreads; i++)
            {
                Writer.CreateConcurrentWriter();
            }

            await Task.Run(() =>
            {
                while (NumberOfWriterThreads > NumberOfFinishedThreads)
                {
                    Task.Delay(1000).Wait();
                }
            });


            btnStartTransfer.Enabled = true;
            OnLoadStageOneFinished();
        }
        public void WriteRecordsTest()
        {
            DestinationTableCreator destinationTableCreator = new DestinationTableCreator(context);

            destinationTableCreator.CreateTable();
            SimpleSqlTableLoader loader = new SimpleSqlTableLoader(context);
            int numberOfRows            = 100;

            for (int i = 0; i < numberOfRows; i++)
            {
                var row = loader.GetEmptyRow();
                foreach (var column in context.ColumnNames)
                {
                    row[column] = $"Value{i}";
                }
                loader.PostRecord(row);
            }
            loader.WriteRecords();

            int rowCount = DropTableAndReturnRows(context.DestinationTableName);

            Assert.AreEqual(expected: numberOfRows, actual: rowCount);
        }
Exemple #10
0
 public static void ExportToSqlTable(this DataTable source, string connectionString, string schemaAndTable, bool AppendIfTableExists = false)
 {
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         conn.Open();
         //does table exist?
         bool tableExists = DoesTableExist(schemaAndTable, conn);
         //error if append is not allowed and table exists
         if (tableExists && !AppendIfTableExists)
         {
             Console.Error.WriteLine($"{schemaAndTable} already exists and appending is set to false");
             return;
         }
         if (!tableExists)
         {
             //create table if it doesn't exist
             string[] cols      = new string[source.Columns.Count];
             string[] datatypes = new string[source.Columns.Count];
             for (int i = 0; i < source.Columns.Count; i++)
             {
                 cols[i]      = source.Columns[i].ColumnName;
                 datatypes[i] = DataTypeMapping.SystemToSql[source.Columns[i].DataType];
             }
             DestinationTableCreator dtc = new DestinationTableCreator(schemaAndTable, cols, datatypes);
             //using d2s class for this
             ConfigVariables.Instance.ConfiguredConnection = connectionString; //this is where the d2s class get their connstring from
             dtc.CreateTable();
         }
         //fill table
         using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
         {
             bulk.BatchSize            = 10000;
             bulk.DestinationTableName = schemaAndTable;
             bulk.WriteToServer(source);
         }
     }
 }
Exemple #11
0
        /// <summary>
        /// creates a table based on the given context
        /// </summary>
        /// <param name="context"></param>
        protected void CreateTable(PipelineContext context)
        {
            var tableMaker = new DestinationTableCreator(context);

            tableMaker.CreateTable();
        }
        public void TestInit()
        {
            DestinationTableCreator d = new DestinationTableCreator(pipelineContext);

            d.CreateTable();
        }