public void TableBodyPartInsertTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List <DBColumn>()
{
col1, col2
};
ColumnMapping colMap1 = new NullColumnMapping(col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(3, bodyParts.Length);
Assert.AreEqual("INSERT INTO dbo.TestTable (TestCol2)", bodyParts[0]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2 INTO @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") +
"(TestCol1, TestCol2)", bodyParts[1]);
Assert.AreEqual("VALUES ('2')", bodyParts[2]);
}
public void WorksheetWithOffsetTest()
{
ExcelReader reader = new ExcelReader(worksheetWorksheetOffset);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
Assert.AreEqual(4, dataTable.NumberOfRows);
}
public void WorksheetWithEmptyRowInTableTest()
{
ExcelReader reader = new ExcelReader(worksheetEmptyRowInTable);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
Assert.AreEqual(3, dataTable.NumberOfRows);
}
public void CellDataTypesTest()
{
ExcelReader reader = new ExcelReader(dataTypesTestFile);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
string[] headers = reader.GetHeaderNames();
SourceDataEntry entry1 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[0]);
Assert.AreEqual(DataType.Bool, entry1.DataType);
Assert.AreEqual("1", entry1.Value);
SourceDataEntry entry2 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[1]);
Assert.AreEqual(DataType.DateTime, entry2.DataType);
Assert.AreEqual("12/30/1899 00:00:00", entry2.Value);
SourceDataEntry entry3 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[2]);
Assert.AreEqual(DataType.Error, entry3.DataType);
Assert.AreEqual("", entry3.Value);
SourceDataEntry entry4 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[3]);
Assert.AreEqual(DataType.String, entry4.DataType);
Assert.AreEqual("Test string", entry4.Value);
SourceDataEntry entry5 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[4]);
Assert.AreEqual(DataType.Number, entry5.DataType);
Assert.AreEqual("1.23", entry5.Value);
SourceDataEntry entry6 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[5]);
Assert.AreEqual(DataType.String, entry6.DataType);
Assert.AreEqual("Test string", entry6.Value);
SourceDataEntry entry7 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[6]);
Assert.AreEqual(DataType.String, entry7.DataType);
Assert.AreEqual("Test string", entry7.Value);
SourceDataEntry entry8 = dataTable.GetDataRow(0).GetSourceDataEntry(headers[7]);
Assert.AreEqual(DataType.Number, entry8.DataType);
Assert.AreEqual("1.23", entry8.Value);
}
public void WorksheetMissingHeaderTest()
{
ExcelReader reader = new ExcelReader(worksheetMissingHeader);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
string[] headers = dataTable.Headers;
Assert.AreEqual("TestHeader1", headers[0]);
Assert.AreEqual("TestHeader3", headers[1]);
Assert.AreEqual(2, headers.Length);
Assert.AreEqual(3, dataTable.NumberOfRows);
}
public void WorksheetNoHeadersTest()
{
ExcelReader reader = new ExcelReader(worksheetNoHeaders);
reader.HasHeaders = false;
SourceDataTable dataTable = reader.ReadToDataTable();
string[] headers = dataTable.Headers;
Assert.AreEqual("A", headers[0]);
Assert.AreEqual("B", headers[1]);
Assert.AreEqual("C", headers[2]);
Assert.AreEqual(4, dataTable.NumberOfRows);
}
public void WorksheetWithHeadersTest()
{
ExcelReader reader = new ExcelReader(worksheetWithHeaders);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
string[] headers = dataTable.Headers;
Assert.AreEqual("TestHeader1", headers[0]);
Assert.AreEqual("TestHeader2", headers[1]);
Assert.AreEqual("TestHeader3", headers[2]);
Assert.AreEqual(4, dataTable.NumberOfRows);
}
public void WorksheetWithEmptyCellTest()
{
ExcelReader reader = new ExcelReader(worksheetEmptyCell);
reader.HasHeaders = true;
SourceDataTable dataTable = reader.ReadToDataTable();
Assert.AreEqual(4, dataTable.NumberOfRows);
string header = reader.GetHeaderNames()[1];
SourceDataEntry emptyDataEntry = dataTable.GetDataRow(1).GetSourceDataEntry(header);
Assert.AreEqual(DataReader.DataType.Null, emptyDataEntry.DataType);
Assert.AreEqual("NULL", emptyDataEntry.Value);
}
public string PreviewSQL()
{
if (wizardViewModel.ConnectionPageViewModel.ExcelReader == null)
{
throw new Exception("No excel file selected.");
}
else
{
SourceDataTable dataTable = wizardViewModel.ConnectionPageViewModel.ExcelReader.ReadFirstRowToDataTable();
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(createImportConfiguration(), dataTable);
ImportStatement previewStatement = statementCreator.CreateStatement(0);
return(previewStatement.SqlStatement);
}
}
public void bw_DoWork(object sender, DoWorkEventArgs e)
{
isImporting = true;
importStates.Add("Starting import...");
NotifyPropertyChanged("ImportStates");
importStates.Add("Reading excel file");
NotifyPropertyChanged("ImportStates");
SourceDataTable dataTable = reader.ReadToDataTable();
importRowCount = dataTable.NumberOfRows;
NotifyPropertyChanged("ImportRowCount");
importStates.Add("Creating SQL statements");
NotifyPropertyChanged("ImportStates");
BackgroundWorker worker = sender as BackgroundWorker;
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(config, dataTable);
ImportStatement[] statements = statementCreator.CreateStatements();
importStates.Add("Importing data...");
NotifyPropertyChanged("ImportStates");
SQLServerDataImporter dataImporter = new SQLServerDataImporter(config);
foreach (ImportStatement statement in statements)
{
ImportResult result = dataImporter.ImportData(statement);
importResults.Add(result);
importProgress++;
NotifyPropertyChanged("ImportProgress");
importStates[3] = String.Format("Importing data... row {0} of {1}", importProgress, statements.Length);
NotifyPropertyChanged("ImportStates");
}
importStates.Add("Import complete");
NotifyPropertyChanged("ImportStates");
finishImport();
}
public void TableBodyPartUpdateTest()
{
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
};
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, col3, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual("UPDATE dbo.TestTable", bodyParts[0]);
Assert.AreEqual("SET TestCol3='3'", bodyParts[1]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2, inserted.TestCol3 INTO @sqlimport_table_" +
tableMapping.TableMappingReference.Replace(".", "_") + "(TestCol1, TestCol2, TestCol3)", bodyParts[2]);
Assert.AreEqual("WHERE TestCol1 = (SELECT TOP 1 t.TestCol1 FROM @sqlimport_table_" +
sourceTablemapping.TableMappingReference.Replace(".", "_") + " t) and TestCol2 = '2'", bodyParts[3]);
}
private void DrawTableContent()
{
var tr = new HtmlTableRow();
HtmlTableCell td;
DataRow[] dr;
var arr_totals = new decimal[13];
var arr_func_totals = new decimal[13];
decimal current_amount;
var func = "";
int m_index;
foreach (DataRow dr_total in TotalsDataTable.Rows)
{
if (func != dr_total["SUM_FUNC"].ToString())
{
//new function section:
/************************************************/
//first, totals row for previous function:
/************************************************/
if (func != "")
{
//func can be equal "" only for the first row - in this case we don't have prev.totals row
tr = new HtmlTableRow();
td = GetNewCellText("Total", "tableTotal", CELL_ALIGN_LEFT);
if (DisplayColumnObjClassCode)
{
td.ColSpan = 2;
}
tr.Cells.Add(td);
if (MonthlyView)
{
foreach (var m in month_arr)
{
m_index = Int32.Parse(m);
tr.Cells.AddNewMoneyCell(arr_func_totals[m_index], "tableTotal", CELL_ALIGN_RIGHT);
arr_func_totals[m_index] = 0;
}
}
tr.Cells.AddNewMoneyCell(arr_func_totals[0], "tableTotal", CELL_ALIGN_RIGHT);
TableToDraw.Rows.Add(tr);
}
/************************************************/
//then, start new function section:
/************************************************/
tr = new HtmlTableRow();
func = dr_total["SUM_FUNC"].ToString();
tr.Cells.AddCell(func, "tableBold", CELL_ALIGN_LEFT);
if (DisplayColumnObjClassCode)
{
tr.Cells.AddCell(dr_total["OBJ_CLASS_CD"].ToString(), "tableBold", CELL_ALIGN_RIGHT);
}
if (MonthlyView)
{
foreach (var m in month_arr)
{
m_index = Int32.Parse(m);
dr = SourceDataTable.Select(String.Format("SUM_FUNC='{0}' AND OBJ_CLASS_CD='{1}' AND BookMonth='{2}'", dr_total["SUM_FUNC"], dr_total["OBJ_CLASS_CD"], m));
if (dr.Length > 0)
{
current_amount = (decimal)dr[0]["Amount"];
}
else
{
current_amount = 0;
}
arr_totals[m_index] += current_amount;
arr_func_totals[m_index] += current_amount;
if (BuildReportForExcel)
{
tr.Cells.AddNewMoneyCell(current_amount, "tableRow");
}
else
{
tr.Cells.AddMoneyCellLink(current_amount, m, dr_total["SUM_FUNC"].ToString(), dr_total["OBJ_CLASS_CD"].ToString(), CellLinkOnClick);
}
}
}
current_amount = (decimal)dr_total["Amount"];
arr_totals[0] += current_amount;
arr_func_totals[0] += current_amount;
tr.Cells.AddNewMoneyCell(current_amount, "tableRow");
TableToDraw.Rows.Add(tr);
}
/************************************************/
//continue the same function section:
/************************************************/
else
{
tr = new HtmlTableRow();
tr.Cells.AddCell("", "tableBold", CELL_ALIGN_LEFT);
if (DisplayColumnObjClassCode)
{
tr.Cells.AddCell(dr_total["OBJ_CLASS_CD"].ToString(), "tableBold", CELL_ALIGN_RIGHT);
}
if (MonthlyView)
{
foreach (var m in month_arr)
{
m_index = Int32.Parse(m);
dr = SourceDataTable.Select(String.Format("SUM_FUNC='{0}' AND OBJ_CLASS_CD='{1}' AND BookMonth='{2}'", dr_total["SUM_FUNC"], dr_total["OBJ_CLASS_CD"], m));
if (dr.Length > 0)
{
current_amount = (decimal)dr[0]["Amount"];
}
else
{
current_amount = 0;
}
arr_totals[m_index] += current_amount;
arr_func_totals[m_index] += current_amount;
if (BuildReportForExcel)
{
tr.Cells.AddNewMoneyCell(current_amount, "tableRow");
}
else
{
tr.Cells.AddMoneyCellLink(current_amount, m, dr_total["SUM_FUNC"].ToString(), dr_total["OBJ_CLASS_CD"].ToString(), CellLinkOnClick);
}
}
}
current_amount = (decimal)dr_total["Amount"];
arr_totals[0] += current_amount;
arr_func_totals[0] += current_amount;
tr.Cells.AddNewMoneyCell(current_amount, "tableRow");
TableToDraw.Rows.Add(tr);
}
}
/************************************************/
//add total totals section:
/************************************************/
tr = new HtmlTableRow();
td = GetNewCellText("Total", "tableFooter", CELL_ALIGN_LEFT);
if (DisplayColumnObjClassCode)
{
td.ColSpan = 2;
}
tr.Cells.Add(td);
if (MonthlyView)
{
foreach (var m in month_arr)
{
m_index = Int32.Parse(m);
tr.Cells.AddNewMoneyCell(arr_totals[m_index], "tableFooter");
}
}
tr.Cells.AddNewMoneyCell(arr_totals[0], "tableFooter");
TableToDraw.Rows.Add(tr);
}
public bool Process(IWorksheet worksheet)
{
bool contains_data = false;
int start_row = -1;
try
{
Logger.Aquire();
Logger.Write("Worksheet.Process", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Process", "PROCESSING WORKSHEET: " + worksheet.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Process", " SOURCE MODULE: " + SourceDataTable.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Process", " FILTER: " + SourceDataTable.FilterExpression, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
if (StartRow.ToLower() == "%lastusedrow%")
{
start_row = worksheet.UsedRange.RowCount;
}
else if (StartRow.ToLower() == "%firstnewrow%")
{
start_row = worksheet.UsedRange.Row + 1;
}
else
{
start_row = Convert.ToInt32(StartRow);
}
Logger.Write("Worksheet.Process", " START COLUMN|ROW: " + StartColumn + start_row, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
}
catch (Exception ex)
{
throw ex;
}
finally
{
Logger.Release();
}
// Rename the worksheet if applicable.
if (!string.IsNullOrEmpty(NewSheetName))
{
Logger.WriteLine("Worksheet.Process", " CHANGING NAME: " + TextParser.Parse(NewSheetName, DrivingData, SharedData, ModuleCommands), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
worksheet.Name = TextParser.Parse(NewSheetName, DrivingData, SharedData, ModuleCommands);
if (worksheet.Name.Length > 31)
{
throw new Exception("The worksheet name (" + worksheet.Name + ") cannot have more than 31 characters.");
}
}
// If the worksheet does not have a source table defined then return false.
// Without a source table the worksheet will be empty.
if (HasSourceTable)
{
if (!SharedData.Data.Contains(SourceDataTable.Name))
{
throw new Exception(string.Format("The source data table '{0}' for sheet at index '{1}' was not found in the global cache set.", SourceDataTable.Name, SheetNumber));
}
// Load the worksheet with the source table's data.
if (!Load(SourceDataTable.Process(ConditionalTables), worksheet))
{
// No data wasloaded into the worksheet.
contains_data = false;
}
else
{
// Perform any required formatting.
Format(worksheet);
contains_data = true;
}
}
else
{
contains_data = false;
}
return(contains_data);
}
protected void TransformData()
{
MemoryStream xslt_stream, xml_stream;
XmlTextReader xml_reader;
XsltSettings xslt_settings;
XPathDocument doc;
DataTable DataSource = null;
XslCompiledTransform xslt;
XsltArgumentList arg_list = null;
string template_path = null;
try
{
CreateResultsTable();
xslt = new XslCompiledTransform();
xslt_settings = new XsltSettings(true, true);
xslt_stream = new MemoryStream();
xml_stream = new MemoryStream();
if (HasTemplate)
{
Logger.WriteLine("XsltWriter.TransformData", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
// Determine if we are using an XSLT template on disk or if we are getting it from another module.
if (!string.IsNullOrEmpty(TemplateName))
{
// Parse the templates path.
template_path = TextParser.Parse(TemplateName, DrivingData, SharedData, ModuleCommands);
// Check for the file expecting relative path.
if (System.IO.File.Exists(System.IO.Path.GetFullPath(template_path)))
{
TemplateName = System.IO.Path.GetFullPath(template_path);
}
// Check for the file from absolute path.
else if (System.IO.File.Exists(template_path))
{
TemplateName = template_path;
}
// Check for the file two layers up incase we are in the bin/debug|release folder.
else if (System.IO.File.Exists(@"..\..\" + template_path))
{
TemplateName = System.IO.Path.GetFullPath(@"..\..\" + template_path);
}
else
{
throw new Exception(string.Format("The XSLT template file '{0}' was not found.", template_path));
}
Logger.WriteLine("XsltWriter.TransformData", "PHYSICAL STYLE SHEET: ", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
// Load the style sheet from the referenced path.
xslt.Load(TextParser.Parse(TemplateName, DrivingData, SharedData, ModuleCommands), xslt_settings, null);
}
else
{
Logger.WriteLine("XsltWriter.TransformData", " DYNAMIC STYLE SHEET: ", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
// Load the style sheet from the referenced module.
xslt.Load(new XmlTextReader(new StringReader(TextParser.Parse(TemplateBody, DrivingData, SharedData, ModuleCommands))), xslt_settings, null);
}
if (SourceDataTable != null)
{
Logger.WriteLine("XsltWriter.TransformData", " DATASOURCE: " + TextParser.Parse(SourceDataTable.Name, DrivingData, SharedData, ModuleCommands), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
//Logger.WriteLine("XsltWriter.TransformData", " FILTER: " + SourceDataTable.FilterExpression, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
// Get the data source table.
DataSource = SourceDataTable.Process(DestinationDataTable.CacheTableCollection);
}
// Check to see if the XSLT writer has any arguments defined.
if (ArgumentContainer != null && ArgumentContainer.Count > 0)
{
Logger.WriteLine("XsltWriter.TransformData", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("XsltWriter.TransformData", " ARGUMENT COUNT: " + ArgumentContainer.Count, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
arg_list = new XsltArgumentList();
// Load the argument list.
foreach (Argument arg in ArgumentContainer.Arguments)
{
Logger.WriteLine("XsltWriter.TransformData", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("XsltWriter.TransformData", " ARGUMENT NAME: " + arg.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("XsltWriter.TransformData", " VALUE: " + TextParser.Parse(arg.Value, DrivingData, SharedData, ModuleCommands), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
arg_list.AddParam(arg.Name, "", TextParser.Parse(arg.Value, DrivingData, SharedData, ModuleCommands));
}
}
if ((DataSource != null && DataSource.Rows.Count > 0) || AllowEmptyReport)
{
if (DataSource == null)
{
DataSource = new DataTable("NOT_USED");
}
if (AllowEmptyReport && !string.IsNullOrEmpty(EmptyMessage) && DataSource.Rows.Count == 0)
{
DataSource.Columns.Add(new DataColumn("Message", typeof(string)));
var row = DataSource.NewRow();
row["Message"] = EmptyMessage;
DataSource.Rows.Add(row);
}
// Get the data from the source table in XML form.
DataSource.WriteXml(xml_stream);
// Make sure we are looking at the begining of our data stream.
xml_stream.Position = 0;
// Load the xml from our xml stream into our xml reader.
xml_reader = new XmlTextReader(xml_stream);
// Create an xml document from the xml reader.
doc = new XPathDocument(xml_reader);
// Transform the xml document with the xslt template and load it into our xslt_stream.
xslt.Transform(doc, arg_list, xslt_stream);
xslt_stream.Flush();
xslt_stream.Position = 0;
Logger.WriteLine("XsltWriter.TransformData", "", TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("XsltWriter.TransformData", " CREATE TRANSFORM: SUCCESSFULL", TraceEventType.Information, 2, 0, SharedData.LogCategory);
Save(xslt_stream);
}
else
{
Logger.WriteLine("XsltWriter.TransformData", "", TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("XsltWriter.TransformData", "DISCARDING TRANSFORM: NO DATA", TraceEventType.Information, 2, 0, SharedData.LogCategory);
}
}
else
{
throw new Exception("XSLT writer must have a template defined.");
}
}
catch (Exception ex)
{
throw ex;
}
}
public SQLServerStatementCreator(ImportConfiguration config, SourceDataTable dataTable)
{
this.config = config;
this.dataTable = dataTable;
}
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]);
}
