Dapper Not able to parse column String

Question

I have the following table

create table tblWorkers
(
    Id int identity,
    WorkerId nvarchar(8),

    Username NVARCHAR(50) not null,
    Password NVARCHAR(12) not null,
    Token NVARCHAR(max) null,
    CONSTRAINT PK_WorkerId PRIMARY KEY (WorkerId)
)

When I try the following Dapper code, by passing Username:W01, Password=check123 I get an error, Error parsing column 0 (UserId=W01 - String)'.FormatException: Input string was not in a correct format.

``

        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@userName", loginModel.Username, DbType.String);
        parameters.Add("@password", loginModel.Password, DbType.String);
        using (IDbConnection con = _connectionManager.GetConnection())
        {
            con.Open();
            var result = con.Query<User>(StoredProcedures.uspAuthenticate, param: parameters, commandType: CommandType.StoredProcedure);
            return result.FirstOrDefault();

`` StoredProcedure is as below.

CREATE PROCEDURE [dbo].[uspAuthenticate] 
    @userName nvarchar(8),
    @password nvarchar(12)
AS
BEGIN

    SET NOCOUNT ON;

    select WorkerId
    from tblWorkers
    where Username = @username and Password = @password
END

Please advise.

This is not a real-world production app where you're storing passwords in plain text, right? — squillman, Oct 28 '19 at 12:36
You can check the examples in the following link: https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper — Ibrahim, Oct 28 '19 at 12:55
Please include the definition of your `User` class. It's probably an error deserializing `User.UserId` from the response. — AlwaysLearning, Oct 28 '19 at 12:55
The error says mentions it has a problem parsing UserId, but your stored procedure only returns WorkerId. You need to post your User class. Also, you have Username defined as both nvarchar(50) and nvarchar(8). Pick one. — Robert McKee, Oct 28 '19 at 14:39
Also, if WorkerId is a natural primary key, then you probably should not have Id in your tblWorkers table, and should just remove that column unless there is a compelling reason (HIPAA, etc). — Robert McKee, Oct 28 '19 at 14:45

score 1 · Accepted Answer · answered Nov 04 '19 at 20:45

1

Dapper expects SQL and .NET to be the same. The error mentions UserId, code examples show parameter as username (not id).

you say 'when passing: Username:W01' The error shows 'Error parsing column 0 (UserId=W01 - String)'

answered Nov 04 '19 at 20:45

SMY

26
4

score 1 · Answer 2 · answered Mar 04 '23 at 13:00

1

Also check the type. I do not think the case needs to match but the type does. In my case the class field was int but SQL was returning varchar2.

answered Mar 04 '23 at 13:00

Rob

2,363
7
36
54

Dapper Not able to parse column String

2 Answers2

Linked