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; }
