Question sql connection when using sqlDataAdapter

[izeko]

Hi,
Im after some advice on how i go about opening my sqlConnection. At the moment when my program is first run i open the sqlConnection and this is made as a public object so i can reference throughout the program without having to close it untill i close the program. I thought this was fine untill i came into problems when using the sqlDataAdapter as i was getting the error:

'There is already an open DataReader associated with this Command which must be closed first'

After reading some forums, i kind of get the feeling i shouldnt have the connection open all the time.

Is this correct, or should i create and close another connection only when using the sqlDataAdapter.

My code keeps falling over here:
sqlDa.Fill(dt)

This is in a method that gets called when i click my refresh button to repopulate a dataGridView.

Thanks for any help.

 

[izeko]

Hi, Can anyone help me on this?
Thanks

 

[jmcilhinney]

You definitely should not keep the connection open constantly. ADO.NET has been designed specifically such that connections should be opened as late as possible, i.e. immediately before you want to use them, and closed as early as possible, i.e. immediately after you use them. In fact, unless you have a specific reason to do otherwise, you should even create your ADO.NET objects just before using them and destroy them just after. If you are using the same adapter to retrieve and save data then that's a good reason for keeping a reference to a connection and data adapter for that period of time at least but once the data has been saved you should destroy and discard them and then create new ones later when needed.

Having said all that, just maintaining one open connection for the life of the application is not the specific cause of that error. If you used the connection properly then it would still work, but you are trying to execute two queries over that connection at the same time. The fact that the error message says that there is an open data reader means that you have opened a data reader and not closed it before calling Fill, which will open another data reader internally. Even if you follow the advice in the first paragraph, you still need to make sure that you close data readers when you're done with them.

You may like to check out some of my ADO.NET code examples to see how to manage your data access objects. Note that the one common thing you will need is the connection string, which should be stored in one place only. That place is often the config file, but you may prefer another location.

Retrieving and Saving Data in Databases