c# Async and await for loop with database

Question

I'm facing a little problem. I've made a function that imports customers from my website to my database to create invoices and so.. When this function starts, it calls another function to import only new clients. Now I want to make this last function an await to prevent that my software at home can search for the newly imported customers. This customer import is pretty easy, it just selects only the customers which are not imported through a loop. But I also made some security's in there in case something goes wrong. You know, most errors come from human input errors... But what is the best way to make this function an async so that the other function can await while it's importing new customers?

public async Task<bool> ImportClients(bool onlyNewCustomers)
{
    System.Data.DataTable Table = new System.Data.DataTable();
    System.Data.DataTable CustomerTable = new System.Data.DataTable();
    System.Data.DataTable KlantTable = new System.Data.DataTable();

    int PrestaCustomerID = 0; //original customer id from the prestashop
    int CustomerID = 0; //Original customerID from software customer
    int CustomerIdInserted = 0; //id from the inserted customer id in the software
    int Wait = 0; //This var is used for the mysql to wait a few miliseconds between x updates

    string Sql = "";
    string Prefix = "";
    DateTime Bday;
    //Vars for logging
    int CustomersImported = 0;
    StringBuilder NewCustomerInfo = new StringBuilder();
    StringBuilder NewCustomerAddress = new StringBuilder();

    //Select everything whithin the customers table. After that we look if the customer is imported else we update the clients credentials.
    Sql =
        "SELECT c.id_customer, id_gender, c.firstname, c.lastname, c.email, c.birthday, c.newsletter, c.optin, c.website, " +
        "c.active, c.date_add, c.date_upd, c.imported FROM ps_customer c " +
        (onlyNewCustomers ? "WHERE imported = 0 " : "") + "ORDER BY c.id_customer DESC;";
    Table = Functions.SelectWebQuery(Sql);
    if (Table.Rows.Count > 0)
    {
        for (int i = 0; i < Table.Rows.Count; i++)
        {
            if (somethingGoesWrong)
            {
                return false;
            }
        }

        return await Task.WhenAll<bool>(true);
    }
}

And what i've tried to call this function

public async static void OrderImport()
{
    Functions fns = new Functions();
    bool importCustomers = await fns.ImportClients(true);
}

I'm using .net 4.5 with mysql database in winforms.

Thanks! Paul

What if you just introduce a static boolean variable. For instance IsCustomersImprotInProgress. When you start your import you set the value of this variable to true - which is indicating that customer import is in progress. Then wrap your importing function in a try/catch/finally block and in finally block setting this variable to false(indicating that import is completed) And where you need you could check the value of this variable before allowing a user to perform the search. — Qwerty Qwerty
– Qwerty Qwerty, Commented Jul 4, 2020 at 17:54
Change async void to async Task, you'll be able to await it then. — aepot
– aepot, Commented Jul 4, 2020 at 17:59
Your code seams a bit out of the ordinary. So I would recommend you learn a bit more about how async code works because async != background thread. Your code is already awaitable only not good. — Filip Cordas
– Filip Cordas, Commented Jul 4, 2020 at 18:11
@FilipCordas I know, but this is just a part of my function otherwise it will be a lot of coding which won't be necassary.. I think... That's why I i'm asking how to do this. I've done a little bit with async but that was with dll's from nuGet. I'll try what aepot said and fix some things. Thanks! — Paul Albers
– Paul Albers, Commented Jul 4, 2020 at 19:15
Well my suggestion is to rewrite the SelectWebQuery function to become async. MySql connector has async interface mysqlconnector.net/tutorials/connect-to-mysql. But given you are running a single operation on one machine you won't see any improvements performance vise async make sense on high load setups. If you want to do it in the background so you don't block the UI thread use Task.Run it will work fine — Filip Cordas
– Filip Cordas, Commented Jul 4, 2020 at 20:26

Harald Coppoolse · Accepted Answer · 2020-07-06 10:14:19Z

My advice would be not to return a Task<bool>, but to return the newly imported customers. If there are no new customers, or there are minor errors, that are probably solved the next time that you import new customers, return an empty list. For errorst that need immediate action raise an exception.

FurtherMore I'd make an extra method to fetch new customers: FetchNewCustomers is easier to understand what it does than FetchCustomers(true).

public Task<ICollection<Customer> FetchNewCustomersAsync()
{
    return FetchCustomersAsync(true);
}
public Task<ICollection<Customer> FetchCustomersAsync(bool newOnly)
{
    ... // TODO: implement
}

Apparently you have a method Functions.SelectWebQuery(string) that returns DataTable. This returned DataTable needs to be converted to a sequence of Customers.

As an extension method. See extension methods demystified

public static IEnumerable<Customer> ToCustomers(this DataTable table)
{
     // in this method you handle the problems if the table is not a table of Customers
    foreach(DataRow dataRow in table.AsEnumerable)
    {
         Customer customer = new Customer()
         {
              ... // fetch values from DataRow: dataRow.Field<string>("Name");
         }
         yield return customer;
    }
}

Usage:

string sql = ...
IEnumerable<Customer> customers = Functions.SelectWebQuery(sql).ToCustomers()

Or the async version:

IEnumerable<Customer> customers = (await Functions.SelectWebQueryAsync(sql))
    .ToCustomers();

You need to select a sql, depending on whether you want all Customers or only the new Customers:

public string sqlTextAllCustomers {get; }
public string sqlTextNewCustomers {get; }

Now we are ready to implement FetchCustomersAsync:

public Task<ICollection<Customer> FetchCustomersAsync(bool newOnly)
{
    string sqlText = newOnly ? sqlTextNewCustomer : sqlTextAllCustomers;

    try
    {
         // in baby steps:
         DataTable fetchedData = await Functions.SelectWebQuery(sqlText);
         return fetchedData.ToCustomers();
    } 
    catch(Exception exc)
    {
        // TODO: detect if serious error or not
        if (isSeriousError)
            throw exc;
        else
        {
            // not a serious error: we'll process the new customers next time
            return Enumerable.Empty<Customer>();
        }
    } 
}

Collectives™ on Stack Overflow

c# Async and await for loop with database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related