public Guid InsertOrUpdateTag(TagDTO tag)
        {
            string spName = "apt_code.SmsAppointmentService.createUpdateTag";

            try
            {
                using (var connection = m_providerFactory.CreateConnection())
                {
                    var oracleParameters = TemplateTagQueryParamsHelper.getQPForCreateORUpdateTag(tag);
                    var newGuid          = UpdateAndReturnKey(spName, "l_tagID", oracleParameters, connection);

                    return(newGuid);
                }
            }
            catch (Oracle.ManagedDataAccess.Client.OracleException ex)
            {
                s_log.ErrorException("InserOrUpdateATag Fails(Error from Oracle)", ex);
                throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
            }
            catch (DBOperationException e)
            {
                s_log.WarnException("Error Occured in InserOrUpdateATag", e);
                throw;
            }
            catch (Exception ex)
            {
                s_log.ErrorException("InserOrUpdateATag Fails", ex);
                throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
            }
        }
        public List <TagDTO> SelectTagsOn(long?departmentId, string keyword, bool active, bool hospitalLevel, long hospitalId)
        {
            const string spName = "apt_code.SmsAppointmentService.searchTag";

            try
            {
                using (var connection = m_providerFactory.CreateConnection())
                {
                    var oracleparams = TemplateTagQueryParamsHelper.GetQPForSearchTagsMoreOptions(departmentId, keyword, active, hospitalLevel, hospitalId);

                    var resultSet = connection.Query(spName, oracleparams, commandType: CommandType.StoredProcedure);
                    if (resultSet == null)
                    {
                        s_log.Error("searchTag returned empty or null result.");
                        throw new DBOperationException("searchTag returned empty or null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
                    }

                    var tags = new List <TagDTO>();
                    foreach (var result in resultSet)
                    {
                        var resultDictionary = result as IDictionary <string, object>;
                        tags.Add(MapToTagDTO(resultDictionary));
                    }
                    return(tags);
                }
            }
            catch (Oracle.ManagedDataAccess.Client.OracleException ex)
            {
                s_log.ErrorException("SelectTagsOn Fails(Error from Oracle)", ex);
                throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
            }
            catch (DBOperationException e)
            {
                s_log.WarnException("Error Occured in SelectTagsOn", e);
                throw;
            }
            catch (Exception ex)
            {
                s_log.ErrorException("SelectTagsOn Fails", ex);
                throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
            }
        }
        public TagDTO SelectTagBy(Guid tagId)
        {
            const string spName = "apt_code.SmsAppointmentService.getTagDetails";

            try
            {
                using (var connection = m_providerFactory.CreateConnection())
                {
                    var oracleparams = TemplateTagQueryParamsHelper.GetQPForGetTagByID(tagId);

                    var resultSet = connection.Query(spName, oracleparams, commandType: CommandType.StoredProcedure);
                    if (resultSet == null)
                    {
                        s_log.Error("getTagDetails returned empty or null result.");
                        throw new DBOperationException("getTagDetails returned empty or null result.", GlobalOptions.DBExceptionScenarios.DBReturnedEmptyOrNullDataSet);
                    }

                    var tags = new List <TagDTO>();
                    foreach (var result in resultSet)
                    {
                        var resultDictionary = result as IDictionary <string, object>;
                        tags.Add(MapToTagDTO(result));
                    }
                    return(tags.FirstOrDefault());
                }
            }
            catch (Oracle.ManagedDataAccess.Client.OracleException ex)
            {
                s_log.ErrorException("SelectTagBy(Guid tagId) Fails(Error from Oracle)", ex);
                throw new DBOperationException(ex.Message, ex.Number, DBExceptionScenarios.OracleExceptionOccured, ex);
            }
            catch (DBOperationException e)
            {
                s_log.WarnException("Error Occured in SelectTagBy(Guid tagId)", e);
                throw;
            }
            catch (Exception ex)
            {
                s_log.ErrorException("SelectTagBy(Guid tagId) Fails", ex);
                throw new DBOperationException(ex.Message, DBExceptionScenarios.ExceptionOccured, ex);
            }
        }