public static bool CreateScratchMdb(HluDataSet.incidDataTable incidTable,
HluDataSet.incid_mm_polygonsDataTable incidMMTable)
{
try
{
_incidTable = incidTable;
_incidMMTable = incidMMTable;
_scratchMdbPath = String.Empty;
try { _scratchMdbPath = Path.GetTempPath(); }
catch
{
_scratchMdbPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
_scratchMdbPath += Path.DirectorySeparatorChar.ToString();
}
_scratchMdbPath += Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb";
OdbcCP32 odbc = new OdbcCP32();
odbc.CreateDatabase(_scratchMdbPath);
string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", _scratchMdbPath);
string defaultSchema = "";
bool promptPwd = false;
_scratchDb = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd,
Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders,
true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255,
Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision,
Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale);
return true;
}
catch
{
if (File.Exists(_scratchMdbPath))
{
try
{
if ((_scratchDb != null) && (_scratchDb.Connection.State != ConnectionState.Closed))
_scratchDb.Connection.Close();
File.Delete(_scratchMdbPath);
}
catch { }
}
return false;
}
}
private string ExportMdb(string targetListStr, string fromClauseStr, List<List<SqlFilterCondition>> exportFilter,
DbBase dataBase, List<ExportField> exportFields, DataTable exportTable, int[] sortOrdinals,
int[] matrixOrdinals, int[] formationOrdinals, int[] managementOrdinals, int[] complexOrdinals, int[] bapOrdinals, int[] sourceOrdinals,
int[][] fieldMap, out int exportRowCount)
{
exportRowCount = -1;
int outputRowCount = 0;
DbOleDb dbOut = null;
string tempPath = String.Empty;
try { tempPath = Path.GetTempPath(); }
catch { tempPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); }
tempPath = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb");
try
{
if (File.Exists(tempPath)) File.Delete(tempPath);
OdbcCP32 odbc = new OdbcCP32();
odbc.CreateDatabase(tempPath);
string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", tempPath);
string defaultSchema = "";
bool promptPwd = false;
dbOut = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd,
Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders,
true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255,
Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision,
Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale);
//---------------------------------------------------------------------
// FIX: 054 Improve error reporting during exports.
//
// Throw an error if the table cannot be created.
if (!dbOut.CreateTable(exportTable))
throw new Exception("Error creating the export table");
//---------------------------------------------------------------------
DataSet datasetOut = new DataSet("Export");
IDbDataAdapter adapterOut = dbOut.CreateAdapter(exportTable);
adapterOut.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapterOut.Fill(datasetOut);
int[] pkOrdinals = exportTable.PrimaryKey.Select(c => c.Ordinal).ToArray();
exportTable.PrimaryKey = pkOrdinals.Select(o => exportTable.Columns[o]).ToArray();
adapterOut.TableMappings.Clear();
adapterOut.TableMappings.Add(exportTable.TableName, datasetOut.Tables[0].TableName);
// Save the export table structure so that the field
// properties can be examined during the export.
DataTable exportTable2 = exportTable;
exportTable = datasetOut.Tables[0];
//---------------------------------------------------------------------
// CHANGED: CR13 (Export features performance)
// Improve the performance as much as possible when
// creating the attribute database.
//
// Turn off notifications and index maintenance whilst
// inserting the records.
exportTable.BeginLoadData();
//---------------------------------------------------------------------
// If there is only one long list then chunk
// it up into smaller lists.
if (exportFilter.Count == 1)
{
try
{
List<SqlFilterCondition> whereCond = new List<SqlFilterCondition>();
whereCond = exportFilter[0];
exportFilter = whereCond.ChunkClause(240).ToList();
}
catch { }
}
//---------------------------------------------------------------------
// FIX: 047 Break exporting attributes into chunks to avoid errors
// with excessive sql lengths.
//
outputRowCount = 0;
exportRowCount = 0;
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Set the field map indexes to the start of the array.
int[] fieldMapIndex = new int[fieldMap.Length];
for (int k = 0; k < fieldMap.Length; k++)
{
fieldMapIndex[k] = 1;
}
//---------------------------------------------------------------------
for (int j = 0; j < exportFilter.Count; j++)
{
DataRow exportRow = exportTable.NewRow();
bool rowAdded = false;
// Union the constituent parts of the export query
// together into a single SQL string.
string sql = ScratchDb.UnionQuery(targetListStr, fromClauseStr,
sortOrdinals, exportFilter[j], dataBase);
// Execute the sql to retrieve the records.
using (IDataReader reader = _viewModelMain.DataBase.ExecuteReader(sql,
_viewModelMain.DataBase.Connection.ConnectionTimeout, CommandType.Text))
{
string currIncid = String.Empty;
string prevIncid = String.Empty;
int currMatrixId = -1;
int currFormationId = -1;
int currManagementId = -1;
int currComplexId = -1;
int currBapId = -1;
int currSourceId = -1;
int currSourceDateStart = 0;
int currSourceDateEnd = 0;
List<int> matrixIds = null;
List<int> formationIds = null;
List<int> managementIds = null;
List<int> complexIds = null;
List<int> bapIds = null;
List<int> sourceIds = null;
string currSourceDateType = String.Empty;
int exportColumn;
// Read each record and process the contents.
while (reader.Read())
{
// Get the current incid.
currIncid = reader.GetString(_incidOrdinal);
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Get the current matrix id.
if (_matrixIdOrdinal != -1)
{
object matrixIdValue = reader.GetValue(_matrixIdOrdinal);
if (matrixIdValue != DBNull.Value)
currMatrixId = (int)matrixIdValue;
else
currMatrixId = -1;
}
// Get the current formation id.
if (_formationIdOrdinal != -1)
{
object FormationIdValue = reader.GetValue(_formationIdOrdinal);
if (FormationIdValue != DBNull.Value)
currFormationId = (int)FormationIdValue;
else
currFormationId = -1;
}
// Get the current Management id.
if (_managementIdOrdinal != -1)
{
object ManagementIdValue = reader.GetValue(_managementIdOrdinal);
if (ManagementIdValue != DBNull.Value)
currManagementId = (int)ManagementIdValue;
else
currManagementId = -1;
}
// Get the current Complex id.
if (_complexIdOrdinal != -1)
{
object ComplexIdValue = reader.GetValue(_complexIdOrdinal);
if (ComplexIdValue != DBNull.Value)
currComplexId = (int)ComplexIdValue;
else
currComplexId = -1;
}
// Get the current bap id (or equivalent lookup table field).
if (_bapIdOrdinal != -1)
{
object bapIdValue = reader.GetValue(_bapIdOrdinal);
if (bapIdValue != DBNull.Value)
currBapId = (int)bapIdValue;
else
currBapId = -1;
}
// Get the current source id (or equivalent lookup table field).
if (_sourceIdOrdinal != -1)
{
object sourceIdValue = reader.GetValue(_sourceIdOrdinal);
if (sourceIdValue != DBNull.Value)
currSourceId = (int)sourceIdValue;
else
currSourceId = -1;
}
//---------------------------------------------------------------------
//---------------------------------------------------------------------
// CHANGED: CR17 (Exporting date fields)
// Store all of the source date fields for use later when
// formatting the attribute data.
//
// Get the current source date start.
if ((_sourceDateStartOrdinals.Count() > 0) &&
!reader.IsDBNull(_sourceDateStartOrdinals[0]))
currSourceDateStart = reader.GetInt32(_sourceDateStartOrdinals[0]);
// Get the current source date type.
if ((_sourceDateEndOrdinals.Count() > 0) &&
!reader.IsDBNull(_sourceDateEndOrdinals[0]))
currSourceDateEnd = reader.GetInt32(_sourceDateEndOrdinals[0]);
// Get the current source date type.
if ((_sourceDateTypeOrdinals.Count() > 0) &&
!reader.IsDBNull(_sourceDateTypeOrdinals[0]))
currSourceDateType = reader.GetString(_sourceDateTypeOrdinals[0]);
//---------------------------------------------------------------------
// If this incid is different to the last record's incid
// then process all the fields.
if (currIncid != prevIncid)
{
// If the last export row has not been added then
// add it now.
if (!exportRow.IsNull(fieldMap[0][1]))
{
exportTable.Rows.Add(exportRow);
rowAdded = true;
// Increment the output row count.
outputRowCount += 1;
// Commit the outputs and update the export row count
// every 10,000 records to avoid excessive memory use.
if (outputRowCount >= 10000)
{
exportRowCount += adapterOut.Update(datasetOut);
outputRowCount = 0;
}
}
// Store the last incid.
prevIncid = currIncid;
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
matrixIds = new List<int>();
formationIds = new List<int>();
managementIds = new List<int>();
complexIds = new List<int>();
bapIds = new List<int>();
sourceIds = new List<int>();
// Reset the field map indexes to the start of the array.
for (int k = 0; k < fieldMap.Length; k++)
{
fieldMapIndex[k] = 1;
}
//---------------------------------------------------------------------
// Create a new export row ready for the next values.
exportRow = exportTable.NewRow();
rowAdded = false;
// Loop through all the fields in the field map
// to transfer the values from the input reader
// to the correct field in the export row.
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Set the export column ordinal from the current
// field map index for this field.
exportColumn = fieldMap[i][fieldMapIndex[i]];
// Increment the field map index for this field.
fieldMapIndex[i] += 1;
//---------------------------------------------------------------------
// If this field is not mapped from the input reader
// set the export table value to null.
if (fieldMap[i][0] == -1)
continue;
// Store the input value of the current column.
object inValue = reader.GetValue(fieldMap[i][0]);
// If the value is null then skip this field.
if (inValue == DBNull.Value)
continue;
// Get the properties for the current export field.
ExportField exportField = exportFields.Find(f => f.FieldOrdinal == i);
//---------------------------------------------------------------------
// FIX: 048 Enable fields to be exported using a different
// data type.
//
// Convert the input value to the output value data type and format.
object outValue;
outValue = ConvertInput(fieldMap[i][0], inValue, reader.GetFieldType(fieldMap[i][0]),
exportTable2.Columns[exportColumn].DataType,
(exportField != null) ? exportField.FieldFormat : null,
currSourceDateStart, currSourceDateEnd, currSourceDateType);
//---------------------------------------------------------------------
// If the value is not null.
if (outValue != null)
{
// Get the maximum length of the column.
int fieldLength = exportTable2.Columns[exportColumn].MaxLength;
// If the maximum length of the column is shorter
// than the value then truncate the value as it
// is transferred to the export row.
if ((fieldLength != -1) && (fieldLength < outValue.ToString().Length))
exportRow[exportColumn] = outValue.ToString().Substring(0, fieldLength);
else
exportRow[exportColumn] = outValue;
}
}
}
else
{
// Loop through all the fields in the field map
// to transfer the values from the input reader
// to the correct field in the export row.
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
// Only process fields that have multiple outputs
// specified in the field map.
if (fieldMapIndex[i] < fieldMap[i].Length)
{
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Set the export column ordinal from the current
// field map index for this field.
exportColumn = fieldMap[i][fieldMapIndex[i]];
// If the value is not null and the string value is different
// to the last string value for this incid, or, the column is
// allowed to have duplicates and the bap or source is different
// to the last bap or source, then output the value.
if (Array.IndexOf(matrixOrdinals, exportColumn) != -1)
{
if (matrixIds.Contains(currMatrixId))
continue;
}
else if (Array.IndexOf(formationOrdinals, exportColumn) != -1)
{
if (formationIds.Contains(currFormationId))
continue;
}
else if (Array.IndexOf(managementOrdinals, exportColumn) != -1)
{
if (managementIds.Contains(currManagementId))
continue;
}
else if (Array.IndexOf(complexOrdinals, exportColumn) != -1)
{
if (complexIds.Contains(currComplexId))
continue;
}
else if (Array.IndexOf(bapOrdinals, exportColumn) != -1)
{
if (bapIds.Contains(currBapId))
continue;
}
else if (Array.IndexOf(sourceOrdinals, exportColumn) != -1)
{
if (sourceIds.Contains(currSourceId))
continue;
}
// Increment the field map index for this field.
fieldMapIndex[i] += 1;
//---------------------------------------------------------------------
// If this field is not mapped from the input reader
// set the export table value to null.
if (fieldMap[i][0] == -1)
continue;
// Store the input value of the current column.
object inValue = reader.GetValue(fieldMap[i][0]);
// If the value is null then skip this field.
if (inValue == DBNull.Value)
continue;
// Get the properties for the current export field.
ExportField exportField = exportFields.Find(f => f.FieldOrdinal == i);
//---------------------------------------------------------------------
// FIX: 048 Enable fields to be exported using a different
// data type.
//
// Convert the input value to the output value data type and format.
object outValue;
outValue = ConvertInput(fieldMap[i][0], inValue, reader.GetFieldType(fieldMap[i][0]),
exportTable2.Columns[exportColumn].DataType,
(exportField != null) ? exportField.FieldFormat : null,
currSourceDateStart, currSourceDateEnd, currSourceDateType);
//---------------------------------------------------------------------
// If the value is not null.
if (outValue != null)
{
// Get the maximum length of the output column.
int fieldLength = exportTable2.Columns[exportColumn].MaxLength;
// If the maximum length of the column is shorter
// than the value then truncate the value as it
// is transferred to the export row.
if ((fieldLength != -1) && (fieldLength < outValue.ToString().Length))
exportRow[exportColumn] = outValue.ToString().Substring(0, fieldLength);
else
exportRow[exportColumn] = outValue;
}
}
}
}
//---------------------------------------------------------------------
// FIX: 046 Don't export duplicate record details for the
// same incid.
//
// Store the current ids so that they are not output again.
matrixIds.Add(currMatrixId);
formationIds.Add(currFormationId);
managementIds.Add(currManagementId);
complexIds.Add(currComplexId);
bapIds.Add(currBapId);
sourceIds.Add(currSourceId);
//---------------------------------------------------------------------
}
}
// If the last export row has not been saved then
// save it now.
if (!rowAdded && (!exportRow.IsNull(fieldMap[0][1])))
{
exportTable.Rows.Add(exportRow);
rowAdded = true;
// Increment the output row count.
outputRowCount += 1;
}
}
//---------------------------------------------------------------------
// Commit any remaining outputs and update the export row count.
exportRowCount += adapterOut.Update(datasetOut);
// Turn notifications and index maintenance back on again.
exportTable.EndLoadData();
//---------------------------------------------------------------------
// FIX: 054 Improve error reporting during exports.
//
// Exit if no records were exported.
if (exportRowCount < 1)
throw new Exception("Export query did not retrieve any rows");
return tempPath;
//---------------------------------------------------------------------
}
//---------------------------------------------------------------------
// FIX: 054 Improve error reporting during exports.
//
catch (Exception ex)
{
MessageBox.Show(String.Format("Export failed. The error message was:\n\n{0}.",
ex.Message), "HLU: Export", MessageBoxButton.OK, MessageBoxImage.Error);
// Delete the temporary database if it was created.
if (File.Exists(tempPath))
{
try { File.Delete(tempPath); }
catch { _viewModelMain.ExportMdbs.Add(tempPath); }
}
// Return a null database path as the export didn't finish.
return null;
}
//---------------------------------------------------------------------
finally
{
if ((dbOut != null) && (dbOut.Connection.State != ConnectionState.Closed))
{
try { dbOut.Connection.Close(); }
catch { }
}
}
}
private string ExportMdb(string sql, DataTable exportTable, int incidOrdinal,
List<int> fieldCountList, out int exportRowCount)
{
exportRowCount = -1;
DbOleDb dbOut = null;
string tempPath = String.Empty;
try { tempPath = Path.GetTempPath(); }
catch { tempPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); }
tempPath = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb");
try
{
if (File.Exists(tempPath)) File.Delete(tempPath);
OdbcCP32 odbc = new OdbcCP32();
odbc.CreateDatabase(tempPath);
string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", tempPath);
string defaultSchema = "";
bool promptPwd = false;
dbOut = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd,
Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders,
true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255,
Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision,
Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale);
dbOut.CreateTable(exportTable);
DataSet datasetOut = new DataSet("Export");
IDbDataAdapter adapterOut = dbOut.CreateAdapter(exportTable);
adapterOut.Fill(datasetOut);
int[] pkOrdinals = exportTable.PrimaryKey.Select(c => c.Ordinal).ToArray();
exportTable.PrimaryKey = pkOrdinals.Select(o => exportTable.Columns[o]).ToArray();
adapterOut.TableMappings.Clear();
adapterOut.TableMappings.Add(exportTable.TableName, datasetOut.Tables[0].TableName);
exportTable = datasetOut.Tables[0];
DataRow exportRow = null;
bool rowAdded = false;
using (IDataReader reader = _viewModelMain.DataBase.ExecuteReader(sql,
_viewModelMain.DataBase.Connection.ConnectionTimeout, CommandType.Text))
{
int runTotal = 0;
int[][] fieldMapTemplate = fieldCountList.Select((i, index) =>
new int[] { index, index + runTotal, i, (runTotal += i - (i > 0 ? 1 : 0)) })
.Select(e => new int[] { e[0], e[1], e[1] + e[2] }).ToArray();
int[] dupsAllowed = (from e in exportTable.Columns.Cast<DataColumn>()
let q = from c in _viewModelMain.HluDataset.incid_sources.Columns.Cast<DataColumn>()
where !Regex.IsMatch(c.ColumnName,
@"(\Aincid\z|_(importance|id)\z)", RegexOptions.IgnoreCase)
select c.ColumnName
where q.Count(n => Regex.IsMatch(e.ColumnName,
n + @"(_[0-9]+)*\z", RegexOptions.IgnoreCase)) == 1
select e.Ordinal).ToArray();
int[][] fieldMap = new int[fieldMapTemplate.Length][];
string currIncid = String.Empty;
string prevIncid = String.Empty;
while (reader.Read())
{
currIncid = reader.GetString(incidOrdinal);
if (currIncid != prevIncid)
{
prevIncid = currIncid;
fieldMap = fieldMapTemplate.Select(a => new int[] { a[0], a[1], a[2] }).ToArray();
if (exportRow != null)
{
exportTable.Rows.Add(exportRow);
rowAdded = true;
}
exportRow = exportTable.NewRow();
rowAdded = false;
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
object item = reader.GetValue(fieldMap[i][0]);
if (item != DBNull.Value)
exportRow[fieldMap[i][1]] = reader.GetValue(fieldMap[i][0]);
fieldMap[i][1]++;
}
}
else
{
for (int i = 0; i < fieldMap.GetLength(0); i++)
{
if (fieldMap[i][1] < fieldMap[i][2])
{
object item = reader.GetValue(fieldMap[i][0]);
if ((item != DBNull.Value) && (!item.Equals(exportRow[fieldMap[i][1] - 1]) ||
(Array.IndexOf(dupsAllowed, fieldMap[i][1]) != -1)))
exportRow[fieldMap[i][1]++] = item;
}
}
}
}
}
if (!rowAdded && (exportRow != null)) exportTable.Rows.Add(exportRow);
exportRowCount = adapterOut.Update(datasetOut);
return exportRowCount != -1 ? tempPath : null;
}
catch
{
if (File.Exists(tempPath))
{
try { File.Delete(tempPath); }
catch { _viewModelMain.ExportMdbs.Add(tempPath); }
}
return null;
}
finally
{
if ((dbOut != null) && (dbOut.Connection.State != ConnectionState.Closed))
{
try { dbOut.Connection.Close(); }
catch { }
}
}
}
/// <summary>
/// Handles event when Manage DSN button is clicked
/// </summary>
/// <param name="param"></param>
/// <remarks></remarks>
private void ManageDsnCommandClick(object param)
{
//DispatcherHelper.DoEvents();
OdbcCP32 odbccp32 = new OdbcCP32();
bool result = odbccp32.ManageDatasources(_windowHandle);
OnPropertyChanged("DsnList");
}
