CODEWITHSUNDEEP
c#sql-server
NeverStopLearning
NeverStopLearning

Reputation: 998

What happens if I dont open instance of SqlConnection?

I am reviewing a piece of code in a application and I came to something very strange in regard to connecting to database.

It is executing queries without opening the connection like this:

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

Shouldnt it fail because of connection is not open? I actually tested this in separate project and it worked.

Upvotes: 4

Views: 145

Answers (4)

Amit
Amit

Reputation: 2565

Refer MSDN

The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update.

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

Upvotes: 3

Soner Gönül
Soner Gönül

Reputation: 98750

From SqlDataAdapter.Fill method;

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.

Also FillSchema method does same thing.

Upvotes: 1

Rowland Shaw
Rowland Shaw

Reputation: 38130

Per the documentation, SqlDataAdapter will open the conenction if it isn't already open, and return it to its previous state.

The connection object associated with the SelectCommand must be valid, but it does not need to be open. If the connection is closed before FillSchema is called, it is opened to retrieve data, then closed. If the connection is open before FillSchema is called, it remains open.

Fill also behaves in the same manner

Upvotes: 3

tim
tim

Reputation: 536

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

Upvotes: 8

Related Questions