CODEWITHSUNDEEP
ado.net
isekaijin
isekaijin

Reputation: 19742

Can I dispose a DataTable and still use its data later?

Noob ADO.NET question: Can I do the following?

  1. Retrieve a DataTable somehow.
  2. Dispose it.
  3. Still use its data. (But not send it back to the database, or request the database to update it.)

I have the following function, which is indirectly called by every WebMethod in a Web Service of mine:

public static DataTable GetDataTable(string cmdText, SqlParameter[] parameters)
{
    // Read the connection string from the web.config file.
    Configuration configuration = WebConfigurationManager.OpenWebConfiguration("/WSProveedores");
    ConnectionStringSettings connectionString = configuration.ConnectionStrings.ConnectionStrings["..."];

    SqlConnection connection = null;
    SqlCommand command = null;
    SqlParameterCollection parameterCollection = null;
    SqlDataAdapter dataAdapter = null;
    DataTable dataTable = null;

    try
    {
        // Open a connection to the database.
        connection = new SqlConnection(connectionString.ConnectionString);
        connection.Open();

        // Specify the stored procedure call and its parameters.
        command = new SqlCommand(cmdText, connection);
        command.CommandType = CommandType.StoredProcedure;
        parameterCollection = command.Parameters;
        foreach (SqlParameter parameter in parameters)
            parameterCollection.Add(parameter);

        // Execute the stored procedure and retrieve the results in a table.
        dataAdapter = new SqlDataAdapter(command);
        dataTable = new DataTable();
        dataAdapter.Fill(dataTable);
    }
    finally
    {
        if (connection != null)
        {
            if (command != null)
            {
                if (dataAdapter != null)
                {
                    // Here the DataTable gets disposed.
                    if (dataTable != null)
                        dataTable.Dispose();
                    dataAdapter.Dispose();
                }

                parameterCollection.Clear();
                command.Dispose();
            }

            if (connection.State != ConnectionState.Closed)
                connection.Close();
            connection.Dispose();
        }
    }

    // However, I still return the DataTable
    // as if nothing had happened.
    return dataTable;
}

Upvotes: 1

Views: 1016

Answers (2)

John Saunders
John Saunders

Reputation: 161773

The general rule is to Dispose anything that implements IDisposable, whether it "needs it" or not. Saves you from the times when it "needs it" and you didn't think to Dispose.

Once you've called Dispose on an object, you shouldn't use it anymore. Period. It violates the entire concept of Dispose.

This is why you shouldn't call Dispose on an object that you are returning from a method. Leave it up to the caller to call Dispose when they're done with the object.

BTW, your code could be simpler. More like this:

using (SqlConnection connection = new SqlConnection(connectionString.ConnectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(cmdText, connection))
    {
        //...
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            return dataTable;
        }
    }
}

Upvotes: 2

kprobst
kprobst

Reputation: 16651

Well, you can't have your cake and eat it :)

Close the connection and so on, sure, but why do you need to dispose the table? That's not necessary. Just return it as-is.

Otherwise you'd be in a position where you... copy the table to something else and then return that instead? If you were using a ORM for example and mapping data to objects and then returning the objects this would make sense, but if you're not then just use the table/dataset directly.

Upvotes: 1

Related Questions