コード例 #1
0
        /// <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);
        }
コード例 #2
0
        /// <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);
        }
コード例 #3
0
 /// <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));
 }
コード例 #4
0
        /// <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
            };
        }
コード例 #5
0
        /// <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));
            }
        }
コード例 #6
0
        /// <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);
                }
            }
        }
コード例 #7
0
        /// <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();
            }
        }
コード例 #8
0
        /// <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();
            }
        }
コード例 #9
0
        /// <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();
                }
            }
        }
コード例 #10
0
        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();
        }
コード例 #11
0
        /// <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());

            ;
        }