Dapper insert into database using list

Question

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:

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.'

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

Just in passing, you will likely be happier in the long term if you enumerate the column names in your INSERT statement: INSERT Into MyTable (ColA, ColB) Values (@ColA, @ColB). It's not usual that the schema for a table ends up subtly different in the same table in different environment-specific versions of the table (i.e., with time, ALTER TABLE scripts get run in different order on the Dev, QA, and Prod databases for the same table). — Flydog57
– Flydog57, Commented Feb 14, 2019 at 15:06
Regarding your new error: Allocated is a string in your model but a decimal in your table. Make the types compatible and the error should be resolved. — Igor
– Igor, Commented Feb 14, 2019 at 15:53

Igor · Accepted Answer · 2019-02-14 14:30:35Z

3

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.

edited Feb 14, 2019 at 14:30

answered Feb 14, 2019 at 14:25

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Igor Over a year ago

@Dave - seeing as this is a new/different problem the best thing to do would be start a new question. You can copy the new text from this question and then roll back the change after the copy/paste (in history you can rollback any edit).

Igor Over a year ago

@Dave - Regarding your new error: Allocated is a string in your model but a decimal in your table. Make the types compatible and the error should be resolved. If not or if it requires additional work/thought please consider creating a new question.

Kristóf Tóth · Accepted Answer · 2019-02-14 14:24:49Z

1

It is because you are not using query parameterization corretly. Start from the following example:

private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);

        // You have to define the parameters, and give the input which it
        // gets value from. This will be put into the query that the 
        // framework produces
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

You can read about this topic here

answered Feb 14, 2019 at 14:24

Kristóf Tóth

8315 silver badges19 bronze badges

2 Comments

stuartd Over a year ago

That's not necessary with Dapper, it can read the properties off the object and automatically assign them.

Kristóf Tóth Over a year ago

Yes thats true, but its an alternative if you for example can't produce mathing property names or smth. And ultimately this is what dapper produces in the end so it's easier to undestand this way.

Collectives™ on Stack Overflow

Dapper insert into database using list

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related