Correct usage of connections/data adapters?

[Alex]

Hey all -

I've found a lot of 'snippet' examples of hitting a sqlserver db from a
csharp windows app, but I want to make sure I understand the proper usage.

In the examples, they usually create the sqlconnection and pass the
connection string in the form, and then open it and create a sqlDataAdapter
to load a widget... immediately following the sqlconnection is closed.

For what I want to accomplish, I'm going to have many forms coming off of a
MDI control window. Would the following be ok?

1. Create a static sqlconnection in a static dbaccess class
2. Open the connection, create the dataadapter/dataset to load the widget,
close the connection.
3. Repear #2 for each data bound widget on each form...


Do I need to create a different dataadapter for each widget that I want to
populate from the db?

Do I need to close the connection after each hit, or can I just keep it
open for the entire time they have the app running?

Just trying to get a handle on how the small examples I've found apply to a
larger application...

Thanks!

 

[Kevin Spencer]

The .Net Data model relies heavily on disconnected data. It uses Connection
Pooling to optimize the most expensive aspect of database operations, which
is opening a Connection. Your best bet is to follow along with the plan.
Load your data, and then re-connect only to change it, or to synchronize
multiple users.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

 

[Robbe Morris [C# MVP]]

As a general rule of them, you'll want to isolate your
database code outside the ui layer. Typically,
you create your sql objects, use them, close them,
and then destroy them immediately.

http://www.eggheadcafe.com/articles/aspnet_how_to_create_object_oriented_applications.asp

 

[Alex]

Thanks for the replies... I'm making my way through the linked articles,
but I wanted to follow up on this about destroying the connection.

Are you saying that if I have, say 10 listboxes on a form, each of those
listboxes should create it's own 'new' sqlConnection object (passing the
connections string, etc), set up a data reader, grab a record set and then
dispose of the sqlConnection... 10 times - 10 sqlConnection .Open()'s?

Seems like it's really redundant to do it that way, but I know it'll be
more difficult later if I don't 'go with the flow'... if that's the flow.

Thx!

 

[Robbe Morris [C# MVP]]

connection pooling large removes the concerns you have.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp

 

[JT]

For the most part, I agree with Robbe, but if your code is going to
look like:
create connection
create adapter
open connection
get data
close connection
populate listbox
....
repeat 10 times

Then I believe it would be fine to substitute "get data" with "get all
data for all 10 listboxes" and just go through it once. The other
alternative is to write a function like GetData(ListBoxDataReceptacle,
QueryString) and just make 10 calls to this function with the
appropriate object and query string pairs and let it handle the
connection and adapter generically.