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.

Pierre-Loup Pagniez · Accepted Answer · 2017-05-16 14:02:46Z

23

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.

answered May 16, 2017 at 14:02

Pierre-Loup Pagniez

3,7813 gold badges31 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Ducroquet Over a year ago

It works, but for 1000 objects, it's still slow, exactly the same when i used iteration over the list and save them one by one :/

Pierre-Loup Pagniez Over a year ago

Try this answer to see if it helps with performance: stackoverflow.com/a/12609410/1166719. By the way, hi! Long time no see :)

Ykok Over a year ago

This creates a request per item in the list, thereby not solving the question.

Daniel Tshuva · Accepted Answer · 2017-05-16 14:16:35Z

1

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

edited May 16, 2017 at 14:16

answered May 16, 2017 at 14:08

Daniel Tshuva

5035 silver badges12 bronze badges

2 Comments

LearningPal Over a year ago

what is lst? is it List<classObject>? or datatable?

Daniel Tshuva Over a year ago

lst is List<classObject> (it's from the question). The AsTableValuedParameter function transform a list to datatable (codeproject.com/Articles/835519/…)

Charles Mager · Accepted Answer · 2017-05-16 14:04:06Z

0

You would just change your SQL to a valid insert statment that has parameters matching the names of the properties on your class.

INSERT INTO MyObject VALUES(@Id, @ObjectType, @Content, @PreviewContent)

Or if you need to specify the table columns (where these aren't all the columns in the table, for example):

INSERT INTO MyObject (Id, ObjectType, Content, PreviewContent)
VALUES(@Id, @ObjectType, @Content, @PreviewContent)

edited May 16, 2017 at 14:04

answered May 16, 2017 at 13:59

Charles Mager

26.3k2 gold badges41 silver badges52 bronze badges

2 Comments

Daniel Tshuva Over a year ago

The question was about insert a list in bulk to database. no with loop in c# code.

Charles Mager Over a year ago

@DanielTshuva this is the same as the accepted answer?

Ron · Accepted Answer · 2017-07-28 14:21:02Z

0

You can use the Dapper.Contrib extensions to simplify the code. I've found this works well for a few hundred records, but for very large inserts, I switch to SqlBulkCopy. The synchronous version is Insert instead of InsertAsync (as you'd guess). Make sure your entities are named as Dapper expects and have a primary key, Id, or, add annotations for table name and key to your entity.

using using Dapper.Contrib.Extensions; //Git

public async Task SaveChangesAsync(IList<MyEntity> myEntityValues)
{
     var conn = new SqlConnection(myconnectionString);
     if(conn.State != ConnectionState.Open)
         conn.Open();
     await conn.InsertAsync(myEntityValues);
     if (conn.State != ConnectionState.Closed)
     {
        conn.Close();
        conn.Dispose();
      }
}

edited Jul 28, 2017 at 14:21

answered Jul 27, 2017 at 14:45

Ron

91711 silver badges26 bronze badges

1 Comment

Patrick Tucci Over a year ago

While this code works, it should ideally instantiate the connection in a using statement, or wrap it in a using block, to ensure it gets closed and disposed correctly.

John Jang · Accepted Answer · 2021-04-27 03:58:59Z

0

In case you need new naming or combined sources:

await connection.ExecuteAsyncWithRetry(SqlSave,
     list.Select(x => 
            new
            {
                x.ID,
                SomeNew = NewSource.SomeNew,  // From other source
                NameNew = x.NameOld,  // New naming
                x.ObjectType,
                x.Content,
                x.ContentList = String.Join(",", x.Content)  // Data transform
            }));

edited Apr 27, 2021 at 3:58

answered Apr 27, 2021 at 3:35

John Jang

3,0551 gold badge27 silver badges29 bronze badges

Collectives™ on Stack Overflow

Insert a list using dapper.NET C#

5 Answers 5

3 Comments

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related