/// <summary>Builds the final SqlCommand</summary>
/// <param name="dataRow"></param>
/// <param name="excelDefinition"></param>
public SqlCommand CreateInsertCommand(DataRow dataRow, ExcelDefinition excelDefinition)
{
var table = dataRow.Table;
var sql = BuildInsertSql(table, excelDefinition);
var command = new SqlCommand(sql);
command.CommandType = CommandType.Text;
if (excelDefinition.ColumnMappings.Count > 0)
{
foreach (var mapping in excelDefinition.ColumnMappings)
{
var parameterName = "@" + mapping.SourceColumn;
InsertParameter(command, parameterName,
mapping.SourceColumn,
dataRow[mapping.SourceColumn]);
}
return(command);
}
foreach (DataColumn column in table.Columns)
{
if (!column.AutoIncrement)
{
var parameterName = "@" + column.ColumnName;
InsertParameter(command, parameterName,
column.ColumnName,
dataRow[column.ColumnName]);
}
}
return(command);
}
/// <summary>Persists Data in DataTable to Sql DB</summary>
/// <param name="excelDefinition"></param>
/// <param name="srcDataTable"></param>
public void SaveRowData(DataTable srcDataTable, ExcelDefinition excelDefinition)
{
Log.Info(string.Format("Saving {0} Row(s)", srcDataTable.Rows.Count));
foreach (DataRow row in srcDataTable.Rows)
{
InsertDataRow(row, excelDefinition);
}
rowCount = srcDataTable.Rows.Count;
SortedDictionary <int, string> errorList = new SortedDictionary <int, string>();
if (srcDataTable.HasErrors)
{
DataRow[] errorRows = srcDataTable.GetErrors();
foreach (var row in errorRows)
{
errorList.Add(srcDataTable.Rows.IndexOf(row), row.RowError);
}
if (excelDefinition.RollbackOnError)
{
Log.Error(string.Format("Error occured! Rolling back imported Data {0} rows.", srcDataTable.Rows.Count));
ResetTable(excelDefinition);
}
}
WriteBackErrors(errorList, excelDefinition);
}
/// <summary>Constructor Method</summary>
/// <param name="definition">Excel Definition</param>
public ExcelReader(ExcelDefinition definition)
{
initializeCulture();
Exceldefinition = definition;
Log = LogManager.GetLogger(typeof(ExcelReader));
Log.Info(string.Format("Starting Import: {0}", Exceldefinition.FileName));
}
/// <summary>Constructor Method</summary>
/// <param name="fileName">Location of Excel File</param>
/// <param name="sheetName">Name of Worksheet</param>
/// <param name="range">Excel Range i.e. A1:C5</param>
/// <param name="hasHeaderRow">Determines existance of Header Row</param>
public ExcelReader(string fileName, string sheetName, string range, bool hasHeaderRow)
{
initializeCulture();
Log = LogManager.GetLogger(typeof(ExcelReader));
Log.Info(string.Format("Starting Import: {0}", fileName));
Exceldefinition = new ExcelDefinition
{
Range = range,
SheetName = sheetName,
FileName = fileName,
HasHeaderRow = hasHeaderRow
};
}
/// <summary>Deletes existing Rows from Target Table</summary>
/// <param name="excelDefinition"></param>
private void ResetTable(ExcelDefinition excelDefinition)
{
SqlCommand command = new SqlCommand();
command.CommandText = String.Format("DELETE FROM {0}", excelDefinition.TargetTable);
using (var connection = new SqlConnection(excelDefinition.ConnectionString))
{
command.Connection = connection;
command.CommandType = CommandType.Text;
connection.Open();
int numberOfRecords = command.ExecuteNonQuery();
connection.Close();
Log.Info(String.Format("Executing Delete Command '{0}' -> {1} rows affected", command.CommandText, numberOfRecords));
}
}
/// <summary>Persists Data in DataTable to Sql DB</summary>
/// <param name="excelDefinition"></param>
/// <param name="srcDataTable"></param>
public void SaveBatchData(DataTable srcDataTable, ExcelDefinition excelDefinition)
{
rowCount = srcDataTable.Rows.Count;
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(excelDefinition.ConnectionString))
{
bulkCopy.DestinationTableName = excelDefinition.TargetTable;
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(srcDataTable);
Log.Info(String.Format("Bulk Inserting Table {0}", excelDefinition.TargetTable));
}
catch (Exception ex)
{
Log.Error(String.Format("Error Bulk Inserting Table {0}", excelDefinition.TargetTable), ex);
}
}
}
/// <summary>Adds Error Comments to Sheet</summary>
/// <param name="definition">Excel Definition</param>
/// <param name="errorList">List with Error Rows</param>
public void WriteBackErrors(SortedDictionary <int, string> errorList, ExcelDefinition definition)
{
ExcelWorksheet excelWorksheet;
var fileinfo = new FileInfo(definition.FileName);
using (ExcelPackage pck = new ExcelPackage(fileinfo))
{
excelWorksheet = pck.Workbook.Worksheets[definition.SheetName];
ExcelRange wsCol = excelWorksheet.Cells[definition.Range];
int worksheetStartColumn = wsCol.Start.Column;
int worksheetStartRow = wsCol.Start.Row;
int worksheetEndRow = wsCol.End.Row;
if (definition.HasHeaderRow)
{
worksheetStartRow = worksheetStartRow + 1;
}
foreach (var cell in excelWorksheet.Cells[worksheetStartRow, worksheetStartColumn, worksheetEndRow, worksheetStartColumn])
{
if (cell.Comment != null)
{
if (cell.Comment.Author.Equals("Excel Importer"))
{
excelWorksheet.Comments.Remove(cell.Comment);
}
}
}
if (errorList.Count > 0)
{
Log.Error(string.Format("Error occured! Adding Error Comments to Excel Workbook {0}", definition.FileName));
errorRowCount = errorList.Count;
}
foreach (var error in errorList)
{
var cell = excelWorksheet.Cells[worksheetStartRow + error.Key, worksheetStartColumn];
cell.AddComment(error.Value, "Excel Importer");
}
pck.Save();
}
}
/// <summary>Returns all defined Excel Definitions from Unittest DB Context</summary>
public void SaveImport(ExcelDefinition definition)
{
using (var context = new ExcelDataContext())
{
ExcelImport import = new ExcelImport();
import.ImportTimestamp = DateTime.Now;
import.RowsImported = rowCount;
import.RowsWithErrors = errorRowCount;
import.ResultStatus = "VALID";
if (errorRowCount > 0)
{
import.ResultStatus = "ERROR";
}
definition.Imports.Add(import);
context.Entry(definition).State = EntityState.Modified;
context.SaveChanges();
context.Entry(import).State = EntityState.Added;
context.SaveChanges();
}
}
/// <summary>Executes Insert Command for each row</summary>
/// <param name="dataRow"></param>
/// <param name="excelDefinition"></param>
public void InsertDataRow(DataRow dataRow, ExcelDefinition excelDefinition)
{
var command = CreateInsertCommand(dataRow, excelDefinition);
using (var connection = new SqlConnection(excelDefinition.ConnectionString))
{
command.Connection = connection;
command.CommandType = CommandType.Text;
connection.Open();
try
{
command.ExecuteScalar();
connection.Close();
}
catch (Exception ex)
{
dataRow.RowError = ex.Message;
Log.Error(String.Format("Error Inserting Row {0} Table {1}", dataRow.Table.Rows.IndexOf(dataRow), dataRow.Table.TableName), ex);
connection.Close();
}
}
}
protected override void Seed(ExcelDataContext context)
{
ExcelDefinition def = new ExcelDefinition
{
FileName = @"TestWorkbooks\UT1.xlsx",
SheetName = "Sheet1",
HasHeaderRow = true,
Range = "A1:D5",
RangeHeightAuto = false,
RangeWidthAuto = false,
TargetTable = "unittest.UT1",
ValidateDataTypes = true,
BulkInsert = false,
DeleteBeforeImport = true,
RollbackOnError = true,
IsActive = true,
ConnectionString = "Data Source=.;Initial Catalog=ExcelDBUNitTest;Integrated Security=true"
};
List <ColumnMapping> map = new List <ColumnMapping>
{
new ColumnMapping()
{
SourceColumn = "StringTest", TargetColumn = "StringTest"
},
new ColumnMapping()
{
SourceColumn = "DecimalTest", TargetColumn = "DecimalTest"
},
new ColumnMapping()
{
SourceColumn = "IntTest", TargetColumn = "IntTest"
},
new ColumnMapping()
{
SourceColumn = "GuidTest", TargetColumn = "GuidTest"
}
};
def.ColumnMappings.AddRange(map);
context.Entry(def).State = EntityState.Added;
def = new ExcelDefinition
{
FileName = @"TestWorkbooks\UT1.xlsx",
SheetName = "Sheet2",
HasHeaderRow = true,
Range = "M14:P18",
RangeHeightAuto = false,
RangeWidthAuto = false,
TargetTable = "unittest.UT1a",
ValidateDataTypes = false,
BulkInsert = false,
DeleteBeforeImport = true,
RollbackOnError = true,
IsActive = true,
ConnectionString = "Data Source=.;Initial Catalog=ExcelDBUNitTest;Integrated Security=true"
};
map = new List <ColumnMapping>
{
new ColumnMapping()
{
SourceColumn = "StringTest", TargetColumn = "StringTest"
},
new ColumnMapping()
{
SourceColumn = "DecimalTest", TargetColumn = "DecimalTest"
},
new ColumnMapping()
{
SourceColumn = "IntTest", TargetColumn = "IntTest"
},
new ColumnMapping()
{
SourceColumn = "GuidTest", TargetColumn = "GuidTest"
}
};
def.ColumnMappings.AddRange(map);
context.Entry(def).State = EntityState.Added;
def = new ExcelDefinition
{
FileName = @"TestWorkbooks\UT2.xlsx",
SheetName = "Sheet1",
HasHeaderRow = true,
Range = "A1:E5",
RangeHeightAuto = false,
RangeWidthAuto = false,
TargetTable = "unittest.UT2",
ValidateDataTypes = true,
RollbackOnError = true,
DeleteBeforeImport = true,
BulkInsert = true,
IsActive = true,
ConnectionString = "Data Source=.;Initial Catalog=ExcelDBUNitTest;Integrated Security=true"
};
map = new List <ColumnMapping>
{
new ColumnMapping()
{
SourceColumn = "StringTest", TargetColumn = "StringTest1"
},
new ColumnMapping()
{
SourceColumn = "DecimalTest", TargetColumn = "DecimalTest1"
},
new ColumnMapping()
{
SourceColumn = "IntTest", TargetColumn = "IntTest1"
},
new ColumnMapping()
{
SourceColumn = "GuidTest", TargetColumn = "GuidTest1"
},
new ColumnMapping()
{
SourceColumn = "DateTest", TargetColumn = "DateTest"
}
};
def.ColumnMappings.AddRange(map);
context.Entry(def).State = EntityState.Added;
context.SaveChanges();
def = new ExcelDefinition
{
FileName = @"TestWorkbooks\UT3.xlsx",
SheetName = "Sheet1",
HasHeaderRow = true,
Range = "A1:A1",
RangeHeightAuto = true,
RangeWidthAuto = true,
TargetTable = "unittest.UT3",
ValidateDataTypes = true,
RollbackOnError = true,
DeleteBeforeImport = true,
BulkInsert = true,
IsActive = true,
ConnectionString = "Data Source=.;Initial Catalog=ExcelDBUNitTest;Integrated Security=true"
};
map = new List <ColumnMapping>
{
new ColumnMapping()
{
SourceColumn = "StringTest", TargetColumn = "StringTest1"
},
new ColumnMapping()
{
SourceColumn = "DecimalTest", TargetColumn = "DecimalTest1"
},
new ColumnMapping()
{
SourceColumn = "IntTest", TargetColumn = "IntTest1"
},
new ColumnMapping()
{
SourceColumn = "GuidTest", TargetColumn = "GuidTest1"
},
new ColumnMapping()
{
SourceColumn = "DateTest", TargetColumn = "DateTest"
}
};
def.ColumnMappings.AddRange(map);
context.Entry(def).State = EntityState.Added;
context.SaveChanges();
}
/// <summary>Returns the Insert Statement</summary>
/// <param name="srcDataTable"></param>
/// <param name="excelDefinition"></param>
public string BuildInsertSql(DataTable srcDataTable, ExcelDefinition excelDefinition)
{
srcDataTable.TableName = excelDefinition.TargetTable;
var sql = new StringBuilder("INSERT INTO " + srcDataTable.TableName + " (");
var values = new StringBuilder("VALUES (");
var bFirst = true;
var bIdentity = false;
string identityType = null;
if (excelDefinition.ColumnMappings.Count > 0)
{
var sourceCols = string.Join(",", excelDefinition.ColumnMappings.Select(x => "@" + x.SourceColumn));
var targetCols = string.Join(",", excelDefinition.ColumnMappings.Select(x => x.TargetColumn));
sql.Append(targetCols);
values.Append(sourceCols);
sql.Append(") ");
sql.Append(values);
sql.Append(")");
return(sql.ToString());
;
}
foreach (DataColumn column in srcDataTable.Columns)
{
if (column.AutoIncrement)
{
bIdentity = true;
switch (column.DataType.Name)
{
case "Int16":
identityType = "smallint";
break;
case "SByte":
identityType = "tinyint";
break;
case "Int64":
identityType = "bigint";
break;
case "Decimal":
identityType = "decimal";
break;
default:
identityType = "int";
break;
}
}
else
{
if (bFirst)
{
bFirst = false;
}
else
{
sql.Append(", ");
values.Append(", ");
}
sql.Append(column.ColumnName);
values.Append("@");
values.Append(column.ColumnName);
}
}
sql.Append(") ");
sql.Append(values);
sql.Append(")");
if (bIdentity)
{
sql.Append("; SELECT CAST(scope_identity() AS ");
sql.Append(identityType);
sql.Append(")");
}
return(sql.ToString());
;
}