SqlDataAdapter question

[kempshall]

When I use an SqlDataAdapter to fill a DataSet from a database, do I
need to explicitly open and close the SqlConnection that I'm using? For
example, I have code that looks like this:

SqlConnection myConnection = null;
try {
myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlDataAdapter myDA = new SqlDataAdapter( sql, myConnection );
// myConnection.Open();
DataTable table = new DataTable();
myDA.Fill( table );
// myConnection.Close();
return table;
}

I'm particularly curious as to whether the SqlDataAdapter is guaranteed
to close automatically, because when I check my MSSQL process info
(after executing all my queries), I find a bunch of excess sleeping
processes and I'm not sure how they got created or why they haven't
been killed.

If anybody has some ideas or help, I'd really appreciate it.

Thanks,

Jay

 

[Teemu Keiski]

Hi,

it is guaranteed to handle the connection if you don't open it yourself
(then it would leave it open)

What comes to seeing connections on EM or where you now do that, it is
related to a concept call connection pooling. What you see on list are the
actual physical database connections, and those are pooled in .NET to save
in creating them (as much reuse as possible) so essentially there are
physical connections in the pool even after queries etc would be executed.
There are lower and upper limits for this pool (set within connection
string, and with certain defaults).

Please see: Using Connection Pooling
http://msdn2.microsoft.com/en-US/library/8xx3tyca.aspx