Proper way to Cache DbParameters

Topics: Data Access Application Block
Jan 26, 2007 at 8:59 PM
I use Oracle Stored procedures for all of my data access. I was using db.DiscoverParameters(command) to query the database for all required parameters for the specified command object.

Instead, I am now caching the parameters so there is no need to make additional trips to the database after the first call.

I came up with the following code to retrieve the command's parameters from either the cache or the database:

private static void GetParameters(Database db, DbCommand command)
{
string strCacheKey = string.Concat(db.ConnectionStringWithoutCredentials, command.CommandText);

if(cacheManager.Contains(strCacheKey))
{
foreach (DbParameter p in cacheManager.GetData(strCacheKey) as DbParameterCollection)
// Cannot add REF_CURSOR parameters otherwise the command would fail
if(!p.ParameterName.ToUpper().Equals("CUR_OUT"))
db.AddParameter(command, p.ParameterName, p.DbType, p.Direction, p.SourceColumn, p.SourceVersion, null);
}
else
{
db.DiscoverParameters(command);
cacheManager.Add(strCacheKey, command.Parameters);
}
}

After adding the parameters from the cache, the resulting db.ExecuteXXX command would fail if the REF_CURSOR parameter was added. I added the IF statement to block it from being added - I don't think the way in which I have done this is the best. Any ideas as to another way?

Does all of this make sense? Are the additional trips to the database that much of an overhead that would warrant adding this code in?

Thanks.
Jan 27, 2007 at 6:28 PM
Hello,

If you use parameterdiscovery, the daab caches these parameters allready for you. So the roundtrip will only be made the very first time.

Best regards,
Ike