public async Task FirstTransform(SqlConnection sourceConnection, SqlConnection targetConnection) { // Retrieve the data from the database var cmd = new SqlCommand("select * from Sales.SalesOrderHeader ", sourceConnection); DbDataReader sourceReader = cmd.ExecuteReader(); // Load the reader into transform source, which will start the transform chain. var transformSource = new ReaderDbDataReader(sourceReader); // Create a custom filter that removes records where PurchaseOrderNumber is null var transformFilter = new TransformFilter( transformSource, new List <TransformFunction>() { new TransformFunction( new Func <string, bool>((value) => value != null), //function code new[] { new TableColumn("PurchaseOrderNumber") }, //input column null, null) }, null ); // Add daily medium and sum columns var transformGroup = new TransformGroup( transformFilter, new List <ColumnPair>() //The fields to groupby { new ColumnPair(new TableColumn("OrderDate")) }, new List <TransformFunction>() { Functions.GetFunction("dexih.functions.BuiltIn.AggregateFunctions", "Median").GetTransformFunction(new[] { new TableColumn("TotalDue") }, new TableColumn("DailyMedian"), null), Functions.GetFunction("dexih.functions.BuiltIn.AggregateFunctions", "Sum").GetTransformFunction(new[] { new TableColumn("TotalDue") }, new TableColumn("DailyTotal"), null) }, null, true //Pass through colums = true will will pass through original fields/rows and merge in the aggregates ); using (var bulkCopy = new SqlBulkCopy(targetConnection)) { bulkCopy.DestinationTableName = "SalesOrderDaily"; await bulkCopy.WriteToServerAsync(transformGroup); } }
public async Task sourceDbReader_UnitTest() { var connection = new SqliteConnection("Data Source=:memory:;"); connection.Open(); var cmd = new SqliteCommand("CREATE TABLE [test_data]([StringColumn] VARCHAR(100) PRIMARY KEY NOT NULL,[IntColumn] INT,[DateColumn] DATETIME);", connection); cmd.ExecuteNonQuery(); for (var i = 0; i < 10; i++) { var sql = "INSERT INTO [test_data] values ('value" + i.ToString().PadLeft(2, '0') + "', " + i.ToString() + ", '2001-01-" + (i + 1).ToString().PadLeft(2, '0') + "');"; cmd = new SqliteCommand(sql, connection); cmd.ExecuteNonQuery(); } cmd = new SqliteCommand("select * from [test_data]", connection); var reader = cmd.ExecuteReader(); //run tests with no cache. var dbReader = new ReaderDbDataReader(reader, null); dbReader.SetCacheMethod(Transform.ECacheMethod.NoCache); //check the fields load correctly Assert.Equal("StringColumn", dbReader.GetName(0)); Assert.Equal("IntColumn", dbReader.GetName(1)); Assert.Equal("DateColumn", dbReader.GetName(2)); var count = 0; while (await dbReader.ReadAsync()) { Assert.Equal("value" + count.ToString().PadLeft(2, '0'), dbReader["StringColumn"]); Assert.Equal(count, Convert.ToInt32(dbReader["IntColumn"])); Assert.Equal(Convert.ToDateTime("2001-01-" + (count + 1).ToString().PadLeft(2, '0')), Convert.ToDateTime(dbReader["DateColumn"])); count++; } cmd = new SqliteCommand("select * from [test_data]", connection); reader = cmd.ExecuteReader(); //run tests with pre-load cache. dbReader = new ReaderDbDataReader(reader, null); dbReader.SetCacheMethod(Transform.ECacheMethod.PreLoadCache); //check the fields load correctly Assert.Equal("StringColumn", dbReader.GetName(0)); Assert.Equal("IntColumn", dbReader.GetName(1)); Assert.Equal("DateColumn", dbReader.GetName(2)); count = 0; while (await dbReader.ReadAsync()) { Assert.Equal("value" + count.ToString().PadLeft(2, '0'), dbReader["StringColumn"]); Assert.Equal(count, Convert.ToInt32(dbReader["IntColumn"])); Assert.Equal(Convert.ToDateTime("2001-01-" + (count + 1).ToString().PadLeft(2, '0')), Convert.ToDateTime(dbReader["DateColumn"])); count++; } Assert.Equal(10, count); //reset the reader and re-test using the cache. dbReader.SetRowNumber(0); count = 0; while (await dbReader.ReadAsync()) { Assert.Equal("value" + count.ToString().PadLeft(2, '0'), dbReader["StringColumn"]); Assert.Equal(count, Convert.ToInt32(dbReader["IntColumn"])); Assert.Equal(Convert.ToDateTime("2001-01-" + (count + 1).ToString().PadLeft(2, '0')), Convert.ToDateTime(dbReader["DateColumn"])); count++; } Assert.Equal(10, count); //peek at a row var peekRow = new object[3]; dbReader.RowPeek(5, peekRow); Assert.Equal("value05", peekRow[0]); Assert.Equal(Convert.ToInt32(5), Convert.ToInt32(peekRow[1])); Assert.Equal(Convert.ToDateTime("2001-01-06"), Convert.ToDateTime(peekRow[2])); }