Hi,
I am calling a store procedure and fetching the dataset. But once i complete the execution i need to clear the temp tables that are created while executing the stroce proc. But i want to do this operation in a Asyncronous method, which means i will fetch the results and return the dataset and paralelly try to drop the temp tables that are created while execution, so that i can reduce the time and increase the response time for the DB call.
ThreadPool and BackgroundWorker are the functionlities that i used for Async call. When I call the method in Async way the transaction object which was created earlier was no more available and now its trying to create a new thread to complete the process. And the temp tables that are created earlier were not present in this thread hence i am getting an exception.
This is the issue i am facing as of now. Can anybody help me out to resolve this issue.
Rob here is the peiece of code which you were asking
storedProcedureName- string
tempTable- string[] these two are the input parameters for this method
dataSet- return value from this method
Database database;//Create Database with help of connection string
DbConnection connection = database.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
using (DbCommand dbCommand = database.GetStoredProcCommand(storedProcedureName))
{
//Loading DataSet after passing inout parameters.
database.LoadDataSet(dbCommand, dataSet, dataTable);
}
BackgroundWorker bw = new BackgroundWorker();
bw.DoWork += (o, t) =>
{
if (tempTable != null && tempTable.Length > 0)
{
foreach (string dropCommand in tempTable)
{
try
{
//Call to drop temp table
database.ExecuteNonQuery(transaction, CommandType.Text, dropCommand);
}
catch (Exception ex)
{
MessageBox.Show(dropCommand + "&& " + ex);
}
}
}
transaction.Commit();
connection.Close();
};
bw.RunWorkerAsync();