Basic Ado.net questions

[JH]

I have an access database and

1. Do I need a new connection to a database for each dataset I create?
2. do I need to close the connection afterm creating the dataset? I believe
I don't need because when I want to save changes to the dataset I need the
connection. Am I right?
3. If I have many connections to a database then does that create a problem?

Thanks
Newbie Here

 

[William Ryan eMVP]

1) No. Connections only relate to DataAdapters, so you technically don't
even need a connection for a dataset.

You shouldn't be creating many datasets though, the metaphor is essentially
that a DataSet corresponds to a database and datatables/Dataviews correspond
to data tables and views in your db.

2)The main thing to remember is Close connections when you are done with
them. IF you don't manually open a connection with a DataAdapter, it will
take care of opening and closing it for you.
3) It can but depending on the database and your connectionstring, you
should be able to take advantage of connection pooling which is on by
default. Don't use any more of any object than you need, including
connections. Your database can run out of them depending on its
configuration, so yes, it can be a very big problem, however as long as you
close everything when you are done with it, (Immediately) then they'll go
back to the pool.

There's no benefit to leaving the connection open in virtually every
situation I can think of . All you need is an open connection during
DataAdapter.Fill or command.ExecuteNonQuery/ExecuteScalar etc.

HTH,

Bill

 

[Angel Saenz-Badillos[MS]]

Welcome to the ado.net community, good questions.

1)No, it is not necessary since the Datasets are disconnected.
2)Keep the connection closed at all times, when you use the adapter to fill
or update a dataset it will open and close the connection in the most
efficient way. To be clear, when you use the adapter with a closed
connection it will open it, do your requested database access, and close it
immediately. If you use the adapter with an open connection the connection
will continue to be open after the adapter is done.
3)Not a programmatic one, some people have per connection licenses to their
backend, some people define "many" as a much bigger number than I do <g>,
for silliest for example each connection takes about 40k of memory so
eventually your server box may run out of memory

Hope this helps,