Insert a list using dapper.NET C#

Question

I'd like to insert a list of objects in an SQL table.

I know this question here but I don't understand.

Here is my class :

public class MyObject 
{
    public int? ID { get; set; }
    public string ObjectType { get; set; }
    public string Content { get; set; }
    public string PreviewContent { get; set; }

    public static void SaveList(List<MyObject> lst)
    {
        using (DBConnection connection = new DBConnection())
        {
            if (connection.Connection.State != ConnectionState.Open)
                connection.Connection.Open();

            connection.Connection.Execute("INSERT INTO [MyObject] VALUE()",lst);                
        }
    }
}

I'd like to know how could I insert my list using Dapper, I don't want to iterate on the list and save them one by one, I would like to insert all of them in one request.

Answer 1

You can insert these just as you would INSERT a single line:

public class MyObject 
{
    public int? ID { get; set; }
    public string ObjectType { get; set; }
    public string Content { get; set; }
    public string PreviewContent { get; set; }

    public static void SaveList(List<MyObject> lst)
    {
        using (DBConnection connection = new DBConnection())
        {
            if (connection.Connection.State != ConnectionState.Open)
                connection.Connection.Open();

            connection.Connection.Execute("INSERT INTO [MyObject] (Id, ObjectType, Content, PreviewContent) VALUES(@Id, @ObjectType, @Content, @PreviewContent)", lst);                
        }
    }
}

Dapper will look for class members named after your SQL parameters (@Id, @ObjectType, @Content, @PreviewContent) and bind them accordingly.

Answer 2

You need pass a table-value parameter.
1. Create table type in your sql database.
2. Create DynamicParameters and add the datatable (new values) to it.
3. Execute.

SQL:

CREATE TYPE [dbo].[tvMyObjects] AS TABLE(
    [ID] INT,
    [ObjectType] [varchar](70), /*Length on your table*/
    [Content] [varchar](70), /*Length on your table*/
    [PreviewContent] [varchar](70) /*Length on your table*/
)

C#:

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@MyObjects", lst
    .AsTableValuedParameter("dbo.tvMyObjects", new[] 
    {
        "ID" ,
        "ObjectType",
        "Content", 
        "PreviewContent"
    }));

connection.Connection.Execute(@"
    INSERT INTO [MyObject] (Id, ObjectType, Content, PreviewContent) 
    SELECT Id,
           ObjectType,
           Content,
           PreviewContent
    FROM   @MyObjects", dynamicParameters);

More info: https://www.codeproject.com/Articles/835519/Passing-Table-Valued-Parameters-with-Dapper