How to use Async Await in a Database Query

Question

I am trying to improve an application I have been working on by changing my database queries to Async as I believe from my internet research it is a much better solution?

However I am having a few issues particularly when returning an ObservableCollection.

The first amendment I made to a standard void query appears to work ok as below

internal async Task GetInfoAsync(string WhereParameter)
        {
            StampModelCollection.ViewItemsCollection.Clear();
            SqlDataReader sqlDataReader = null;
            using (SqlConnection connection = new SqlConnection("Server =.\\SQLEXPRESS; Database = TestNewStructure; Trusted_Connection = Yes;"))
            {
                await connection.OpenAsync();
                try
                {
                    Sqlcmd = new SqlCommand("spGetStamps", connection);
                    Sqlcmd.CommandType = CommandType.StoredProcedure;
                    Sqlcmd.Parameters.Add(new SqlParameter("@Where", WhereParameter));
                    Sqlcmd.Parameters.Add(new SqlParameter("@Offset", StaticFilterParameters.RecordsFrom));
                    Sqlcmd.Parameters.Add(new SqlParameter("@NoRows", StaticFilterParameters.RecordsToo));
                    sqlDataReader = await Sqlcmd.ExecuteReaderAsync();
                    {
                        while (sqlDataReader.Read())
                        {
                            StampModel cardModel = new StampModel
                            {
                                Series = sqlDataReader["Series"].ToString(),
                                Country = sqlDataReader["Country"].ToString(),
                                Color1 = sqlDataReader["Color1"].ToString(),
                                Color2 = sqlDataReader["Color2"].ToString(),
                                Color3 = sqlDataReader["Color3"].ToString(),
                                Color4 = sqlDataReader["Color4"].ToString(),
                                Color5 = sqlDataReader["Color5"].ToString(),
                            };
                            StampModelCollection.ViewItemsCollection.Add(cardModel);
                        }
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    connection.Close();
                    if (sqlDataReader != null)
                    {
                        sqlDataReader.Close();
                    }
                }
            }
            if (StaticFilterParameters.currentComponentViewModel != null)
            {
                StaticFilterParameters.currentComponentViewModel.SelectedViewItem = StampModelCollection.ViewItemsCollection.FirstOrDefault();
            }
        }

When I try to amend the following

internal ObservableCollection<String> GetInfo(ObservableCollection<String> optionsValueInfo, string sQLQueryString)
        {
            if (sQLQueryString != "")
            {
                SqlDataReader sqlDataReader = null;
                using (SqlConnection connection = new SqlConnection("Server =.\\SQLEXPRESS; Database = TestNewStructure; Trusted_Connection = Yes;"))
                {
                    connection.Open();
                    try
                    {
                        Sqlcmd = new SqlCommand(sQLQueryString, connection);
                        Sqlcmd.CommandType = CommandType.StoredProcedure;
                        sqlDataReader = Sqlcmd.ExecuteReader();
                        {
                            while (sqlDataReader.Read())
                            {
                                if (sqlDataReader[0].ToString() != "")
                                {
                                    optionsValueInfo.Add(sqlDataReader[0].ToString());
                                }
                            }
                        }
                    }
                    catch /*(Exception ex)*/
                    {

                    }
                    finally
                    {
                        connection.Close();
                        if (sqlDataReader != null)
                        {
                            sqlDataReader.Close();
                        }
                    }
                }
            }
            return optionsValueInfo;
        }

To

internal async Task<ObservableCollection<String>> GetInfo(ObservableCollection<String> optionsValueInfo, string sQLQueryString)
        {
            if (sQLQueryString != "")
            {
                SqlDataReader sqlDataReader = null;
                using (SqlConnection connection = new SqlConnection("Server =.\\SQLEXPRESS; Database = TestNewStructure; Trusted_Connection = Yes;"))
                {
                    await connection.OpenAsync();
                    try
                    {
                        Sqlcmd = new SqlCommand(sQLQueryString, connection);
                        Sqlcmd.CommandType = CommandType.StoredProcedure;
                        sqlDataReader = await Sqlcmd.ExecuteReaderAsync();
                        {
                            while (sqlDataReader.Read())
                            {
                                if (sqlDataReader[0].ToString() != "")
                                {
                                    optionsValueInfo.Add(sqlDataReader[0].ToString());
                                }
                            }
                        }
                    }
                    catch /*(Exception ex)*/
                    {

                    }
                    finally
                    {
                        connection.Close();
                        if (sqlDataReader != null)
                        {
                            sqlDataReader.Close();
                        }
                    }
                }
            }
            return optionsValueInfo;
        }

I receive an error at the code that calls the Query

return getOptionsValueInfo.GetInfo(YearFromToList, "spGetDistinctYearOfIssue");

The error message is "Cannot Implicitly convert type System.Threading.Tasks.Task<System.Collections.ObjectModel.ObservableCollection<String>> to System.Collections.ObjectModel.ObservableCollection<String>"

Any assistance would be much appreciated.

Answer 1

you changed the GetInfo() method to a task, you have two options:

1. change all callers in the call tree to task/async. this will give the full benefit of async:

Task<ObservableCollection<String>> MyCaller()
{ 
   return getOptionsValueInfo.GetInfo(YearFromToList, "spGetDistinctYearOfIssue"); 
}

2. sync wait on the async call. as you are just creating a thread to make the db call, this is actually less efficient than the sync database call:

   var task = getOptionsValueInfo.GetInfo(YearFromToList, "spGetDistinctYearOfIssue"); 
   task.Wait();
   return task.Result;