Getting parameter information from custom source

[henkosch]

I have noticed that the SqlInsightDbProvider makes 2 database calls before each stored procedure call in order to retrieve the list of parameters and also information about table value parameters so it can do the proper binding.

Here they are:

public override void DeriveParametersFromStoredProcedure(IDbCommand command)
{
    if (command == null) throw new ArgumentNullException("command");

    SqlCommand sqlCommand = command as SqlCommand;
    SqlCommandBuilder.DeriveParameters(sqlCommand);

public override void SetupTableValuedParameter(IDbCommand command, IDataParameter parameter, System.Collections.IEnumerable list, Type listType)
{
    ...

    // see if we already have a reader for the given type and table type name
    // we can't use the schema cache because we don't have a schema yet
    var key = Tuple.Create<string, Type>(tableTypeName, listType);
    ObjectReader objectReader = (ObjectReader)_tvpReaders.GetOrAdd(
        key,
        k => command.Connection.ExecuteAndAutoClose(
            _ => null,
            (_, __) =>
            {
                using (var reader = GetTableTypeSchema(command, parameter))
                    return ObjectReader.GetObjectReader(command, reader, listType);
            },
            CommandBehavior.Default));

private static IDataReader GetTableTypeSchema(IDbCommand command, IDataParameter parameter)
{
    if (command == null) throw new ArgumentNullException("command");

    // select a 0 row result set so we can determine the schema of the table
    SqlParameter p = (SqlParameter)parameter;
    string sql = String.Format(CultureInfo.InvariantCulture, "DECLARE @schema {0} SELECT TOP 0 * FROM @schema", p.TypeName);
    return command.Connection.GetReaderSql(sql, commandBehavior: CommandBehavior.SchemaOnly, transaction: command.Transaction);
}

In DeriveParametersFromStoredProcedure the SqlCommandBuilder.DeriveParameters is always called before calling a stored procedure, which always makes a database request for the parameter list and the results are not cached.

In the second case in SetupTableValuedParameter it seems that you cache the retrieved table type information, but I'm not sure if it works, because I can see these requests in the Intellitrace Events window before each stored procedure call.

In my project I'm using a Visual Studio Database Project to develop and deploy my database schema. This means that my database always matches with the schema that I have in that project. The Database Project outputs a .dacpac file which contains all the database objects that are defined in the project. It can easily be read by a library called DacFx.

I was wondering if it would be possible to read the parameter information and table type information from this locally available dacpac file instead of always making a request to the database to retrieve them before each stored procedure call. Why should I always query the database for schema information that is already available. I'm not sure about the performance costs of these extra requests, but it seems quite unnesessary to me.

I'm thinking about implementing a custom db provider that inherits from SqlInsightProvider and overriding DeriveParametersFromStoredProcedure and SetupTableValuedParameter, so that instead of making those database requests they could get the required schema information from some kind of schema information store interface which I could implement with the dacpac reader.

The problem is that I don't know anything about what exactly SqlCommandBuilder.DeriveParameters does or what type information do I need in what form.

What do you think about these questions? Would it be worth to try something like this?

[jonwagner]

It appears that the results aren't cached, but the schema (either proc signature or TVP signature) is used to create a dynamically-generated IL method, which IS cached. So you should only see a call to DeriveParameters on the first call to a procedure (or a one time fetch of the TVP signature).

If you're seeing more than one, let me know and we can fix that.

IMO, it's probably not worth the effort to eliminate the one-time setup calls. There are a lot of edge cases that you would have to account for, and you would be introducing another dependency (Insight -> DacFx -> SQL, instead of Insight->SQL).

But if you wanted to, DeriveParametersFromStoredProcedure just needs to create an appropriate set of SqlParameters on the command. Insight uses those as a template and clones them for each call.

SetupTVP has to create a recordset with a properly populated IDataReader.GetSchemaReader(). This varies from provider to provider and isn't well documented.

[jonwagner]

TVPs (for SQL Server, at least), are cached in SqlInsightDbProvider._tvpReaders.

It's keyed off of this:

var key = Tuple.Create<string, Type>(tableTypeName, listType);

So, if you're binding a given table to two different types of lists (e.g. List and IList), you may see more than one call to get the schema of the table type. I suppose that could be cached at the tabletype level, but then we'd be keeping the schema around when we don't need it, and that has a bigger memory footprint.)

[jonwagner]

I double-checked to make sure that the caching is working as designed. Let me know if there is a specific case that doesn't seem to behave properly.