1

I am using .net as backend, and SQL Server as the database. I am inserting records into the database in 2 ways, first way is using the crud operations and the second way using a SQL Server stored procedure that is called using dapper.

When using Dapper, the long string is auto trimmed, but when using the crud operations, the insert fails with the following error:

String or binary data would be truncated

I just want to know if Dapper is responsible for trimming these strings on its own or not.

Here is the code:

int result = await unitOfWork.GetService<IUserService>().AddUserAsync(userModel); // here I get an error during insert

and here using dapper:

return await GetDapper().ExcecuteScalarAsync<int>("dbo.insert_user", new { @userId = userId, @userName = userName });
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wael
  • 1,533
  • 4
  • 20
  • 35

1 Answers1

1

The silent truncation is done not by Dapper, but by the stored procedure in SQL Server. You can easily test this: A CRUD operation would error when the string is too long, but the stored procedure doesn't:

CREATE TABLE Person (Name VARCHAR(4));

GO

INSERT INTO Person VALUES ('John Smith');
-- Error: String or binary data would be truncated.

GO

CREATE PROCEDURE InsertPerson 
    @Name VARCHAR(5)
AS
    INSERT INTO Person VALUES (@Name);

GO

EXECUTE InsertPerson 'Jane Smith';
-- Inserts 'Jane'

There are many discussions about this behaviour; for example: SQL Server silently truncates varchar's in stored procedures.

Douglas
  • 53,759
  • 13
  • 140
  • 188