Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 855
Praveen Avatar asked Jun 17 '13 14:06

Praveen


People also ask

How do you declare an array in C?

To create an array, define the data type (like int ) and specify the name of the array followed by square brackets []. To insert values to it, use a comma-separated list, inside curly braces: int myNumbers[] = {25, 50, 75, 100}; We have now created a variable that holds an array of four integers.


1 Answers

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); 
like image 191
Haney Avatar answered Oct 15 '22 10:10

Haney