Ado.net and Sp's Parameters in cache?

Question

Im reading the code for Microsoft.ApplicationBlocks.Data ( code is here )

But I've notice something strange :

in one of the function ( executeNonQuery) he tries to read Sp's param from cache and if not exists , he put them into the cache ( not asp.net cache - but an internal HashSet)

public static int ExecuteNonQuery(string connectionString, string spName, params SqlParameter[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            //------------------------------------

            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

           //------------------------------------
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
            // Call the overload that takes an array of SqlParameters
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
        }
    }

Please look at the isolated line.

Why did they do that? How does it help me if I have the params in Cache ? Im gonna send the params anyway from my dal ...( and I must send my params to a SP)...

What am I missing ?

Maybe AssignParameterValues() throws exception if the parameterValues dont match commandParamters, stopping the execution before the database is "touched"? Can you post that method? — StingyJack, Oct 25 '12 at 16:41
@StingyJack I checked the source, it does throw an exception if the the two parameter arrays don't match. — Eric H, Oct 25 '12 at 17:11

score 0 · Accepted Answer · answered Oct 25 '12 at 17:16

0

Without the caching, they'd end up calling SqlCommandBuilder.DeriveParameters for each call to ExecuteNonQuery, for which MSDN states:

DeriveParameters requires an additional call to the database to obtain the information. If the parameter information is known in advance, it is more efficient to populate the parameters collection by setting the information explicitly.

So it makes sense to cache the retrieved parameter information to avoid these extra calls.

UPDATE:

Im gonna send the params anyway from my dal ...( and I must send my params to a SP)...

Note the int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) overload -- you may or may not send the parameters.

answered Oct 25 '12 at 17:16

Eren Ersönmez

38,383
7
71
92

why do i need the parameter information anyway? why do I need to call the DB to get the SP information ? What's wrong with send the param+value ? please remember that `SQLPARAMETER` has a `ctor ` `public SqlParameter(string parameterName, object value);` which **doesnt need parameter information** , just the name and value.... – Royi Namir Oct 25 '12 at 19:14
@RoyiNamir You could use that `SqlParameter` ctor (or `AddWithValue(string, object)`); however you'd need to assume the implicit conversion from the CLR type to T-SQL type will be accurate. There may be cases where this is not the case; e.g. the stored proc param is `varchar(2)` and you send the string "Hello world" which will be implicitly converted to `nvarchar(11)`. Besides the size problem, you may have performance issues as explained [here](http://stackoverflow.com/questions/345323/addwithvalue-without-dbtype-causing-queries-to-run-slowly). – Eren Ersönmez Oct 26 '12 at 08:48
Ok , so If a sql DB table has varchar(2) as param type (and im about to send a string), this `DeriveParameters ` thing should be checked by me **BEFORE** send it to the SP ? is that why this `DeriveParameters` exists for ? verify one by one ? – Royi Namir Oct 26 '12 at 10:19
For example, say you have a stored procedure `GetCustomersByState(@state char(2))`. As the .Net developer, if you know the SP parameters in advance, there is no need to call DeriveParameters (you can just create a `new SqlParameter("@state", SqlDbType.VarChar, 2)`)? But note that the API in question allows you to simply pass the string "CA" as a parameter (without specifying the _name_ or the _type_ of the parameter). – Eren Ersönmez Oct 26 '12 at 11:01
Thanks for answering , so if the sqlParmeters are filled from `DeriveParameters` and im sending long string to a varchar(2) param , will it go exception or truncate ? – Royi Namir Oct 26 '12 at 11:49
It will truncate the value to the first 2 characters. – Eren Ersönmez Oct 26 '12 at 15:58
and if i send it with `SqlParameter( "@parameterName", LongString;` )? the one who will truncate it will be the sql server....right ? so any way it will truncate....what am i gaining here ? sorry for all those questions ....:-) – Royi Namir Oct 26 '12 at 16:17

Ado.net and Sp's Parameters in cache?

1 Answers1