Search code examples
c#dapper

Dapper insert into database using list

I am using dapper and I'm trying to use the following tutorial for inserting a list into the database.

https://dapper-tutorial.net/knowledge-base/17150542/how-to-insert-a-csharp-list-to-database-using-dapper-net

I first thought from this example that it meant that @A @B had to be in my class, it was not obvious from the example that they had to be in my class.

public void ExportTOSql()
{
   string connectionString;
   connectionString = System.Configuration.ConfigurationManager.
   ConnectionStrings["Dapper"].ConnectionString.ToString();
     _salesOrders = Program.SageDatabaseHelper.FetchSoPOrdersODBC().OrderByDescending(o => o.OrderDate).ToList();

 using (SqlConnection conn = new SqlConnection(connectionString))
 {
     conn.Open();              
     string processQuery = "INSERT INTO SalesOrders VALUES (@OrderDate, @OrderNumber, @DespatchDate,@AccountReference,@CustomerOrderNumber,@Name,@TotalAmount,@Allocated,@Despatched,@Printed)"; 
     conn.Execute(processQuery, _salesOrders);

}

My Sales Order class is as follows and you can see OrderDate is there.

public class SalesOrder
{
    public DateTime OrderDate;
    public int OrderNumber;
    public byte OrderType;
    public string DespatchDate;
    public string AccountReference;
    public string CustomerOrderNumber;
    public string Name;
    public double TotalAmount;
    public string Allocated;
    public string Despatched;
    public bool Printed;
}

But as you can see from the screenshot, this is the message I got:

enter image description here

Edit 2 OK: I have gotten a step further thanks to help improving my knowledge on this. Now the structure is:

public class SalesOrder
{
    public int OrderNumber { get; set; }
    public DateTime OrderDate { get; set; }   
    public byte OrderType { get; set; }
    public DateTime DespatchDate { get; set; }
    public string AccountReference { get; set; }
    public string CustomerOrderNumber { get; set; }
    public string Name { get; set; }
    public double TotalAmount { get; set; }
    public string Allocated { get; set; }
    public string Despatched { get; set; }
    public bool Printed { get; set; }
}

And my export method is as follows:

public void ExportTOSql()
{
        string connectionString;
        connectionString = System.Configuration.ConfigurationManager.
ConnectionStrings["Dapper"].ConnectionString.ToString();
        _salesOrders = Program.SageDatabaseHelper.FetchSoPOrdersODBC().OrderByDescending(o => o.OrderDate).ToList();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            string processQuery = "INSERT INTO SalesOrders VALUES ( @OrderNumber,@OrderDate,@OrderType , @DespatchDate,@AccountReference,@CustomerOrderNumber,@Name,@TotalAmount,@Allocated,@Despatched,@Printed)";
            conn.Execute(processQuery, _salesOrders);

}

And my sql table is as follows, but now I am getting the following:

System.Data.SqlClient.SqlException: 'Error converting data type nvarchar to numeric.'

enter image description here

So the issue here is that it still fails sending the data to the SQL table.

Solution

  • It is because you are using fields and not properties in your model. Try adding {get;set;} to each field to make them properties.

    public class SalesOrder
{
    public DateTime OrderDate { get; set; }
    public int OrderNumber { get; set; }
    public byte OrderType { get; set; }
    public string DespatchDate { get; set; }
    public string AccountReference { get; set; }
    public string CustomerOrderNumber { get; set; }
    public string Name { get; set; }
    public double TotalAmount { get; set; }
    public string Allocated { get; set; }
    public string Despatched { get; set; }
    public bool Printed { get; set; }
}

    From the documentation you provided:

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

    Once you do that @OrderDate can be mapped back to the model's property OrderDate.