/// <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);
}