使用 dapper 插入一对多实体

发布于 2024-11-25 18:48:50 字数 624 浏览 0 评论 0原文

我有以下两个类和相应的数据库表。我正在尝试插入完整的对象图(具有多门课程的学生)。我正在寻找一个关于如何使用 Dapper 执行此操作的示例。 Id 是自动递增的身份字段。

班级

public class Student
{
    public int Id {get;set;}
    public string Name {get;set;}
    public IEnumerable<Course> Courses {get;set;}
}
public class Course
{
    public int Id {get;set;}
    public string Name {get;set;}
}

学生
  Id [int] (pk)
名称 [varchar(50)]

学生课程
  学生 ID [int] (fk)
  课程 ID [int] (fk)

课程
  Id [int] (fk)
名称 [varchar(50)]

I have following two classes and corresponding db tables. I am trying to insert the full object graph (student with multiple courses). I am looking for an example on how would one do this using Dapper. The Id's are auto-increment identity fields.

Class

public class Student
{
    public int Id {get;set;}
    public string Name {get;set;}
    public IEnumerable<Course> Courses {get;set;}
}
public class Course
{
    public int Id {get;set;}
    public string Name {get;set;}
}

Table

Student
  Id [int] (pk)
  Name [varchar(50)]

StudentCourse
  StudentId [int] (fk)
  CourseId [int] (fk)

Course
  Id [int] (fk)
  Name [varchar(50)]

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

生寂 2024-12-02 18:48:50

Dapper 没有通用的帮助程序可以为您解决所有这些问题...但是,在简单的情况下连接起来非常容易:

Student student;
// populate student ... 

student.Id = (int)cnn.Query<decimal>(@"INSERT Student(Name) values(@Name)
select SCOPE_IDENTITY()", student);

if (student.Courses != null && student.Courses.Count > 0)
{
   foreach(var course in student.Courses)
   {
      course.Id = (int)cnn.Query<decimal>(@"INSERT Course(Name) values(@Name)
select SCOPE_IDENTITY()", course);
   }
   cnn.Execute(@"INSERT StudentCourse(StudentId,CourseId) 
values(@StudentId,@CourseId)", 
   student.Courses.Select(c => new {StudentId = student.Id, CourseId = c.Id}));
}

关于此示例的一个有趣的说明是 dapper 能够将 IEnumerable 作为输入参数处理并分派多个命令对于集合中的每个成员。这允许有效的参数重用。

当然,如果数据库中已经存在图表的某些部分,事情就会变得有点棘手。

Dapper has no general helper that would solve all of this for you ... however it is quite easy to wire in the trivial case:

Student student;
// populate student ... 

student.Id = (int)cnn.Query<decimal>(@"INSERT Student(Name) values(@Name)
select SCOPE_IDENTITY()", student);

if (student.Courses != null && student.Courses.Count > 0)
{
   foreach(var course in student.Courses)
   {
      course.Id = (int)cnn.Query<decimal>(@"INSERT Course(Name) values(@Name)
select SCOPE_IDENTITY()", course);
   }
   cnn.Execute(@"INSERT StudentCourse(StudentId,CourseId) 
values(@StudentId,@CourseId)", 
   student.Courses.Select(c => new {StudentId = student.Id, CourseId = c.Id}));
}

One interesting note about this sample is that dapper is able to handle IEnumerable as an input param and dispatch multiple commands for each member of the collection. This allows for efficient param reuse.

Of course, stuff gets a bit tricky if portions of the graph already exist in the db.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文