/// <summary>
/// Returns a query to create Metadata_Category table
/// </summary>
/// <param name="tableName"><DataPrefix>_Metadata_Category_<LanguageCode></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.Metadata_Category.CategoryNId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.Metadata_Category.CategoryNId + ")," +
DIColumns.Metadata_Category.CategoryName + " varchar(255), " +
DIColumns.Metadata_Category.CategoryType + " varchar(2), " +
DIColumns.Metadata_Category.CategoryOrder + " int(4))";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.Metadata_Category.CategoryNId + " int Identity(1,1) primary key," +
DIColumns.Metadata_Category.CategoryName + " varchar(255), " +
DIColumns.Metadata_Category.CategoryType + " varchar(2))," +
DIColumns.Metadata_Category.CategoryOrder + " int)";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.Metadata_Category.CategoryNId + " counter primary key, " +
DIColumns.Metadata_Category.CategoryName + " text(255), " +
DIColumns.Metadata_Category.CategoryType + " text(2), " +
DIColumns.Metadata_Category.CategoryOrder + " Long)";
}
return RetVal;
}
/// <summary>
/// Returns a query to create Metadata_Category table
/// </summary>
/// <param name="tableName"><DataPrefix>_Metadata_Category_<LanguageCode></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.MetadataReport.MetadataReportNid + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.MetadataReport.MetadataReportNid + ")," +
DIColumns.MetadataReport.TargetNid + " int(4), " +
DIColumns.MetadataReport.CategoryNid + " int(4), " +
DIColumns.MetadataReport.Metadata + " longtext )";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.MetadataReport.MetadataReportNid + " int Identity(1,1) primary key," +
DIColumns.MetadataReport.TargetNid + " int , " +
DIColumns.MetadataReport.CategoryNid + " int ," +
DIColumns.MetadataReport.Metadata + " ntext )";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.MetadataReport.MetadataReportNid + " counter primary key, " +
DIColumns.MetadataReport.TargetNid + " long , " +
DIColumns.MetadataReport.CategoryNid + " long , " +
DIColumns.MetadataReport.Metadata + " Memo)";
}
return RetVal;
}
/// <summary>
/// Returns a query to create UT_Subgroup_Type_en table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="tableName"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(bool forOnlineDB, string tableName, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.SubgroupTypes.SubgroupTypeNId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.SubgroupTypes.SubgroupTypeNId + ")," +
DIColumns.SubgroupTypes.SubgroupTypeName + " varchar(128), " +
DIColumns.SubgroupTypes.SubgroupTypeGID + " varchar(60), " +
DIColumns.SubgroupTypes.SubgroupTypeOrder + " int(4), " +
DIColumns.SubgroupTypes.SubgroupTypeGlobal + " TINYINT )";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.SubgroupTypes.SubgroupTypeNId + " int Identity(1,1) primary key," +
DIColumns.SubgroupTypes.SubgroupTypeName + " varchar(128), " +
DIColumns.SubgroupTypes.SubgroupTypeGID + " varchar(60), " +
DIColumns.SubgroupTypes.SubgroupTypeOrder + " int , " +
DIColumns.SubgroupTypes.SubgroupTypeGlobal + " bit )";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.SubgroupTypes.SubgroupTypeNId + " counter primary key," +
DIColumns.SubgroupTypes.SubgroupTypeName + " Text(128), " +
DIColumns.SubgroupTypes.SubgroupTypeGID + " Text(60), " +
DIColumns.SubgroupTypes.SubgroupTypeOrder + " number ," +
DIColumns.SubgroupTypes.SubgroupTypeGlobal + " bit )";
}
return RetVal;
}
/// <summary>
/// Returns a query to create DBVersion table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DBMetaData.NID + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.DBMetaData.NID + ")," +
DIColumns.DBMetaData.Description + " ntext, " +
DIColumns.DBMetaData.PublisherName + " varchar(50), " +
DIColumns.DBMetaData.PublisherDate + " DateTime," +
DIColumns.DBMetaData.PublisherCountry + " varchar(50)," +
DIColumns.DBMetaData.PublisherRegion + " varchar(50)," +
DIColumns.DBMetaData.PublisherOffice + " varchar(50)," +
DIColumns.DBMetaData.AreaCount + " int(4) ," +
DIColumns.DBMetaData.IndicatorCount + " int(4)," +
DIColumns.DBMetaData.IUSCount + " int(4)," +
DIColumns.DBMetaData.TimeperiodCount + " int(4)," +
DIColumns.DBMetaData.SourceCount + " int(4)," +
DIColumns.DBMetaData.DataCount + " int(4))";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DBMetaData.NID + " int Identity(1,1) primary key," +
DIColumns.DBMetaData.Description + " ntext, " +
DIColumns.DBMetaData.PublisherName + " varchar(50), " +
DIColumns.DBMetaData.PublisherDate + " DateTime," +
DIColumns.DBMetaData.PublisherCountry + " varchar(50)," +
DIColumns.DBMetaData.PublisherRegion + " varchar(50)," +
DIColumns.DBMetaData.PublisherOffice + " varchar(50)," +
DIColumns.DBMetaData.AreaCount + " int ," +
DIColumns.DBMetaData.IndicatorCount + " int," +
DIColumns.DBMetaData.IUSCount + " int," +
DIColumns.DBMetaData.TimeperiodCount + " int," +
DIColumns.DBMetaData.SourceCount + " int," +
DIColumns.DBMetaData.DataCount + " int)";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DBMetaData.NID + " counter primary key," +
DIColumns.DBMetaData.Description + " MEMO, " +
DIColumns.DBMetaData.PublisherName + " text(50), " +
DIColumns.DBMetaData.PublisherDate + " DateTime," +
DIColumns.DBMetaData.PublisherCountry + " text(50)," +
DIColumns.DBMetaData.PublisherRegion + " text(50)," +
DIColumns.DBMetaData.PublisherOffice + " text(50)," +
DIColumns.DBMetaData.AreaCount + " number ," +
DIColumns.DBMetaData.IndicatorCount + " number," +
DIColumns.DBMetaData.IUSCount + " number," +
DIColumns.DBMetaData.TimeperiodCount + " number," +
DIColumns.DBMetaData.SourceCount + " number," +
DIColumns.DBMetaData.DataCount + " number)";
}
return RetVal;
}
/// <summary>
/// Returns sql query to update dataexist to false for all records
/// </summary>
/// <param name="serverType"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static string UpdateDataExistToFalse(DIServerType serverType, string tableName)
{
string RetVal = string.Empty;
switch (serverType)
{
case DIServerType.SqlServer:
break;
case DIServerType.MsAccess:
RetVal = "UPDATE " + tableName + " AS I SET I." + DIColumns.Indicator.DataExist + "=false";
break;
case DIServerType.Oracle:
break;
case DIServerType.MySql:
break;
case DIServerType.SqlServerExpress:
break;
case DIServerType.Excel:
break;
default:
break;
}
return RetVal;
}
/// <summary>
/// Returns a query to create RecommendedSources table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.RecommendedSources.NId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.RecommendedSources.NId + ")," +
DIColumns.RecommendedSources.DataNId + " int(4), " +
DIColumns.RecommendedSources.ICIUSLabel + " varchar(255))";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.RecommendedSources.NId + " int Identity(1,1) primary key," +
DIColumns.RecommendedSources.DataNId + " int, " +
DIColumns.RecommendedSources.ICIUSLabel + " varchar(255))";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.RecommendedSources.NId + " counter primary key, " +
DIColumns.RecommendedSources.DataNId + " Long, " +
DIColumns.RecommendedSources.ICIUSLabel + " text(255))";
}
return RetVal;
}
/// <summary>
/// Returns query to delete blank datavalue records from Data table
/// </summary>
/// <returns></returns>
public string DeleteBlankData(DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "DELETE FROM " + this.TablesName.Data;
switch (serverType)
{
case DIServerType.SqlServer:
case DIServerType.MySql:
case DIServerType.SqlServerExpress:
case DIServerType.Sqlite:
RetVal += " WHERE (" + DIColumns.Data.TextualDataValue + " is null or " + DIColumns.Data.TextualDataValue + "= '') AND " + DIColumns.Data.IsTextualData + "=1";
break;
case DIServerType.MsAccess:
RetVal += " WHERE " + DIColumns.Data.DataValue + " is null or " + DIColumns.Data.DataValue + "= '' AND " + DIColumns.Data.IsTextualData + "=TRUE";
break;
default:
RetVal += " WHERE " + DIColumns.Data.DataValue + " is null or " + DIColumns.Data.DataValue + "= '' AND " + DIColumns.Data.IsTextualData + "=TRUE";
break;
}
return RetVal;
}
/// <summary>
/// Updates data exists values
/// </summary>
public void UpdateDataExistValues()
{
DIServerType ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
DITables TablesName;
string LanguageCode = string.Empty;
try
{
// 1. set all indicators' data_exist value to false in default language table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistToFalse(ServerType, this.DBQueries.TablesName.Indicator));
// 2. set data_exist to true but where data exists
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistValues(ServerType, this.DBQueries.TablesName));
// 3. update data_exist in other language tables
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(LanguageRow[Language.LanguageCode]);
// update all Language tables except default language table
if (("_" + LanguageCode) != this.DBQueries.LanguageCode)
{
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Update.UpdateDataExistValuesInOtherLanguage(this.DBQueries.TablesName.Indicator, TablesName.Indicator));
}
}
}
catch (Exception ex)
{
DevInfo.Lib.DI_LibBAL.ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnIntoDataTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Data.TextualDataValue, tablesName.Data))
{
// Add Textual_Data_Value Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7TextualDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.IsTextualData, tablesName.Data))
{
// Add IsTextualData Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsTextualDataColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.IsMRD, tablesName.Data))
{
// Add IsMRD Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsMRDColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.IsPlannedValue, tablesName.Data))
{
// Add IsPlannedValue Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IsPlannedValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.IUNId, tablesName.Data))
{
// Add IUNId Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7IUNIdColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.ConfidenceIntervalUpper, tablesName.Data))
{
// Add ConfidenceIntervalUpper Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7ConfidenceIntervalUpperColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.ConfidenceIntervalLower, tablesName.Data))
{
// Add ConfidenceIntervalLower Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7ConfidenceIntervalLowerColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Data.MultipleSource, tablesName.Data))
{
// Add MultipleSource Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Insert.InsertDI7MultipleSourceColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns Object of ConnectionString which helps to get connection string.
/// </summary>
/// <param name="serverType">Server Type :SQL, Oracel, MySql, MsAccess, etc </param>
/// <param name="serverName">Name of the server to connect to the database. Optional for MsAccess</param>
/// <param name="portNo">Port No. Required for MySql(default portNo: 3306). </param>
/// <param name="databaseName">Database name.Set full file path for MsAccess </param>
/// <param name="userName">User name to access database.Optional for MsAccess</param>
/// <param name="password">Password to access database</param>
public DIConnectionDetails(DIServerType serverType, string serverName, string portNo, string databaseName, string userName, string password)
{
this._ServerType = serverType;
this._ServerName = serverName;
this._PortNo = portNo;
this._DbName = databaseName;
this._UserName = userName;
this._Password = password;
}
public string CheckDIUserExistence(FilterFieldType filterFieldType, string filterText, FieldSelection fieldSelection, bool editMode, int userNId, DIServerType serverType)
{
StringBuilder sbQuery = new StringBuilder();
string RetVal = string.Empty;
sbQuery.Append("SELECT " + DIUser.UserNId+ "," + DIUser.UserName + "," + DIUser.UserPWD + "," + DIUser.IsAdmin + "," + DIUser.ISloggedIn);
//- Get User table Name for Servertype
if (serverType == DIServerType.MySql)
sbQuery.Append(" FROM " + TablesName.DIUser + " ");
else
sbQuery.Append(" FROM [" + TablesName.DIUser + "] ");
// WHERE Clause
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
sbQuery.Append(" WHERE ");
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIUser.UserNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.Name:
sbQuery.Append(DIUser.UserName + " = ('" + filterText + "')");
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIUser.UserNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIUser.UserName + " <> ('" + filterText + "')");
break;
default:
break;
}
if (editMode)
sbQuery.Append(" AND User_NId <> " + userNId);
}
RetVal = sbQuery.ToString();
//--dispose
sbQuery = null;
return RetVal;
}
private void AddColumnIntoAreaTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add Data_Exist Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Area.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void CreateDocumentTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// create document table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DIDocument.Insert.CreateTable(tablesName.Document, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns object of DIConnection.
/// </summary>
/// <param name="connectionString">Connection string with all parameters</param>
/// <param name="serverType">ServerType like : Sql,MsAccess, MySql,Oracle ,etc.</param>
public DIConnection(string connectionString, DIServerType serverType)
{
this.ConnectionString = connectionString;
// get provider
this.DBType = serverType;
this.DBProvider = GetProviderInstance(serverType);
// set the server type into connection parameter object
this.SetSeverTypeInConnectionParams();
//create connnection
this.InitializeConnectionObject();
}
/// <summary>
/// Insert subgroupVal record into subgroupVal Table
/// </summary>
/// <param name="dataPrefix">Data prefix like UT_</param>
/// <param name="languageCode">Language code like _en </param>
/// <param name="subgroupVal">Subgroup val name</param>
/// <param name="subgroupValGid">SubgroupVal GId </param>
/// <param name="isGlobal">Ture/False. True if subgroupval is global otherwise false</param>
/// <returns></returns>
public static string InsertSubgroupVal(string dataPrefix, string languageCode, string subgroupVal, string subgroupValGid, bool isGlobal, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "INSERT INTO " + dataPrefix + Insert.SubgroupValTableName + languageCode + "(" + DIColumns.SubgroupVals.SubgroupVal + ","
+ DIColumns.SubgroupVals.SubgroupValGId + ","
+ DIColumns.SubgroupVals.SubgroupValGlobal + ")"
+ " VALUES('" + DIQueries.RemoveQuotesForSqlQuery(subgroupVal) + "','" + DIQueries.RemoveQuotesForSqlQuery(subgroupValGid) + "', ";
switch (serverType)
{
case DIServerType.SqlServer:
case DIServerType.Sqlite:
if (isGlobal)
{
RetVal += " 1 ) ";
}
else
{
RetVal += " 0 ) ";
}
break;
case DIServerType.MsAccess:
RetVal += isGlobal + " ) ";
break;
case DIServerType.Oracle:
break;
case DIServerType.MySql:
if (isGlobal)
{
RetVal += " 1 )";
}
else
{
RetVal += " 0 )";
}
break;
break;
case DIServerType.SqlServerExpress:
break;
case DIServerType.Excel:
break;
default:
break;
}
return RetVal;
}
private void AddColumnsIntoIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// add subgroup_nids column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertSubgroupNidsColumn(tablesName, forOnlineDB, serverType));
////// add subgroup_type_nids column
////this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertSubgroupTypeNidsColumn(tablesName, forOnlineDB, serverType));
// add data_exist column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns a query to create DBVersion table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName,bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName +
" (" + DIColumns.SDMXUser.SenderNId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.SDMXUser.SenderNId + ")," +
DIColumns.SDMXUser.IsSender + " bit ,"
+ DIColumns.SDMXUser.ID + " varchar(255), "
+ DIColumns.SDMXUser.Name + " varchar(255)), " + DIColumns.SDMXUser.ContactName + " varchar(255)), "
+ DIColumns.SDMXUser.Department + " varchar(255)), " + DIColumns.SDMXUser.Email + " varchar(255)), "
+ DIColumns.SDMXUser.Telephone + " varchar(50)), "
+ DIColumns.SDMXUser.Role + " varchar(255)), " + DIColumns.SDMXUser.Fax + " varchar(50))";
}
else
{
RetVal = "CREATE TABLE " + tableName
+ "(" + DIColumns.SDMXUser.SenderNId + " int Identity(1,1) primary key," +
DIColumns.SDMXUser.IsSender + " bit ,"
+ DIColumns.SDMXUser.ID + " nvarchar(255), "
+ DIColumns.SDMXUser.Name + " nvarchar(255)), " + DIColumns.SDMXUser.ContactName + " nvarchar(255)), "
+ DIColumns.SDMXUser.Department + " nvarchar(255)), " + DIColumns.SDMXUser.Email + " nvarchar(255)), "
+ DIColumns.SDMXUser.Telephone + " nvarchar(50)), "
+ DIColumns.SDMXUser.Role + " nvarchar(255)), " + DIColumns.SDMXUser.Fax + " nvarchar(50))";
}
}
else
{
RetVal = "CREATE TABLE " + tableName
+ " (" + DIColumns.SDMXUser.SenderNId + " counter primary key, "
+ DIColumns.SDMXUser.IsSender + " bit ,"
+ DIColumns.SDMXUser.ID + " Text(255), "
+ DIColumns.SDMXUser.Name + " Text(255), " + DIColumns.SDMXUser.ContactName + " Text(255), "
+ DIColumns.SDMXUser.Department + " Text(255), " + DIColumns.SDMXUser.Email + " Text(255), "
+ DIColumns.SDMXUser.Telephone + " Text(50), "
+ DIColumns.SDMXUser.Role + " Text(255), " + DIColumns.SDMXUser.Fax + " Text(50))";
}
return RetVal;
}
/// <summary>
/// Returns a query to create Notes table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="notesTableName"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateNotes(bool forOnlineDB, string notesTableName, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + notesTableName + " (Notes_Nid int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (Notes_Nid),Profile_NId int(4), Classification_NId int(4),Notes LongText,Notes_DateTime DateTime,Notes_Approved TINYINT)";
}
else
{
RetVal = "CREATE TABLE " + notesTableName + " (Notes_Nid int Identity(1,1) primary key,Profile_NId int, Classification_NId int, Notes nvarchar(100),Notes_DateTime DateTime ,Notes_Approved bit)";
}
}
else
{
RetVal = "CREATE TABLE " + notesTableName + " (Notes_Nid counter primary key,Profile_NId number, Classification_NId number, Notes memo,Notes_DateTime DateTime,Notes_Approved Bit)";
}
return RetVal;
}
/// <summary>
/// Returns query to create UT_notes_Data table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="tablePrefix"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateNotesData(bool forOnlineDB, string tablePrefix, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tablePrefix + "Notes_Data (Notes_Data_NId int(4) NOT NULL AUTO_INCREMENT,PRIMARY KEY (Notes_Data_NId),Notes_NId int(4),Data_NId int(4))";
}
else
{
RetVal = "CREATE TABLE " + tablePrefix + "Notes_Data (Notes_Data_NId int Identity(1,1) primary key,Notes_NId int,Data_NId int)";
}
}
else
{
RetVal = "CREATE TABLE " + tablePrefix + "Notes_Data (Notes_Data_NId counter primary key,Notes_NId number,Data_NId number)";
}
return RetVal;
}
/// <summary>
/// Returns a query to create UT_Subgroup_Val_Subgorup table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="dataPrefix"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(bool forOnlineDB,string dataPrefix, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + new DITables(dataPrefix,string.Empty).SubgroupValsSubgroup + " (" + DIColumns.SubgroupValsSubgroup.SubgroupValSubgroupNId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.SubgroupValsSubgroup.SubgroupValSubgroupNId + ")," + DIColumns.SubgroupValsSubgroup.SubgroupValNId + " int(4), " + DIColumns.SubgroupValsSubgroup.SubgroupNId + " int(4))";
}
else
{
RetVal = "CREATE TABLE " + new DITables(dataPrefix, string.Empty).SubgroupValsSubgroup + " (" + DIColumns.SubgroupValsSubgroup.SubgroupValSubgroupNId + " int Identity(1,1) primary key," + DIColumns.SubgroupValsSubgroup.SubgroupValNId + " int, " + DIColumns.SubgroupValsSubgroup.SubgroupNId + " int)";
}
}
else
{
RetVal = "CREATE TABLE " + new DITables(dataPrefix, string.Empty).SubgroupValsSubgroup + " (" + DIColumns.SubgroupValsSubgroup.SubgroupValSubgroupNId + " counter primary key," + DIColumns.SubgroupValsSubgroup.SubgroupValNId + " Long, " + DIColumns.SubgroupValsSubgroup.SubgroupNId + " Long)";
}
return RetVal;
}
/// <summary>
/// add column AvlMaxDataValue
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertDI7AvlMaxDataValueColumn(string dataPrefix, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + new DITables(dataPrefix, String.Empty).IndicatorUnitSubgroup + " ADD COLUMN " + DIColumns.Indicator_Unit_Subgroup.AvlMaxDataValue + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " Double(18,5) ";
}
else
{
RetVal += " Decimal(18,5) ";
}
}
else
{
RetVal += " Double ";
}
return RetVal;
}
/// <summary>
/// Returns sql query to update dataexists value to false for all records.
/// </summary>
/// <param name="serverType"></param>
/// <returns></returns>
public string UpdateDataExistsToFalse(DIServerType serverType)
{
string RetVal = string.Empty;
switch (serverType)
{
case DIServerType.SqlServerExpress:
case DIServerType.MySql:
case DIServerType.SqlServer:
case DIServerType.MsAccess:
RetVal = "UPDATE " + this.TablesName.IndicatorUnitSubgroup + " AS IUS SET IUS." + DIColumns.Indicator_Unit_Subgroup.DataExist + "=0";
break;
case DIServerType.Oracle:
break;
case DIServerType.Excel:
break;
default:
break;
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert SubgroupValOrder column into SubgroupVal table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string AddOrderColumn(string dataPrefix,string languageCode, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + new DITables(dataPrefix, languageCode).SubgroupVals + " ADD COLUMN [" + DIColumns.SubgroupVals.SubgroupValOrder + "] ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " int(4) ";
}
else
{
RetVal += " int ";
}
}
else
{
RetVal += " Long ";
}
return RetVal;
}
internal override string SQL_GetDataValueFilter(DIServerType DIServerType)
{
string RetVal = String.Empty;
string sDataValueFilter = base.SQL_GetDataValueFilter(DIServerType);
if (sDataValueFilter.Length > 0) // Filter string might be empty in case OpertorType.None
{
if (this._ShowIUS==true)
{
// Sample (D.IUSNId = 93 AND (D.Data_value BETWEEN 0 AND 100))
RetVal = "(D." + Data.IUSNId;
}
else
{
// Sample (IUS.Indicator_NId = 74 AND (D.Data_value BETWEEN 0 AND 100))
RetVal = "(D." + Data.IndicatorNId;
}
RetVal += "=" + this._IndicatorNId + sDataValueFilter + ")";
}
return RetVal;
}
private void AddColumnsIntoIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Indicator_Unit_Subgroup.IsDefaultSubgroup, tablesName.IndicatorUnitSubgroup))
{
// add ISDefaultsubgroup column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7IsDefaultSubgroupColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMinDataValue, tablesName.IndicatorUnitSubgroup))
{
// add AvlMinDataValue column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMinDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMaxDataValue, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMaxDataValue Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMaxDataValueColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMinTimePeriod, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMinTimePeriod Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMinTimePeriodColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Indicator_Unit_Subgroup.AvlMaxTimePeriod, tablesName.IndicatorUnitSubgroup))
{
// Add AvlMaxTimePeriod Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IUS.Insert.InsertDI7AvlMaxTimePeriodColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Returns a query to create DBVersion table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + new DITables(string.Empty, string.Empty).DBVersion + " (" + DIColumns.DBVersion.VersionNId + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.DBVersion.VersionNId + ")," + DIColumns.DBVersion.VersionNumber + " varchar(50), " + DIColumns.DBVersion.VersionChangeDate + " varchar(50), " + DIColumns.DBVersion.VersionComments + " varchar(255))";
}
else
{
RetVal = "CREATE TABLE " + new DITables(string.Empty, string.Empty).DBVersion + " (" + DIColumns.DBVersion.VersionNId + " int Identity(1,1) primary key," + DIColumns.DBVersion.VersionNumber + " varchar(50), " + DIColumns.DBVersion.VersionChangeDate + " varchar(50) , " + DIColumns.DBVersion.VersionComments + " varchar(255))";
}
}
else
{
RetVal = "CREATE TABLE " + new DITables(string.Empty, string.Empty).DBVersion + " (" + DIColumns.DBVersion.VersionNId + " counter primary key," + DIColumns.DBVersion.VersionNumber + " text(50), " + DIColumns.DBVersion.VersionChangeDate + " text(50), " + DIColumns.DBVersion.VersionComments + " text(255))";
}
return RetVal;
}
/// <summary>
/// Returns a query to create Notes_Classification table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="notesClassificationTableName"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateClassification(bool forOnlineDB, string notesClassificationTableName, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + notesClassificationTableName + " (Classification_Nid int(4) NOT NULL AUTO_INCREMENT,PRIMARY KEY (Classification_Nid),Classification_Name varchar(255))";
}
else
{
RetVal = "CREATE TABLE " + notesClassificationTableName + " (Classification_Nid int Identity(1,1) primary key,Classification_Name varchar(255))";
}
}
else
{
RetVal = "CREATE TABLE " + notesClassificationTableName + " (Classification_Nid counter primary key,Classification_Name Text(150))";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert AreaShortName column into UT_Area_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string AddAreaShortNameColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Area + " ADD COLUMN " + DIColumns.Area.AreaShortName + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(255) ";
}
else
{
RetVal += " nvarchar(255) ";
}
}
else
{
RetVal += " Text(255) ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert HighIsGood column into UT_Indicator_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertHighIsGoodColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Indicator + " ADD COLUMN " + DIColumns.Indicator.HighIsGood + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " TinyInt(1) ";
}
else
{
RetVal += " Bit ";
}
}
else
{
RetVal += " Bit ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert Data_Exist column into UT_Indicator_Unit_Subgroup table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertDataExistColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.IndicatorUnitSubgroup + " ADD COLUMN " + DIColumns.Indicator_Unit_Subgroup.DataExist + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " TinyInt(1) ";
}
else
{
RetVal += " Bit ";
}
}
else
{
RetVal += " Bit ";
}
return RetVal;
}
/// <summary>
/// Returns qurey to insert DataValue column into UT_Area_en table
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string AlterDataValueColumnDataTypeToDouble(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.Data + " Add COLUMN " + DIColumns.Data.DataValue + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " Decimal(18,5) ";
}
else
{
RetVal += " Decimal(18,5) ";
}
}
else
{
RetVal += " Double ";
}
return RetVal;
}
private void AddOrderColumn(bool forOnlineDB, DIServerType serverType)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DIQueries TempQueries;
try
{
// step1: 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);
// Add Order Column Into Subgroup_Val Table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.SubgroupVal.Insert.AddOrderColumn(TempQueries.DataPrefix, TempQueries.LanguageCode, forOnlineDB, serverType));
// Add Order Column Into Subgroup Table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Subgroup.Insert.AddOrderColumn(TempQueries.DataPrefix, TempQueries.LanguageCode, forOnlineDB, serverType));
// Add Order Column Into IC
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.AddOrderColumn(TempQueries.DataPrefix, TempQueries.LanguageCode, forOnlineDB, serverType));
}
}
}
catch (Exception)
{
}
}
/// <summary>
/// Returns a query to create Document table
/// </summary>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string CreateTable(string tableName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DIDocument.DocumentNid + " int(4) NOT NULL AUTO_INCREMENT ,PRIMARY KEY (" + DIColumns.DIDocument.DocumentNid + ")," +
DIColumns.DIDocument.DocumentType + " varchar(4), " +
DIColumns.DIDocument.ElementType + " varchar(2)," +
DIColumns.DIDocument.ElementNid + " int(4), " +
DIColumns.DIDocument.ElementDocument + " LongText )";
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DIDocument.DocumentNid + " int Identity(1,1) primary key," +
DIColumns.DIDocument.DocumentType + " varchar(4), " +
DIColumns.DIDocument.ElementType + " varchar(2)," +
DIColumns.DIDocument.ElementNid + " int ," +
DIColumns.DIDocument.ElementDocument + " image )";
}
}
else
{
RetVal = "CREATE TABLE " + tableName + " (" + DIColumns.DIDocument.DocumentNid + " counter primary key, " +
DIColumns.DIDocument.DocumentType + " text(4), " +
DIColumns.DIDocument.ElementType + " text(2)," +
DIColumns.DIDocument.ElementNid + " number ," +
DIColumns.DIDocument.ElementDocument + " OLEObject )";
}
return RetVal;
}
internal virtual string SQL_GetDataValueFilter(DIServerType DIServerType)
{
string RetVal = String.Empty;
switch (this._OpertorType)
{
case OpertorType.None:
break;
case OpertorType.EqualTo:
RetVal = " AND (" + SQL_GetNumericDataValue(DIServerType) + " = " + this._FromDataValue + ")";
break;
case OpertorType.Between:
RetVal = " AND (" + SQL_GetNumericDataValue(DIServerType) + " BETWEEN " + this._FromDataValue + " AND " + this._ToDataValue + ")";
break;
case OpertorType.GreaterThan:
RetVal = " AND (" + SQL_GetNumericDataValue(DIServerType) + " > " + this._FromDataValue + ")";
break;
case OpertorType.LessThan:
RetVal = " AND (" + SQL_GetNumericDataValue(DIServerType) + " < " + this._ToDataValue + ")";
break;
default:
break;
}
return RetVal;
}
/// <summary>
/// Add column CategoryDescription
/// </summary>
/// <param name="tablesName"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertCategoryDescriptionColumn(DITables tablesName, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + tablesName.MetadataCategory + " ADD COLUMN " + DIColumns.Metadata_Category.CategoryDescription + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(4000) ";
}
else
{
RetVal += " nvarchar(4000) ";
}
}
else
{
RetVal += " Memo ";
}
return RetVal;
}
/// <summary>
/// add Perodicity column in UT_TimePeriod column
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="languageCode"></param>
/// <param name="forOnlineDB"></param>
/// <param name="serverType"></param>
/// <returns></returns>
public static string InsertDI7PerodicityColumn(string dataPrefix, bool forOnlineDB, DIServerType serverType)
{
string RetVal = string.Empty;
RetVal = "ALTER TABLE " + new DITables(dataPrefix, string.Empty).TimePeriod + " ADD COLUMN " + DIColumns.Timeperiods.Periodicity + " ";
if (forOnlineDB)
{
if (serverType == DIServerType.MySql)
{
RetVal += " varchar(25) ";
}
else
{
RetVal += " varchar(25) ";
}
}
else
{
RetVal += " Text(25) ";
}
return RetVal;
}
internal string GetIndicatorNId_SubgroupNIds(string IndGId_SubgroupGIds, DIServerType DIServerType)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIQueries.SQL_GetConcatenatedValues("I." + DIColumns.Indicator.IndicatorNId + ",SGV." + DIColumns.SubgroupVals.SubgroupValNId, ",", Delimiter.NUMERIC_SEPARATOR, DIServerType));
sbQuery.Append(" FROM " + this.TablesName.Indicator + " AS I," + this.TablesName.SubgroupVals + " AS SGV");
sbQuery.Append(" WHERE (" + DIQueries.SQL_GetConcatenatedValues("I." + DIColumns.Indicator.IndicatorGId + ",SGV." + DIColumns.SubgroupVals.SubgroupValGId, ",", Delimiter.TEXT_SEPARATOR, DIServerType) + ") IN (" + IndGId_SubgroupGIds + ")");
RetVal = sbQuery.ToString();
return RetVal;
}
private void RenameIndicatorClassificationIUSTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (DICommon.ISColumnExistInTable(this.DBConnection, " top 1 * ", this._DBQueries.DataPrefix + "ic_ius"))
{
//-- Carate New IC_IUS table from existing table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.CreateOldICIUSTableFromExisting(this._DBQueries.DataPrefix));
//-- Delete new table
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteNewICIUSTable(this._DBQueries.DataPrefix));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Updates publisher, title & year values
/// </summary>
public void UpdatePublisherTitleYear()
{
DIServerType ServerType = this.DBConnection.ConnectionStringParameters.ServerType;
DITables TablesName;
DataTable Table;
string LanguageCode = string.Empty;
string PublisherName = string.Empty;
string ICName = string.Empty;
int Index = -1;
int ICNid = 0;
try
{
// 1.Do the following for the default langauge
// 1.1 update publisher
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdatePublisherColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
// 1.2 update year
//this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateYearColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetAllSourceColumnsWithoutPublishers());
foreach (DataRow Row in Table.Rows)
{
ICNid = Convert.ToInt32(Row[IndicatorClassifications.ICNId]);
PublisherName = Convert.ToString(Row[IndicatorClassifications.Publisher]) + "_";
ICName = Convert.ToString(Row[IndicatorClassifications.ICName]);
if (ICName.StartsWith(PublisherName))
{
ICName = ICName.Substring(PublisherName.Length);
}
Index = ICName.LastIndexOf("_");
if (Index >= 0)
{
ICName = ICName.Substring(Index + 1);
if (ICName.Length > 10)
{
ICName = string.Empty;
}
}
else
{
ICName = string.Empty;
}
// update diyear into database
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateYearColumn(this.DBQueries.TablesName.IndicatorClassifications, ICNid, ICName));
}
// 1.3 update title
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateTitleColumn(ServerType, this.DBQueries.TablesName.IndicatorClassifications));
// 2. update Publisher, title & year in other language tables
foreach (DataRow LanguageRow in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows)
{
LanguageCode = Convert.ToString(LanguageRow[Language.LanguageCode]);
// update all Language tables except default langauge table
if (("_" + LanguageCode) != this.DBQueries.LanguageCode)
{
TablesName = new DITables(this.DBQueries.DataPrefix, LanguageCode);
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdatePublisherTitleYearInOtherLanguages(this.DBQueries.TablesName.IndicatorClassifications, TablesName.IndicatorClassifications));
}
}
}
catch (Exception ex)
{
DevInfo.Lib.DI_LibBAL.ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnsIntoIndicatorTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add short_name Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertShortNameColumn(tablesName, forOnlineDB, serverType));
// Add keywords Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertKeywordsColumn(tablesName, forOnlineDB, serverType));
// Add indicator_order Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertIndicatorOrderColumn(tablesName, forOnlineDB, serverType));
// Add Data exist Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertDataExistColumn(tablesName, forOnlineDB, serverType));
// Add HighIsGood Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Indicator.Insert.InsertHighIsGoodColumn(tablesName, forOnlineDB, serverType));
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnsIntoIndicatorClassificationsTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add IC_short_name Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.ICShortName, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertICShortNameColumn(tablesName, forOnlineDB, serverType));
}
// Add publisher Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Publisher, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertPublisherColumn(tablesName, forOnlineDB, serverType));
}
// Add title Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.Title, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertTitleColumn(tablesName, forOnlineDB, serverType));
}
// Add DIYear Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.DIYear, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDIYearColumn(tablesName, forOnlineDB, serverType));
}
// Add SourceLink1 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink1, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink1Column(tablesName, forOnlineDB, serverType));
}
// Add SourceLink2 Column
if (!DICommon.ISColumnExistInTable(this._DBConnection, IndicatorClassifications.SourceLink2, tablesName.IndicatorClassifications))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertSourceLink2Column(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnIntoTimeperiodTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
// Add StartDate Column
if (!this.ISColumnExist(Timeperiods.StartDate, tablesName.TimePeriod))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7StartDateColumn(this._DBQueries.DataPrefix));
}
// Add EndDate Column
if (!this.ISColumnExist(Timeperiods.EndDate, tablesName.TimePeriod))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7EndDateColumn(this._DBQueries.DataPrefix));
}
// Add Perodicity Column
if (!this.ISColumnExist(Timeperiods.Periodicity, tablesName.TimePeriod))
{
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Timeperiod.Insert.InsertDI7PerodicityColumn(this._DBQueries.DataPrefix, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnsIntoMetadataCategoryTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(Metadata_Category.ParentCategoryNId, tablesName.MetadataCategory))
{
// check and create metadata category table
this.CheckNCreateMetadataCategoryTable(forOnlineDB, tablesName);
// Add ParentCategoryNId Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertParentCategoryNIdColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.CategoryGId, tablesName.MetadataCategory))
{
// Add CategoryGId Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertCategoryGIdColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.CategoryDescription, tablesName.MetadataCategory))
{
// Add CategoryDescription Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertCategoryDescriptionColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.IsPresentational, tablesName.MetadataCategory))
{
// Add IsPresentational Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertIsPresentationalColumn(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(Metadata_Category.IsMandatory, tablesName.MetadataCategory))
{
// Add IsMandatory Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.InsertIsMandatoryColumn(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void AddColumnsIntoIndicatorClassificationsTable(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
try
{
if (!this.ISColumnExist(IndicatorClassifications.ISBN, tablesName.IndicatorClassifications))
{
// Add ISBN Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDI7ISBNColumns(tablesName, forOnlineDB, serverType));
}
if (!this.ISColumnExist(IndicatorClassifications.Nature, tablesName.IndicatorClassifications))
{
// Add Nature Column
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Insert.InsertDI7NatureColumns(tablesName, forOnlineDB, serverType));
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void UpdateTextualAndNumericData(bool forOnlineDB, DITables tablesName, DIServerType serverType)
{
string SqlQuery = string.Empty;
const string DataValueTempColumn = "Data_Value_Temp";
try
{
//-- Create Temp Column
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " ADD COLUMN " + DataValueTempColumn + " Double";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.IsTextualData + "=1";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.IsTextualData + "=0 WHERE ISNUMERIC(" + Data.DataValue + ")";
this.DBConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.TextualDataValue + "=" + Data.DataValue + " WHERE " + Data.IsTextualData + "<>0";
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Update Numeric data
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + DataValueTempColumn + " =" + Data.DataValue + " WHERE " + Data.IsTextualData + " = 0";
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Drop DataValue col
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " DROP COLUMN " + Data.DataValue;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- Drop DataValue col
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Data.Update.AlterDataValueColumnDataTypeToDouble(tablesName, forOnlineDB, serverType));
//-- Update Numeric column into
SqlQuery = "UPDATE " + this.DBQueries.TablesName.Data + " SET " + Data.DataValue + " =" + DataValueTempColumn;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//-- DROp Temp Column
SqlQuery = "ALTER TABLE " + this.DBQueries.TablesName.Data + " DROP COLUMN " + DataValueTempColumn;
this.DBConnection.ExecuteNonQuery(SqlQuery);
//#region "//-- Exception handled:File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. --"
//DIConnectionDetails Obj = this._DBConnection.ConnectionStringParameters;
//if (this._DBConnection != null)
//{
// this._DBConnection.Dispose();
//}
//this._DBConnection = new DIConnection(Obj);
//#endregion
}
catch (Exception ex)
{
throw ex;
}
}