/// <summary>
        /// The method retrieves the <see cref="TranscodingRulesEntity"/> object
        /// by the transcoding system identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="sysId">
        /// The mapping set with the specified system identifier
        /// </param>
        /// <param name="timeDimensionMapping">
        /// The TRANSCODING.TR_ID value of the TimeDimension, if there is TimeDimension and is transcoded; otherwise set to null 
        /// </param>
        /// <param name="mappingsMap">
        /// The map between COMPONENT_MAPPING.MAP_ID value and MappingEntity 
        /// </param>
        private static void GetTranscodingRulesByMapSetId(Database mappingStoreDb, long sysId, MappingEntity timeDimensionMapping, IDictionary<long, MappingEntity> mappingsMap)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
            string timeDimensionTranscodingIdParam = mappingStoreDb.BuildParameterName(ParameterNameConstants.TranscodingId);

            object timedimensionID = timeDimensionMapping == null ? (object)null : timeDimensionMapping.Transcoding.SysId;

            // holds the dsd codes by rule
            var componentsByRule = new Dictionary<long, CodeCollection>();

            string sql = string.Format(
               CultureInfo.InvariantCulture,
               MappingStoreSqlStatements.TranscodingRulesDsdCodes,
               paramId,
               timeDimensionTranscodingIdParam);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sql))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.TranscodingId, DbType.Int64, timedimensionID);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    int pos = 0;

                    long prevMapId = -1;

                    TranscodingRulesEntity currentRules = null;
                    long firstTrRuleID = -1;
                    long curTrRuleID = -1;
                    long prevTrRuleID = -1;
                    
                    var componentList = new CodeCollection();

                    int ruleIDIdx = dataReader.GetOrdinal("TR_RULE_ID");
                    int componentIdx = dataReader.GetOrdinal("COMPONENT");
                    int codeIdx = dataReader.GetOrdinal("CODE");
                    int mapIdIdx = dataReader.GetOrdinal("MAP_ID");
                    while (dataReader.Read())
                    {
                        long curMapId = DataReaderHelper.GetInt64(dataReader, mapIdIdx);
                        if (prevMapId != curMapId || currentRules == null)
                        {
                            prevMapId = curMapId;
                            if (curTrRuleID > -1)
                            {
                                componentsByRule.Add(curTrRuleID, componentList);
                            }

                            componentList = new CodeCollection();
                            firstTrRuleID = -1;
                            prevTrRuleID = -1;
                            pos = 0;
                            MappingEntity curretMapping = mappingsMap[curMapId];
                            currentRules = new TranscodingRulesEntity();
                            curretMapping.Transcoding.TranscodingRules = currentRules;
                        }

                        curTrRuleID = DataReaderHelper.GetInt64(dataReader, ruleIDIdx);
                        if (firstTrRuleID == -1 || firstTrRuleID == curTrRuleID)
                        {
                            firstTrRuleID = curTrRuleID;
                            prevTrRuleID = curTrRuleID;
                            currentRules.AddComponent(DataReaderHelper.GetInt64(dataReader, componentIdx), pos);

                            pos++;
                        }
                        else if (prevTrRuleID != curTrRuleID)
                        {
                            componentsByRule.Add(prevTrRuleID, componentList);
                            componentList = new CodeCollection();
                            prevTrRuleID = curTrRuleID;
                        }

                        componentList.Add(DataReaderHelper.GetString(dataReader, codeIdx));
                    }

                    if (curTrRuleID > -1)
                    {
                        componentsByRule.Add(curTrRuleID, componentList);
                    }
                }
            }

            if (componentsByRule.Count > 0)
            {
                sql = string.Format(
              CultureInfo.InvariantCulture,
              MappingStoreSqlStatements.TranscodingRulesLocalCodes,
              paramId,
              timeDimensionTranscodingIdParam);
                using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sql))
                {
                    mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
                    mappingStoreDb.AddInParameter(command, ParameterNameConstants.TranscodingId, DbType.Int64, timedimensionID);

                    using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                    {
                        long prevMapId = -1;

                        int pos = 0;
                        TranscodingRulesEntity currentRules = null;
                        long firstTrRuleID = -1;
                        long curTrRuleID = -1;
                        long prevTrRuleID = -1;
                        var columnList = new CodeCollection();

                        int ruleIDIdx = dataReader.GetOrdinal("TR_RULE_ID");
                        int columnIdx = dataReader.GetOrdinal("LOCAL_COLUMN");
                        int codeIdx = dataReader.GetOrdinal("CODE");
                        int mapIdIdx = dataReader.GetOrdinal("MAP_ID");
                        while (dataReader.Read())
                        {
                            long curMapId = DataReaderHelper.GetInt64(dataReader, mapIdIdx);
                            if (prevMapId != curMapId || currentRules == null)
                            {
                                prevMapId = curMapId;
                                if (curTrRuleID > -1 && currentRules != null)
                                {
                                    currentRules.Add(columnList, componentsByRule[curTrRuleID]);
                                }

                                firstTrRuleID = -1;
                                prevTrRuleID = -1;
                                pos = 0;
                                columnList = new CodeCollection();
                                MappingEntity curretMapping = mappingsMap[curMapId];
                                currentRules = curretMapping.Transcoding.TranscodingRules;
                            }

                            curTrRuleID = DataReaderHelper.GetInt64(dataReader, ruleIDIdx);
                            if (firstTrRuleID == -1 || firstTrRuleID == curTrRuleID)
                            {
                                firstTrRuleID = curTrRuleID;
                                prevTrRuleID = curTrRuleID;
                                currentRules.AddColumn(DataReaderHelper.GetInt64(dataReader, columnIdx), pos);
                                pos++;
                            }
                            else if (prevTrRuleID != curTrRuleID)
                            {
                                currentRules.Add(columnList, componentsByRule[prevTrRuleID]);
                                columnList = new CodeCollection();
                                prevTrRuleID = curTrRuleID;
                            }

                            columnList.Add(DataReaderHelper.GetString(dataReader, codeIdx));
                        }

                        if (curTrRuleID > -1 && currentRules != null)
                        {
                            currentRules.Add(columnList, componentsByRule[curTrRuleID]);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// The method retrieves the <see cref="DsdEntity"/> object
        /// by the data flow system identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="sysId">
        /// The data flow with the specified system identifier
        /// </param>
        /// <returns>
        /// The populated <see cref="DsdEntity"/>object
        /// </returns>
        private static DsdEntity GetDsdByDataFlowSysId(Database mappingStoreDb, long sysId)
        {
            DsdEntity ret = null;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT DSD.DSD_ID, ART.ID, ART.VERSION, ART.AGENCY ");
            sqlCommand.Append("FROM DSD, DATAFLOW DF, ARTEFACT_VIEW ART ");
            sqlCommand.Append("WHERE DF.DSD_ID = DSD.DSD_ID ");
            sqlCommand.Append("AND DSD.DSD_ID = ART.ART_ID ");
            sqlCommand.AppendFormat("AND DF.DF_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    // we expect only 1 record here
                    if (dataReader.Read())
                    {
                        ret = new DsdEntity(DataReaderHelper.GetInt64(dataReader, "DSD_ID"))
                                  {
                                      Id = DataReaderHelper.GetString(dataReader, "ID"),
                                      Version = DataReaderHelper.GetString(dataReader, "VERSION"),
                                      Agency = DataReaderHelper.GetString(dataReader, "AGENCY")
                                  };
                    }
                }
            }

            if (ret == null)
            {
                throw new MappingStoreException(
                    string.Format(
                        CultureInfo.InvariantCulture, 
                        ErrorMessages.MappingStoreNoEntityFormat2, 
                        typeof(DsdEntity), 
                        sysId));
            }

            // populate the Dsd fields by the correct components
            PoulateDsdComponentsByDsdSysId(mappingStoreDb, ret.SysId, ref ret);

            return ret;
        }
        /// <summary>
        /// This method retrieves a list of <see cref="MappingEntity"/> objects
        /// by the mapping set system identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="mappingSet">
        /// The mapping set
        /// </param>
        private static void GetMappingsByMappingSetSysId(Database mappingStoreDb, MappingSetEntity mappingSet)
        {
            long sysId = mappingSet.SysId;

            var dictionary = new Dictionary<long, MappingEntity>();

            var componentMap = new Dictionary<long, ComponentEntity>();

            bool transcoded = false;

            MappingEntity timeDimensionMapping = null;

            AddToComponentMap(mappingSet.Dataflow.Dsd.Dimensions, componentMap);
            if (mappingSet.Dataflow.Dsd.TimeDimension != null)
            {
                componentMap.Add(mappingSet.Dataflow.Dsd.TimeDimension.SysId, mappingSet.Dataflow.Dsd.TimeDimension);
            }

            componentMap.Add(mappingSet.Dataflow.Dsd.PrimaryMeasure.SysId, mappingSet.Dataflow.Dsd.PrimaryMeasure);

            AddToComponentMap(mappingSet.Dataflow.Dsd.Attributes, componentMap);
            AddToComponentMap(mappingSet.Dataflow.Dsd.CrossSectionalMeasures, componentMap);

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();

            // select
            sqlCommand.Append("SELECT CMAP.MAP_ID, CMAP.TYPE, CMAP.CONSTANT, CMAPCOMP.COMP_ID, TR.TR_ID, TR.EXPRESSION ");
           
            // from1
            sqlCommand.Append("FROM COMPONENT_MAPPING CMAP INNER JOIN  MAPPING_SET MSET ON MSET.MAP_SET_ID = CMAP.MAP_SET_ID INNER JOIN COM_COL_MAPPING_COMPONENT CMAPCOMP ON CMAPCOMP.MAP_ID = CMAP.MAP_ID ");
            sqlCommand.Append(" LEFT OUTER JOIN TRANSCODING TR ON TR.MAP_ID = CMAP.MAP_ID ");

            // where
            sqlCommand.AppendFormat(" WHERE MSET.MAP_SET_ID = {0} ", paramId);
            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    int mapID = dataReader.GetOrdinal("MAP_ID");
                    int type = dataReader.GetOrdinal("TYPE");
                    int constant = dataReader.GetOrdinal("CONSTANT");
                    int compIDIdx = dataReader.GetOrdinal("COMP_ID");
                    int tridIdx = dataReader.GetOrdinal("TR_ID");
                    int exprIdx  = dataReader.GetOrdinal("EXPRESSION");
                    while (dataReader.Read())
                    {
                        // get existing or create new mapping object
                        long mapId = DataReaderHelper.GetInt64(dataReader, mapID);
                        MappingEntity mapping;
                        if (!dictionary.TryGetValue(mapId, out mapping))
                        {
                            // create new mapping and append it to the mapping set
                            mapping = new MappingEntity(mapId) { MappingType = DataReaderHelper.GetString(dataReader, type), Constant = DataReaderHelper.GetString(dataReader, constant) };

                            long trid = DataReaderHelper.GetInt64(dataReader, tridIdx);
                            if (trid > 0)
                            {
                                mapping.Transcoding = new TranscodingEntity(trid) { Expression = DataReaderHelper.GetString(dataReader, exprIdx) };
                                transcoded = true;
                            }

                            dictionary.Add(mapId, mapping);
                            mappingSet.Mappings.Add(mapping);
                        }

                        long componentSysId = DataReaderHelper.GetInt64(dataReader, compIDIdx);

                        ComponentEntity component;
                        if (componentMap.TryGetValue(componentSysId, out component))
                        {
                            mapping.Components.Add(component);
                            if (component.Equals(mappingSet.Dataflow.Dsd.TimeDimension) && mapping.Transcoding != null)
                            {
                                timeDimensionMapping = mapping;
                            }
                        }
                    }
                }
            }

            // second select
            sqlCommand = new StringBuilder();
            sqlCommand.Append(
                "SELECT CMAP.MAP_ID, CMAP.TYPE, CMAP.CONSTANT, DSCOL.COL_ID, DSCOL.NAME, DSCOL.DS_ID ");
            sqlCommand.Append(
                "FROM COMPONENT_MAPPING CMAP, MAPPING_SET MSET, COM_COL_MAPPING_COLUMN CMAPCOL, DATASET_COLUMN DSCOL ");
            sqlCommand.Append("WHERE MSET.MAP_SET_ID = CMAP.MAP_SET_ID ");
            sqlCommand.Append("AND CMAP.MAP_ID = CMAPCOL.MAP_ID ");
            sqlCommand.Append("AND CMAPCOL.COL_ID = DSCOL.COL_ID ");
            sqlCommand.AppendFormat("AND MSET.MAP_SET_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    int mapID = dataReader.GetOrdinal("MAP_ID");
                    int type = dataReader.GetOrdinal("TYPE");
                    int constant = dataReader.GetOrdinal("CONSTANT");
                    int colIDIdx = dataReader.GetOrdinal("COL_ID");
                    int nameIdx = dataReader.GetOrdinal("NAME");
                    ////int descIdx = dataReader.GetOrdinal("DESCRIPTION");
                    while (dataReader.Read())
                    {
                        // get existing or create new mapping object
                        long mapId = DataReaderHelper.GetInt64(dataReader, mapID);
                        MappingEntity mapping;
                        if (!dictionary.TryGetValue(mapId, out mapping))
                        {
                            // create new mapping and append it to the mapping set
                            mapping = new MappingEntity(mapId) { MappingType = DataReaderHelper.GetString(dataReader, type), Constant = DataReaderHelper.GetString(dataReader, constant) };

                            dictionary.Add(mapId, mapping);
                            mappingSet.Mappings.Add(mapping);
                        }

                        var column = new DataSetColumnEntity(DataReaderHelper.GetInt64(dataReader, colIDIdx)) { Name = DataReaderHelper.GetString(dataReader, nameIdx) };

                        // dsColumn.SysId = DataReaderHelper.GetInt64(dataReader, "DS_ID");
                        ////column.Description = DataReaderHelper.GetString(dataReader, descIdx);

                        mapping.Columns.Add(column);
                    }
                }
            }
            
            if (transcoded)
            {
                GetTranscodingRulesByMapSetId(mappingStoreDb, sysId, timeDimensionMapping, dictionary);
                RetrieveTimeDimensionTranscoding(mappingStoreDb, timeDimensionMapping);
            }
        }
        /// <summary>
        /// This method retrieves a <see cref="ConnectionEntity"/> object
        /// by its identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="connectionId">
        /// The connection with the specified identifier
        /// </param>
        /// <returns>
        /// The populated <see cref="ConnectionEntity"/>object
        /// </returns>
        private static ConnectionEntity GetConnectionById(Database mappingStoreDb, long connectionId)
        {
            ConnectionEntity ret = null;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT CON.CONNECTION_ID, CON.DB_NAME, CON.DB_TYPE, CON.NAME, ");
            sqlCommand.Append("CON.DB_PASSWORD, CON.DB_PORT, CON.DB_SERVER, CON.DB_USER, ");
            sqlCommand.Append("CON.ADO_CONNECTION_STRING, CON.JDBC_CONNECTION_STRING ");
            sqlCommand.Append("FROM DB_CONNECTION CON ");
            sqlCommand.AppendFormat("WHERE CON.CONNECTION_ID = {0} ", paramId);

            DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString());

            mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, connectionId);

            using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
            {
                // we expect only 1 record here
                while (dataReader.Read())
                {
                    ret = new ConnectionEntity(DataReaderHelper.GetInt64(dataReader, "CONNECTION_ID"));
                    ret.DBName = DataReaderHelper.GetString(dataReader, "DB_NAME");
                    ret.DBType = DataReaderHelper.GetString(dataReader, "DB_TYPE");
                    ret.Name = DataReaderHelper.GetString(dataReader, "NAME");

                    // ret.Owner = DataReaderHelper.GetString(dataReader, "OWNER");
                    ret.DBPassword = DataReaderHelper.GetString(dataReader, "DB_PASSWORD");
                    //ret.DBPort = DataReaderHelper.GetInt32(dataReader, "DB_PORT");
                    ret.DBServer = DataReaderHelper.GetString(dataReader, "DB_SERVER");
                    ret.DBUser = DataReaderHelper.GetString(dataReader, "DB_USER");
                    ret.AdoConnectionString = DataReaderHelper.GetString(dataReader, "ADO_CONNECTION_STRING");
                    ret.JdbcConnectionString = DataReaderHelper.GetString(dataReader, "JDBC_CONNECTION_STRING");
                }
            }

            if (ret == null)
            {
                throw new MappingStoreException(
                    string.Format(
                        CultureInfo.InvariantCulture, 
                        ErrorMessages.MappingStoreNoEntityFormat2, 
                        typeof(ConnectionEntity), 
                        connectionId));
            }

            return ret;
        }
        /// <summary>
        /// This method retrieves a <see cref="DataSetEntity"/> by its identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="datasetId">
        /// The dataset identifier, by which the dataset will be retrieved
        /// </param>
        /// <returns>
        /// The populated <see cref="DataSetEntity"/> object
        /// </returns>
        private static DataSetEntity GetDatasetById(Database mappingStoreDb, long datasetId)
        {
            DataSetEntity ret = null;
            long connectionId = 0;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT DS.DS_ID, DS.NAME, DS.QUERY, DS.CONNECTION_ID ");
            sqlCommand.Append("FROM DATASET DS ");
            sqlCommand.AppendFormat("WHERE DS.DS_ID = {0} ", paramId);

            DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString());

            mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, datasetId);

            using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
            {
                // we expect only 1 record here
                while (dataReader.Read())
                {
                    ret = new DataSetEntity(DataReaderHelper.GetInt64(dataReader, "DS_ID"))
                              {
                                  Description = DataReaderHelper.GetString(dataReader, "NAME"),
                                  Query = DataReaderHelper.GetString(dataReader, "QUERY")
                              };

                    connectionId = DataReaderHelper.GetInt64(dataReader, "CONNECTION_ID");
                }
            }

            if (ret == null)
            {
                throw new MappingStoreException(
                    string.Format(CultureInfo.InvariantCulture, "There was no dataset for Id:{0}", datasetId));
            }

            ret.Connection = GetConnectionById(mappingStoreDb, connectionId);

            return ret;
        }
        /// <summary>
        /// The method retrieves the <see cref="CodeListEntity"/> object
        /// by the component system identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="sysId">
        /// The component with the specified system identifier
        /// </param>
        /// <returns>
        /// The populated <see cref="CodeListEntity"/>object
        /// </returns>
        private static CodeListEntity GetCodeListByComponentSysId(Database mappingStoreDb, long sysId)
        {
            CodeListEntity ret = null;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();

            sqlCommand.Append("SELECT CL.CL_ID, A.ID as CODELIST_ID, A.AGENCY, A.VERSION ");
            sqlCommand.Append("FROM CODELIST CL, COMPONENT COMP, ARTEFACT_VIEW A ");
            sqlCommand.Append("WHERE COMP.CL_ID = CL.CL_ID ");
            sqlCommand.Append("AND CL.CL_ID = A.ART_ID ");
            sqlCommand.AppendFormat("AND COMP.COMP_ID = {0} ", paramId);
            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    // we expect only 1 record here
                    if (dataReader.Read())
                    {
                        ret = new CodeListEntity(DataReaderHelper.GetInt64(dataReader, "CL_ID"))
                                  {
                                      Id = DataReaderHelper.GetString(dataReader, "CODELIST_ID"),
                                      Agency = DataReaderHelper.GetString(dataReader, "AGENCY"),
                                      Version = DataReaderHelper.GetString(dataReader, "VERSION")
                                  };

                        // ret.CodeList.Add(DataReaderHelper.GetString(dataReader, "CODE_ID"));
                        // while (dataReader.Read())
                        // {
                        // ret.CodeList.Add(DataReaderHelper.GetString(dataReader, "CODE_ID"));
                        // }
                    }
                }
            }

            return ret;
        }
        /// <summary>
        /// The method retrieves the <see cref="ConceptEntity"/> object
        /// by the component system identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="sysId">
        /// The component with the specified system identifier
        /// </param>
        /// <returns>
        /// The populated <see cref="ConceptEntity"/>object
        /// </returns>
        private static ConceptEntity GetConceptByComponentSysId(Database mappingStoreDb, long sysId)
        {
            ConceptEntity ret = null;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT CON.CON_ID, IT.ID ");
            sqlCommand.Append("FROM CONCEPT CON, COMPONENT COMP, ITEM IT ");
            sqlCommand.Append("WHERE COMP.CON_ID = CON.CON_ID ");
            sqlCommand.Append("AND CON.CON_ID = IT.ITEM_ID ");
            sqlCommand.AppendFormat("AND COMP.COMP_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    // we expect only 1 record here
                    if (dataReader.Read())
                    {
                        ret = new ConceptEntity(DataReaderHelper.GetInt64(dataReader, "CON_ID")) { Id = DataReaderHelper.GetString(dataReader, "ID") };
                    }
                }
            }

            return ret;
        }
        /// <summary>
        /// Add where clauses and populate <paramref name="parameters"/>
        /// </summary>
        /// <param name="maintainableRef">
        ///     The maintainable reference which may contain ID, AGENCY ID and/or VERSION.
        /// </param>
        /// <param name="database">
        ///     The database.
        /// </param>
        /// <param name="sqlCommand">
        ///     The SQL command.
        /// </param>
        /// <param name="parameters">
        ///     The parameters.
        /// </param>
        /// <param name="allowedDataflows">
        ///     The allowed dataflows.
        /// </param>
        /// <param name="whereState">the current state of the WHERE clause in <paramref name="sqlCommand"/></param>
        /// <returns>
        /// The <paramref name="parameters"/>
        /// </returns>
        public static IList<DbParameter> AddWhereClauses(IMaintainableRefObject maintainableRef, Database database, StringBuilder sqlCommand, IList<DbParameter> parameters, ICollection<IMaintainableRefObject> allowedDataflows, WhereState whereState)
        {
            if (allowedDataflows == null)
            {
                return parameters;
            }

            maintainableRef = maintainableRef ?? new MaintainableRefObjectImpl();
            switch (whereState)
            {
                case WhereState.Nothing:
                    sqlCommand.Append(" WHERE (");
                    break;
                case WhereState.Where:
                    break;
                case WhereState.And:
                    sqlCommand.Append(" AND (");
                    break;
            }

            int lastClause = sqlCommand.Length;
            int count = 0;
            bool addedClauses = false;
            foreach (IMaintainableRefObject allowedDataflow in allowedDataflows)
            {
                // TODO check if allowed dataflow id is mandatory. If not we need to change this.
                if (!maintainableRef.HasMaintainableId() || string.Equals(maintainableRef.MaintainableId, allowedDataflow.MaintainableId))
                {
                    string countString = count.ToString(CultureInfo.InvariantCulture);
                    sqlCommand.Append("(");

                    // id
                    string idParam = ParameterNameConstants.IdParameter + countString;
                    sqlCommand.AppendFormat(" A.ID = {0} ", database.BuildParameterName(idParam));
                    parameters.Add(database.CreateInParameter(idParam, DbType.String, allowedDataflow.MaintainableId));

                    // version
                    var versionParameters = allowedDataflow.GenerateVersionParameters(database, parameters, "A.VERSION", versionNumber => string.Format(CultureInfo.InvariantCulture, "{0}{1}_{2}", ParameterNameConstants.VersionParameter, versionNumber, countString));
                    if (versionParameters.Length > 0)
                    {
                        sqlCommand.AppendFormat(CultureInfo.InvariantCulture, "AND {0}", versionParameters);
                    }

                    // agency
                    if (allowedDataflow.HasAgencyId())
                    {
                        string name = ParameterNameConstants.AgencyParameter + countString;
                        sqlCommand.AppendFormat(" AND A.AGENCY = {0} ", database.BuildParameterName(name));
                        parameters.Add(database.CreateInParameter(name, DbType.String, allowedDataflow.AgencyId));
                    }

                    sqlCommand.Append(" ) ");
                    lastClause = sqlCommand.Length;
                    sqlCommand.Append(" OR ");
                    count++;
                    addedClauses = true;
                }
            }

            sqlCommand.Length = lastClause;

            if (!addedClauses)
            {
                sqlCommand.Append("'ACCESS' = 'DENIED'");
            }

            sqlCommand.Append(" ) ");

            return parameters;
        }
        /// <summary>
        /// The method populates all the <see cref="ComponentEntity">DSD components</see>
        /// of a <see cref="DsdEntity"/> object by the data structure definition identifier
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="sysId">
        /// The data flow with the specified system identifier
        /// </param>
        /// <param name="dsd">
        /// The populated <see cref="DsdEntity"/>object containing also the
        /// <see cref="ComponentEntity">DSD components</see> 
        /// </param>
        private static void PoulateDsdComponentsByDsdSysId(Database mappingStoreDb, long sysId, ref DsdEntity dsd)
        {
            GroupEntity group;
            string id;

            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT G.GR_ID AS GR_ID, G.ID AS ID, DG.COMP_ID AS COMP_ID ");
            sqlCommand.Append("FROM DSD D, DSD_GROUP G, DIM_GROUP DG ");
            sqlCommand.AppendFormat("WHERE D.DSD_ID = G.DSD_ID AND G.GR_ID = DG.GR_ID AND D.DSD_ID = {0} ", paramId);
            sqlCommand.Append("ORDER BY G.GR_ID");

            // used to store the groups given the dimension system id. Is used later to gather actual ComponentVO objects and set them to Group
            var groupsFromDim = new Dictionary<long, List<GroupEntity>>();

            // holds the groups created given their group id
            var groupsFromId = new Dictionary<string, GroupEntity>();
            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    // we expect only 1 record here ?
                    while (dataReader.Read())
                    {
                        id = DataReaderHelper.GetString(dataReader, "ID");

                        // do we have a new group id
                        if (!groupsFromId.ContainsKey(id))
                        {
                            group = new GroupEntity(DataReaderHelper.GetInt64(dataReader, "GR_ID")) { Id = id };

                            dsd.AddGroup(group);
                            groupsFromId.Add(id, group);
                        }
                        else
                        {
                            group = groupsFromId[id];
                        }

                        long dimId = DataReaderHelper.GetInt64(dataReader, "COMP_ID");
                        List<GroupEntity> groupsList;
                        if (!groupsFromDim.TryGetValue(dimId, out groupsList))
                        {
                            groupsList = new List<GroupEntity>();
                            groupsFromDim.Add(dimId, groupsList);
                        }

                        groupsList.Add(group);
                    }
                }
            }

            sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT COMP.COMP_ID, COMP.ID as CID, COMP.TYPE, COMP.DSD_ID, COMP.CON_ID, COMP.CL_ID, ");
            sqlCommand.Append("COMP.IS_FREQ_DIM, COMP.IS_MEASURE_DIM, COMP.ATT_ASS_LEVEL, COMP.ATT_STATUS, ");
            sqlCommand.Append("COMP.ATT_IS_TIME_FORMAT, COMP.XS_ATTLEVEL_DS, COMP.XS_ATTLEVEL_GROUP, ");
            sqlCommand.Append("COMP.XS_ATTLEVEL_SECTION, COMP.XS_ATTLEVEL_OBS, COMP.XS_MEASURE_CODE, ");
            sqlCommand.Append("AG.GR_ID, GR.ID AS GID, MC.ID as MID ");

            sqlCommand.Append("FROM DSD INNER JOIN COMPONENT COMP ON DSD.DSD_ID = COMP.DSD_ID ");
            sqlCommand.Append("LEFT OUTER JOIN ATT_GROUP AG ON COMP.COMP_ID = AG.COMP_ID ");
            sqlCommand.Append("LEFT OUTER JOIN DSD_GROUP GR ON AG.GR_ID = GR.GR_ID ");
            sqlCommand.Append("LEFT OUTER JOIN ATT_MEASURE AM ON AM.ATT_COMP_ID = COMP.COMP_ID ");
            sqlCommand.Append("LEFT OUTER JOIN COMPONENT MC ON AM.MEASURE_COMP_ID = MC.COMP_ID ");

            sqlCommand.AppendFormat("WHERE DSD.DSD_ID = {0} ", paramId);

            // make sure that they are returned in the order they were inserted (proper order)
            sqlCommand.Append("ORDER BY COMP.COMP_ID ");

            // hashmap keeping components already met with key the sys id.
            var componentsMap = new Dictionary<long, ComponentEntity>();
            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    // GetOrdinal positions
                    int compIdIdx = dataReader.GetOrdinal("COMP_ID");
                    int type = dataReader.GetOrdinal("TYPE");
                    int isFreqDim = dataReader.GetOrdinal("IS_FREQ_DIM");
                    int isMeasureDim = dataReader.GetOrdinal("IS_MEASURE_DIM");
                    int attAssLevel = dataReader.GetOrdinal("ATT_ASS_LEVEL");
                    int attStatus = dataReader.GetOrdinal("ATT_STATUS");
                    int attIsTimeFormat = dataReader.GetOrdinal("ATT_IS_TIME_FORMAT");
                    int crossSectionalAttachmentlevelDataSet = dataReader.GetOrdinal("XS_ATTLEVEL_DS");
                    int attlevelGroup = dataReader.GetOrdinal("XS_ATTLEVEL_GROUP");
                    int attlevelSection = dataReader.GetOrdinal("XS_ATTLEVEL_SECTION");
                    int attlevelObs = dataReader.GetOrdinal("XS_ATTLEVEL_OBS");
                    int measureCode = dataReader.GetOrdinal("XS_MEASURE_CODE");
                    int gid = dataReader.GetOrdinal("GID");
                    int cid = dataReader.GetOrdinal("CID");
                    int mid = dataReader.GetOrdinal("MID");
                    while (dataReader.Read())
                    {
                        long compSysId = DataReaderHelper.GetInt64(dataReader, compIdIdx);

                        // check if new populate
                        ComponentEntity component;
                        if (!componentsMap.TryGetValue(compSysId, out component))
                        {
                            component = new ComponentEntity(compSysId);
                            component.Id = DataReaderHelper.GetString(dataReader, cid);
                            component.SetType(DataReaderHelper.GetString(dataReader, type));
                            component.CrossSectionalLevelDataSet = DataReaderHelper.GetBoolean(
                                dataReader, crossSectionalAttachmentlevelDataSet);
                            component.CrossSectionalLevelGroup = DataReaderHelper.GetBoolean(dataReader, attlevelGroup);
                            component.CrossSectionalLevelSection = DataReaderHelper.GetBoolean(
                                dataReader, attlevelSection);
                            component.CrossSectionalLevelObs = DataReaderHelper.GetBoolean(dataReader, attlevelObs);
                            component.CrossSectionalMeasureCode = DataReaderHelper.GetString(dataReader, measureCode);
                            switch (component.ComponentType)
                            {
                                case SdmxComponentType.Dimension:
                                    component.FrequencyDimension = DataReaderHelper.GetBoolean(dataReader, isFreqDim);
                                    component.MeasureDimension = DataReaderHelper.GetBoolean(dataReader, isMeasureDim);
                                    dsd.Dimensions.Add(component);
                                    break;
                                case SdmxComponentType.Attribute:
                                    component.SetAttachmentLevel(DataReaderHelper.GetString(dataReader, attAssLevel));
                                    component.SetAssignmentStatus(DataReaderHelper.GetString(dataReader, attStatus));
                                    component.AttTimeFormat = DataReaderHelper.GetBoolean(dataReader, attIsTimeFormat);
                                    dsd.Attributes.Add(component);
                                    break;
                                case SdmxComponentType.TimeDimension:
                                    dsd.TimeDimension = component;
                                    break;
                                case SdmxComponentType.PrimaryMeasure:
                                    dsd.PrimaryMeasure = component;
                                    break;
                                case SdmxComponentType.CrossSectionalMeasure:
                                    dsd.CrossSectionalMeasures.Add(component);
                                    break;
                            }

                            componentsMap.Add(compSysId, component);
                        }

                        // populate for current groups the related assigned groups if any
                        id = DataReaderHelper.GetString(dataReader, gid);
                        if (!string.IsNullOrEmpty(id))
                        {
                            group = groupsFromId[id];
                            component.AttAssignmentGroups.Add(group);
                        }

                        var measureId = DataReaderHelper.GetString(dataReader, mid);
                        if (!string.IsNullOrEmpty(measureId))
                        {
                            component.AttAttachmentMeasures.Add(measureId);
                        }

                        // if component is a dimension used in group definition, add it in their object
                        List<GroupEntity> groupFromDim;
                        if (groupsFromDim.TryGetValue(compSysId, out groupFromDim))
                        {
                            foreach (GroupEntity groupsItem in groupFromDim)
                            {
                                groupsItem.Dimensions.Add(component);
                            }
                        }
                    }
                }
            }

            foreach (var kv in componentsMap)
            {
                kv.Value.Concept = GetConceptByComponentSysId(mappingStoreDb, kv.Key);
                kv.Value.CodeList = GetCodeListByComponentSysId(mappingStoreDb, kv.Key);
            }
        }
        /// <summary>
        /// This method populates the Senders and Receivers of a <see cref="IHeader"/>
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="headerSysId">
        /// The header system identifier. In the database the column HEADER.HEADER_ID
        /// </param>
        /// <param name="header">
        /// The <see cref="IHeader"/> to be populated in terms of Senders and Receivers
        /// </param>
        private static void PoulateHeaderSendersAndReceivers(Database mappingStoreDb, long headerSysId, IHeader header)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT PARTY.PARTY_ID, PARTY.ID, PARTY.HEADER_ID, PARTY.TYPE ");
            sqlCommand.Append("FROM PARTY ");
            sqlCommand.AppendFormat("WHERE PARTY.HEADER_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, headerSysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        var id = DataReaderHelper.GetString(dataReader, "ID");
                        long partySysId = DataReaderHelper.GetInt64(dataReader, "PARTY_ID");
                        string partyType = DataReaderHelper.GetString(dataReader, "TYPE");

                        var names = new List<ITextTypeWrapper>();
                        PopulatePartyLocalisedStrings(mappingStoreDb, partySysId, names);

                        var contacts = new List<IContact>();
                        PopulatePartyContacts(mappingStoreDb, partySysId, contacts);

                        var party = new PartyCore(names, id, contacts, null);

                        // is it a sender or a receiver?
                        if (partyType.Equals(SenderText, StringComparison.OrdinalIgnoreCase))
                        {
                            header.Sender = party;
                        }
                        else if (partyType.Equals(ReceiverText, StringComparison.OrdinalIgnoreCase))
                        {
                            header.AddReciever(party);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// This method populates the international strings
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="partySysId">
        /// The party system identifier. In the database the column PARTY.PARTY_ID
        /// </param>
        /// <param name="partyNames">
        /// The <see cref="ITextTypeWrapper"/>lists  to be populated in terms of Names
        /// </param>
        private static void PopulatePartyLocalisedStrings(Database mappingStoreDb, long partySysId, ICollection<ITextTypeWrapper> partyNames)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT HLS.HLS_ID, HLS.TYPE, HLS.HEADER_ID, HLS.PARTY_ID, HLS.CONTACT_ID, HLS.LANGUAGE, HLS.TEXT ");
            sqlCommand.Append("FROM HEADER_LOCALISED_STRING HLS ");
            sqlCommand.AppendFormat("WHERE HLS.PARTY_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, partySysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        var text = new TextTypeWrapperMutableCore { Locale = DataReaderHelper.GetString(dataReader, "LANGUAGE"), Value = DataReaderHelper.GetString(dataReader, "TEXT") };
                        string textType = DataReaderHelper.GetString(dataReader, "TYPE");

                        // is it a sender or a receiver?
                        if (textType.Equals(NameText, StringComparison.OrdinalIgnoreCase))
                        {
                            partyNames.Add(new TextTypeWrapperImpl(text, null));
                        }
                    }
                }
            }
        }
        /// <summary>
        /// This method populates the Contacts.
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="partySysId">
        /// The party system identifier. In the database the column PARTY.PARTY_ID
        /// </param>
        /// <param name="contacts">
        /// The list of contacts to be populated i
        /// </param>
        private static void PopulatePartyContacts(Database mappingStoreDb, long partySysId, ICollection<IContact> contacts)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT CONTACT.CONTACT_ID, CONTACT.PARTY_ID ");
            sqlCommand.Append("FROM CONTACT ");
            sqlCommand.AppendFormat("WHERE CONTACT.PARTY_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, partySysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        var contact = new ContactMutableObjectCore();
                        long contactSysId = DataReaderHelper.GetInt64(dataReader, "CONTACT_ID");

                        PopulateContactLocalisedStrings(mappingStoreDb, contactSysId, contact);
                        PopulateContactDetails(mappingStoreDb, contactSysId, contact);

                        contacts.Add(new ContactCore(contact));
                    }
                }
            }
        }
        /// <summary>
        /// This method populates the Localized Strings (Names, Departments, Roles)
        ///     of a <see cref="IContactMutableObject"/>
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="contactSysId">
        /// The contact system identifier. In the database the column CONTACT.CONTACT_ID
        /// </param>
        /// <param name="contact">
        /// The <see cref="IContactMutableObject"/> to be populated in terms of Names, Departments, Roles
        /// </param>
        private static void PopulateContactLocalisedStrings(Database mappingStoreDb, long contactSysId, IContactMutableObject contact)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT HLS.HLS_ID, HLS.TYPE, HLS.HEADER_ID, HLS.PARTY_ID, HLS.CONTACT_ID, HLS.LANGUAGE, HLS.TEXT ");
            sqlCommand.Append("FROM HEADER_LOCALISED_STRING HLS ");
            sqlCommand.AppendFormat("WHERE HLS.CONTACT_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, contactSysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        var text = new TextTypeWrapperMutableCore { Locale = DataReaderHelper.GetString(dataReader, "LANGUAGE"), Value = DataReaderHelper.GetString(dataReader, "TEXT") };
                        if (!string.IsNullOrWhiteSpace(text.Value) && !string.IsNullOrWhiteSpace(text.Locale))
                        {
                            string textType = DataReaderHelper.GetString(dataReader, "TYPE");

                            if (textType.Equals(NameText, StringComparison.OrdinalIgnoreCase))
                            {
                                contact.Names.Add(text);
                            }
                            else if (textType.Equals(DepartmentText, StringComparison.OrdinalIgnoreCase))
                            {
                                contact.Departments.Add(text);
                            }
                            else if (textType.Equals(RoleText, StringComparison.OrdinalIgnoreCase))
                            {
                                contact.Roles.Add(text);
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// This method populates the contact details of a <see cref="IContactMutableObject"/>
        /// </summary>
        /// <param name="mappingStoreDb">
        /// The <see cref="Database"/> instance for Mapping Store database
        /// </param>
        /// <param name="contactSysId">
        /// The contact system identifier. In the database the column CONTACT.CONTACT_ID
        /// </param>
        /// <param name="contact">
        /// The <see cref="IContactMutableObject"/> for which the details will be populated
        /// </param>
        private static void PopulateContactDetails(Database mappingStoreDb, long contactSysId, IContactMutableObject contact)
        {
            string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);

            var sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT CD.CD_ID, CD.CONTACT_ID, CD.TYPE, CD.VALUE ");
            sqlCommand.Append("FROM CONTACT_DETAIL CD ");
            sqlCommand.AppendFormat("WHERE CD.CONTACT_ID = {0} ", paramId);

            using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
            {
                mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, contactSysId);

                using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        string coordinateType = DataReaderHelper.GetString(dataReader, "TYPE");
                        string coordinateData = DataReaderHelper.GetString(dataReader, "VALUE");

                        contact.AddCoordinateType(coordinateType, coordinateData);
                    }
                }
            }
        }