public void Test_SetupComparison() { ExcelDbConnectionStringBuilder builder1 = new ExcelDbConnectionStringBuilder(@".\dm_document1.xlsx"); ExcelDataSource dql1 = new ExcelDataSource(builder1.ConnectionString); var source = dql1.GetSchemaModel("Sheet1$"); ExcelDbConnectionStringBuilder builder2 = new ExcelDbConnectionStringBuilder(@".\dm_document3.xlsx"); ExcelDataSource dql2 = new ExcelDataSource(builder2.ConnectionString); var target = dql2.GetSchemaModel("Sheet1$"); var model = new CompareModel(); //model.Source.DataSource = dql1; //model.Source.TableSchemas = source; //model.Source.SelectedTable = "Sheet1$"; //model.Target = dsl2; //model.Target.TableSchemas = target; //model.Target.SelectedTable = "Sheet1$"; var pairs = source.Fields. Join(target.Fields, s => new { s.Name }, t => new { t.Name }, (s, t) => new CompareMappingModel(s.Name, t.Name) ).ToList(); // model.ColumnCompare = pairs; //QueryBuilder query = new QueryBuilder(); //var select1 = query.BuildSql("r_object_id", pairs.Select(s=> s.LeftSide.Field).ToArray(), "Sheet1$", "left"); //var select2 = query.BuildSql("r_object_id", pairs.Select(s => s.RightSide.Field).ToArray(), "Sheet1$", "right"); //var t1 = dql1.ExecuteQuery(select1).Tables[0]; //var t2 = dql2.ExecuteQuery(select2).Tables[0]; //t1.SetPrimaryKey(Alias.Primary_Key, t2); //t1.Merge(t2); //TODO: order columns //TODO Now we should do the compare. //Next Dump to Excel? or do we do the compare within excel }
public void Test_Merge() { //var b = new ExcelDbConnectionStringBuilder(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\dm_document1.xlsx;Extended Properties='Excel 12.0;IMEX=1;ImportMixedTypes=Text;READONLY=TRUE"); ExcelDbConnectionStringBuilder builder1 = new ExcelDbConnectionStringBuilder(@".\dm_document1.xlsx"); ExcelDataSource dql1 = new ExcelDataSource(builder1.ConnectionString); Serializer.Serialize(@".\dql.xml", dql1); var qb1 = dql1.GetQueryBuilder(); var cb2 = dql1.GetConnectionStringBuilder(); qb1.BuildSql("r_object_id2", new string[] { "r_object_id2", "r_object_type" }, "Sheet1$", "right", "0", null); QueryBuilder query = new QueryBuilder(); var select1 = query.BuildSql("r_object_id", new string[] { "object_name", "r_object_type" }, "Sheet1$", "", "0", null); var table1 = dql1.ExecuteQuery(select1); dql1.SetAliases(table1.Tables[0], "left"); var schema1 = dql1.GetSchemaModel("Sheet1$"); ExcelDbConnectionStringBuilder builder2 = new ExcelDbConnectionStringBuilder(@".\dm_document3.xlsx"); OleDbDataSource dql2 = new OleDbDataSource(builder2.ConnectionString); var schema2 = dql2.GetSchemaModel("Sheet1$"); var select2 = query.BuildSql("r_object_id", new string[] { "r_object_id2", "r_object_type" }, "Sheet1$", "", "0", null); var table2 = dql2.ExecuteQuery(select2); dql2.SetAliases(table2.Tables[0], "right"); var source = table1.Tables[0]; var target = table2.Tables[0]; source.SetPrimaryKey(Alias.Primary_Key, target); source.Merge(target); }