Should I let a DataAdapter open a connection multiple times?

[emaduddeen]

Hi Everyone,

Should I let a DataAdapter open a connection multiple times?

I'm trying to figure out the best way to handle this:

My form contains a DataGrid and many TextBox controls and everytime the user moves the highlight bar on the DataGrid I display detail data representing the information in the DataGrid.

Every time the highlight bar is moved on the DataGrid this code is executed which connects to the database every time it's moved. Is this the best way to handle a situation like this? Would it be better to connect to the database once and leave the connection open and just to clear out the DataAdapter and somehow refill it instead? If it's best to clear it out and refill it, can you show me some code examples on how to do this?

Here's the code:

            ' Create the data adapter for the Customer Details.
            ' This data adapter will be used when displaying the Customer Details.
            '---------------------------------------------------------------------
            objDataAdapterParentDetails = New SqlDataAdapter(strSQL, _
                                          (BuildConnectionString("EMAD-PC\SQLEXPRESS", _
                                              "EmadElectronics", _
                                              strUser, _
                                              strPassword)))

The code works ok but I'm just looking for the best way to do it.

Thanks.

Truly,
Emad

 

[jmcilhinney]

ADO.NET is specifically designed to work that way. Open a database connection as late as possible and close it as soon as possible. The only time a connection should stay open beyond a single data access operation is if you're executing multiple data access operations in quick succession, i.e. one line of code immediately after another.

 

[emaduddeen]

Hi jmcilhinney,

Thanks for clearing that one up for me. Glad to know I don't have to worry about it.

I'm assuming then that I can create a login form just to establish the fact the user can legally log into the database then set some global variables to hold the user and password and use them later in the program.

Truly,
Emad

 

[emaduddeen]

I'd like to ask another question.

Does it also mean that I can use "New" multiple times as well? I ask because my code creates a new DataAdapter object multiple times.

Truly,
Emad