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