C# SafeDataReader.GetString方法代码示例

        public static ArrayList GetPatientChartFilterList()
        {
            ArrayList _filterList = new ArrayList();
            using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "select Name, Description,FilterID"
                        + " from PatientChartFilter"
                        + " where AccountID =" + Common.AccountID
                        + " and FilterId > 0"
                        + " order by Description";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                        {
                            _filterList.Add(dr.GetString(0) + "~"
                                + dr.GetString(1) + "~"
                                + dr.GetInt32(2));
                        }
                        return _filterList;
                    }
                }
            }
        }

        private VoucherEditDto FetchVoucherEdit(SafeDataReader dr)
        {
            var voucherEdit = new VoucherEditDto();
            // Value properties
            voucherEdit.Id = dr.GetInt32("tran_id");
            voucherEdit.Amount = (Decimal?)dr.GetValue("tran_amount");
            voucherEdit.PersonId = !dr.IsDBNull("person_id") ? dr.GetString("person_id") : null;
            voucherEdit.NamePrefix = !dr.IsDBNull("name_prefix") ? dr.GetString("name_prefix") : null;
            voucherEdit.First = !dr.IsDBNull("first_name") ? dr.GetString("first_name") : null;
            voucherEdit.Middle = !dr.IsDBNull("middle_name") ? dr.GetString("middle_name") : null;
            voucherEdit.Last = !dr.IsDBNull("last_name") ? dr.GetString("last_name") : null;
            voucherEdit.Suffix = !dr.IsDBNull("name_suffix") ? dr.GetString("name_suffix") : null;
            voucherEdit.Title = !dr.IsDBNull("title") ? dr.GetString("title") : null;
            voucherEdit.Company = !dr.IsDBNull("company") ? dr.GetString("company") : null;
            voucherEdit.AddressLine1 = !dr.IsDBNull("address_1") ? dr.GetString("address_1") : null;
            voucherEdit.AddressLine2 = !dr.IsDBNull("address_2") ? dr.GetString("address_2") : null;
            voucherEdit.Municipality = !dr.IsDBNull("municipality") ? dr.GetString("municipality") : null;
            voucherEdit.Region = !dr.IsDBNull("region") ? dr.GetString("region") : null;
            voucherEdit.PostalCode = !dr.IsDBNull("postal_code") ? dr.GetString("postal_code") : null;
            voucherEdit.Country = !dr.IsDBNull("country") ? dr.GetString("country") : null;
            voucherEdit.PhoneNumber = !dr.IsDBNull("phone_number") ? dr.GetString("phone_number") : null;
            voucherEdit.EmailAddress = !dr.IsDBNull("email") ? dr.GetString("email") : null;
            voucherEdit.Updated = !dr.IsDBNull("updated") ? dr.GetSmartDate("updated", true) : null;

            return voucherEdit;
        }

        public IEnumerable<EDCEditDto> FetchEdcList()
        {
            const string sql = @"
  SELECT [Id]
        ,[Name]
        ,[SystemName]
        ,[Documentation]       
        ,[GuidId]
  FROM [dbo].[ExternalDataConfiguration] edc 
";
            var result = new List<EDCEditDto>();

            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var cn = ctx.Connection;
                if (cn.State != ConnectionState.Open)
                {
                    cn.Open();
                }

                using (var cmd = new SqlCommand(sql, cn))
                {
                    using (var sr = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (sr.Read())
                        {
                            var parameter = new EDCEditDto
                            {
                                Id = sr.GetInt32(0),
                                Name = sr.GetString(1),
                                SystemName = sr.GetString(2),
                                Documentation = sr.GetString(3),
                                Guid = sr.GetGuid(4)
                            };

                            result.Add(parameter);
                        }
                    }

                    foreach (var dto in result)
                    {
                        dto.ConnectionList = new List<EDCConnectionEditDto>();
                        dto.DataVariableList = new List<EDCDataVariablesEditDto>();
                        FetchEDCConnectionList(cn, dto);
                        FetchDataVariableList(cn, dto);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Fetch System Parameters
        /// </summary>
        /// <returns>IEnumerable{SystemParametersDto}.</returns>
        /// <exception cref="DataAccessException"></exception>
        public IEnumerable<SystemParametersDto> FetchSystemParameters()
        {
            const string Sql = @"
  SELECT [Id]
      ,[Name]
      ,[Documentation]
	  ,[Type]
	  ,[ProcessSystemName]  
	  ,[Expression]  
      ,[GuidId]
      ,[LastModifiedOn]
  FROM [dbo].[SystemParameters] sp
  WHERE  sp.IsRemoved = 0
";

            var result = new List<SystemParametersDto>();
            Database.GetDataReader(
                Sql,
                reader =>
                {
                    if (reader == null)
                    {
                        throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
                    }

                    using (var sr = new SafeDataReader(reader))
                    {
                        while (reader.Read())
                        {
                            var parameter = new SystemParametersDto
                                                {
                                                    ParameterId = sr.GetInt32(0),
                                                    Name = sr.GetString(1),
                                                    Documentation = sr.GetString(2),
                                                    ParameterType = !string.IsNullOrEmpty(reader.GetString(3))
                                                        ? (SystemParameterType)Enum.Parse(typeof(SystemParameterType), reader.GetString(3))
                                                        : SystemParameterType.Int,
                                                    PersonProcessSystemName = sr.GetString(4),
                                                    Expression = sr.GetString(5),
                                                    Guid = sr.GetGuid(6),
                                                    LastModifiedOn = sr.GetDateTime(7)
                                                };

                            result.Add(parameter);
                        }
                    }
                });

            return result;
        }

 public static ArrayList GetTemplates(string _templateType)
 {
     ArrayList aTemplates = new ArrayList();
     using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
     {
         cn.Open();
         using (SqlCommand cm = cn.CreateCommand())
         {
             cm.CommandText = "select B.Name ,B.TemplateID from TemplateType A,Templates B"
             + " where A.Name = '" + _templateType + "'"
             + " and A.AccountID = " + Common.AccountID
             + " and B.AccountID = " + Common.AccountID
             + " and A.TemplateTypeID = B.TemplateTypeID"
             + " order by B.Name";
             Common.Log(cm.CommandText);
             using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
             {
                 while (dr.Read())
                 {
                     aTemplates.Add(dr.GetString(0) + "~"
                         + dr.GetInt32(1).ToString());
                       //  + dr.GetDateTime(1).ToShortDateString() + "~"
                       //  + dr.GetString(2) + "~"
                       //  + dr.GetString(3) + "~"
                       //  + dr.GetInt32(4).ToString());
                 }
                 return aTemplates;
             }
         }
     }
 }

        public static StringCollection GetStringCollectionOfPatientChart(string _id)
        {
            StringCollection items = new StringCollection();
            using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "select ScanPath"
                        + " from ScanPatientDocuments"
                        + " where PatientID = " + _id
                        + " and AccountID =" + Common.AccountID
                        + " order by TabId,DateOfService";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                        {
                            items.Add(Common.StoreComputer 
                                + Common.StoreShare + dr.GetString(0));
                        }
                        return items;
                    }
                }
            }
        }

        /// <summary>
        /// Gets the user dictionary words.
        /// </summary>
        /// <returns>
        /// The collection of words.
        /// </returns>
        public IList<string> GetUserWords()
        {
            const string CommandText = @"
SELECT [Word]
FROM
    [dbo].[SpellCheckerUserWords]";

            using (var connectionManager = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        using (var safeReader = new SafeDataReader(reader))
                        {
                            var words = new List<string>();

                            while (safeReader.Read())
                            {
                                words.Add(safeReader.GetString("Word"));
                            }

                            return words;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Retrieves action groups.
        /// </summary>
        /// <returns>The list of Action Group DTO objects.</returns>
        public IEnumerable<ActionGroupDto> FetchActionGroups()
        {
            var result = new List<ActionGroupDto>();
            const string Sql = @"
SELECT DISTINCT
     [GroupName]
FROM
    [dbo].[ActionDefinitions]
";
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var cn = ctx.Connection;
                
                using (var cmd = new SqlCommand(Sql, cn))
                {
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            var dto = new ActionGroupDto { Name = reader.GetString(0) };
                            result.Add(dto);
                        }
                    }
                }                
            }

            return result;
        }

 public string GetDatabaseVersion()
 {
     var mySQLString = "Select mdbVersion From tblCaptions";
     if (GetConnection(out _myAccessConn)) return null;
     try
     {
         using (var myAccessCommand = new OleDbCommand(mySQLString, _myAccessConn))
         {
             _myAccessConn.Open();
             using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
             using (dr)
                 while (dr.Read())
                 {
                     _mdbVersion = dr.GetString(dr.GetOrdinal("mdbVersion"));
                 }
         }
     }
     catch (Exception ex)
     {
         Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
         return null;
     }
     finally
     {
         _myAccessConn.Close();
     }
     return _mdbVersion;
 }

        private AccountListItemDto Fetch(SafeDataReader dr)
        {
            var accountListItem = new AccountListItemDto();
            accountListItem.Id = dr.GetInt32("account_id");
            accountListItem.Name = dr.GetString("account_name");

            return accountListItem;
        }

        private OpenBatchListItemDto Fetch(SafeDataReader dr)
        {
            var openBatchListItem = new OpenBatchListItemDto();
            openBatchListItem.Num = dr.GetInt32("batch_num");
            openBatchListItem.Label = !dr.IsDBNull("batch_label") ? dr.GetString("batch_label") : null;

            return openBatchListItem;
        }

 private AccountDto Fetch(IDataReader data)
 {
     var account = new AccountDto();
     using (var dr = new SafeDataReader(data))
     {
         if (dr.Read())
         {
             account.Id = dr.GetInt32("account_id");
             account.Name = dr.GetString("account_name");
             account.Description = !dr.IsDBNull("account_dscr") ? dr.GetString("account_dscr") : null;
             account.Number = !dr.IsDBNull("account_number") ? dr.GetString("account_number") : null;
             account.Balance = (Decimal?)dr.GetValue("balance");
             account.LastReconciliationDate = !dr.IsDBNull("last_rec_date") ? dr.GetSmartDate("last_rec_date", true) : null;
             account.LastReconciliationBalance = (Decimal?)dr.GetValue("last_rec_balance");
         }
     }
     return account;
 }

        public IList<LocalizationDto> FetchLocalizationList()
        {
            var result = new List<LocalizationDto>();
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var connection = ctx.Connection;

                var commandText = string.Format(CultureInfo.InvariantCulture, 
                    @"
SELECT  loc.[Id],
        loc.[LocalName],
        loc.[EnglishName],
        loc.[CultureName],
        loc.[IconId],
        loc.[IsRightToLeft],
        loc.[IsDefault]
FROM    [dbo].[Localizations] loc
ORDER BY loc.EnglishName");
                using (var cmd = new SqlCommand(commandText, connection))
                using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    while (reader.Read())
                    {
                        var dto = new LocalizationDto
                        {
                            Id = reader.GetInt32(0),
                            LocalName = reader.GetString(1),
                            EnglishName = reader.GetString(2),
                            CultureName = reader.GetString(3),
                            IconId = reader.GetInt32(4),
                            IsRightToLeft = reader.GetBoolean(5),
                            IsDefault = reader.GetBoolean(6)
                        };

                        result.Add(dto);
                    }
            }

            return result;
        }

 private BatchEditDto Fetch(IDataReader data)
 {
     var batchEdit = new BatchEditDto();
     using (var dr = new SafeDataReader(data))
     {
         if (dr.Read())
         {
             batchEdit.Num = dr.GetInt32("batch_num");
             batchEdit.Date = !dr.IsDBNull("batch_date") ? dr.GetSmartDate("batch_date", true) : null;
             batchEdit.PayDate = !dr.IsDBNull("pay_date") ? dr.GetSmartDate("pay_date", true) : null;
             batchEdit.Amount = (Decimal?)dr.GetValue("batch_amount");
             batchEdit.JobNum = (int?)dr.GetValue("job_num");
             batchEdit.Description = !dr.IsDBNull("batch_dscr") ? dr.GetString("batch_dscr") : null;
             batchEdit.Updated = !dr.IsDBNull("updated") ? dr.GetSmartDate("updated", true) : null;
             batchEdit.ThankYou1 = !dr.IsDBNull("thank_you_1") ? dr.GetString("thank_you_1") : null;
             batchEdit.StudyTopic = !dr.IsDBNull("study_topic") ? dr.GetString("study_topic") : null;
             batchEdit.ThankYou2 = !dr.IsDBNull("thank_you_2") ? dr.GetString("thank_you_2") : null;
             batchEdit.MarketingResearchMessage = !dr.IsDBNull("marketing_research_message") ? dr.GetString("marketing_research_message") : null;
         }
         FetchChildren(dr);
     }
     return batchEdit;
 }

        public static ArrayList GetAmazingChartSchedule(string _beginDate, string _endDate)
        {
            ArrayList items = new ArrayList();
            using (SqlConnection cn = new SqlConnection(Database.AmazingChartsConnection))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "SELECT VisitID,Date,PatientID,Name,Phone,VisitType,"
                        + "Comments,Booker,DateBooked,ProviderID,Duration,XLinkProviderID,"
                        + "VisitIdExternal,DateLastTouched,LastTouchedBy,DateRowAdded"
                        + " FROM Scheduling"
                        + " where Date between '" + _beginDate + "'" + " and '" + _endDate + "'"
                        + " order by Date;";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                        {

                            items.Add(dr.GetInt32(0).ToString() + "~"
                                + dr.GetDateTime(1).ToShortDateString() + "~"
                                + dr.GetDateTime(1).ToShortTimeString() + "~"
                                + dr.GetInt32(2).ToString() + "~"
                                + dr.GetString(3) + "~"
                                + dr.GetString(4) + "~"
                                + dr.GetString(5) + "~"
                                + dr.GetString(6) + "~"
                                + dr.GetString(7) + "~"
                                + dr.GetDateTime(8).ToShortDateString() + "~"
                                + dr.GetInt32(9).ToString() + "~");
                        }
                        return items;
                    }
                }
            }
        }