Question: Is there a way to setup/define dynamic parameter as in Dapper?

[rafek1241]

I've got RAW SQL + expando object with parameters list and in the database field that I'm looking for is of type VARCHAR(200) and my string parameter is parsed by repoDB to NVARCHAR(XXX) which causes slow performance when querying data.

I mitigate this issue by using:

            var @params = new
            {
                Id = id
            };

            TypeMapper.Add<string>(DbType.AnsiString);
            var data = await connection.ExecuteQueryMultipleAsync(query, @params);
            TypeMapper.Remove<string>();

but I'd rather to have something like in dapper:

var parameters = new DynamicParameters();
var customerId = "ALFKI";
parameters.Add("@CustomerId", customerId, DbType.String, ParameterDirection.Input, customerId.Length);
var sql = "select * from customers where CustomerId = @CustomerId";
using (var connection = new SqlConnection(connString))
{
    var customer = connection.QuerySingle<Customer>(sql, parameters);
}

reference: https://www.learndapper.com/parameters

Is it doable in the repodb? Can we select datatype for a query parameters?

[mikependon]

AFAIK, RepoDB is leveraging the default behavior of ADO.NET when passing the parameters via parameterized SQL. But allow us to double check this.

There is no other way to override the data type you passed into the parameter, unlesd of course you use the type mapper.

Have you also tried using the QueryField and see if that has change the thing? See here

[rafek1241]

@mikependon
For that approach what is missing for me is that I can't explicitly decide what DBType will that be

[rafek1241]

Example from https://repodb.net/operation/executequery#queryfieldquerygroup:

  var param = new QueryGroup(new []
    {
        new QueryField("Id", 10045)
    });

I wish to have:

  var param = new QueryGroup(new []
    {
        new QueryField("Id", DBType.AnsiString,10045)
    });

[rafek1241]

If we would like to have it ADO.NET-like then at least something like this would be great:

 var results = await _connection.ExecuteQueryMultipleAsync(
                query,
                new { Id = new Microsoft.Data.SqlClient.SqlParameter("Id", SqlDbType.VarChar) }
            );

[mikependon]

Yes, we got it. But, there is a logic behind where RepoDB auto detects the type of the column from the DB table and associate the proper type to your command parameter, but AFAIK, by executing via raw SQL it is leveraging the default behavior of ADO. So the NVARCHAR(*) is something like ADO.NET default behavior.

[rafek1241]

Unfortunately using classes and no raw SQL is not an option in the company that I'm working in. I will create enhancement request for QueryField datatype parameter possibility.

[mikependon]

That would work. We will look on this enhancement request afterwards.

[rafek1241]

No description provided.