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!

Bejarano

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

Hengelo

Connection open code is already commented by the OP. It should be a comment. – Valentine

@PankajGarg that's exactly the reason of the failure of the OP code. Is my understanding that when you find the error, you post an answer. – Hengelo

Thank you for your answer! After fill to DataSet the the connection will be closed, So after that, if we need to update database then we need to open and close connection manually right? and do you know about the second question? – Bejarano

I don't know the sequence of your commands. From the code above you should open the connection open before the Fill and close after. Better if using statement. I will update my answer with some pseudocode. – Hengelo

also do you know how to use da.InsertCommand = new AdsCommand() too? :) – Bejarano

You don't need to build an InsertCommand because you have created a AdsCommandBuilder that take care of that. (However your table need to have a primary key defined) – Hengelo

Test tables have ID (autoint) and Name (vchar30) fields. could you show me how to put "Hello" word into Name filed using InserCommand please? Im so sorry about asking this. Thank you! – Bejarano