Dapper 2.0.123 broke ExecuteImplAsync string mapping (AnsiString)

[amis92]

I'm using Dapper 2.0.90, and tried to update to 2.0.123, however the update caused my code to throw.

I'm working with Snowflake DB using Snowflake.Data.Client v1.2.8, and it worked perfectly fine with Dapper v2.0.90. After the update, both QueryAsync and ExecuteAsync that contain any string parameters are failing with:

Snowflake.Data.Client.SnowflakeDbException : No corresponding Snowflake type for type AnsiString.

This means that somewhere in the changes made between .90 and .123, there is a breaking change in how strings are mapped.

Is there a chance for this to be fixed?

[mgravell]

Without a repro? No. Can you show me something that exhibits this behaviour?

On Thu, 4 Nov 2021, 14:10 Amadeusz Sadowski, @.***> wrote:

I'm using Dapper 2.0.90, and tried to update to 2.0.123, however the update caused my code to throw.

I'm working with Snowflake DB using Snowflake.Data.Client v1.2.8, and it worked perfectly fine with Dapper v2.0.90. After the update, both QueryAsync and ExecuteAsync that contain any string parameters are failing with:

Snowflake.Data.Client.SnowflakeDbException : No corresponding Snowflake type for type AnsiString.

This means that somewhere in the changes made between .90 and .123, there is a breaking change in how strings are mapped.

Is there a chance for this to be fixed?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/DapperLib/Dapper/issues/1725, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAEHMC7UPOROR4FQS665MDUKKH3VANCNFSM5HLRAZDQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[vzalamea]

@amis92 did you check out the diff?

https://github.com/DapperLib/Dapper/compare/2.0.90...2.0.123?diff=split

[markledwich2]

Also hit this and downgrading to 2.0.90 worked. Sorry no repro steps.

[jyeros]

@mgravell I am having a similar issue in v2.0.123, string parameters work well for me, but DateTime doesn't work. The following code is able to reproduce it. The first part, the command execution works properly, but the Query throw an exception Snowflake.Data.Client.SnowflakeDbException: 'No corresponding Snowflake type for type AnsiString.'.

// The DataType of DATE is TIMESTAMP_NTZ
var sql = "SELECT * FROM <TABLE_NAME> WHERE DATE IN (:param_1_0)";
var paramValue = new DateTime(2003, 07, 15);

using var connection = new SnowflakeDbConnection { ConnectionString = "<connection_string>" };
connection.Open();

var cmd = connection.CreateCommand();
cmd.CommandText = sql;

var param_1_0 = cmd.CreateParameter();
param_1_0.ParameterName = "param_1_0";
param_1_0.DbType = System.Data.DbType.DateTime;
param_1_0.Value = paramValue;
cmd.Parameters.Add(param_1_0);
// Works 
var reader = cmd.ExecuteReader();
while (reader.Read())
{
	var obj1 = reader.GetValue(0);
	var obj2 = reader.GetValue(1);
	var obj3 = reader.GetValue(2);
	var obj4 = reader.GetValue(3);
	var obj5 = reader.GetValue(4);
	var obj6 = reader.GetValue(5);
	var obj7 = reader.GetValue(6);
	var obj8 = reader.GetValue(7);
}
// Throw Snowflake.Data.Client.SnowflakeDbException
var result = connection.Query<dynamic>(sql, new { param_1_0 = paramValue });

OS: Windows 11 Pro 21H2 (22000.469) Framework: .Net 6 (6.0.101) Dependencies: Snowflake.Data 2.0.9

Also, downgrading to 2.0.90 worked for me too.

[pedrommuller]

I'm getting an issue when passing null instead of an array in snowflake, any ideas?

[afilatov-st]

The SqlMapper.typeMap field contains this mapping: [typeof(DateTime)] = null, so when a DateTime parameter is created, its type will be set to a default one (AnsiString). The error can be mitigated by calling

Dapper.SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime);

Though I could not use command parameters anyway, neither as ? nor as :param_name -- I was getting errors from Snowflake.

[dpolyanskii]

@afilatov-st, thank you, I was able to write DateTime into Snowflake using your workaround.

[dazinator]

I am hitting this error too.

The snowflake driver throws if it is passed a paramater where the DbType is DbType.AnsiString: https://github.com/snowflakedb/snowflake-connector-net/blob/4cf73d42d6f40fc42608e3e88a642487a97ce09f/Snowflake.Data/Core/SFDataConverter.cs#L218

On windows I can run a test that uses dapper just fine to execute a sql query. Yet on linux, the query fails with this exception.

I haven't fully investigated yet, but it seems to me that because DbType.AnsiString = 0 it will be the default DbType for any parameters unless the DbType is explicitly set to something else.

My query looks like this:

           var docTypes = new List<string>();            

            docTypes.Add("AB");
            docTypes.Add("CD");
            docTypes.Add("EF");
            docTypes.Add("GH");
            docTypes.Add("IJ");

            var accountingYears = new List<string>();
            accountingYears.Add("2021");
            accountingYears.Add("2023");
            accountingYears.Add("2023");

            var results = conn.Query<TestInvoiceExtractionResponseItemDto>(sql,
                new { DocTypes = docTypes, CompanyCode="ABCD", AccountingYears= accountingYears, ItemStatus='C' });

As I say, running this on my local winndows machine, no errors.

Yet when running within a linux docker container on a linux (ubuntu) host, here is the stack trace:

Snowflake.Data.Client.SnowflakeDbException (0x80004005): Error: No corresponding Snowflake type for type AnsiString. SqlState: , VendorCode: 270053, QueryId: 
   at Snowflake.Data.Core.SFDataConverter.csharpTypeValToSfTypeVal(DbType srcType, Object srcVal)
   at Snowflake.Data.Client.SnowflakeDbCommand.convertToBindList(List`1 parameters)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternal(Boolean describeOnly)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1066
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1094
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 734

So my initial hypothesis is that for some reason, on linux, for string parameters, Dapper ends up creating parameters with a DbType of AnsiString (or not setting the DbType). Perhaps someone could comment on whether this has any merrit?

[dazinator]

My hypothesis was wrong.. The issue occurred when passing an empty list param. In my test the list wasn't empty. When running on test environment it was - and error was produced. Linux vs Windows was a red herring.

[dpolyanskii]

I had this issue when I was trying to do a bulk insert using Dictionary<string, object> as a parameter in this extension method:

public static Task<int> ExecuteAsync(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) =>
            ExecuteAsync(cnn, new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.Buffered, default));

Null values in my dictionary consistently led to Snowflake.Data.Client.SnowflakeDbException: 'No corresponding Snowflake type for type AnsiString.' because there is no way to infer a correct type for a null value.

[luizs81]

2 years later, any updates on this?

[mgravell]

Issue logged with snowflake ^^^