I used Dapper ExecuteAsync
method to execute a SQL query with params.
insertedId =await connection.ExecuteAsync(query,params, transaction);
But this error occurs:
"Column name or number of supplied values does not match table definition."
The Good table definition:
[Id] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](50) NULL,
[Title] [nvarchar](200) NOT NULL,
[GoodEnumId] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
And the PaperDetail table definition:
[Id] [int] IDENTITY(1,1) NOT NULL,
[GoodId] [int] NOT NULL,
[Length] [float] NULL,
[Width] [float] NULL,
[Grammage] [float] NULL,
[Date] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
This is the query:
declare @Good1Scalar int
create table #Good1 (Id int)
insert into [Good] (Code, Title, GoodEnumId, Date, IsActive, IsDeleted)
output inserted.Id into #Good1
values (@Param2, @Param3, @Param4, @Param5, @Param6, @Param7)
SELECT @Good1Scalar = id from #Good1
drop table #Good1
insert into [PaperDetail](GoodId, Length, Width, Grammage, Date, IsActive, IsDeleted)
values (@Good1Scalar, @Param8, @Param9, @Param10, @Param11, @Param12, @Param13)
And this is my params value and DbType object (of DynamicParameters type):
What's wrong?
Update:
It's still unclear from the question what is the type of param
variable passed to Dapper's ExecuteAsync
call. The preferred way of passing parameters is through DynamicParameters
bag.
Here is the code that works with your table definitions and the query.
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Param2", "TheCode");
parameters.Add("Param3", "TheTitle");
parameters.Add("Param4", 4);
parameters.Add("Param5", "2018-01-28");
parameters.Add("Param6", true);
parameters.Add("Param7", false);
parameters.Add("Param8", 300);
parameters.Add("Param9", 30);
parameters.Add("Param10", 3);
parameters.Add("Param11", "2018-01-28");
parameters.Add("Param12", true);
parameters.Add("Param13", true);
var insertedId = await connection.ExecuteAsync(query, parameters, transaction);
Give it a try, it should work.