private void CreateDBMetaTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
DBMetadataTableBuilder DBMetadataTblBuilder;
DIQueries TempQueries;
try
{
// create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, TempQueries);
if (DBMetadataTblBuilder.IsDBMetadataTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.CreateTable(TableNames.DBMetadata,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
// insert blank records with counts
// reset DBMetadata builder with main DIQuerie's object
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, this._DBQueries);
DBMetadataTblBuilder.InsertRecord(string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
DBMetadataTblBuilder.GetNUpdateCounts();
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Returns true/false. True if Database is in valid format otherwise false.
/// </summary>
/// <param name="forOnlineDB"></param>
/// <returns></returns>
public override bool IsValidDB(bool forOnlineDB)
{
bool RetVal = false;
DBMetadataTableBuilder DBMetadataBuilder = new DBMetadataTableBuilder(this._DBConnection, this._DBQueries);
RetVal = DBMetadataBuilder.IsDBMetadataTableExists();
return RetVal;
}
public static bool CompactNUpdateOnlineDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries)
{
bool RetVal = false;
DIConnectionDetails SourceDBConnectionDetails;
DBMetadataTableBuilder DBMetadataTable;
DIDatabase DBDatabase;
string DataPrefix = string.Empty;
try
{
if (sourceDBConnection != null)
{
// update counts in DBMetadata table( only if database/template is in DI6 format)
if (dbQueries == null)
{
DataPrefix = sourceDBConnection.DIDataSetDefault();
dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix));
}
DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries);
if (DBMetadataTable.IsDBMetadataTableExists())
{
DBMetadataTable.GetNUpdateCounts();
}
if (DIDatabase.SeperateDataValueColumn)
{
// Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries);
}
// update indicator unit and subgroup nids in Data table
DBDatabase = new DIDatabase(sourceDBConnection, dbQueries);
DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData();
// remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table).
DBDatabase.RemoveFootnoteNIdsInconsistencyInData();
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
//public void Dispose()
//{
// if (this.DBConnection != null)
// {
// this.DBConnection.Dispose();
// }
//}
/// <summary>
/// Disposes the current object
/// </summary>
public void Dispose()
{
DBMetadataTableBuilder DBMetadataTable;
try
{
if (this.DBConnection != null)
{
// update counts in DBMetadata table( only if database/template is in DI6 format)
DBMetadataTable = new DBMetadataTableBuilder(this.DBConnection, this.DBQueries);
if (DBMetadataTable.IsDBMetadataTableExists())
{
DBMetadataTable.GetNUpdateCounts();
}
// update database name in DB_Available table
this.DBConnection.InsertNewDBFileName(this.DBQueries.DataPrefix, DICommon.RemoveQuotes(System.IO.Path.GetFileName(this.FileNameWPath)));
//update indicator unit and subgroup nids in Data table
this.UpdateIndicatorUnitSubgroupNIDsInData();
// dispose source database connection
this.DBConnection.Dispose();
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Compact the database mainly Access database(.mdb) and save as specified destination file.
/// </summary>
///<param name="sourceDBConnection"> Source database connection</param>
/// <param name="destFilePath">destination file name with path.</param>
/// <param name="disposeConnection"> Set true when calling this methods for finish process otherwise false.For only saving the database, pass False and for finish , pass true. </param>
/// <returns>true, if success.</returns>
/// <remarks>Before calling this function,dont dispose source database connection </remarks>
public static bool CompactDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries, string destFilePath, bool disposeConnection)
{
bool RetVal = false;
bool IsFileOpen = false;
DIConnectionDetails SourceDBConnectionDetails;
DBMetadataTableBuilder DBMetadataTable;
DIDatabase DBDatabase;
string SourceDBNameWPath;
JRO.JetEngine je;
string DataPrefix = string.Empty;
DI7MetadataCategoryBuilder DI7MetadataCategory;
// -- NOTE: USE sDestFile only when sDestFile <> sSourceDB
try
{
if (sourceDBConnection != null && !string.IsNullOrEmpty(destFilePath))
{
//Drop index for ut_data table for IUSNID, areanid column
DIDatabase.DropDefaultIndex(sourceDBConnection);
// update counts in DBMetadata table( only if database/template is in DI6 format)
if (dbQueries == null)
{
DataPrefix = sourceDBConnection.DIDataSetDefault();
dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix));
}
DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries);
if (DBMetadataTable.IsDBMetadataTableExists())
{
DBMetadataTable.GetNUpdateCounts();
}
//Updating XSLT from Resourse File into database.
DI7MetadataCategory = new DI7MetadataCategoryBuilder(sourceDBConnection, dbQueries);
DI7MetadataCategory.UpdateXSLT(dbQueries.DataPrefix);
// update database name in DB_Available table
sourceDBConnection.InsertNewDBFileName(dbQueries.DataPrefix, DICommon.RemoveQuotes(System.IO.Path.GetFileName(destFilePath)));
if (DIDatabase.SeperateDataValueColumn)
{
// Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries);
}
// update indicator unit and subgroup nids in Data table
DBDatabase = new DIDatabase(sourceDBConnection, dbQueries);
DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData();
// remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table).
DBDatabase.RemoveFootnoteNIdsInconsistencyInData();
// Update auto calculated columns ( IC table - Publisher, Year & title ,Indicator table- Data_Exists, area table - data_exist, IUS table - subgroup_nids & data_exist) into the database/template
DBDatabase.UpdateAutoCalculatedFieldsInTables();
// Update auto calculated column of DI7
DBDatabase.UpdateAutoCalculatedDI7FieldsInTables();
//Update those subgroupVals which is not associated with any subgroup type.Then insert association with others for those subgroup
DBDatabase.UpdateSubgroupValsInOthersSGDimensionInTables();
//Create index for ut_data table for IUSNID, areanid column
DIDatabase.CreateDefaultIndex(sourceDBConnection);
// dispose source database connection
SourceDBConnectionDetails = sourceDBConnection.ConnectionStringParameters;
SourceDBNameWPath = SourceDBConnectionDetails.DbName;
sourceDBConnection.Dispose();
System.Threading.Thread.Sleep(10);
try
{
if (File.Exists(destFilePath))
{
File.SetAttributes(destFilePath, FileAttributes.Normal);
File.Delete(destFilePath);
}
}
catch { }
//-- Copy SourceFile to temp file so that any existing connection on database shall not stop compact database process.
//string TempFile = DICommon.GetValidFileName(DateTime.Now.ToString()) + Path.GetExtension(SourceDBNameWPath);
//File.Copy(SourceDBNameWPath, TempFile, true);
try
{
// compacting the database
je = new JRO.JetEngine();
je.CompactDatabase("Data Source=\"" + SourceDBNameWPath + "\";Jet OLEDB:Database Password="******"Data Source=\"" + destFilePath + "\";Jet OLEDB:Database Password="******"Database already exists"))
IsFileOpen = true;
}
// reconnect to source database
if (!disposeConnection)
{
sourceDBConnection = new DIConnection(SourceDBConnectionDetails);
}
if (IsFileOpen == false)
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
