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

Posted by Eduardo León on Stack Overflow See other posts from Stack Overflow or by Eduardo León
Published on 2011-03-04T23:23:12Z Indexed on 2011/03/04 23:24 UTC
Read the original article Hit count: 218

Filed under:

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

© Stack Overflow or respective owner

Related posts about ADO.NET