How to combine multiple INSERT INTO's with prepared statements?-CodePudding

Using Dapper, is there a way to form this into one query with the prepared statements? The length of userIds is dynamic.

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

CodePudding user response：

One of the many advantages of Dapper over straight ADO.NET is the simplicity of inserting a list of data to a table.
You just need a list of a class <T> where the properties of the class have the same name of your database fields, so...

private class ChatRoomUser
{
    public int chatroomId {get;set;}
    public int userId {get;set;}
}

public static void LinkUsersToChatroom(int chatroomId, int[] userIds)
{
    //Transform the array in an IEnumerable of ChatRoomUser
    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);

}

You can use this approach if you want to maintain the actual interface of your current method, but, of course, nothing prevents you to build the list directly where you build the array and pass the list instead of the two parameters or even create an overload of this method that accepts the list as its input.