Noob ADO.NET question: Can I do the following?
Retrieve a DataTable somehow.
Dispose it.
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;
}
