c# - Dapper 使用列表插入数据库

Question

我正在使用 dapper，我正在尝试使用以下教程将列表插入数据库。

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

我首先从这个例子中想到这意味着@A @B 必须在我的类(class)，从这个例子中他们必须在我的类(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);

}

我的销售订单类如下，您可以看到 OrderDate 在那里。

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

但是正如您从屏幕截图中看到的，这是我收到的消息:

编辑 2OK:感谢帮助我提高了这方面的知识，我又向前迈进了一步。现在的结构是:

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

而我的导出方法如下:

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

}

我的 sql 表如下，但现在我得到以下信息:

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

所以这里的问题是它仍然无法将数据发送到 SQL 表。

Answer 1

那是因为你用的是fields而不是 properties在你的模型中。尝试将 {get;set;} 添加到每个字段以使其成为属性。

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

根据您提供的文档:

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

一旦您这样做了，@OrderDate 就可以映射回模型的 property 订单日期。