三行代码  ›  专栏  ›  技术社区  ›  Thimo Reumerman

如何将多个INSERT INTO与准备好的语句结合起来?

  •  1
  • Thimo Reumerman  · 技术社区  · 1 月前

    使用Dapper,是否有一种方法可以将其形成一个带有准备语句的查询? userIds

            public static void LinkUsersToChatroom(int chatroomId, int[] userIds)
            {
                using SqlConnection connection = new(connectionString);
    
                connection.Execute(
                "INSERT INTO chatroom_users (chatroomId, userId) VALUES (@chatroomId, @userId)",
                    new { chatroomId, userId = userIds[0] });
    
                connection.Execute(
                "INSERT INTO chatroom_users (chatroomId, userId) VALUES (@chatroomId, @userId)",
                    new { chatroomId, userId = userIds[1] });
    
                connection.Execute(
                "INSERT INTO chatroom_users (chatroomId, userId) VALUES (@chatroomId, @userId)",
                    new { chatroomId, userId = userIds[2] });
            }
    
    
    1 回复  |  直到 1 月前
        1
  •  3
  •   Steve    1 月前

    它的许多优点之一 Dapper 在ADO.NET上,将相同数据的列表插入到表中非常简单。
    您只需要一个类的列表,其中类的属性与数据库字段的名称相同,因此。。。

    private class ChatRoomUser
    {
        public int chatroomId {get;set;}
        public int userId {get;set;}
    }
    
    public static void LinkUsersToChatroom(int chatroomId, int[] userIds)
    {
        var users = userIds.Select(x => new ChatRoomUser
        { 
            chatroomId = chatroomId, 
            userIds = x
        };
        using SqlConnection connection = new(connectionString);
        connection.Execute("INSERT INTO chatroom_users (chatroomId, userId) VALUES (@chatroomId, @userId)", 
                            users);
    
    }