Exemplo n.º 1
0
        /// <summary>
        /// Stores data in temporary Oracle tables
        /// </summary>
        public static Int32 storeData(CswNbtResources CswNbtResources, string FileName, string FullFilePath, string ImportDefinitionName, bool Overwrite)
        {
            CswNbtSchemaModTrnsctn _CswNbtSchemaModTrnsctn = new CswNbtSchemaModTrnsctn(CswNbtResources);

            //StringCollection ret = new StringCollection();
            DataSet ExcelDataSet = ReadExcel(FullFilePath);

            // Store the job reference in import_data_job
            CswTableUpdate ImportDataJobUpdate = CswNbtResources.makeCswTableUpdate("Importer_DataJob_Insert", CswNbtImportTables.ImportDataJob.TableName);
            DataTable      ImportDataJobTable  = ImportDataJobUpdate.getEmptyTable();
            DataRow        DataJobRow          = ImportDataJobTable.NewRow();

            DataJobRow[CswNbtImportTables.ImportDataJob.filename]    = FileName;
            DataJobRow[CswNbtImportTables.ImportDataJob.userid]      = CswNbtResources.CurrentNbtUser.UserId.PrimaryKey;
            DataJobRow[CswNbtImportTables.ImportDataJob.datestarted] = CswConvert.ToDbVal(DateTime.Now);
            ImportDataJobTable.Rows.Add(DataJobRow);
            Int32 JobId = CswConvert.ToInt32(DataJobRow[CswNbtImportTables.ImportDataJob.importdatajobid]);

            ImportDataJobUpdate.update(ImportDataJobTable);

            foreach (DataTable ExcelDataTable in ExcelDataSet.Tables)
            {
                string          SheetName  = ExcelDataTable.TableName;
                CswNbtImportDef Definition = null;
                //try
                //{
                Definition = new CswNbtImportDef(CswNbtResources, ImportDefinitionName, SheetName);
                //}
                //catch( Exception ex )
                //{
                //    //OnMessage( "Sheet '" + SheetName + "' is invalid: " + ex.Message );
                //}

                // ignore bad sheetnames
                if (null != Definition)
                {
                    // Determine Oracle table name
                    Int32  i = 1;
                    string ImportDataTableName = CswNbtImportTables.ImportDataN.TableNamePrefix + i.ToString();
                    while (_CswNbtSchemaModTrnsctn.isTableDefinedInDataBase(ImportDataTableName))
                    {
                        i++;
                        ImportDataTableName = CswNbtImportTables.ImportDataN.TableNamePrefix + i.ToString();
                    }

                    // Generate an Oracle table for storing and manipulating data
                    _CswNbtSchemaModTrnsctn.addTable(ImportDataTableName, CswNbtImportTables.ImportDataN.PkColumnName);
                    _CswNbtSchemaModTrnsctn.addBooleanColumn(ImportDataTableName, CswNbtImportTables.ImportDataN.error, "", false);
                    _CswNbtSchemaModTrnsctn.addClobColumn(ImportDataTableName, CswNbtImportTables.ImportDataN.errorlog, "", false);
                    foreach (DataColumn ExcelColumn in ExcelDataTable.Columns)
                    {
                        _CswNbtSchemaModTrnsctn.addStringColumn(ImportDataTableName, CswNbtImportDefBinding.SafeColName(ExcelColumn.ColumnName), "", false, 4000);
                    }
                    foreach (CswNbtImportDefOrder Order in Definition.ImportOrder.Values)
                    {
                        _CswNbtSchemaModTrnsctn.addLongColumn(ImportDataTableName, Order.PkColName, "", false);
                    }
                    CswNbtResources.commitTransaction();
                    CswNbtResources.beginTransaction();
                    CswNbtResources.DataDictionary.refresh();

                    // Store the sheet reference in import_data_map
                    CswTableUpdate ImportDataMapUpdate = CswNbtResources.makeCswTableUpdate("Importer_DataMap_Insert", CswNbtImportTables.ImportDataMap.TableName);
                    DataTable      ImportDataMapTable  = ImportDataMapUpdate.getEmptyTable();
                    DataRow        DataMapRow          = ImportDataMapTable.NewRow();
                    DataMapRow[CswNbtImportTables.ImportDataMap.datatablename]   = ImportDataTableName;
                    DataMapRow[CswNbtImportTables.ImportDataMap.importdefid]     = Definition.ImportDefinitionId;
                    DataMapRow[CswNbtImportTables.ImportDataMap.importdatajobid] = JobId;
                    DataMapRow[CswNbtImportTables.ImportDataMap.overwrite]       = CswConvert.ToDbVal(Overwrite);
                    DataMapRow[CswNbtImportTables.ImportDataMap.completed]       = CswConvert.ToDbVal(false);
                    ImportDataMapTable.Rows.Add(DataMapRow);
                    ImportDataMapUpdate.update(ImportDataMapTable);

                    // Copy the Excel data into the Oracle table
                    CswTableUpdate ImportDataUpdate = CswNbtResources.makeCswTableUpdate("Importer_Update", ImportDataTableName);
                    DataTable      ImportDataTable  = ImportDataUpdate.getEmptyTable();
                    foreach (DataRow ExcelRow in ExcelDataTable.Rows)
                    {
                        bool    hasData   = false;
                        DataRow ImportRow = ImportDataTable.NewRow();
                        ImportRow[CswNbtImportTables.ImportDataN.error] = CswConvert.ToDbVal(false);
                        foreach (DataColumn ExcelColumn in ExcelDataTable.Columns)
                        {
                            if (ExcelRow[ExcelColumn] != DBNull.Value)
                            {
                                hasData = true;
                            }
                            ImportRow[CswNbtImportDefBinding.SafeColName(ExcelColumn.ColumnName)] = ExcelRow[ExcelColumn];
                        }
                        if (hasData == true)
                        {
                            ImportDataTable.Rows.Add(ImportRow);
                        }
                    }
                    ImportDataUpdate.update(ImportDataTable);
                    //OnMessage( "Sheet '" + SheetName + "' is stored in Table '" + ImportDataTableName + "'" );
                } // if( null != Definition )
            }     // foreach( DataTable ExcelDataTable in ExcelDataSet.Tables )

            CswNbtResources.commitTransaction();
            CswNbtResources.beginTransaction();

            //return ret;
            return(JobId);
        }