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;}
    }
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

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;
    }
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.

获取

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;
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

作者：VipSoft