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(C onfigurationSet tings.AppSettin gs["connectionStri ng"]);
SqlDataAdapter myDA = new SqlDataAdapter( sql, myConnection );
// myConnection.Op en();
DataTable table = new DataTable();
myDA.Fill( table );
// myConnection.Cl ose();
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]

Re: SqlDataAdapter question

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

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU

http://blogs.aspadvice.com/joteke

"kempshall" <jayharris@gmai l.com> wrote in message
news:1147203470 .928320.136080@ y43g2000cwc.goo glegroups.com.. .[color=blue]
> 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(C onfigurationSet tings.AppSettin gs["connectionStri ng"]);
> SqlDataAdapter myDA = new SqlDataAdapter( sql, myConnection );
> // myConnection.Op en();
> DataTable table = new DataTable();
> myDA.Fill( table );
> // myConnection.Cl ose();
> 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
>[/color]