.net 抛出异常时，DataAdapter.Fill() 是否关闭其连接？

Does DataAdapter.Fill() close its connection when an Exception is thrown?

提问by motto

I am using ADO.NET (.NET 1.1) in a legacy app. I know that DataAdapter.Fill() opens and closes connections if the connection hasn't been opened manually before it's given to the DataAdapter.

我在旧版应用程序中使用 ADO.NET (.NET 1.1)。我知道如果在将连接提供给 DataAdapter 之前没有手动打开连接，DataAdapter.Fill() 会打开和关闭连接。

My question: Does it also close the connection if the .Fill() causes an Exception?(due to SQL Server cannot be reached, or whatever). Does it leak a connection or does it have a built-in Finally-clause to make sure the connection is being closed.

我的问题：如果 .Fill() 导致异常，它是否也会关闭连接？（由于无法访问 SQL Server 或其他原因）。它是否泄漏连接或是否具有内置的 finally 子句以确保连接正在关闭。

Code Example:

代码示例：

Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet
cmd.Connection = New SqlConnection(strConnection)
cmd.CommandText = strSQL
da.SelectCommand = cmd
da.Fill(ds)

回答by David

If the connection is open before the Fill() method is called, then no, the connection will not be closed by the DataAdapter.

如果在调用 Fill() 方法之前连接已打开，则不，DataAdapter 不会关闭连接。

However, if you do not explicitly open the connection, and instead let the DataAdapter open and close the connection within the Fill() command, then the connection will be closed on error.

但是，如果您没有明确打开连接，而是让 DataAdapter 在 Fill() 命令中打开和关闭连接，则连接将在出错时关闭。

This can be implied from multiple sources of documentation, including this one: Data Access Strategies Using ADO.NET and SQL

这可以从多个文档来源中得到暗示，包括这个：使用 ADO.NET 和 SQL 的数据访问策略

Further, this can be demonstrated in code by writing a routine that will error out and then checking the connection's State.

此外，这可以在代码中通过编写一个会出错的例程然后检查连接的状态来演示。

This code from a Windows Forms app proves it. The first message box will say "Open" and the second "Closed".

来自 Windows 窗体应用程序的此代码证明了这一点。第一个消息框将显示“打开”，第二个显示“关闭”。

              string connString = "";
        private void Form1_Load(object sender, EventArgs e)
        {
            connString = Properties.Settings.Default.EventLoggingConnectionString;
            ExplicitlyOpenConnection();
            LetDataAdapterHandleIt();
        }

        private void ExplicitlyOpenConnection()
        {
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connString);
            System.Data.DataSet ds = new DataSet();
            System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter("Select bogusdata from nonexistenttable", cn);

            cn.Open();
            try
            {
                ad.Fill(ds);
            }
            catch (Exception ex)
            {

            }

            MessageBox.Show(cn.State.ToString());
            cn.Close();
        }
        private void LetDataAdapterHandleIt()
        {
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connString);
            System.Data.DataSet ds = new DataSet();
            System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter("Select bogusdata from nonexistenttable", cn);

            try
            {
                ad.Fill(ds);
            }
            catch (Exception ex)
            {

            }
            MessageBox.Show(cn.State.ToString());
        }

回答by scottm

It does notclose the connection. This example works and outputs the Id of "ARealTable"

它不会关闭连接。这个例子工作并输出“ARealTable”的Id

            using (SqlConnection conn = new SqlConnection("Data Source=server;Initial Catalog=database;user id=sa; password=password;"))
            {
                conn.Open();

                try
                {
                    SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM NotATable", conn); 
                    /* Exception thrown next */
                    adap.Fill(new DataSet("test"));
                }
                catch (Exception) { }

                using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 Id FROM ARealTable", conn))
                {
                    string result = Convert.ToString(cmd.ExecuteScalar());
                    Console.WriteLine(result);
                }
                Console.ReadKey();

Edit:

编辑：

If you open the connection before hand (calling Open on the IDbConnection object), the IDataAdapter will not close it. However, if you allow the IDataAdapter to manage the connection wholly, it will be closed.

如果您事先打开连接（在 IDbConnection 对象上调用 Open），IDataAdapter 将不会关闭它。但是，如果您允许 IDataAdapter 完全管理连接，它将被关闭。