C# 使用 DbDataReader 来访问数据库

C# 使用SqlDataAdapter和DataSet来访问数据库
实体

namespace VipSoft.Entity
{
    [Table(Name = "PH_Prescription")]
    public class Prescription : Web.Core.Orm.Entity
    { 
        [Column(Name = "ID")]
        public String Id {get;set;}
        
        [Column(Name = "PATIENT_NAME")]
        public String PatientName {get;set;}
    }
}

DataReader扩展类，通过反射，进行实体赋值

public static class DbDataReaderExtensions
{
    public static List<T> ToList<T>(this DbDataReader dataReader)
    {
        List<T> list = new List<T>();
        using (dataReader)
        {
            while (dataReader.Read())
            {
                T model = Activator.CreateInstance<T>();
                foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                {
                    var columnAttribute = property.GetCustomAttributes(typeof(ColumnAttribute), false).FirstOrDefault() as ColumnAttribute;
                    if (!dataReader.IsDBNull(dataReader.GetOrdinal(columnAttribute.Name)))
                    {
                        Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                        property.SetValue(model, Convert.ChangeType(dataReader[columnAttribute.Name], convertTo), null);
                    }
                }
                list.Add(model);
            }
        }
        return list;
    }
}

获取

public List<Prescription> GetPrescriptionList()
{
    List<Prescription> result = new List<Prescription>();
    string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";
    string sql = $@"select * from PH_Prescription o with(nolock) where o.Status=0 ";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataReader reader = null;
        try
        {
            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            reader = command.ExecuteReader();
            result = reader.ToList<Prescription>();
        }
        catch (Exception e)
        {
            logger.Error(e, e.Message + " => " + sql);
        }
        finally
        {
            if (reader != null)
            {
                reader.Close();
            }
        }
    }
    return result;
}