How to use Dapper to put a C# List into a database

c# dapper sql-server

Question

How can I insert a dapper using this?C# List data base. I previously used the following code to get to the values from the List into the database without dapper.

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've used dapper to get data before, but this is my first time using place query.

Using the code below, I attemptedExceute connected to the query but was having looping issues; I believe the dapper tool eliminates the requirement for looping statements.

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

I need your help, please. FYI: I useSQL Server 2008 .

1
67
5/2/2018 7:03:49 AM

Accepted Answer

You would need to approach it somewhat differently. The norm in Dapper is that property or field names should be the same as SQL parameters. Determining that you had aMyObject :

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

    public string B { get; set; }
}

In addition,processList = List<MyObject> , You should carry out this.

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

Observe that theMyObject The SQL parameters @A and @B have the same names as the properties A and B.

You may do the mappings using anonymous types rather than concrete types if you don't want to rename the objects:

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:

Marc Gravell mentioned that you could also ask Dapper to do the loop for you.

string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";        
connection.Execute(processQuery, processList);
122
7/26/2017 10:23:59 AM

Popular Answer

I think mass inserting is preferable than iterating the list and inserting each item one at a time.

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

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



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