Database question - Database Open, Close Design

[Guest]

Hi,

I have an windows forms application that accesses a SQL database

I have a few questions as to connecting to the database.
This application will run 24 hours a day. It is a monitoring application and
will store events that happen in the database (These events happen randomly
without pattern, between 10-50 a day) .

There are a number of situations where the database is accessed.


1. Configure the system at start before system is starts monitoring

2. At Random times when events occured.

3. When user want to generate a report of event occured during
Specified time period.

4. System uses configuration data inputted in 1. throughout system
operation

I was wondering what is the most efficient methods to control access to the
database for the above scenarios.

How many connection strings should i use. When should i open and close these
connections?

For 1., should i do this in a disconnected manner? Open connection, get
local copy of config data, update config data then synchronise changes with
database? Or should i open a direct connection to database do changes
directly and then close database?
What are pros and cons of both methods?

For point 2. I assume the best way to update the database is to open a
connection and update databse directly rather than doing it through
disconneted DataSet?

Also i was wondering if i should leave the database connection open always
rather than opening and closing for each event that takes place?

Would this be inefficent?

For point 3, should i use a separate connection string for this access and
is the connected or disconnected model best for this?


For Point 4, i was thinking of utilising the same connection string as used
in point 2 as the system accesses the database randomly for theses 2 points.

I'd appreciate any comments or advice,

Thanks In Advance
Macca

 

[Vadym Stetsyak]

as your app will access the db 10-50 times per day, there is no reason to
keep db connections open.

For 1., should i do this in a disconnected manner? Open connection, get
local copy of config data, update config data then synchronise changes with
database? Or should i open a direct connection to database do changes
directly and then close database?
What are pros and cons of both methods?

I do not see any particular difference, in the first way database connection
will be closed earlier then in the second. Configuring the system IMO will
not take much time...

For point 2. I assume the best way to update the database is to open a
connection and update databse directly rather than doing it through
disconneted DataSet?

If your assume that db server will be online and operational, then it is
better to it directly

Also i was wondering if i should leave the database connection open always
rather than opening and closing for each event that takes place?

Would this be inefficent?

If your system is using db intensively ( great amount of operations with db
in the unit of time ) then it is better to cache db connections. In your
situation db will be accessed rarely it doesn't make sense to keep the
connections. ( Db connection can be considered a valueable resource if db
server is heavily loaded )

For point 3, should i use a separate connection string for this access and
is the connected or disconnected model best for this?

Connection string determines to what server, database and with what
credentials you want to connect. Generally one connection string can be
used. However if you work in the connect/disconnect pattern that makes no
difference.

 

[Ignacio Machin \( .NET/ C# MVP \)]

How many connection strings should i use. When should i open and close
these
connections?

You need only one connection string, you will get pooling then.
You shoudl close the conn as late as possible and close it as soon as
possible, keep it open the less time possible

For 1., should i do this in a disconnected manner? Open connection, get
local copy of config data, update config data then synchronise changes
with
database? Or should i open a direct connection to database do changes
directly and then close database?

It does depend of the actual work , what are you using, ( DataAdapter, SP,
etc) , IMO I prefer to do the changes in the SQL server. In anyway you will
have the connection open while you are updating the DB

What are pros and cons of both methods?

For point 2. I assume the best way to update the database is to open a
connection and update databse directly rather than doing it through
disconneted DataSet?

Dont quite understand what you mean, you probably maintain the data in a
dataset anyway, and when you do an update you only persist the changes of
the dataset since the last time you read them.

Also i was wondering if i should leave the database connection open always
rather than opening and closing for each event that takes place?

NO, always close the connection ASAP

Would this be inefficent? INDEED

For point 3, should i use a separate connection string for this access and
is the connected or disconnected model best for this?

As I said before, you only need one connection string.

cheers,

 

[Alfredo Novoa]

How many connection strings should i use.
One.

When should i open and close these
connections?

It depends. Connections are often an scarce resource, but to open a
new connection is very slow.

If you can afford a dedicated connection your application will run
faster.

For 1., should i do this in a disconnected manner? Open connection, get
local copy of config data, update config data then synchronise changes with
database? Or should i open a direct connection to database do changes
directly and then close database?

Only if you have very good reasons to do that because you would have
to work a lot more. I don't see any reason in your case.

Also i was wondering if i should leave the database connection open always
rather than opening and closing for each event that takes place?

Would this be inefficent?

It would be efficient in speed, but it could be inefficient in the use
of the connections whether you have scarcity of them.


Regards
Alfredo

 

[Guest]

Thanks for the replies guys.

There are two sides to my application.

Firstly the main application and databse will be installed locally on the
same PC on each customers site. Here the main app will write events to the
database as they happen.

The system can be calibrated locally on each machine and reports can be
generated and viewed locally.

The second side is remote operation. This will allow user to calibrate and
generate/view reports remotely from another site.

What i wanted to do was to write two small apps, calibration app and report
app that can be run as standalone(Remotely) but which will also be integrated
into the main app running locally on each machine i.e run the calibration and
report exes from a menu in main app on local machine..

Now that i have hopefully cleared that up (my explanation on original post
wasnt clear) how would you answer the points i raised on my original post?

I have thought it would be best to manipulate database directly for
inserting events and reading reports but maybe using disconnected mode for
calibrating?

Because the Calibration app and Report app can be run remotely aswell as
locally will they need their own connection?

Regards
Macca