Why Does A Connection Have To Be Open For A Nonquery But Not To Fill A Dataset?

When I connect to a SQL datasource in my C# application, I can fill a dataset using the following code. Note that I am not explictly opening a connection to the datasource. SqlCon

Solution 1:

That is because a DataAdapter opens the connection implicitely when it's closed initially. It'll close it afterwards if it was closed before, otherwise the connection stays open.

From MSDN:

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. 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.

When using a SqlCommand you must explicitely open and close connections yourself.

As a side note:

1. Use parameters to avoid SQL-Injection
2. Use using-statement for anything implementing IDisposable, especially Connections since it ensures that it gets disposed/closed as soon as possible, even in the case of an error

Solution 2:

From the DataAdapter.Fill documentation

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

When you use the SqlCommand you're using a lower level class, so you're expected to manage the connection yourself.

Here's a use case where you wouldn't want the connection to automatically be opened because it would be opened and closed several times and its better to simply throw the exception

SqlCommand InsertCmd = new ("Insert statement", cw_con)

cw_con.Open();

foreach (var newitem in collection)
{
   --Set Parameters
   UpdateCmd.ExecuteNonQuery();
}

cw_con.Close();

Solution 3:

The documentation states that

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

Look here for more information.