How to manually specify parameter mapping?

[darshanrampatel]

Hi,

I'm trying to eliminate the call to sp_procedure_params_100_managed as I have full control of the stored procedures in my database and I want to eliminate the overhead (I see around ~50ms).

I think I should be able to do this by manually specify the mapping for my procedure. I've read the Query Parameter Mapping wiki but haven't found exactly what I need yet.

Is there any way to manually specify the mapping for the parameters?

[jonwagner]

The parameter query is cached in the library so you'll only get that hit once per query when your process starts up. I wouldn't go through the trouble to try to optimize that out.

(If you're seeing that it's not cached, then please open an issue for that.)

[darshanrampatel]

@jonwagner - is it possible though? I ask because we have a lot of databases and instances (which means a lot of unique connection strings; at least 1000) so the caching doesn't really help us much. I also am worried that we might hit #332 so it would be useful to be able to explicitly pass in the expected parameters and not use what may or may not be cached.

[jonwagner]

Well, we can definitely solve #332 if there is enough interest.

As for specifying parameters, the goal of Insight was to make all of that automatic, so there isn't a facility to do that manually at this point. How would you envision it working?

[darshanrampatel]

@jonwagner, how about something like:

var parameters = new List<SqlParameter>()
{
    new SqlParameter("@Param1", System.Data.SqlDbType.DateTime)
    {
        Value = DateTime.UtcNow
    },
    new SqlParameter("@Param2", System.Data.SqlDbType.Int)
    {
        Value = 12
    }
};
var results = await connection.QueryAsync<GetProcedure>(ControllerName, parameters);

[jonwagner]

One option is to build your own SqlCommand instances, and use Insight just to read the results:

var cmd = new SqlCommand("query");
cmd.Connection = new SqlConnection(connectionString);
cmd.AddParameters(...);
var results = cmd.QueryAsync(Query.Returns<GetProcedure>());

[darshanrampatel]

@jonwagner, that seems to be working fine for me - thanks!