如何将sqldatareader转换为数据列表
本文关键字:数据 列表 转换 sqldatareader | 更新日期: 2023-09-27 18:15:47
我刚开始将所有ado.net代码从asp.net页面移动到repo,并为每个表(手动)创建dto,但现在我不知道将sqldatareader转换为dto对象列表的有效方法是什么?
例如,我的dto是Customer。
我正在使用webforms,我不使用ORM。我想慢慢来,慢慢来
通常模式看起来像:
List<Customer> list = new List<Customer>();
using(SqlDataReader rdr = GetReaderFromSomewhere()) {
while(rdr.Read()) {
Customer cust = new Customer();
cust.Id = (int)rdr["Id"];
list.Add(cust)
}
}
这里有一个关于如何使用数据阅读器检索数据的简短示例:
var customers = new List<Customer>();
string sql = "SELECT * FROM customers";
using (var cnn = new SqlConnection("Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;")) {
cnn.Open();
using (var cmd = new SqlCommand(sql, cnn)) {
using (SqlDataReader reader = cmd.ExecuteReader()) {
// Get ordinals (column indexes) from the customers table
int custIdOrdinal = reader.GetOrdinal("CustomerID");
int nameOrdinal = reader.GetOrdinal("Name");
int imageOrdinal = reader.GetOrdinal("Image");
while (reader.Read()) {
var customer = new Customer();
customer.CustomerID = reader.GetInt32(custIdOrdinal);
customer.Name = reader.IsDBNull(nameOrdinal) ? null : reader.GetString(nameOrdinal);
if (!reader.IsDBNull(imageOrdinal)) {
var bytes = reader.GetSqlBytes(imageOrdinal);
customer.Image = bytes.Buffer;
}
customers.Add(customer);
}
}
}
}
如果一个表列是可空的,那么在检索数据之前检查reader.IsDBNull
你一定要看看Massive——这个简单的ADO包装器。净
var table = new Customer();
//grab all
var customers = table.All();
//just grab from customer 4. This uses named parameters
var customerFour = table.All(columns: "CustomerName as Name", where: "WHERE customerID=@0",args: 4);