ADO.NET Recommendations for Connectivity and the DataAdaptor Object Area

Question

This is my first StackOverflow post, so please be careful ...

I have some questions regarding the scope of objects for ADO.NET.

When I connect to the database, I usually use this code:

OleDbConnection conn = new OleDbConnection("my_connection_string"); conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * from Employees", conn); OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter); DataTable dt = new DataTable(); adapter.Fill(dt); conn.Close(); conn.Dispose(); 

Let's say that I bind the resulting DataTable to the grid control and allow my users to edit the contents of the grid. Now, when my users click the "Save" button, I need to call this code:

 adapter.Update(dt); 

Here are my questions:

1) Do I need to save the adapter object that I created when I originally uploaded the data, or can I create another adapter object in the "Click" Save "event to update?

2) If I need to save the source adapter object, do I also need to save the available connection object and open it?

I understand the unrelated ADO.NET model - I just got confused in the area of ​​objects when it came time to update the database. If someone could give me some guidance on best practices for this scenario, I would really appreciate it!

Thanks in advance...

Answer 1

1) You do not need the same DataAdapter, but if you create a new one, it should use the same query as its base.

2) The DataAdapter will open its connection if the connection is closed. In this case, it will close the connection again after it is completed. If the connection is already open, it will leave the connection open even after it is completed.

Usually you will work as in your example. Create a Conneciton and DataAdapter, then populate the DataTable and remove the connection and DataAdapter.

Two comments on your code:

• Here you do not need CommandBuilder, as you only choose. The command constructor is only necessary if you want to automatically create Insert, Update, or Delete statements. In this case, you also need to manually install InsertCommand, UpdateCommand, or DeleteCommand in the DataAdapter manually from CommandBuilder.
• Secondly. Instead of calling Dispose manually, you should use the Using clause. This ensures that your objects will be deleted even if an exception is thrown.

Try changing your code to this:

 DataTable dt = new DataTable(); using (OleDbConnection conn = new OleDbConnection("my_connection_string")) using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * from Employees", conn)) { adapter.Fill(dt); } 

Please note that I am defining a DataTable outside of usage suggestions. This is necessary to ensure that the table is in scope when you post. Also note that you do not need a call to Dispose on the DataAdapter or a call to Close on Connection. Both of them are executed implicitly when you leave them.

A. And welcome to SO :-)

Answer 2

To answer your questions:

• Ideally, you should keep the same DataAdapter, because it has already performed its initialization. The DataAdapter provides properties such as SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand that allow you to set different Command objects to perform these various functions in the data source. So, you see, the DataAdapter is designed to be reused for several commands (for the same database connection). Using CommandBuilder (although not recommended) creates other commands by parsing SelectCommand, allowing you to perform updates, deletes, and pastes using the same CommandBuilder.

• It is best to allow the DataAdapter to implicitly access database connections. @Rune Grimstad has already developed this implicit behavior, and it is helpful to understand this. Ideally, the connections should be closed as soon as possible.

Answer 3

There are two additional details that are worth adding to Rune Grimstad's wonderful answer.

Firstly, CommandBuilder (if necessary) implements IDisposable and therefore must be wrapped in its own 'using' statement. Surprisingly (at least for me), the Disposing DataAdapter does not appear to have Dispose bind CommandBuilder. The problem that I observed when I did not do this was that even if I called Dispose in the DataAdapter and the Connection state was "Closed", I could not delete the temporary database as soon as I used CommandBuilder to update this database data.

Secondly, the statement "... in this case, you also need to install InsertCommand, UpdateCommand or DeleteCommand in the DataAdapter manually ..." is not always correct. For many trivial cases, CommandBuilder will automatically generate the correct INSERT, UPDATE, and DELETE statements based on the SELECT statement provided by the DataAdapter and metadata from the database. "Trivial", in this case, means that one table is accessed, and this table has a primary key that is returned as part of the SELECT statement.