连接和DataAdaptor对象范围的ADO.NET最佳实践 [英] ADO.NET Best Practices for Connection and DataAdaptor Object Scope

问题描述

这是我关于StackOverflow的第一篇文章，请保持柔和...

This is my first post on StackOverflow, so please be gentle...

我对ADO.NET的对象范围有一些疑问.

I have some questions regarding object scope for ADO.NET.

当我连接到数据库时，通常使用如下代码:

When I connect to a database, I typically use code like this:

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

比方说，我将生成的DataTable绑定到网格控件，并允许我的用户编辑网格内容.现在，当我的用户按下保存"按钮时，我需要调用以下代码:

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

adapter.Update(dt);

这是我的问题:

1)我是否需要保留最初加载数据表时创建的适配器对象，还是可以在保存"按钮单击事件中创建另一个适配器对象以执行更新?

1) Do I need to retain the adapter object that I created when I originally loaded the datatable, or can I create another adapter object in the Save button click event to perform the update?

2)如果确实需要保留原始适配器对象，是否还需要保持连接对象可用并保持打开状态?

2) If I do need to retain the original adapter object, do I also need to keep the connection object available and open?

我了解ADO.NET断开连接的模型-当需要更新数据库时，我只是对对象范围感到困惑.如果有人可以给我一些有关这种情况的最佳实践的指导，我将不胜感激！

I understand the disconnected model of ADO.NET - I'm just confused on object scope when it's time to update the database. If someone could give me a few pointers on best practices for this scenario, I would greatly appreciate it!

预先感谢...

推荐答案

1)您不需要相同的DataAdapter，但是如果创建一个新的DataAdapter，则它必须使用与其基础相同的查询.

1) You don't need the same DataAdapter, but if you create a new one it must use the same query as its base.

2)如果连接关闭，则DataAdapter将打开其连接.在这种情况下，它将在完成后再次关闭连接.如果连接已经打开，则即使完成连接也会保持打开状态.

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

通常，您将按照示例进行工作.创建一个Conneciton和一个DataAdapter，填充一个DataTable，然后再处理Connection和DataAdapter.

Normally you would work as in your example. Create a Conneciton and a DataAdapter, fill a DataTable and dispose of the Connection and the DataAdapter afterwards.

您的代码有两个注释:

• 这里您不需要CommandBuilder，因为您只需要选择即可.仅当您要自动生成Insert，Update或Delete语句时，才需要命令构建器.在这种情况下，您还需要从CommandBuilder手动设置DataAdapter上的InsertCommand，UpdateCommand或DeleteCommand.
• 第二.而不是手动调用Dispose，您应该使用Using子句.它确保即使抛出异常也将丢弃您的对象.

尝试将您的代码更改为此:

Try to change 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);    
}

请注意，我在using子句之外定义了DataTable.这是必要的，以确保当您离开使用表时，该表在范围内.还要注意，您不需要在DataAdapter上进行Dispose调用或在Connection上进行Close调用.离开用法时，两者都是隐式完成的.

Note that I define the DataTable outside the using clauses. This is needed to ensure that the table is in scope when you leave the usings. Also note that you don't need the Dispose call on the DataAdapter or the Close call on the Connection. Both are done implicitly when you leave the usings.

哦.欢迎来到SO:-)

Oh. And welcome to SO :-)

这篇关于连接和DataAdaptor对象范围的ADO.NET最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！