public static async Task BulkInserterDemo()
{
string connStr;
//initialize table
using (var conn = LocalDB.GetLocalDB("BulkInserterDemo"))
{
var cmd = new SqlCommand(@"
IF OBJECT_id('dbo.People', 'U') IS NOT NULL
DROP TABLE dbo.People;
CREATE TABLE dbo.People
(
Id INT IDENTITY(1,1) NOT NULL,
NameCol nvarchar(50) NOT NULL,
AgeCol INT NOT NULL
)
", conn);
cmd.ExecuteNonQuery();
connStr = conn.ConnectionString;
}
var f = new PeopleFlow(DataflowOptions.Default);
var dbInserter = new DbBulkInserter <Person>(connStr, "dbo.People", DataflowOptions.Default, "PersonTarget");
f.LinkTo(dbInserter);
f.Post("{Name: 'aaron', Age: 20}");
f.Post("{Name: 'bob', Age: 30}");
f.Post("{Age: 80}"); //Name will be default value: "N/A"
f.Post("{Name: 'neo' }"); // Age will be default value: -1
await f.SignalAndWaitForCompletionAsync();
await dbInserter.CompletionTask;
}
public static async Task BulkInserterDemo2()
{
string connStr;
//initialize table
using (var conn = LocalDB.GetLocalDB("BulkInserterDemo"))
{
var cmd = new SqlCommand(@"
IF OBJECT_id('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
Id INT IDENTITY(1,1) NOT NULL,
Date DATETIME NOT NULL,
Value FLOAT NOT NULL,
CustomerName NVARCHAR(50) NOT NULL,
CustomerAge INT NOT NULL
)
", conn);
cmd.ExecuteNonQuery();
connStr = conn.ConnectionString;
}
var dbInserter = new DbBulkInserter <Order>(connStr, "dbo.Orders", DataflowOptions.Default, "OrderTarget");
dbInserter.Post(new Order {
OrderDate = DateTime.Now, OrderValue = 15, Customer = new Person()
{
Name = "Aaron", Age = 38
}
});
dbInserter.Post(new Order {
OrderDate = DateTime.Now, OrderValue = 25, Customer = new Person()
{
Name = "Bob", Age = 30
}
});
dbInserter.Post(new Order {
OrderDate = DateTime.Now, OrderValue = 35, Customer = new Person()
{
Age = 48
}
});
dbInserter.Post(new Order {
OrderDate = DateTime.Now, OrderValue = 45, Customer = new Person()
{
Name = "Neo"
}
});
await dbInserter.SignalAndWaitForCompletionAsync();
}
public static async Task ETLLookupDemo()
{
string connStr;
//initialize table
using (var conn = LocalDB.GetLocalDB("ETLLookupDemo"))
{
//We will create a dimension table and a fact table
//We also populate the dimension table with some pre-defined rows
var cmd = new SqlCommand(@"
IF OBJECT_id('dbo.Product', 'U') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
ProductKey INT IDENTITY(1,1) NOT NULL,
Category nvarchar(50) NOT NULL,
ProductName nvarchar(50) NOT NULL,
ProductFullName nvarchar(100) NOT NULL
)
INSERT INTO dbo.Product VALUES ('Books', 'The Great Gatsby', 'Books-The Great Gatsby');
INSERT INTO dbo.Product VALUES ('Games', 'Call of Duty', 'Games-Call of Duty');
IF OBJECT_id('dbo.FactOrders', 'U') IS NOT NULL
DROP TABLE dbo.FactOrders;
CREATE TABLE dbo.FactOrders
(
Id INT IDENTITY(1,1) NOT NULL,
Date DATETIME NOT NULL,
Value FLOAT NOT NULL,
ProductKey INT NULL,
)
", conn);
cmd.ExecuteNonQuery();
connStr = conn.ConnectionString;
}
var lookupNode = new ProductLookupFlow(new TargetTable("LookupDemo", connStr, "dbo.Product"));
lookupNode.Post(new OrderEx {
OrderValue = 10, Product = new Product("Books", "The Call of the Wild")
});
lookupNode.Post(new OrderEx {
OrderValue = 20, Product = new Product("Games", "Call of Duty")
});
lookupNode.Post(new OrderEx {
OrderValue = 30, Product = new Product("Games", "Call of Duty")
});
lookupNode.Post(new OrderEx {
OrderValue = 20, Product = new Product("Books", "The Call of the Wild")
});
lookupNode.Post(new OrderEx {
OrderValue = 20, Product = new Product("Books", "The Great Gatsby")
});
var factInserter = new DbBulkInserter <OrderEx>(connStr, "dbo.FactOrders", DataflowOptions.Default, "OrderTarget");
lookupNode.LinkTo(factInserter);
await lookupNode.SignalAndWaitForCompletionAsync();
await factInserter.CompletionTask;
}