/// <summary>
/// Builds a connection string based on the connection settings.
/// </summary>
private string BuildConnectionsString()
{
DataSourceType dataSourceType = (DataSourceType)cbDataSourceType.SelectedIndex;
DbConnSettings connSettings = new DbConnSettings()
{
Server = txtServer.Text,
Database = txtDatabase.Text,
User = txtUser.Text,
Password = txtPassword.Text
};
switch (dataSourceType)
{
case DataSourceType.MSSQL:
return(SqlDataSource.BuildSqlConnectionString(connSettings));
case DataSourceType.Oracle:
return(OraDataSource.BuildOraConnectionString(connSettings));
case DataSourceType.PostgreSQL:
return(PgSqlDataSource.BuildPgSqlConnectionString(connSettings));
case DataSourceType.MySQL:
return(MySqlDataSource.BuildMySqlConnectionString(connSettings));
case DataSourceType.OLEDB:
return(OleDbDataSource.BuildOleDbConnectionString(connSettings));
default:
return("");
}
}
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_AccessSchema()
{
AccessDbConnectionStringBuilder builder = new AccessDbConnectionStringBuilder(@".\Northwinds.accdb");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var model = dql.GetSchemaModel("Customers");
Assert.AreEqual(model.TableName, "Customers");
}
public void Test_Accesschemas()
{
AccessDbConnectionStringBuilder builder = new AccessDbConnectionStringBuilder(@".\Northwinds.accdb");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var model = dql.GetSchemaModel();
Assert.AreEqual(1, model.Where(w => w.TableName == "Customers").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$");
}
public void Test_KeyTypes()
{
AccessDbConnectionStringBuilder builder = new AccessDbConnectionStringBuilder(@".\Northwinds.accdb");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
var strings = new string[] { "1", "2" };
var ints = Array.ConvertAll(strings, int.Parse);
QueryBuilder query = dql.GetQueryBuilder();
var select = query.BuildSql("ID", new string[] { "City", "Company" }, "Customers", "left", "0", "ID", ints, null);
var table = dql.ExecuteQuery(select);
}
public void Test_AccessQueryBuilder()
{
AccessDbConnectionStringBuilder builder = new AccessDbConnectionStringBuilder(@".\Northwinds.accdb");
OleDbDataSource dql = new OleDbDataSource(builder.ConnectionString);
QueryBuilder query = dql.GetQueryBuilder();
var select = query.BuildSql("ID", new string[] { "City", "Company" }, "Customers", "left", "0", "ID", new int[] { 1, 2 }, null);
var table = dql.ExecuteQuery(select);
Assert.AreEqual(table.Tables.Count, 1);
Assert.AreEqual(table.Tables[0].Rows.Count, 2);
}
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_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);
}
