Caching or reusing parameters populated with SqlCommandBuilder.DeriveParameters

Hello,

I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!

So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.

My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?

LOL

I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.

I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object...put a file watch so that if the DB's change my params it re-pulls them again.

*nice*....

Then I get the best of both worlds...caching...and no parameter writing...

Ericsp_sproc_columns [[@.procedure_name =] 'name']
[,[@.procedure_owner =] 'owner']
[,[@.procedure_qualifier =] 'qualifier']
[,[@.column_name =] 'column_name']
[,[@.ODBCVer =] 'ODBCVer']

sp_stored_procedures [[@.sp_name =] 'name']
[,[@.sp_owner =] 'owner']
[,[@.sp_qualifier =] 'qualifier']

does that help?|||well yeah that's what I was going to do once I have the params in some cachable state...
I was just wondering if you could fetch the SqlParametersCollection from the Command object...alal IDbCommandParameters

and then cache those dudes...:)

I can manually interogate my hash for the parameters based on type key..."UpdSales", "InsSales","DelSales"...etc..|||those are the system procedures you need to get ALL the procedures from your database, then get their parameters.

Check out the details from books online.|||Duh...

I know that!

The SqlCommandBuilder.DerieveParameters(SqlCommand)

And those parameters have to stored somewhere in command object right?

Ahhh the Parameters which implments the IDbParameterCollection...

So can you fetch or store that Parameters collection...

That is what I"m asking...

I think I will just write the code "AsyncService" to fetch them at Application on start and then store them....I just don't like all those chatty calls ...that's all