How to insert a C# List to database using Dapper.NET

Question

Using dapper, how can I insert a C# List to database. Previously without dapper I used the below code to insert the List values to database.

try
{                
    connection.Open();

    for (int i = 0; i < processList.Count; i++)
    {
        string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@Id, @st_Time, @ed_Time, @td_Time)";
        command = new SqlCommand(processQuery, connection);
        command.Parameters.Add("Id", SqlDbType.Int).Value = processList[i].ID;
        command.Parameters.Add("st_Time", SqlDbType.DateTime).Value = processList[i].ST_TIME;
        command.Parameters.Add("ed_Time", SqlDbType.DateTime).Value = processList[i].ED_TIME;
        command.Parameters.Add("td_Time", SqlDbType.DateTime2).Value = processList[i].TD_TIME;
        dataReader.Close();
        dataReader = command.ExecuteReader();
    }

    connection.Close();
}
catch (SqlException ex)
{
    //--Handle Exception
}

I'm familiar with fetching the data using dapper but this is my first try using insert query.

I tried the below code, using Exceute linked to query but stuck up with looping; I think using dapper tool, there is no need for looping statement.

connection.Execute(processQuery ... );

EDIT:

class ProcessLog
    {
        public int ID { get; set; }
        public DateTime ST_TIME { get; set; }
        public DateTime ED_TIME { get; set; }
        public DateTime TD_TIME { get; set; }
        public string frequency { get; set; }
    }

Please advice on this. FYI: I'm using SQL Server 2008.

Solution 1

You'd have to do it a little differently. In Dapper, it matches on convention AKA property or field names being identical to SQL parameters. So, assuming you had a MyObject:

public class MyObject
{
    public int A { get; set; }

    public string B { get; set; }
}

And assuming processList = List<MyObject>, You'd want to do this

foreach (var item in processList)
{
    string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";        
    connection.Execute(processQuery, item);
}

Note that the MyObject property names A and B match the SQL parameter names @A and @B.

If you don't want to rename objects, you can use anonymous types to do the mappings instead of concrete types:

foreach (var item in processList)
{
    string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";        
    connection.Execute(processQuery, new { A = item.A, B = item.B });
}

EDIT:

Per Marc Gravell's comment, you can also have Dapper do the loop for you:

string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";        
connection.Execute(processQuery, processList);

Solution 2

I believe the bulk insert is better than iterate the list and insert one by one.

SqlTransaction trans = connection.BeginTransaction();

connection.Execute(@"
insert PROCESS_LOGS(Id, st_Time, ed_Time, td_Time)
values(@Id, @st_Time, @ed_Time, @td_Time)", processList, transaction: trans);

trans.Commit();

Reference: https://stackoverflow.com/a/12609410/1136277

Solution 3

Use Dapper.Contrib https://dapper-tutorial.net/insert#example---insert-single ! You can effortlessly insert list of objects in db.

Comments

[Marc Gravell]

Actually, little known fact: dapper will iterate for you; the middle one can be: connection.Execute("INSERT INTO PROCESS_LOGS VALUES (@A, @B)", processList);

[Praveen]

@DavidH I thought there will be no need for loops?

[Haney]

@MarcGravell - awesome, didn't know that. We built a fancy wrapper over Dapper at my job (which we internally call the DapperWrapper, haha) and it offers CRUD operations. We didn't have a need for bulk inserts/updates/upserts so we didn't map that method over. Good stuff!

[Steve]

My god, Marc you never stop to amaze me. Now I have to go and re-check all the code I've written in the past 10 days. (Since I decided to try this little gem)

[Haney]

@user1671639 - you must loop to insert multiple values, unless you build a VERY fancy SPROC that would be quite convoluted. See Marc's comment on how to have Dapper do the loop for you.

[Praveen]

@DavidH I'm facing an exception Must declare the scalar variable "@Id" should this be the same name given in the database?

[Haney]

@user1671639 can you paste your processQuery?

[Praveen]

@DavidH string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@Id, @st_Time, @ed_Time, @td_Time)"; you can refer the question above.

[Haney]

Your object that you passed to Dapper did not have a field or property named "Id"

[Praveen]

@DavidH Am I missing parameter?

[Haney]

Yes, you're saying "i am declaring @Id as a parameter that I will set" but you're not supplying a matching "Id" property or field on your object that you're passing to Dapper.

[Praveen]

@DavidH This working good, using your first approach. I'm trying to get it as Marc Gravell suggested. Something wrong in my code, I will try to fix it and let you know my mistakes Thanks.

[Praveen]

@MarcGravell Do we need to open the database connection when using Execute(ie., when I tried to use insert query, I faced an exception "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.")

[Marc Gravell]

@user1671639 that depends: which library version are you using? it is a feature in newer builds

[Praveen]

@MarcGravell I'm using Dapper NET40.

[Praveen]

@MarcGravell FYI: Also for my select statement I'm not opening any connection.

[Chris]

Is there any way to simulate (@a1, @b1),(@a2, @b2) with Dapper? I'm trying to use SQL Server MERGE tble USING VALUES (('a1', 'b1'),('a2','b2')) AS foo(a,b) ON tble.a=foo.a WHEN MATCHED.... If not I'd rather iterate and run many statements than use a temp table, but Dapper creating my bracketed list would be very nice. Am doubtful as I think it is vendor specific. Although MySQL uses comma-delimited brackets to do multiple row inserts so perhaps it isn't? (INS INTO tbl (a,b) VALUES (a1,b1),(a2,b2)).

[Mackan]

@MarcGravell Is it possible to do this using one static value and a list: con.Execute("INSERT INTO Many2Many VALUES (@A, @B)", new { A = parentId, B = listOfChildren) });? If not, will the param accept a Tuple?

[Marc Gravell]

@Mackan dapper won't, but LINQ: use listOfChildren.Select(childId => new { A = parentId, B = childId}) as the arg

[MaYaN]

@MarcGravell, How would one return all the inserted ids using this format? FYI here's something that does not work! stackoverflow.com/questions/39884768/…

[Andrei Bazanov]

@Haney is it not a bit dangerous to do inserts without specifying the columns as well? What if the table schema changes? I had negative experience by using this model in the past. The table fields moved around (order), new were added, etc., and my insert would not work any longer.

[Haney]

@AndreiBazanov yeah it definitely is dangerous. Best practice I'd say is to include the column names. This is an older answer - I have changed my ways since. ;)

[Bob Horn]

I don't believe that's a bulk insert. Individual inserts are done. See the comments in the link you provided.

[niico]

Is this not a REALLY bad idea performance wise? n round trips to SQL server? Just adding all the inserts to 1 string (somehow) then sending that to SQL Server would be far better surely.

[Vin Shahrdar]

Dumb question: What if my ID field in my object is auto-incremented, and I don't want to specify it? Is there a way to exclude it?

[Haney]

@VinShahrdar yup, make sure the object you're inserting doesn't have a property matching the ID.

[niico]

Anyone know if this does n round trips / execute to SQL server or does it neatly create 1 insert statement with multiple inserts? Surely the former will be very inefficient, specially if it's a long list?!