C# SqlQuery.getSql方法代码示例

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="info">
        /// The current state of the data retrieval which containts the current query and mapping set 
        /// </param>
        public void GenerateSql(DataRetrievalInfo info)
        {
            Logger.Info(Resources.InfoBeginGenerateSql);

            var seriesInfo = info as DataRetrievalInfoSeries;
            if (seriesInfo == null)
            {
                throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type");
            }

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                sql = GenerateSelect(false, seriesInfo.ComponentMapping.Values);
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(seriesInfo.MappingSet));

                AppendCachedWhere(seriesInfo, sqlQuery);

                //sqlQuery.appendSql(GenerateComplexWhere(info));

                var orderComponents = _orderedComponentBuilder.Build(seriesInfo);
                sqlQuery.appendSql(GenerateOrderBy(seriesInfo, orderComponents));
            }
            catch (DataRetrieverException)
            {
                throw;
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            info.SqlString = sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="info">
        /// The current state of the data retrieval which containts the current query and mapping set 
        /// </param>
        private static void GenerateDataSetSql(DataRetrievalInfoSeries info)
        {
            if (info.DataSetAttributes.Count == 0)
            {
                return;
            }

            MappingSetEntity mappingSet = info.MappingSet;
            Logger.Info(Resources.InfoBeginGenerateSql);

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                // Generate Query subparts
                sql = GenerateSelect(true, ConvertToMapping(info.DataSetAttributes));
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(mappingSet));

                if (string.IsNullOrEmpty(info.SqlWhereCache))
                {
                    info.SqlWhereCache = GenerateWhere(info);
                }

                sqlQuery.appendSql(info.SqlWhereCache);

            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
                //ErrorTypes.QUERY_PARSING_ERROR, Resources.ErrorUnableToGenerateSQL, ex);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            info.DataSetSqlString = sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="info">
        /// The current state of the data retrieval which containts the current query and mapping set 
        /// </param>
        public void GenerateSql(DataRetrievalInfo info)
        {

            MappingSetEntity mappingSet = info.MappingSet;
            Logger.Info(Resources.InfoBeginGenerateSql);

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                // Generate Query subparts
                sql = GenerateSelect(false, info.ComponentMapping.Values);
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(mappingSet));

                //the WHERE part
                sqlQuery.appendSql(GenerateWhere(info));

                sqlQuery.appendSql(GenerateXSOrderByLocalColumns(info));
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
                //ErrorTypes.QUERY_PARSING_ERROR, Resources.ErrorUnableToGenerateSQL, ex);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            info.SqlString = sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="groupBean">
        /// The group Bean. 
        /// </param>
        /// <param name="info">
        /// The current data retrieval state 
        /// </param>
        /// <returns>
        /// The string containing the SQL query that needs to be executed on the dissemination database, in order to return the data required by the input query 
        /// </returns>
        private static string GenerateGroupSql(GroupInformation groupBean, DataRetrievalInfoSeries info)
        {
            MappingSetEntity mappingSet = info.MappingSet;
            Logger.Info(Resources.InfoBeginGenerateSql);

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                // Generate Query subparts
                sql = GenerateSelect(true, ConvertToMapping(groupBean.ComponentMappings));
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(mappingSet));

                if (string.IsNullOrEmpty(info.SqlWhereCache))
                {
                    info.SqlWhereCache = GenerateWhere(info);
                }

                sqlQuery.appendSql(info.SqlWhereCache);

                bool bFlat = false;
                var allDimensions = DimensionAtObservation.GetFromEnum(DimensionAtObservationEnumType.All).Value;
                IBaseDataQuery baseDataQuery = (IBaseDataQuery)info.ComplexQuery ?? info.Query;
                //the Flat option will be read only when we have flat aka AllDimensions
                if (baseDataQuery.DimensionAtObservation.Equals(allDimensions))
                    Boolean.TryParse(ConfigurationManager.AppSettings["QueryFlatFormat"], out bFlat);
                if (!bFlat)
                    sqlQuery.appendSql(GenerateOrderBy(info, groupBean.ThisGroup.Dimensions));
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
                //ErrorTypes.QUERY_PARSING_ERROR, Resources.ErrorUnableToGenerateSQL, ex);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            return sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="info">
        /// The current state of the data retrieval which containts the current query and mapping set 
        /// </param>
        public void GenerateSql(DataRetrievalInfo info)
        {
            Logger.Info(Resources.InfoBeginGenerateSql);

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                sql = GenerateSelect(false, info.ComponentMapping.Values);
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(info.MappingSet));

                if (info.ComplexQuery != null)
                {
                    sqlQuery.appendSql(GenerateComplexWhere(info));
                }
                else
                {
                    sqlQuery.appendSql(GenerateWhere(info));
                }

                var orderComponents = _orderedComponentBuilder.Build(info);
                sqlQuery.appendSql(GenerateOrderBy(info, orderComponents));
            }
            catch (DataRetrieverException dex)
            {
                throw new DataRetrieverException(dex, dex.SdmxErrorCode, dex.Message);
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            info.SqlString = sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="info">
        /// The current state of the data retrieval which containts the current query and mapping set 
        /// </param>
        public void GenerateSql(DataRetrievalInfo info)
        {
            Logger.Info(Resources.InfoBeginGenerateSql);

            var seriesInfo = info as DataRetrievalInfoSeries;
            if (seriesInfo == null)
            {
                throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type");
            }

            MappingSetEntity mappingSet = info.MappingSet;

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                // Generate Query subparts
                var mappingEntities = ConvertToMapping(seriesInfo.AllComponentMappings);
                mappingEntities.Add(seriesInfo.TimeMapping);
                sql = GenerateSelect(false, mappingEntities);
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(mappingSet));

                AppendCachedWhere(seriesInfo, sqlQuery);

                sqlQuery.appendSql(GenerateOrderByLocalColumns(seriesInfo));
            }
            catch (DataRetrieverException dex)
            {
                throw new DataRetrieverException(dex, dex.SdmxErrorCode, dex.Message);
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            info.SqlString = sqlQuery.getSql();
        }

        /// <summary>
        /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
        /// </summary>
        /// <param name="groupBean">
        /// The group Bean. 
        /// </param>
        /// <param name="info">
        /// The current data retrieval state 
        /// </param>
        /// <returns>
        /// The string containing the SQL query that needs to be executed on the dissemination database, in order to return the data required by the input query 
        /// </returns>
        private static string GenerateGroupSql(GroupInformation groupBean, DataRetrievalInfoSeries info)
        {
            MappingSetEntity mappingSet = info.MappingSet;
            Logger.Info(Resources.InfoBeginGenerateSql);

            SqlQuery sqlQuery = new SqlQuery();
            string sql = string.Empty;

            try
            {
                // Generate Query subparts
                sql = GenerateSelect(true, ConvertToMapping(groupBean.ComponentMappings));
                sqlQuery.appendSql(sql);

                sqlQuery.appendSql(GenerateFrom(mappingSet));

                if (string.IsNullOrEmpty(info.SqlWhereCache))
                {
                    info.SqlWhereCache = GenerateWhere(info);
                }

                sqlQuery.appendSql(info.SqlWhereCache);

                // In SDMX Group we don't care if it is flat or not.
                sqlQuery.appendSql(GenerateOrderBy(info, groupBean.ThisGroup.Dimensions));
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
                throw new DataRetrieverException(ex,
                    SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
                    Resources.ErrorUnableToGenerateSQL);
                //ErrorTypes.QUERY_PARSING_ERROR, Resources.ErrorUnableToGenerateSQL, ex);
            }

            // log for easy debug
            Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql));
            Logger.Info(Resources.InfoEndGenerateSql);

            return sqlQuery.getSql();
        }