public ActionResult Import()
{
string importJson = Request["hidImport"];
JavaScriptSerializer serializer = new JavaScriptSerializer();
dynamic jsonObject = serializer.Deserialize <dynamic>(importJson);
//ImportInfo jsonObject;
// Do the import
string sourceConnectionString = "@File=" + jsonObject["ImportFileLocation"] + ";@Type=Delimited;RowSeperator=" +
jsonObject["RowDelimiter"] + ";ColumnSeperator=" + jsonObject["ColumnDelimiter"] + ";FirstRowHasNames=" +
jsonObject["FirstRowHasColumnNames"].ToString();
string destConnectionString = ConfigurationManager.AppSettings["SqlConnection"];
ILinkProvider sourceProvider = null;
ILinkProvider destProvider = null;
destProvider = new SqlLinkProvider();
sourceProvider = new TextProvider();
IDataLink linkSource = sourceProvider.CreateLink(sourceConnectionString);
IDataLink linkDestination = destProvider.CreateLink(destConnectionString);
SqlDataLink sqlLink = new SqlDataLink();
sqlLink.Initialize(ConfigurationManager.AppSettings["SqlConnection"]);
sqlLink.Connect();
DataFlowTask copy = new DataFlowTask()
{
Name = "DataCopyTask"
};
ILinkReader reader = sourceProvider.CreateReader(linkSource, ""); // Since we are using same query
string destTable = jsonObject["DestinationTableName"].ToString();
ILinkWriter writer = destProvider.CreateWriter(linkDestination, destTable); // Dest table
copy.Input = reader;
copy.Output = writer;
copy.TableName = destTable;
copy.IsAutoMap = false;
dynamic maps = jsonObject["Maps"];
foreach (dynamic map in maps)
{
ColumnMap cmap = new ColumnMap();
cmap.Destination = map["FieldName"];
Expression exp = new Expression()
{
Code = map["TargetExpression"]
};
cmap.TransformExpression = exp;
copy.Mapping.Add(cmap);
}
StringBuilder sb = new StringBuilder();
ReflowEngine engine = new ReflowEngine();
engine.Tasks.Add(copy);
ExecutionEventListener eventListener = new ExecutionEventListener();
eventListener.OnTaskExecutionEvent += delegate(string taskname, string eventName, string description)
{
sb.Append(string.Format("{0,15} |{1,10} | {2} <br>", taskname, eventName, description));
};
eventListener.LoggingLevel = ExecutionEventListener.LogLevel.Verbose;
engine.Execute(eventListener);
linkSource.Disconnect();
linkDestination.Disconnect();
//System.IO.File.Delete(jsonObject["ImportFileLocation"]);
Session.Remove("import_file_path");
this.ViewBag.Log = sb.ToString();
return(View());
}
static void Main(string[] args)
{
// Read a table from sql server via a query, then make an exact copy
// of that table.
// Step 1. Read source table schema
// Step 2. Create exact same schema on destination
// Step 3. Copy Data
// Prepare reflow engine
ReflowEngine engine = new ReflowEngine();
// Test DB Path
string test = "text"; // "access";
string sourceConnectionString = string.Empty;
ILinkProvider sourceProvider = null;
if (test == "access")
{
string accessDBPath = AppDomain.CurrentDomain.BaseDirectory + "db1.accdb";
sourceConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;",
accessDBPath);
sourceProvider = new Provider.Access.AccessLinkProvider();
}
else if (test == "text")
{
// TODO: Test and implement text driver
string txtDBPath = AppDomain.CurrentDomain.BaseDirectory + "text\\HumanResources_Employee.txt";
sourceConnectionString = "@File=" + txtDBPath + ";@Type=Delimited;RowSeperator=\r\n;ColumnSeperator=,;FirstRowHasNames=True";
// TODO: Fixed length - Need to implement
//sourceConnectionString = "@File="+txtDBPath + ";@Type=Fixed;";
sourceProvider = new Provider.Text.TextProvider();
}
IDataLink linkSource = sourceProvider.CreateLink(sourceConnectionString);
ILinkProvider sqlProvider = new Provider.SqlServer.SqlLinkProvider();
string destConnectionString = "Server=localhost;Database=REflow;Trusted_Connection=True;";
IDataLink linkDestination = sqlProvider.CreateLink(destConnectionString);
// Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;
string selectQuery = "Select * from HumanResources_Employee";
// First lets discover the schema of the query [source table]
DiscoverSchemaTask task1 = new DiscoverSchemaTask();
task1.Name = "DiscoverSchema";
task1.Query = selectQuery;
task1.Link = linkSource;
// Then we need to create a table in the destinaton database.
TableCreateTask task2 = new TableCreateTask();
task2.Name = "CreateTableAbc";
task2.TableName = "abc";
task2.Link = linkDestination;
task2.ShouldDropExisting = true;
// Now we need to map task1 output to be the input of task2 since
// task1 will discover column names and we need to map discovered
// columns to the Columns property of task 2. We can do this one of
// two way.
// First, we can use a delegate to call onbeforeexecute for task2 and
// assign task result of task 1 to the column property of task 2. But that
// would be problematic for the scenario where we want to serialize the task
// and deserialize.
// Second, We can we task link object map output of task1 to go into certain
// properties of task 2. This can be serialized. We would use this technique
TaskLink link = new TaskLink();
link.LastTask = task1;
link.NextTask = task2;
// Map the output called DiscoveredColumns ( found in TaskResult.Output["DiscoveredColumns"]) to
// task2.Columns once task1 has been executed
link.TaskPipe["Columns"] = "Columns";
link.Bind();
// Now we will execute the data copy task. Will need to do the column mapping
// Interestingly, since our output columns are same as input columns at source,
// we can use the automap property to map the columns automatically.
DataFlowTask task3 = new DataFlowTask()
{
Name = "DataCopyTask"
};
ILinkReader reader = sourceProvider.CreateReader(linkSource, selectQuery); // Since we are using same query
ILinkWriter writer = sqlProvider.CreateWriter(linkDestination, task2.TableName); // Dest table
task3.Input = reader;
task3.Output = writer;
/* We would notmally create column mapping here and and map source and
* destination and put custom expressions if needed. But since we are doing a
* direct table copy, we can just use Automap property. */
// ColumnMappings maps = new ColumnMappings();
task3.IsAutoMap = true;
task3.TableName = task2.TableName;
// TODO: Add column mapping support
// Add scripting transformation
ColumnMap map = new ColumnMap();
map.Destination = "Title";
Expression exp = new Expression()
{
Code = "NationalIDNumber & \" \" & UCASE(Title) & CSTR(LEN(Title))"
};
map.TransformExpression = exp;
task3.Mapping.Add(map);
engine.Tasks.Add(task1);
engine.Tasks.Add(task2);
engine.Tasks.Add(task3);
ExecutionEventListener eventListener = new ExecutionEventListener();
eventListener.OnTaskExecutionEvent += delegate(string taskname, string eventName, string description)
{
Console.WriteLine(string.Format("{0,15} |{1,10} | {2}", taskname, eventName, description));
};
eventListener.LoggingLevel = ExecutionEventListener.LogLevel.Verbose;
engine.Execute(eventListener);
}
public ActionResult Import()
{
string importJson = Request["hidImport"];
JavaScriptSerializer serializer = new JavaScriptSerializer();
dynamic jsonObject = serializer.Deserialize<dynamic>(importJson);
//ImportInfo jsonObject;
// Do the import
string sourceConnectionString = "@File=" + jsonObject["ImportFileLocation"] + ";@Type=Delimited;RowSeperator=" +
jsonObject["RowDelimiter"] +";ColumnSeperator="+ jsonObject["ColumnDelimiter"] +";FirstRowHasNames=" +
jsonObject["FirstRowHasColumnNames"].ToString();
string destConnectionString = ConfigurationManager.AppSettings["SqlConnection"];
ILinkProvider sourceProvider = null;
ILinkProvider destProvider = null;
destProvider = new SqlLinkProvider();
sourceProvider = new TextProvider();
IDataLink linkSource = sourceProvider.CreateLink(sourceConnectionString);
IDataLink linkDestination = destProvider.CreateLink(destConnectionString);
SqlDataLink sqlLink = new SqlDataLink();
sqlLink.Initialize(ConfigurationManager.AppSettings["SqlConnection"]);
sqlLink.Connect();
DataFlowTask copy = new DataFlowTask() { Name = "DataCopyTask" };
ILinkReader reader = sourceProvider.CreateReader(linkSource, ""); // Since we are using same query
string destTable = jsonObject["DestinationTableName"].ToString();
ILinkWriter writer = destProvider.CreateWriter(linkDestination, destTable); // Dest table
copy.Input = reader;
copy.Output = writer;
copy.TableName = destTable;
copy.IsAutoMap = false;
dynamic maps = jsonObject["Maps"];
foreach (dynamic map in maps)
{
ColumnMap cmap = new ColumnMap();
cmap.Destination = map["FieldName"];
Expression exp = new Expression() { Code = map["TargetExpression"] };
cmap.TransformExpression = exp;
copy.Mapping.Add(cmap);
}
StringBuilder sb = new StringBuilder();
ReflowEngine engine = new ReflowEngine();
engine.Tasks.Add(copy);
ExecutionEventListener eventListener = new ExecutionEventListener();
eventListener.OnTaskExecutionEvent += delegate(string taskname, string eventName, string description)
{
sb.Append(string.Format("{0,15} |{1,10} | {2} <br>", taskname, eventName, description));
};
eventListener.LoggingLevel = ExecutionEventListener.LogLevel.Verbose;
engine.Execute(eventListener);
linkSource.Disconnect();
linkDestination.Disconnect();
//System.IO.File.Delete(jsonObject["ImportFileLocation"]);
Session.Remove("import_file_path");
this.ViewBag.Log= sb.ToString();
return View();
}
static void Main(string[] args)
{
// Read a table from sql server via a query, then make an exact copy
// of that table.
// Step 1. Read source table schema
// Step 2. Create exact same schema on destination
// Step 3. Copy Data
// Prepare reflow engine
ReflowEngine engine = new ReflowEngine();
// Test DB Path
string test = "text"; // "access";
string sourceConnectionString = string.Empty;
ILinkProvider sourceProvider = null;
if (test == "access")
{
string accessDBPath = AppDomain.CurrentDomain.BaseDirectory + "db1.accdb";
sourceConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;",
accessDBPath);
sourceProvider = new Provider.Access.AccessLinkProvider();
}
else if (test == "text")
{
// TODO: Test and implement text driver
string txtDBPath = AppDomain.CurrentDomain.BaseDirectory + "text\\HumanResources_Employee.txt";
sourceConnectionString = "@File="+txtDBPath + ";@Type=Delimited;RowSeperator=\r\n;ColumnSeperator=,;FirstRowHasNames=True";
// TODO: Fixed length - Need to implement
//sourceConnectionString = "@File="+txtDBPath + ";@Type=Fixed;";
sourceProvider = new Provider.Text.TextProvider();
}
IDataLink linkSource = sourceProvider.CreateLink ( sourceConnectionString);
ILinkProvider sqlProvider = new Provider.SqlServer.SqlLinkProvider();
string destConnectionString = "Server=localhost;Database=REflow;Trusted_Connection=True;";
IDataLink linkDestination = sqlProvider.CreateLink(destConnectionString);
// Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;
string selectQuery = "Select * from HumanResources_Employee";
// First lets discover the schema of the query [source table]
DiscoverSchemaTask task1 = new DiscoverSchemaTask();
task1.Name = "DiscoverSchema";
task1.Query = selectQuery;
task1.Link = linkSource;
// Then we need to create a table in the destinaton database.
TableCreateTask task2 = new TableCreateTask();
task2.Name = "CreateTableAbc";
task2.TableName = "abc";
task2.Link = linkDestination;
task2.ShouldDropExisting = true;
// Now we need to map task1 output to be the input of task2 since
// task1 will discover column names and we need to map discovered
// columns to the Columns property of task 2. We can do this one of
// two way.
// First, we can use a delegate to call onbeforeexecute for task2 and
// assign task result of task 1 to the column property of task 2. But that
// would be problematic for the scenario where we want to serialize the task
// and deserialize.
// Second, We can we task link object map output of task1 to go into certain
// properties of task 2. This can be serialized. We would use this technique
TaskLink link = new TaskLink();
link.LastTask = task1;
link.NextTask = task2;
// Map the output called DiscoveredColumns ( found in TaskResult.Output["DiscoveredColumns"]) to
// task2.Columns once task1 has been executed
link.TaskPipe["Columns"] = "Columns";
link.Bind();
// Now we will execute the data copy task. Will need to do the column mapping
// Interestingly, since our output columns are same as input columns at source,
// we can use the automap property to map the columns automatically.
DataFlowTask task3 = new DataFlowTask() { Name = "DataCopyTask" };
ILinkReader reader = sourceProvider.CreateReader(linkSource, selectQuery); // Since we are using same query
ILinkWriter writer = sqlProvider.CreateWriter(linkDestination, task2.TableName); // Dest table
task3.Input = reader;
task3.Output = writer;
/* We would notmally create column mapping here and and map source and
* destination and put custom expressions if needed. But since we are doing a
* direct table copy, we can just use Automap property. */
// ColumnMappings maps = new ColumnMappings();
task3.IsAutoMap = true;
task3.TableName = task2.TableName;
// TODO: Add column mapping support
// Add scripting transformation
ColumnMap map = new ColumnMap();
map.Destination = "Title";
Expression exp = new Expression() { Code = "NationalIDNumber & \" \" & UCASE(Title) & CSTR(LEN(Title))"};
map.TransformExpression = exp;
task3.Mapping.Add(map);
engine.Tasks.Add(task1);
engine.Tasks.Add(task2);
engine.Tasks.Add(task3);
ExecutionEventListener eventListener = new ExecutionEventListener();
eventListener.OnTaskExecutionEvent+= delegate (string taskname, string eventName, string description)
{
Console.WriteLine(string.Format("{0,15} |{1,10} | {2}", taskname, eventName, description));
};
eventListener.LoggingLevel = ExecutionEventListener.LogLevel.Verbose;
engine.Execute(eventListener);
}
