Best way to access my SQLce Database?q

  • Thread starter Thread starter Atley
  • Start date Start date
A

Atley

I have written an application in VB.NET 2003 and I occasionally get one form
not showing any data, because it is trying to open a connection to the
database before the last form has completely released it's hold on the db.

What is the best way to connect to the database?
Is it better to connect once at the beginning and leave the connection open
through the whole application. Will idividual datasets have problems
running into each other?

Or is it better to do it the way i have done it, connect only when you need
the data and then close the connection right away.

Which one is better, why?

Thanks
 
connect once and release when you close the application... that is the best
way... overhead for keeping connection in memory is less then overheard of
trying to open and close db everytime you want to do something.

One note... if you want some other application to use the db you will have
to close and wait then reopen.

Regards,

éric
 
Can two different datasets be accessing through one connection at the same
time?

How would you go about this? just create a global variable for the data
connection?

Will my application run any quicker?

Thanks for your input.
 
Can two different datasets be accessing through one connection at the same
time? YES


How would you go about this? just create a global variable for the data
connection? YES


Will my application run any quicker?
YES

Thanks for your input.
Click to expand...
 
Eric:

I have to respectfully disagree here and would not recommend leaving a
connection open for the life of an application. And the issue of overhead
efficiency can't be made categorically like that. It depends on many
factors and if anything, I think the consensus is that closing your
connections when you don't need them is a superior approach in most every
regard.
 
Atley:

DataSet's don't need or use a Connection for access. The DataAdapter may
use a Connection(if you are using a DataAdapter) to fill your datasets and
get data back to the server, but datasets don't have a connectino property.

Also, you should be able to accomplish most scenarios with only one dataset.

Since I disagree wholeheartedly about leaving a connection open for the life
of the app, I'd recommend storing the connection string somewhere globally
accessible...that would allow you to create connections anywhere with the
same connection string.

HTH,

Bill
 
William is right... can not be said categorically that open and close
everytime is better then open and close once... but I assumed that if you
have an application that was using multiple datasets with dataadapters your
scenario was likely to use the database often. If you only connect to sqlce
for the odd data retreival or update you should concider closing the
connection.

In my scenario I use have a point of sale application which retrieves a
customer from the database then inventory from the database then writes an
invoice to the database. I have tested both ways and open once close later
was without a doubt faster.

I rarely use datasets though.. mostly sqlcecommand and sqlcedatareader. If
speed is important don't use datasets.

Regards,

éric
 
Back
Top