c# - C# 使用 SqlDataReader 从 SQL Server 检索数据到主详细信息列表
问题描述
我有Master
和Detail
课程:
class Master
{
public int ID { get; set; }
public string Name { get; set; }
public List<Detail> Details { get; set; }
}
class Detail
{
public Description { get; set; }
public Amount { get; set; }
}
我使用下面的方法并且现在工作正常。
List<Master> result = new List<Master>();
// SQL Connection
string sqlCommand = "SELECT * FROM Master LEFT JOIN Detail on Master.ID = Detail.ID";
using (System.Data.SqlClient.SqlDataReader dr = db.DbDataReader as System.Data.SqlClient.SqlDataReader)
{
if (dr.HasRows)
{
Master LastMaster = null;
while (dr.Read())
{
if (LastMaster == null || Convert.ToInt(dr["ID"]) != LastMaster.ID)
{
Master h = new Master();
h.ID = Convert.ToInt(dr["ID"]);
h.Name = Convert.ToString(dr["Name"]);
result.Add(h);
LastMaster = h;
}
if (dr["Description"] == DBNull.Value)
continue;
if (h.Detail == null)
h.Detail = new List<Detail>();
Detail d = new Detail();
d.Description = dr["Description"] as string;
d.Amount = Convert.ToDouble(dr["Amount"]);
LastMaster.Detail.Add(d);
......
}
}
.....
}
有没有更好的方法来填充 C# 中的列表对象列表?我很感激任何建议。谢谢。
解决方案
您可以在您的场景中使用 Dapper(一种微型 ORM)。下面是一个示例代码
const string createSql = @"
create table #Users (Id int, Name varchar(20))
create table #Posts (Id int, OwnerId int, Content varchar(20))
insert #Users values(99, 'Sam')
insert #Users values(2, 'I am')
insert #Posts values(1, 99, 'Sams Post1')
insert #Posts values(2, 99, 'Sams Post2')
insert #Posts values(3, null, 'no ones post')";
using(var connection = new SqlConnection("database connection string"))
{
connection.Execute(createSql);
try
{
const string sql =@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
}
catch(Exception ex){}
}
推荐阅读
- visual-studio - 在 Visual Studio 2017 15.9 升级后,WebDeploy 失败并出现 SSL/TLS 错误
- ssl - Websphere 管理控制台:禁用从 http 到 https 的重定向
- java - JavaFX:如果另一个对象处于活动状态,则 FXML 脚本禁用按钮
- docker - How to use docker container as host with ansible running in a docker container on gitlab-ci?
- c# - How do I prevent a SaveFileDialog from prompting twice to replace/overwrite a file?
- linux - 使用python将文本文件中的文件列表复制到文件夹
- gitlab - kiwi tcms - Gitlab bug tracker Integration
- java - 在单个 json POST 上保存父母和孩子
- angular - How to set HTTP Params in Angular without the key?
- jenkins - jenkins中用于多个邮件ID的邮件配置