Ado, connections methods etc.

[Lou Elston]

I read that there are two ways to connect to a database now, the direct connect, (where you keep your connection open), and the other method where even if you are doing multiple inserts to a DB, you open and close the connection for each insert.

 

What is the scoop here? Does it matter what type of Db you are accessing, SQL, Access, whatever)? What are the pros and cons of doing each method?

What is the preferred way. Make it a point to always use the dataAdaper and dataset methods?

 

Thanks

 

Lou

[Robby]

What is the context of the application?

[Lou Elston]

Context

 

What is the context of the application?

 

Bobby

 

By your question, I get the feeling that there are reasons to use one method over the other depending on the situation.

 

I will be bringing my VB.net application to a production system where we will not be allowed to install Access. Since .Net will allow access to an Access database without actually having Access installed, I will be inclucing a .mdb file in my install kit.

 

First, I will be inserting into an Access table, using my companys API's to get the data from another source. Then I will load that data into a grid\whatever to allow for viewing and possibly modifying the data. Then I will allow the user to take the data modified and using my companys API's, to update the origional data source.

 

I am not sure of the timing of things, in that there might periods of time in between the downloads, and modifying, and uploads, so that is why I am using an Access table to store things, else I might have just used a MSGlexGrid, without using Access at all, to do all this work in.

 

Lou

[Nerseus]

You can create a connection and keep it around for the life of your app. As for each call, you can use a DataAdapter to get a dataset locally and work with it. A DataSet has NOTHING about about a Database associated with it. You can also use a DataReader, which is basically a client side cursor. The datareader holds a connection as long as it's open.

 

Which you use, as Robby was asking, is up to you and what you want. If you only have one user talking to your Access database, you might as well open the connection and leave it open - easier to code and no performance issues since you're the only one.

 

If there are multiple users to one Access DB, you might still want a persistent connection - depends on how many users and other factors.

 

Regardless of the connection staying open or closing, you can still use a DataAdapter to fill a DataSet. A DataSet is like a very simple in-memory database filled with tables, relationships and data. You can bind to a DataSet if you want, too. When you're done modify the data in a DataSet and want to save it, you go through the DataAdapter again to have it update the database. As I said above, a DataSet is database-agnostic - it knows nothing of the underlying database. That's the job of the DataAdapter.

 

-ner