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