Search code examples
sql-serverdapper

How do I get Dapper to ignore invalid data?

I'm using Dapper to load an int? value. The value of the int? is stored in a VARCHAR column in a SQL Server database (I can't change this). Occasionally the column contains invalid data that includes alpha characters.

When this happens I see this error:

Error parsing column 5 (Foo=ABC123 - String) ---> System.FormatException: Input string was not in a correct format.

How do I get Dapper to ignore invalid values and use null instead?

Solution

  • You can modify your SQL to parse and return the value accordingly on SQL Server itself. This way, there will be no changes in your calling code.

    I would do something like :

    select case when isnumeric(foo) = 1 then foo else null end foo from table;