Asynchronous SqlDataAdapter?

Question

_{Friday, July 25, 2014 1:59 PM}

The SqlDataAdapter has a synchronous fill method, inherited from DbDataAdapter.
Is there a way I can wrap an asynchronous Task around the fill method and make my code asynchronous.
I am programming WPF forms in C# and I need to fill a datagrid with 9000 records. I need my dataview with rowfilter and sort.

Is there a better way to fill a dataview or datagrid?

Certified Geek

All replies (8)

_{Friday, August 1, 2014 6:03 AM ✅Answered}

Hello,

>>How come procedure returning a task does not have the async keyword?

They are two different ways to implement the async step.The Task.Factory.StartNew is not preserving the synchronization context whereas when using async/await this context is preserved. For example in an ASP.NET application this means that if you use Task.Factory.StartNew the HttpContext might not be accessible inside the task whereas if you use async/await it will be available.

Since the SqlDataAdapter does not have the asynchronous method like FillAsync(), we have to use the Task.Factory.StartNew without async keyword by using Task.Factory.StartNew.

Regards.

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Monday, July 28, 2014 2:39 AM}

Hello,

>> Is there a way I can wrap an asynchronous Task around the fill method and make my code asynchronous.

You could try to write the Task as:

static Task<DataSet> GetAlbumsAsync()

        {

            DataSet ds = new DataSet();

            return Task<DataSet>.Factory.StartNew(() =>

            {

                using (SqlConnection con = new SqlConnection(connectionString))

                {

                    string sqlSelect = @"WAITFOR DELAY '000:00:05'

                                        SELECT TOP 10 * FROM Album";

                    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, con);

                    da.Fill(ds);

                    ds.Tables[0].TableName = "Albums";

                }

                Console.WriteLine("Thread: " + Thread.CurrentThread.Name);

                return ds;

            });

        }

Calling it as:

static void Main(string[] args)

        {


            GetResutlAsync().ContinueWith(task =>

            {

                DataSet dsArtists = task.Result;

                foreach (DataRow row in dsArtists.Tables[0].Rows)

                {

                    foreach (DataColumn col in dsArtists.Tables[0].Columns)

                    {

                        Console.Write(row[col] + "\t");

                    }

                    Console.WriteLine();

                }

            });

>> I need my dataview with rowfilter and sort.

I am not very familiar with this control, you could ask it to the WPF forum.

>> Is there a better way to fill a dataview or datagrid?

Do you need to fill all 9000 records to your data control? From my opinion, I would use Page Query to fill about 100 rows one page.

Regards.

_{Monday, July 28, 2014 8:53 AM}

I 100% agree with the answer from Fred.

Also be aware that in fact the DataAdapter is running already in a separate process inside the windows service for SQL Server.

The resultset is returned as one resultset by the fill and not row by row.

Success
Cor

_{Monday, July 28, 2014 12:30 PM}

How can I get GetResutlAsync() to compile?

Do I need a special name space? It look misspelled!

Certified Geek

_{Tuesday, July 29, 2014 6:22 AM}

Oh, I am sorry for that, it misses the “}” symbol for the Main() method in my second code block.

Regards.

_{Tuesday, July 29, 2014 10:07 AM}

Success
Cor

_{Wednesday, July 30, 2014 3:45 PM}

How come procedure returning a task does not have the async keyword?

Certified Geek

_{Thursday, March 24, 2016 4:36 AM}

This will block the current thread while fetching data from Sql server.

If you instead use SqlConnection / SqlCommand / SqlDataReader you can have it run asynchronously. It will also give you the option to just get the number of rows you actually want.

How about something like the below code? It can be rewritten to get all columns values, their names and data types from the data reader.

        public static async Task<DataTable> GetData(CancellationToken cancellationToken)
        {
            var result = new DataTable();
            result.Columns.Add("Col1", typeof(int));
            result.Columns.Add("Col2", typeof(int));
            result.Columns.Add("Col3", typeof(int));

            using (var connection = new SqlConnection("ConnectionString"))
            {
                await connection.OpenAsync(cancellationToken);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select col1, col2, col3 from table1";
                    command.CommandType = CommandType.Text;

                    int col1Ordinal = -1, col2Ordinal = -1, col3Ordinal = -1;

                    using (var reader = await command.ExecuteReaderAsync(cancellationToken))
                    {
                        while (await reader.ReadAsync(cancellationToken))
                        {
                            if (col1Ordinal == -1)
                            {
                                col1Ordinal = reader.GetOrdinal("Col1");
                                col2Ordinal = reader.GetOrdinal("Col2");
                                col3Ordinal = reader.GetOrdinal("Col3");
                            }

                            result.Rows.Add(reader.GetValue(col1Ordinal), reader.GetValue(col2Ordinal), reader.GetValue(col3Ordinal));
                        }
                    }
                }
            }
        }

Last updated on 2014-07-25

Share via

Asynchronous SqlDataAdapter?

Question

All replies (8)

Additional resources