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]));
}
