Reusing Adapter/Command Builder without disposing

[John]

Hi

I am running below statement repeatedly with different SQL;

QuerySt = "SELECT * FROM ... "
CompanyAdapter = New OleDbDataAdapter(QuerySt, LocalConn)
CompanyBuilder = New OleDbCommandBuilder(CompanyAdapter)

Is there a way to avoid having to recreate the adapter and command builder
every time and reuse the previous adapter/command builder?

Thanks

Regards

 

[Gregory A. Beamer]

Hi

I am running below statement repeatedly with different SQL;

QuerySt = "SELECT * FROM ... "
CompanyAdapter = New OleDbDataAdapter(QuerySt, LocalConn)
CompanyBuilder = New OleDbCommandBuilder(CompanyAdapter)

Is there a way to avoid having to recreate the adapter and command builder
every time and reuse the previous adapter/command builder?

There are ways to reuse by storing the adapter and builder (Application
comes to mind). The problem with this direction is you end up tying up a
connection from the pool. As long as you are sure this particular adapter
will not be permanently locking up connections, it might not be horrible,
but if you cannot guarantee that, you can end up with situations like all of
the connection objects for the entire pool being locked up using instances
of the same adapter. Or, if you code in another direction, many users locked
up waiting for the adapter. Either one is an ouch.

You can avoid having to write the full code over and over again by putting
the actual creation of the adapter and builder in their own routine(s) and
returning them for your application.

NOTE: I assume "SELECT *" was just a sample to illustrate, but it is bad
form in real applications for a number of reasons.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************