public Database GetDatabase(string databaseName)
{
Database db = new Database(databaseName, null);
db.Tables = getTables(db);
setForeignKeys(db);
return db;
}
void DBBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
db = dbConnector.GetDatabase(dbName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public ImportViewModel(ImportConfiguration config, IDataReader reader, Database database)
{
this.config = config;
this.reader = reader;
this.database = database;
bw.WorkerSupportsCancellation = false;
bw.WorkerReportsProgress = true;
bw.DoWork += new DoWorkEventHandler(bw_DoWork);
bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_DoWorkCompleted);
}
private List<DBTable> getTables(Database database)
{
string tablCol = "TABLE";
string schemaCol = "SCEMA";
string command = "SELECT TABLE_NAME AS [{0}], TABLE_SCHEMA AS [{1}] FROM {2}.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ASC";
List<DBTable> tables = new List<DBTable>();
using (SqlConnection sqlConnection = createSqlConnection())
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = String.Format(command, tablCol, schemaCol, database.Name);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string schema = (string) reader[schemaCol];
string tableName = (string) reader[tablCol];
tables.Add(new DBTable(schema, tableName));
}
}
}
setColumns(tables, database);
return tables;
}
private void setForeignKeys(Database database)
{
string fkSchemaCol = "FK_SCHEMA";
string fkTableCol = "FK_TABLE";
string fkColumnCol = "FK_COLUMN";
string pkSchemaCol = "PK_SCHEMA";
string pkTableCol = "PK_TABLE";
string pkColumnCol = "PK_COLUMN";
string command = "SELECT FK.TABLE_SCHEMA as [{0}], FK.TABLE_NAME AS [{1}], FK.COLUMN_NAME AS [{2}], PK.TABLE_SCHEMA AS [{3}], PK.TABLE_NAME AS [{4}], PK.COLUMN_NAME AS [{5}] FROM {6}.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC INNER JOIN {6}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK ON FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN {6}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK ON PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME";
using (SqlConnection sqlConnection = createSqlConnection())
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = String.Format(command, fkSchemaCol, fkTableCol, fkColumnCol, pkSchemaCol, pkTableCol, pkColumnCol, database.Name);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string fkSchemaName = (string)reader[fkSchemaCol];
string fkTableName = (string)reader[fkTableCol];
string fkColumnName = (string)reader[fkColumnCol];
string pkSchemaName = (string)reader[pkSchemaCol];
string pkTableName = (string)reader[pkTableCol];
string pkColumnName = (string)reader[pkColumnCol];
DBColumn fkColumn = database.GetColumn(fkSchemaName, fkTableName, fkColumnName);
DBColumn pkColumn = database.GetColumn(pkSchemaName, pkTableName, pkColumnName);
if (fkColumn != null && pkColumn != null)
{
database.AddForeignKeys(new DBForeignKey(null, pkColumn, null, fkColumn));
}
}
}
}
}
private void setColumns(List<DBTable> tables, Database database)
{
string schemaCol = "SCHEMA";
string tableCol = "TABLE";
string columnCol = "COLUMN";
string pkCol = "PRIMARY_KEY";
string dataTypeCol = "DATA_TYPE";
string command = "SELECT C.TABLE_SCHEMA AS [{0}], C.TABLE_NAME AS [{1}], C.COLUMN_NAME AS [{2}], CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS [{3}], C.DATA_TYPE AS {4} FROM {5}.INFORMATION_SCHEMA.COLUMNS C LEFT JOIN (SELECT TC.CONSTRAINT_CATALOG, TC.CONSTRAINT_SCHEMA, TC.TABLE_NAME, KCU.COLUMN_NAME FROM {5}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN {5}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') PK ON C.TABLE_CATALOG = PK.CONSTRAINT_CATALOG AND C.TABLE_SCHEMA = PK.CONSTRAINT_SCHEMA AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME";
List<DBColumn> columns = new List<DBColumn>();
using (SqlConnection sqlConnection = createSqlConnection())
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = String.Format(command, schemaCol, tableCol, columnCol, pkCol, dataTypeCol, database.Name);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string schema = (string)reader[schemaCol];
string tableName = (string)reader[tableCol];
string columnName = (string)reader[columnCol];
int pk = (int)reader[pkCol];
bool isPk = false;
if ((int)reader[pkCol] == 1) isPk = true;
DBDatatype dataType = stringToDatatype((string)reader[dataTypeCol]);
DBTable table = tables.Where<DBTable>(t => t.Name == tableName && t.Schema == schema).FirstOrDefault();
if (table != null)
{
DBColumn column = new DBColumn(table, columnName, isPk, dataType);
table.addColumn(column);
}
}
}
}
}
internal void tableMappingSwitchDatabase(TableMapping[] tableMappings, Database db)
{
Dictionary<int, TableMapping> newTableMappings = new Dictionary<int, TableMapping>();
foreach (TableMapping tableMapping in tableMappings)
{
DBTable table = db.Tables.Where(t => t.Reference == tableMapping.DestinationTable.Reference).FirstOrDefault();
if (table != null)
{
TableMapping newTableMapping = new TableMapping(table, tableMapping.ImportType, new ColumnMapping[0]);
newTableMappings.Add(tableMapping.Index, newTableMapping);
}
}
foreach (int index in newTableMappings.Keys)
{
TableMapping newTableMapping = newTableMappings[index];
List<ColumnMapping> newColumnMappings = new List<ColumnMapping>();
TableMapping oldTableMapping = tableMappings
.Where(t => t.Index == index).First();
ColumnMapping[] oldColumnMappings = oldTableMapping.ColumnMappings;
foreach (ColumnMapping oldColumnMapping in oldColumnMappings)
{
DBColumn column = newTableMapping.DestinationTable.Columns
.Where(c => c.Name == oldColumnMapping.DestinationColumn.Name)
.FirstOrDefault();
if (column != null)
{
string type = oldColumnMapping.GetType().ToString();
ColumnMapping newColumnMapping = null;
if (type == typeof(ExcelColumnMapping).ToString())
{
var excelColumnMapping = (ExcelColumnMapping)oldColumnMapping;
newColumnMapping = new ExcelColumnMapping(excelColumnMapping.SourceHeader, column, oldColumnMapping.ColumnUse);
}
else if (type == typeof(TableColumnMapping).ToString())
{
var tableColumnMapping = (TableColumnMapping)oldColumnMapping;
if (newTableMappings.ContainsKey(tableColumnMapping.SourceTableMapping.Index))
{
TableMapping newSourceTableMapping = newTableMappings[tableColumnMapping.SourceTableMapping.Index];
DBColumn newSourceColumn = newSourceTableMapping.DestinationTable.Columns
.Where(c => c.Name == tableColumnMapping.SourceColumn.Name)
.FirstOrDefault();
if (newSourceColumn != null)
{
newColumnMapping = new TableColumnMapping(newSourceTableMapping, newSourceColumn, column, oldColumnMapping.ColumnUse);
}
}
}
else if (type == typeof(LiteralColumnMapping).ToString())
{
var literalColumnMapping = (LiteralColumnMapping)oldColumnMapping;
newColumnMapping = new LiteralColumnMapping(literalColumnMapping.Literal, literalColumnMapping.LiteralType, column, literalColumnMapping.ColumnUse);
}
if (newColumnMapping == null)
{
newColumnMapping = new NullColumnMapping(column, oldColumnMapping.ColumnUse);
}
newColumnMappings.Add(newColumnMapping);
}
}
newTableMapping.ColumnMappings = newColumnMappings.ToArray();
}
this.TableMappings = newTableMappings.Values.ToList();
}
private TableMapping[] readTableMappings(XmlReader reader, Database database)
{
Dictionary<int, TableMapping> tableMappings = new Dictionary<int, TableMapping>();
Dictionary<TableMapping, XElement> columnMappingReaders = new Dictionary<TableMapping, XElement>();
if (reader.ReadToFollowing("TableMappings"))
{
using (XmlReader tmlReader = reader.ReadSubtree())
{
while (tmlReader.ReadToFollowing("TableMapping"))
{
{
int index = int.Parse(tmlReader.GetAttribute("index"));
string destinationTableReference = tmlReader.GetAttribute("destinationTableReference");
string importType = tmlReader.GetAttribute("importType");
DBTable table = database.Tables.Where(t => t.Reference == destinationTableReference).First();
TableMapping tableMapping = new TableMapping(table,
(TableMappingImportType)Enum.Parse(typeof(TableMappingImportType), importType), null);
tableMappings.Add(index, tableMapping);
if (tmlReader.ReadToFollowing("ColumnMappings"))
{
XmlReader cmlReader = tmlReader.ReadSubtree();
XElement cml = XElement.Load(cmlReader);
columnMappingReaders.Add(tableMapping, cml);
}
}
}
foreach (TableMapping tableMapping in columnMappingReaders.Keys)
{
List<ColumnMapping> columnMappings = new List<ColumnMapping>();
XElement columnMappingsElement = columnMappingReaders[tableMapping];
IEnumerable<XElement> columnMappingElements = columnMappingsElement.Elements();
foreach (XElement columnMappingElement in columnMappingElements)
{
if (columnMappingElement.Name == "ColumnMapping")
{
string type = columnMappingElement.Attribute("type").Value;
ColumnUse columnUse = (ColumnUse)Enum.Parse(typeof(ColumnUse), columnMappingElement.Attribute("columnUse").Value);
string destinationColumnReference = columnMappingElement.Attribute("destinationColumnReference").Value;
DBColumn destinationColumn = tableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == destinationColumnReference.ToLower()).First();
ColumnMapping columnMapping = null;
if (type == typeof(ExcelColumnMapping).Name)
{
string sourceHeader = columnMappingElement.Attribute("sourceHeader").Value;
columnMapping = new ExcelColumnMapping(sourceHeader, destinationColumn, columnUse);
}
else if (type == typeof(TableColumnMapping).Name)
{
int sourceTableMappingIndex = int.Parse(columnMappingElement.Attribute("sourceTableMappingIndex").Value);
string sourceColumnReference = columnMappingElement.Attribute("sourceColumnReference").Value;
TableMapping sourceTableMapping = tableMappings[sourceTableMappingIndex];
DBColumn sourceColumn = sourceTableMapping.DestinationTable.Columns
.Where(c => c.Name.ToLower() == sourceColumnReference.ToLower()).First();
columnMapping = new TableColumnMapping(sourceTableMapping, sourceColumn, destinationColumn, columnUse);
}
else if (type == typeof(LiteralColumnMapping).Name)
{
string litearal = columnMappingElement.Attribute("literal").Value;
string literalType = columnMappingElement.Attribute("literalType").Value;
columnMapping = new LiteralColumnMapping(litearal,
(LiteralType)Enum.Parse(typeof(LiteralType), literalType), destinationColumn, columnUse);
}
else
{
columnMapping = new NullColumnMapping(destinationColumn, columnUse);
}
columnMappings.Add(columnMapping);
}
}
tableMapping.ColumnMappings = columnMappings.ToArray();
}
}
}
return tableMappings.Values.ToArray();
}
public ConnectionPageViewModel(WizardViewModel wizardViewModel)
{
this.wizardViewModel = wizardViewModel;
selectedDatabase = new Database("", new List<DBTable>());
}
public void StatementCreatorOrderTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List<DBColumn>() { col1, col2, col3 };
Database db = new Database("TestDB", new List<DBTable>() { table });
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col2, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ErrorHandling errorHandling = new ErrorHandling();
ImportConfiguration config = new ImportConfiguration(new TableMapping[] { tableMapping }, null, "TestDB", errorHandling);
SourceDataEntry[] entries = new SourceDataEntry[] {SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(config, dt);
ImportStatement statement = statementCreator.CreateStatement(0);
ImportStatement[] statements = statementCreator.CreateStatements();
Assert.AreEqual(1, statements.Length);
Assert.AreEqual(statement.RowReference, statements[0].RowReference);
Assert.AreEqual(statement.SqlStatement, statements[0].SqlStatement);
string[] lines = statement.SqlStatement
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
StatementSetupPart setupPart = new StatementSetupPart(config);
Assert.AreEqual(setupPart.GetDatabasePart(), lines[0]);
Assert.AreEqual(setupPart.GetWarningsPart(), lines[1]);
StatementTransactionPart transPart = new StatementTransactionPart(config);
string[] transStartPart = transPart.GetTransactionStartPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray(); ;
Assert.AreEqual(2, transStartPart.Length);
Assert.AreEqual(transStartPart[0], lines[2]);
Assert.AreEqual(transStartPart[1], lines[3]);
StatementTableMappingPart tmParts = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string variablePart = tmParts.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual(variablePart, lines[4]);
string[] bodyParts = tmParts.GetStatementBodyPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual(bodyParts[0], lines[5]);
Assert.AreEqual(bodyParts[1], lines[6]);
Assert.AreEqual(bodyParts[2], lines[7]);
Assert.AreEqual(bodyParts[3], lines[8]);
string[] transEndPart = transPart.GetTransactionEndPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(12, transEndPart.Length);
Assert.AreEqual(transEndPart[0], lines[9]);
Assert.AreEqual(transEndPart[1], lines[10]);
Assert.AreEqual(transEndPart[2], lines[11]);
Assert.AreEqual(transEndPart[3], lines[12]);
Assert.AreEqual(transEndPart[4], lines[13]);
Assert.AreEqual(transEndPart[5], lines[14]);
Assert.AreEqual(transEndPart[6], lines[15]);
Assert.AreEqual(transEndPart[7], lines[16]);
Assert.AreEqual(transEndPart[8], lines[17]);
Assert.AreEqual(transEndPart[9], lines[18]);
Assert.AreEqual(transEndPart[10], lines[19]);
Assert.AreEqual(transEndPart[11], lines[20]);
}
