'Must declare the scalar variable "@SKU".'

Question

I am using dapper to insert to a database, have been looking at my code to find what has happened, could not find anything out of the ordinary.

The code works with the SELECT statement, but not when I do an insert I always get the error:

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@SKU".

When I remove the first parameter from the database, the class, the function and the procedure, I always get the same error for the first parameter.

public class Products
{
    public string SKU;
    public string Title;
    public string ImageLink;
}

Using dapper function to insert:

public void insertItem(Products newProduct)
{
    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SellersDB")))
    {
        List<Products> dbnewProduct = new List<Products>();
        dbnewProduct.Add(newProduct);

        connection.Execute("dbo.Item_Insert @SKU, @Title, @ImageLink", dbnewProduct);
    }
}

The procedure:

CREATE PROCEDURE [dbo].[Item_Insert]

@SKU nchar(10),
@Title nchar(100),
@ImageLink nchar(50)

AS
BEGIN
SET NOCOUNT ON;

insert into dbo.ProductsTable (SKU, Title, ImageLink) values (@SKU, @Title, @ImageLink);

END

The database:

CREATE TABLE [dbo].[ProductsTable] (

[SKU]       NCHAR (10)  NULL,
[Title]     NCHAR (100) NULL,
[ImageLink] NCHAR (50)  NULL
);

The error happened on the excite line.

Answer 1

Dapper wants properties, not fields; try:

public class Products
{
    public string SKU {get;set;}
    public string Title {get;set;}
    public string ImageLink {get;set;}
}

and try again; you only need to pass the single object - you don't need a list here. You can combine this with the CommandType.StoredProcedure approach if you choose (as noted by MarcinJ) - but be careful that this exchanges positional parameter passing (in the original question) to named parameter passing - so be sure to check that this doesn't change the meaning.