Does SqlDataAdapter close the SqlConnection after Fill() function?

Question

Does SqlDataAdapter close the SqlConnection after the Fill() function or do I need close it myself?

string cnStr = @"Data Source=TEST;Initial Catalog=Suite;Persist Security Info=True;User ID=app;Password=Immmmmm";
cn = new SqlConnection(cnStr);
SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Date", cn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
adapter.Fill(ds);

cn.Close() // ????????

Console.WriteLine(ds.Tables[0].Rows.Count);
Console.WriteLine(cn.State);

Adam Houldsworth · Accepted Answer · 2011-09-12T13:51:18.093

16

In your current usage, it will close for you:

If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx

I think it's always better to explicitly cater for it yourself with a using statement:

using (SqlConnection conn = new SqlConnection(""))
{
    conn.Open();

    // Do Stuff.

} // Closes here on dispose.

This is often more readable and doesn't rely on people understanding the inner workings of SqlDataAdapter.Fill, just the using statement and connections.

However, if you know the connection is closed before the adapter uses it (as in, you've just created the connection) and it's not used for anything else, your code is perfectly safe and valid.

Personally, I'd write something like this:

    string cnStr = "Data Source=TEST;Initial Catalog=Suite;Persist Security Info=True;User ID=app;Password=Immmmmm";
    DataSet ds = new DataSet();

    using (SqlConnection cn = new SqlConnection(cnStr))
    using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Date", cn))
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    { 
        conn.Open();
        adapter.Fill(ds);       
    }

edited Sep 12 '11 at 13:51

answered Sep 12 '11 at 13:36

Adam Houldsworth

63,413
11
150
187

but if I write this code without using construction cn whatever have Closed state before exit of function – Wachburn Sep 12 '11 at 13:41
1

@Wachburn indeed, and consulting MSDN verifies this. Sorry, I didn't catch this the first time around. – Adam Houldsworth Sep 12 '11 at 13:44
It handles closing it for you if the command is executed successfully, but what about in an exception case? (deadlock, timeout, etc.) – Joel C Mar 16 '12 at 14:20
@JoelC The `using` statement calls dispose on exception as well, it simply compiles into a try-finally block under the hood. – Adam Houldsworth Mar 16 '12 at 14:38
Sorry, I wasn't clear, I meant before introducing the `using` block. You started by saying `In your current usage, it will close for you` but I wanted to clarify whether that would be the case if an exception is thrown. – Joel C Mar 16 '12 at 18:07
@JoelC Oh, in that case no it won't - it will be left for the GC to handle assuming exceptions are handled and don't cause a massive app crash. – Adam Houldsworth Mar 16 '12 at 19:17
Just to be clear: in your last block of code, the `conn.Open();` isn't actually needed. It's just there in case other programmers don't know that `adapter.Fill(ds);` will open (and close) the connection itself. Is that correct? – comecme Jul 14 '12 at 09:29
Yeah pretty much. I believe it works both ways, personal preference really. – Adam Houldsworth Jul 14 '12 at 10:27
There is no need for three separate using statements. The using can have a comma separated list of expressions to create IDisposable objects, and it will create nested try-finally statements for them. One using will suffice here. – Concrete Gannet Mar 31 '17 at 02:56
1

@ConcreteGannet Just tried it in case something had changed for C# 7, but unfortunately that still is only true when the type is shared, such as `using (IDisposable conn = new SqlConnection(), comm = new SqlCommand())` you cannot do `using (SqlConnection conn = new SqlConnection(), SqlCommand comm = new SqlCommand())` so three `using` statements are required in this case. – Adam Houldsworth Mar 31 '17 at 06:44
Thanks @AdamHouldsworth, I hadn't realised that. The using isn't quite as cool as I thought. I agree three usings is the best answer. You could declare them all as IDisposable, then downcast inside the block, but that's very ugly. You could declare them before the using, but that's dangerous because the variables would be still in scope after they've been disposed. Feel free to downvote my comment above, SO won't let me :-) – Concrete Gannet Apr 02 '17 at 02:03

score 5 · Answer 2 · answered Sep 12 '11 at 13:36

As I know you need to close the connection by you own

Best way to do is

using(SqlConnection con = new SqlConnection())
{
   // you code 
}

this will close you connection automatically

using block in C# comes very handly while dealing with disposable objects. Disposable objects are those objects that can explicitly release the resources they use when called to dispose. As we know .Net garbage collection is non-deterministic so you can’t predict when exactly the object will be garbage collected.

Read this post for more in details : understanding ‘using’ block in C#

Does SqlDataAdapter close the SqlConnection after Fill() function?

2 Answers2

Linked