DataAdapter does not need to make db connection open?

Question

I try to use DataAdapter in C#.net. and still I do not understand something about DataAdapter.

I read many article and blogs about DataAdapter and DataReader.

I understood DataAdapter will open and close database automatically when they need.

But,

//conn.Open();
AdsDataAdapter da;
da = new AdsDataAdapter("Select * from Test", conn);
AdsCommandBuilder cb;
cb = new AdsCommandBuilder(da);

DataSet ds = new DataSet();
da.Fill(ds, "Test");

DataRow newrow = ds.Tables["Test"].NewRow();
newrow["Name"] = "How about";
ds.Tables["Test"].Rows.Add(newrow);
da.Update(ds, "Test");

When I run the code above, I get an error message that say "Connection must be open."

Why the adapter can not open connection automatically?

and, I want to insert data using insertCommand (For this test, I opened the connection).

da.InsertCommand = new AdsCommand("INSERT INTO test (NAME) values('Insert Test #1')", conn);
//da.InsertCommand.ExecuteNonQuery(); // it works
da.Update(ds,"Test"); //but it does not works.

Many example using Adapter.Update(), but for me, it does not work :(

No error and nothing inserted.

and using da.InsertCommand.ExecuteNonQuery(); instead Update(), it works.

what am I doing wrong?

Thanks!

Answer 1

MSDN says that

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)

EDIT: Pseudocode derived from your code above

using(AdsConnection com = new AdsConnection(connectionString));
{
    conn.Open();
    using(AdsDataAdapter da = new AdsDataAdapter("Select * from Test", conn))
    {
        AdsCommandBuilder cb = new AdsCommandBuilder(da); 
        DataSet ds = new DataSet(); 
        da.Fill(ds, "Test"); 

        // Now the connection is still open and you can issue other commands

       DataRow newrow = ds.Tables["Test"].NewRow(); 
       newrow["Name"] = "How about"; 
       ds.Tables["Test"].Rows.Add(newrow); 

       // da.Update should work here. No more connection closed.
       da.Update(ds, "Test"); 
    }
} // Exiting from the using block, the connection will be closed