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);

Merissameristem

Answer 1

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);       
    }

Insight

but if I write this code without using construction cn whatever have Closed state before exit of function – Merissameristem

@Merissameristem indeed, and consulting MSDN verifies this. Sorry, I didn't catch this the first time around. – Insight

It handles closing it for you if the command is executed successfully, but what about in an exception case? (deadlock, timeout, etc.) – Resistencia

@JoelC The using statement calls dispose on exception as well, it simply compiles into a try-finally block under the hood. – Insight

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. – Resistencia

@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. – Insight

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? – Shanel

Yeah pretty much. I believe it works both ways, personal preference really. – Insight

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. – Kabul

@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. – Insight

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 :-) – Kabul

Answer 2

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#

Andromeda