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

Caching Deleted

I cached about 7 reports last night, and they all looked fine and worked
real fast this morning.
However, something happened today and now the caching is gone? What
would delete the cache? Does the cache delete when I deploy new
reports?
I have all of my reports set to use a shared schedule to delete the
cache.
Any suggestions?
Thanks...Check the cache configuration to see when they're set to expire and to
rerun.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:OC$swB28EHA.2192@.TK2MSFTNGP14.phx.gbl...
>I cached about 7 reports last night, and they all looked fine and worked
>real fast this morning.
> However, something happened today and now the caching is gone? What would
> delete the cache? Does the cache delete when I deploy new reports?
> I have all of my reports set to use a shared schedule to delete the cache.
> Any suggestions?
> Thanks...
>

C#/Delphi

Hi Tim,
I am new to the sql world, and am not the most knowledgeable programmer.
Why is VB not a real development tool?
why are C# and Delphi so much better?
I am doing my first bit of commercial production. It is not supposed to be state of the art, but I will likely use VB with SQL. I know it will work and there's lots of code on the web. Just curious about the merits of using something else.
andrew.
Hi Andrew,
VB is a real development tool. I write both VB and C# regularly and they are
functionally equivalent. In fact, even though VB is "wordier", I can write
it faster because the Intellisense works better. For large projects, we've
also had significant (and as yet unresolved) issues with C# in the Visual
Studio IDE. These problems don't affect us with VB.
Most of the comments regarding VB date from the days when VB was limited by
comparison with C++. They just aren't valid anymore when comparing it with
C#.
While I loved Delphi from the minute I first saw it, it just doesn't have
the market share and it always seems to be playing catch-up in many areas.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"andrewnichols" <anonymous@.discussions.microsoft.com> wrote in message
news:E1D9DBB6-E5A9-4353-966E-AB74E760C59E@.microsoft.com...
> Hi Tim,
> I am new to the sql world, and am not the most knowledgeable programmer.
> Why is VB not a real development tool?
> why are C# and Delphi so much better?
> I am doing my first bit of commercial production. It is not supposed to be
state of the art, but I will likely use VB with SQL. I know it will work and
there's lots of code on the web. Just curious about the merits of using
something else.
> andrew.