/// <summary>
/// Creates the excel.
/// </summary>
private void CreateExcel()
{
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(btnDataSource.Text)
{
["User ID"] = txtUserId.Text,
["Password"] = txtPassword.Text,
["Extended Properties"] = txtExt.Text
};
Model.DataSource = new ExcelDataSource(builder.ConnectionString);
Model.RefreshSchemaModel();
//var macros = Model.TableSchemas.SelectMany(s => s.Fields).
// Where(w => string.IsNullOrEmpty(w.ValidationMacros) == false);
//var schema = Model.DataSource.GetSchemaModel();
//foreach (var macro in macros)
//{
// // var item = Model.TableSchemas.Where(w => w.TableName == macro.TableName).SingleOrDefault().
// // Fields.Where(w => w.Name == macro.Name).SingleOrDefault();
// var item = schema.Where(w => w.TableName == macro.TableName).SingleOrDefault().
// Fields.Where(w => w.Name == macro.Name).SingleOrDefault();
// if (item != null)
// item.ValidationMacros = macro.ValidationMacros;
//}
//Model.TableSchemas = schema;
Model.DataSource.ProviderName = "Microsoft Excel";
}
public void Test_ExcelSchemas()
{
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(@".\cd_clinical.xlsx");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var model = dql.GetSchemaModel();
Assert.AreEqual(1, model.Where(w => w.TableName == "Sheet1$").Count());
}
public void Test_ExcelSchema()
{
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(@".\cd_clinical.xlsx");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var model = dql.GetSchemaModel("Sheet1$");
Assert.AreEqual(model.TableName, "Sheet1$");
}
/// <summary>
/// Tests the excel.
/// </summary>
private void TestExcel()
{
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(btnDataSource.Text)
{
["Extended Properties"] = txtExt.Text
};
using (OleDbConnection cn = new OleDbConnection(builder.ConnectionString))
{
cn.Open();
}
}
public void Test_ExcelQueryBuilder()
{
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(@".\cd_clinical.xlsx");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
QueryBuilder query = dql.GetQueryBuilder();
var select = query.BuildSql("r_object_id", new string[] { "object_name", "r_object_type" }, "Sheet1$", "left", "0", "r_object_id", new string[] { "090200f1800de14c" }, null);
var table = dql.ExecuteQuery(select);
Assert.AreEqual(table.Tables.Count, 1);
Assert.AreEqual(table.Tables[0].Rows.Count, 1);
}
public void Test_ExcelConnection()
{
string file = @"C:\Users\mcarlucci\Documents\Visual Studio 2017\Projects\Fme.Database.Verification\Fme.Library.Tests\bin\Debug\dm_document1.xlsx";
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(file);
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var x = dql.GetConnectionStringBuilder();
var y = x["Data Source"];
OleDbConnection cn = new OleDbConnection(x.ConnectionString);
cn.Open();
}
public void ValidationTest_Datatable()
{
string path = @"C:\Users\mcarlucci\Desktop\Tim\";
string modelFile = "Validation Test Model.xml";
string xlsFile = "SmartSolve Extract.xlsx";
var temp = Serializer.DeSerialize <CompareModel>(path + modelFile);
ExcelDbConnectionStringBuilder builder = new ExcelDbConnectionStringBuilder(path + xlsFile);
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var ds = dql.ExecuteQuery(string.Format("select * from [{0}]", temp.Source.SelectedTable));
ValidatorRepository repo = new ValidatorRepository();
repo.Execute(ds.Table(), temp.Source.SelectedSchema().Fields);
}
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);
}
