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!

Steve · Accepted Answer · 2012-04-14T13:40:10.437

19

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

edited Apr 14 '12 at 13:40

answered Apr 14 '12 at 13:20

Steve

213,761
22
232
286

Connection open code is already commented by the OP. It should be a comment. – Pankaj Apr 14 '12 at 13:26
1

@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. – Steve Apr 14 '12 at 13:32
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? – Expert wanna be Apr 14 '12 at 13:33
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. – Steve Apr 14 '12 at 13:36
also do you know how to use da.InsertCommand = new AdsCommand() too? :) – Expert wanna be Apr 14 '12 at 13:47
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) – Steve Apr 14 '12 at 13:55
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! – Expert wanna be Apr 14 '12 at 14:04

DataAdapter does not need to make db connection open?

1 Answers1