CODEWITHSUNDEEP
c#multithreadingado.netdatasetthread-safety
Virender
Virender

Reputation: 51

Fill Dataset Async

Below method is getting used to fill Dataset.

if we are calling this method in synchronous way it is working fine.

But now we need to call this method in Asynchronous way.so what changes i need to do so that below method should work properly without any issue.

public DataSet Filldata(string ProcName, string TableName)
{
    DataSet ds = new DataSet();
    try
    {
        da = new SqlDataAdapter(ProcName, con);
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        da.SelectCommand.CommandTimeout = 15000;
        da.Fill(ds, TableName);
    }
    catch (Exception ex)
    {
        ErrorMsg = ex.Message.ToString();
        HMISLogger.logger.Error(ex.Message.ToString() + " " + ProcName, ex);
    }
    finally
    {
        con.Close();
        da.Dispose();
    }
    return ds;
}

Upvotes: 5

Views: 12377

Answers (3)

Yogi
Yogi

Reputation: 460

You can do this:

DataSet ds = await Task.Run(() => FillData(spName, tableName));

but the statement must be inside some asynchronous function, i.e:

public async Task<DataSet> GetDataSetAsync() {
      ..... 
   }

So the end result looks something like this:

public async Task<DataSet> GetDataSetAsync() {
    DataSet ds = await Task.Run(() => FillData(spName, tableName)); 
    return ds;
}

That's the wrapper, which only gives you the idea. You probably should not do that though. I would put the Task.Run in the lowest level where the SqlDataAdapter .Fill is invoked

This will not work in .NetFrameWork WebAPI but it will work in .Net Core WebAPI and Core or regular .Net Console programs as long as you reference NuGet Package System.Data.SqlClient 4.6.0. In .Net Framework WebAPI (Not Core), it seems that the await returns the DataSet in a different thread because the process disappeared at the await statement.

Upvotes: 3

Deepak Kothari
Deepak Kothari

Reputation: 1763

You can declare the class level static object as below

private static object lockObject = new object();

And modify the your method as below , As Fill method takes care of connection open and close we can add lock statement before it.

 public DataSet Filldata(string ProcName, string TableName)
        {
            DataSet ds = new DataSet();
            try
            {

                da = new SqlDataAdapter(ProcName, con);
                da.SelectCommand.CommandTimeout = 15000;
                lock (lockObj)
                {
                    da.Fill(ds, TableName);
                }
            }            
            catch (Exception ex) {
                ErrorMsg = ex.Message.ToString();
                HMISLogger.logger.Error(ex.Message.ToString() + " " + ProcName, ex);
            }
            finally {
                con.Close();
                da.Dispose();
            }
            return ds;
        }

Upvotes: -2

Deepak Kothari
Deepak Kothari

Reputation: 1763

You can use something as below, returning Task which is an async operation

public Task<DataSet> FilldataAsync(string ProcName, string TableName)
{
    try
            {
                return Task.Run(() =>
                {
                    DataSet ds = new DataSet();
                    using (var da = new SqlDataAdapter(ProcName, con))
                    {
                        da.SelectCommand.CommandTimeout = 15000;
                        da.Fill(ds, TableName);
                        return ds;
                    }
                });
            }
            catch (Exception ex)
            {
                ErrorMsg = ex.Message.ToString();
                HMISLogger.logger.Error(ex.Message.ToString() + " " + ProcName, ex);
            }
}

You may call it as below using async/await keywords,

private async Task GetSomeData(string sSQL)
{
    DataSet results = await FilldataAsync(ProcName, TableName);
    //Populate once data received
    grdRes.DataSource = results.Tables[0];
}

Upvotes: 1

Related Questions