VB.NET and Connection pooling...

[PogoWolf]

Hey all,
I'm wondering how to go about setting up connection pooling with in my VB.NET App. What I'm looking to do, is create somesort of way to set up 1 connection to the database (access) and then leave it open. Then by setting that connection global (or by a class or something) use subs and functions that will create a new connection, Query the data, and then close the connection.

first off, is this description correct for even HOW to setup connection pooling? and if so, how would one go about doing it?


The PogoWolf

http://pogowolf.GamersCommune.com

 

[chiph]

What type of app are you writing? If you're doing a web-based app, or writing a DLL, then you want to open a database connection on a as-needed basis. If you're writing a WinForms app, then opening a connection and leaving it open might be acceptable (depends on your app).

But in both cases, as long as the connection string is identical, ADO.NET will cache connection objects, making subsequent connections to the database much faster (no need to re-authenticate).

Once you have an open connection, it's perfectly acceptable to pass it to various functions. In some cases it's required, as ADO transactions occur on the connection object, not at the DataSet or DataReader level (if you need two tables updated in a single transaction, use the same connection for both).

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[PogoWolf]

It's a Winform app.

So, would it be easier to run a 'fake' query.. something like 'Select top 1 Name from tbl_Users' to force the first connection, and then not worry about making the new ones?

Ok, let me back track here a little. =)

I'm a new to the whole ADO.NET thing. I understand ASP (2 - 3.0) ways of connecting to a database, and like you stated above, you create.. read.. kill.. when working with a website.

I applied that knowledge to my Winform app, and noticed massive amounts of slow down when ever the program was hitting the database (granted, a lot of that was the way my code was hitting the DB, and that's been changed)

the question that I was trying to ask before, was basicly how to make an efficent way to handle connections and grabing data from a database (Access in this case)

The project is a log parser, using Regluar expressions for the parse. massive amounts of data is gathered from the logs (using Expression groups) and dumped into the database.

I'm trying to understand how to do that quickly...


The PogoWolf

http://pogowolf.GamersCommune.com

 

[chiph]

So, would it be easier to run a 'fake' query.. something like 'Select top 1 Name from tbl_Users' to force the first connection, and then not worry about making the new ones?

I think you're getting the query mixed up with the connecting. First you open a connection, which you can then use to make 0 or more queries. When you're done, you close the connection. That's all there is to it.

Everything else is optimization. Access isn't the world's best database, but if you look at how you're access the data you can probably make improvements. Maybe replicating the database on the local machine will help (cuts out all the network traffic).

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[PogoWolf]

Ahhh.. so ADO.NET is still based on the 'open connection, Query Data, Close Connection' method?

and yes, I do understand that Access isn't the best DB to use, but the project will have the DB stored on the users machine, so it'll help. =)


The PogoWolf

http://pogowolf.GamersCommune.com

 

[paulrw1]

ADO.Net is totally different from previous versions of ADO. ADO.net uses 2 layers - a connected layer (for inserts, updates, deletes) and a disconnected layer (XML strams, simply working with temp data).

A good book for database programming and vb.net is "Database programming with Visual basic.net" Second edition from Apress.

 

[PogoWolf]

The only thing I'm worried about (at the moment) is the (instert/update/delete) layer. And the most efficent way to connect to said DB and get data.

from what I'm seeing above I should just stick with what I was doing before. Open the connection, get the data, close the connection.

then kill the DB object at the program termination.


The PogoWolf

http://pogowolf.GamersCommune.com