BulkCopy.WriteToServerAsync() Not Working Importing Records

asynchronous c# sqlbulkcopy

Question

I wrote a routine to bulk import records. It is not working and I think the problem is that I don't have any kind of await in here but I don't know how or where to put it in. Due to the nature of my project, I don't want the method to be an async method. I am just using async to notify of updates.

    public int LoadTempFile(string fn, string tableName)
    {
        int retVal = 1;
        // loads a CSV file into a temporary file of the same structure
        StatusWindow sw = new StatusWindow("Loading Temp Files");
        sw.Show();

        try
        {
            string cs = GetConnectionString() + ";Asynchronous Processing=true;"; 
            SqlConnection cxs = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand("Truncate table " + tableName, cxs);
            cxs.Open();
            cmd.ExecuteNonQuery();
            cmd.CommandTimeout = 640;
            cxs.Close();

            using (SqlBulkCopy copy = new SqlBulkCopy(cs))
            {
                using (StreamReader file = new StreamReader(fn))
                {
                    CsvReader csv = new CsvReader(file, true);
                    copy.DestinationTableName = tableName;
                    copy.BulkCopyTimeout = 1640;
                    copy.NotifyAfter = 100;
                    copy.SqlRowsCopied += (sender, eventArgs) =>
                     {
                        sw.Update(eventArgs.RowsCopied.ToString() + " Records Copied");
                    };


                    try
                    {
                       copy.WriteToServerAsync(csv);
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show("SQL Error Importing " + fn + Environment.NewLine + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error Importing " + fn + Environment.NewLine + ex.Message);
                    }

                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show("Error In Temp Files " + fn + Environment.NewLine + e.ToString());
            retVal = 0;
        }
        finally
        { sw.Close(); }
        return (retVal);
    }

I would appreciate any help or input. Also, comments on my coding style or things like that are welcome, too.

Accepted Answer

You are correct, the reason this code does not work is that you never await the call to copy.WriteToServerAsync(csv); The method returns you a Task object

I don't want the method to be an async method. I am just using async to notify of updates.

Getting notifications is not conditioned upon using async version of the method. In fact, Microsoft's own example for SqlRowsCopied uses a synchronous version, i.e. copy.WriteToServer(...).

Using async wouldn't help getting UI notifications unless you go async all the way. See this Q&A for more information.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why