public static int CreateDISearchResultsTable(DIConnection dbConnection, string languageCode)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.NId + "] COUNTER, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SearchLanguage + "] Text(50) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.UnitNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsAreaNumeric + "] YESNO NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IndicatorName + "] Text(255) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Unit + "] Text(128) NOT NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Area + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DefaultSG + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRDTP + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.MRD + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaNIds + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SGNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.SourceNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.TPNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVNIds + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.DVSeries + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.Dimensions + "] Text NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.BlockAreaParentNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IUSNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.AreaParentNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.DISearchResult.Columns.IsBlockAreaRecord + "] YESNO");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
/// <summary>
/// Create new table schema of DI_Search_Result table
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="tableName"></param>
/// <param name="languageCode"></param>
/// <returns></returns>
private static int CreateSearchResultCopyTable(DIConnection dbConnection, string tableName, string languageCode)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
try
{
dbConnection.DropTable(tableName);
SBQry.Remove(0, SBQry.Length);
SBQry.Append("SELECT * INTO " + tableName);
SBQry.Append(" FROM " + QDSConstants.QDSTables.DISearchResult.TableName + "_" + languageCode);
SBQry.Append(" WHERE 1=2");
StrQry = SBQry.ToString();
RetVal = dbConnection.ExecuteNonQuery(StrQry);
}
catch (Exception)
{
}
return RetVal;
}
public static int CreateTempMRDRecordsTable(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.TempMRDRecords.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IUSNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.IndicatorNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.UnitNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.SubgroupValNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.AreaNId + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.Timeperiod + "] Text(255) NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DVCount + "] Long NULL, ");
SBQry.Append("[" + QDSConstants.QDSTables.TempMRDRecords.Columns.DV + "] Text(255) NULL ");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateSplittedListTable(DIConnection dbConnection, bool isNumericValues)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
string ColumnDataType = "Text(255)";
try
{
TblName = QDSConstants.QDSTables.SplittedList.TableName;
dbConnection.DropTable(TblName);
if (isNumericValues)
{
ColumnDataType = "Long";
}
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SplittedList.Columns.Value + "] " + ColumnDataType);
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateSearchIndicatorsTable1(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.SearchIndicators.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.Id + "] COUNTER CONSTRAINT PKey PRIMARY KEY, ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long DEFAULT 0, ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorName + "] Varchar(255) DEFAULT '' NOT NULL , ");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.ICName + "] Varchar DEFAULT '' NOT NULL ");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
public static int CreateSearchIndicatorsTable(DIConnection dbConnection)
{
int RetVal = -1;
StringBuilder SBQry = new StringBuilder();
string TblName = string.Empty;
try
{
TblName = QDSConstants.QDSTables.SearchIndicators.TableName;
dbConnection.DropTable(TblName);
SBQry.Append("Create Table " + TblName);
SBQry.Append("(");
SBQry.Append("[" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + "] Long");
SBQry.Append(")");
RetVal = dbConnection.ExecuteNonQuery(SBQry.ToString());
}
catch (Exception)
{
throw;
}
return RetVal;
}
/// <summary>
/// Copies the given sourceTable from SourceDatabase into TargetDatabase
/// </summary>
public static void CreateLinkTableForAccessDB(string targetDatabase, string sourceDatabase, string targetTable, string sourceTable)
{
// Create another connection to read SourceDataBase.
OleDbConnection Connection;
OleDbCommand Command;
string SqlString = string.Empty;
DIConnection TargetDBConnection = null;
try
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + sourceDatabase + ";Jet OLEDB:Database Password="******";Persist Security Info=False;";
Connection = new OleDbConnection(ConnectionString);
Command = new OleDbCommand();
Command.Connection = Connection;
Connection.Open();
// 1. Drop target table if already exits
// 1.1 Connect to target database
try
{
TargetDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, targetDatabase, string.Empty, string.Empty);
TargetDBConnection.DropTable(targetTable);
}
catch (Exception)
{
}
finally
{
// 1.2 Dispose connection
if (TargetDBConnection != null)
{
TargetDBConnection.Dispose();
}
}
// 2. Insert Source table into targetDatabase.targetTable
SqlString = "INSERT INTO [MS Access;Database=" + targetDatabase + ";pwd=" + DIConnectionDetails.MSAccessDefaultPassword + ";].[" + targetTable + "]" +
" SELECT * FROM " + sourceTable;
Command.CommandText = SqlString;
Command.ExecuteNonQuery();
Connection.Close();
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}