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

November 30, 2021

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

>Solution :

One of the many advantages of Dapper over straight ADO.NET is the simplicity of inserting a list of the same data to your table.
You just need a list of a class 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)
{
    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);

}

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 create an overload of this method that accepts the list as its input.