What happens if I do not open the SqlConnection instance?

Question

I am looking at a code snippet in an application, and I came up with something very strange with regards to connecting to a database.

Executes queries without opening a connection as follows:

using (sqlConnection1 = new SqlConnection(connString) { SqlCommand comm = new SqlCommand(query,sqlConnection1); // ... parameters are handled here... SqlDataAdapter ad = new SqlDataAdapter(comm); ds = new DataSet(); ad.FillSchema(ds, SchemaType.Source); ad.Fill(ds); } 

Is this not so, because the connection is not open? I really tested this in a separate project and it worked.

Answer 1

If the connection is closed, using SqlDataAdapter.Fill will open the connection http://msdn.microsoft.com/en-us/library/377a8x4t.aspx

Answer 2

In the documentation, SqlDataAdapter will open the connection, if it is not already open, and return it to its previous state.

The connection object associated with SelectCommand must be valid, but it must not be open. If a connection is closed before calling FillSchema, it opens to retrieve data, and then closes. If the connection is open before calling FillSchema, it remains open.

Fill also behaves the same way

Answer 3

Contact MSDN

The Fill method implicitly opens a connection that the DataAdapter uses if it detects that the connection is not already open. If the fill opened the connection, it will also close the connection when the fill is finished. This can simplify your code when working with one such as Fill or Update.

This means that after da.Fill(ds, "Test"); Your connection is closed by the method itself. But you need to open it for the next update (and it fails)

Answer 4

From the SqlDataAdapter.Fill method;

The Fill method retrieves rows from the data source using the SELECT specified by the corresponding SelectCommand property. The connection object associated with the SELECT statement must be valid , but it must not be open. If a connection is closed before calling Fill, it opens to retrieve data, and then closes. If the connection is open before calling Fill, it remains open.

Also, the FillSchema method does the same.