C# DIConnection.ExecuteNonQuery方法代码示例

        /// <summary>
        /// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue 
        /// and merge both column value into new column Data_Value
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="dbQueries"></param>
        public static void MergeTextualandNumericDataValueColumn(DIConnection dbConnection, DIQueries dbQueries)
        {
            try
            {
                if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
                {

                    //1. Rename Textual_Data_Value & Data_value  to orgTextual_Data_value & orgData_Value respectively
                    RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo");
                    RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double ");
                    System.Threading.Thread.Sleep(100);
                    //2. Create new column Data_Value of memo data type
                    string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL";
                    dbConnection.ExecuteNonQuery(SqlQuery);
                    System.Threading.Thread.Sleep(10);
                    //3. Merge all data values into Data_Value column
                    SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn;
                    dbConnection.ExecuteNonQuery(SqlQuery);

                    SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true;
                    dbConnection.ExecuteNonQuery(SqlQuery);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void ClearIcon(DIConnection dbConnection, string dataPrefix, IconElementType elementType)
        {
            try
            {
                dbConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Icon.Delete.ClearIcon(dataPrefix, DIIcons.Elements[elementType].ToString()));

            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.ToString());
            }
        }

        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 and insert into TmpDI7SearchAreas table
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="searchAreas"></param>
        /// <param name="isSearchForQS"></param>
        public static void CreateTmpAreaSearchTbl(DIConnection dbConnection, string searchAreas, bool isSearchForQS)
        {
            StringBuilder SBQry = new StringBuilder();
            string StrQry = string.Empty;

            string SearchLanguage = string.Empty;

            try
            {
                #region "-- Create table schema --"

                if (isSearchForQS)
                {
                    CacheUtility.CreateSearchAreasTable(dbConnection, false);
                }
                else
                {
                    CacheUtility.CreateSearchAreasTable(dbConnection, true);
                }

                #endregion

                if (!string.IsNullOrEmpty(searchAreas))
                {
                    CacheUtility.GetSplittedList(dbConnection, searchAreas, ",", false);

                    SBQry.Remove(0, SBQry.Length);

                    SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchAreas.TableName);
                    SBQry.Append(" SELECT List." + QDSConstants.QDSTables.SplittedList.Columns.Value + " as AreaNId");
                    SBQry.Append(" FROM " + QDSConstants.QDSTables.SplittedList.TableName + " List");

                    StrQry = SBQry.ToString();

                    dbConnection.ExecuteNonQuery(StrQry);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool CreateMetadataFromSDMX(string templateFileName,string tempFolderPath, string languageCode,string sdmxRegistryUrl)
        {
            bool RetVal = true;
            DIConnection DBConnection = null;
            DIQueries DBQueries;
            try
            {
                //create temp template file
                DIDatabase TempTemplateFile = new DIDatabase(templateFileName, "UT_", languageCode);

                // create database object
                DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
                DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), languageCode);

                // delete default categories from blank template
                DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(DBQueries.TablesName.MetadataCategory, string.Empty));

                // import data from SDMX
                BaseSDMXHelper HelperObj = new BaseSDMXHelper(DBConnection, DBQueries);
                string TempXmlFile = tempFolderPath + "\\IMPORT_MSDXML_" + this.GetDateTimeStamp() + DICommon.FileExtension.XML;

                HelperObj.GenerateMetadataXMLFromSDMXWebservice(sdmxRegistryUrl, TempXmlFile);

            }
            catch (Exception ex)
            {
                RetVal = false;

            }
            finally
            {
                // dispose database object
                if (DBConnection != null)
                {
                    DBConnection.Dispose();
                }

            }

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

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

 /// <summary>
 /// Creates Icon tables for all available language
 /// </summary>
 /// <param name="dbConnection"></param>
 /// <param name="dbQueries"></param>
 /// <param name="forOnlineDB"></param>
 public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
 {
     if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
     {
         try
         {
             //-- create Icon table
             dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
         }
         catch (Exception ex)
         {
             throw new ApplicationException(ex.Message);
         }
     }
 }

        /// <summary>
        /// Create and insert into TmpDI7SearchIndicators table
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="tableNames"></param>
        /// <param name="searchIndicators"></param>
        /// <param name="isSearchForQS"></param>
        public static void CreateTmpIndSearchTbl(DIConnection dbConnection, DITables tableNames, string searchIndicators, bool isSearchForQS)
        {
            StringBuilder SBQry = new StringBuilder();
            string StrQry = string.Empty;

            string SearchLanguage = string.Empty;

            try
            {
                CacheUtility.CreateSearchIndicatorsTable(dbConnection);

                if (!string.IsNullOrEmpty(searchIndicators))
                {
                    CacheUtility.GetSplittedList(dbConnection, searchIndicators, ",", true);

                    SBQry.Remove(0, SBQry.Length);
                    SBQry.Append("INSERT INTO " + QDSConstants.QDSTables.SearchIndicators.TableName);
                    SBQry.Append(" (" + QDSConstants.QDSTables.SearchIndicators.Columns.IndicatorNId + ")");
                    SBQry.Append(" SELECT Ind."+ Indicator.IndicatorNId +" As IndicatorNId FROM "+ tableNames.Indicator +" As Ind");
                    SBQry.Append(" INNER JOIN " + QDSConstants.QDSTables.SplittedList.TableName + " L");
                    SBQry.Append(" ON Ind." + Indicator.IndicatorNId + " = L." + QDSConstants.QDSTables.SplittedList.Columns.Value);
                    StrQry = SBQry.ToString();
                    dbConnection.ExecuteNonQuery(StrQry);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static void DeleteTempTables(DIConnection connection)
        {
            try
            {

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Indicator);

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Unit);

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Subgroup);

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupType);
                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.SubgroupVals);

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.Area);

                connection.ExecuteNonQuery("DROP TABLE " + MergetTemplateConstants.TempTable.IndicatorClassification);

            }
            catch (Exception)
            { }
        }

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

    private void UpdateMRDTable(DIConnection ObjDIConnection)
    {
        string Query = string.Empty;
        string TempTableName = "temp1";
        string DTData = ObjDIConnection.DIDataSetDefault() + "Data";
        // 1. drop temp1 table
        try
        {
            ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
        }
        catch (Exception)
        {

        }

        // 2. Create Temp1 table for IsMRD calculation
        Query = "Select MRDTable.*, T2.Timeperiod_nid into " + TempTableName + " from  ( SELECT  d.IUSNId, d.Area_NId, MAX(t.TimePeriod) AS timeperiod  FROM " + ObjDIConnection.DIDataSetDefault() + "Data" + " d," + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " t WHERE d.TimePeriod_NId= t.TimePeriod_NId GROUP BY d.IUSNId,d.Area_NId) AS MRDTable , " + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " T2 where MRDTable.timeperiod=T2.Timeperiod";
        ObjDIConnection.ExecuteNonQuery(Query);

        // 3. set IsMrd to false in data table
        Query = "UPDATE  " + ObjDIConnection.DIDataSetDefault() + "Data" + " SET IsMRD=0, MultipleSource=0";
        ObjDIConnection.ExecuteNonQuery(Query);

        // 4. update IsMrd in DataTable
        Query = "UPDATE " + DTData + " SET " + DTData + ".IsMRD=1, " + DTData + ".MultipleSource=1 FROM temp1 INNER JOIN " + DTData + " ON temp1.IUSNId = " + DTData + ".IUSNId AND temp1.Timeperiod_nid = " + DTData + ".TimePeriod_NId AND temp1.Area_NId = " + DTData + ".Area_NId";
        ObjDIConnection.ExecuteNonQuery(Query);

        //4. drop table
        ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
    }

    // this method is used for creating cms database if database not exist
    /// <summary>
    /// Create CMS database for adaptation If Database Not exise
    /// </summary>
    /// <param name="CMSDatabaseName">Name of the created Cms Database</param>
    /// <returns>True if database created or already exist</returns>
    public static bool CheckNCreateCMSDatabase(out string CMSDatabaseName)
    {
        bool RetVal = false;
        DIConnection ObjDIConnection = null;
        FileInfo ScriptFile;
        string DbScripts = string.Empty;
        string LngDbScripts = string.Empty;
        string DBConn = string.Empty;
        string[] DBConnArr;
        string DbNId = string.Empty;
        string Password = string.Empty;
        string DatabaseName = string.Empty; // Name of Adaptation Database
        CMSDatabaseName = string.Empty; // Name of CMS Database
        string DatabasePath = string.Empty;
        try
        {
            // Get Database NID of Default Database
            DbNId = Global.GetDefaultDbNId();
            // If Database NID is null or empty break further execution of code
            if (string.IsNullOrEmpty(DbNId))
            {
                RetVal = false;
                return RetVal;
            }
            //Get connection details of database
            DBConnArr = Global.GetDbNConnectionDetails(DbNId, string.Empty);
            // If Database returned database details are less than 6 break further execution of code
            if (DBConnArr.Length < 6)
            {
                RetVal = false;
                return RetVal;
            }
            //Get Decrypted password
            Password = DBConnArr[6].ToString();
            //Get Adaptation Database name
            DatabaseName = DBConnArr[4].ToString();
            //Name of CMS Database
            CMSDatabaseName = DatabaseName + "_CMS";
            // Create Database connection
            ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[2])), DBConnArr[3].ToString(), string.Empty, DatabaseName, DBConnArr[5].ToString(), Password);

            //Create and Execute database creation script
            ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsDbCreationFile));
            // If Script File not exist in the folder break further execution of code
            if (!ScriptFile.Exists)
            {
                RetVal = false;
                return RetVal;
            }
            // get path of database file from web config file
            DatabasePath = Global.CmsDataBasePath;// ConfigurationManager.AppSettings[Constants.WebConfigKey.CmsDataBasePath].ToString();

            // This Script file contains sql syntax to creates database, if database is not already existing in database
            // Replace DBName with new database name
            DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName).Replace("GO", "");
            DbScripts = DbScripts.Replace("CmsDataBasePath", DatabasePath);
            // Execute script file to check and create database schema
            ObjDIConnection.ExecuteNonQuery(DbScripts);
            // Close file object
            ScriptFile.OpenText().Close();
            //Create and Execute database tables creation script
            ScriptFile = new FileInfo(Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, Global.CmsTableDefinition));
            // If Script File not exist in the folder break further execution of code
            if (!ScriptFile.Exists)
            {
                RetVal = false;
                return RetVal;
            }
            // This Script file contains sql syntax to creates datatables, if datatables are not already existing in database
            DbScripts = ScriptFile.OpenText().ReadToEnd().Replace("DBName", CMSDatabaseName);//.Replace("GO", "\nGO");
            if (DbScripts.Contains("--GO"))
            {
                List<string> ListQuery = new List<string>();
                ListQuery.AddRange(Global.SplitString(DbScripts, "--GO"));
                foreach (string StrQuery in ListQuery)
                {
                    // Execute script file to create database Tables
                    ObjDIConnection.ExecuteNonQuery(StrQuery);
                }
            }
            else
            {
                ObjDIConnection.ExecuteNonQuery(DbScripts);
            }
            // Close file object
            ScriptFile.OpenText().Close();
            // Set Retval to true
            RetVal = true;
            // Dispose connection object
            ObjDIConnection.Dispose();
        }
        catch (Exception ex)
        {
            RetVal = false;
//.........这里部分代码省略.........