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

Question

Hi, 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.

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.

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)

Answer 1

If the connection is open before the Fill() method is called, then no, the connection will not be closed by the 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.

This can be implied from multiple sources of documentation, including this one: http://msdn.microsoft.com/en-us/magazine/cc163799.aspx

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".

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

Answer 2

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

            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.