How to use Dapper to insert a list?

c# dapper list sql

Question

In a SQL database, I want to enter a list of items.

I am aware of the answer to question here, however I am confused.

This 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 want to know how I can input my list using Dapper; I don't want to go through the list iteratively and save each item separately; I want to insert them all at once.

1
7
5/23/2017 11:55:01 AM

Accepted Answer

Similar to how you would INSERT a single line, you may insert these:

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

As you provide your SQL parameters (@Id, @ObjectType, @Content, and @PreviewContent), Dapper will search for class members with those names and bind them appropriately.

13
5/16/2017 2:02:46 PM

Popular Answer

A table-value argument is required.
1. In your SQL database, create a table type.
2. Create DynamicParameters and populate it with the datatable's updated values.
3. Carry out.

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

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



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow