Multiple async Ado.net sql inserts in intent service

Question

I have an intent service that do the following:

extracts from intent a string parameter,
composes http query using the parameter,
executes it using async task and System.Net.Http,
parses received json and puts some field values to string sqlQuery which should insert data into sqlite db,
executes sqlQuery using Ado.net (with method that open a connection, then closes it).

Note that there can be several intents calling the service, with different PutExtra values. So, activity tells to the service: "Load information for City1", "Load information for City2" .... etc.

UPD. The intent service code:

public WeatherService() : base("WeatherService")
    {
    }

    public string sqlQuery;

    protected override async void OnHandleIntent(Android.Content.Intent intent)
    {

        // Get extras
        string wsMode = intent.GetStringExtra("mode");
        string wsLocation = intent.GetStringExtra("location");
        string wsProvider = intent.GetStringExtra("provider");
        string wsUpdId = intent.GetStringExtra("upd_id");
        //string debugMsg = "INTENT_SERVICE_OUTPUT: Mode " + wsMode + ", location " + wsLocation + " , provider " + wsProvider;
        //Console.WriteLine(debugMsg);

        // Generate http string
        string httpQuery = "";
        switch (wsProvider)
        {
            case "openweathermap":
                httpQuery = "http://api.openweathermap.org/data/2.5/weather?&appid=1&units=metric&lang=ru"; //fake key here
                if (wsMode == "code") { httpQuery = httpQuery + "&id=" + wsLocation; }
                break;
            default:
                httpQuery = "";
                break;
        }

        // Get weather from the provider
        dynamic results = await DataService.getDataFromService(httpQuery);
        if (results["weather"] != null)
        {

            // Compose insert queries
            if (sqlQuery == null) { sqlQuery = ""; Console.WriteLine("SQL QUERY FROM NULL TO EMPTY STRING"); }
            sqlQuery = sqlQuery +
                "INSERT INTO [Weather] ([IND_ID], [UPD_ID], [CITY_ID], [VALUE], [SHOW], [DATE], [TIME]) " +
                    "SELECT [IND_ID],'" + wsUpdId + "','" + (string)results["id"] + "','" + (string)results["weather"][0]["description"] +
                    "',1,NULL,NULL FROM [Indicators] WHERE [CAPTION] = 'currentConditions';" +
                "INSERT INTO [Weather] ([IND_ID], [UPD_ID], [CITY_ID], [VALUE], [SHOW], [DATE], [TIME]) " +
                    "SELECT [IND_ID],'" + wsUpdId + "','" + (string)results["id"] + "','" + (string)results["main"]["temp"] +
                    "',1,NULL,NULL FROM [Indicators] WHERE [CAPTION] = 'currentTemperature';";
            Console.WriteLine(sqlQuery);
        }


    }

    public override void OnDestroy()
    {

        // Insert data into databse
        if (sqlQuery != null) { Sql.ExecQueryScalarOrNonQuery(sqlQuery, 0); Console.WriteLine("SQL_QUERY: INSERTED"); } else { Console.WriteLine("SQL_QUERY: EMPTY!!!"); }

        // Update current view
        // TODO

        // End Service
        base.OnDestroy();

So, as you can see, I try to declare a string named sqlQuery, and trying to put in it some inserts of data. But the problem is: to correctly ask http async task, I had to make all OnHandleIntent method

protected override async void OnHandleIntent()

so, OnHandleIntent is started and OnDestroy() launches immidiately, when sqlQuery is still empty!!! And only few seconds ago, OnHandleIntent-s (in my example, two) are completed.

So, how to make the following: in OnHandleIntent we get extras, generate and launch http query (using async task on not???), then we generate and save inserts for db in (for simpleness, let's do that in string sqlQuery ;) ). Then, when all OnHandleIntents are ready, we execute the final sqlQuery (once) and end the service by calling base.OnDestroy(). Thnaks in advance.

Post actual code. The only thing that's clear from the description is that there was an error. What does asynchronous execution have to do with the problem - unless you tried to use the same connection to run multiple commands (don't) ? — Panagiotis Kanavos
If you want to insert a lot of data quickly, use SqlBulkCopy. Don't try to "parallelize" inserts, you'll only make things a lot worse. Sending 100 inserts in a single statement can be 100 times faster than trying to execute 100 inserts in parallel and end up locking one another. You can go even faster if you use an INSERT VALUES statement with 100 values. Nothing beats SqlBulkCopy though - it uses the same bulk import mechanism as bcp and BULK INSERT. — Panagiotis Kanavos
SqlBulkCopy uses minimal logging - instead of logging each statement, it logs the modified data pages only. — Panagiotis Kanavos
@PanagiotisKanavos Thanks! I'll research bulk insert or at least executing one insert values construction — Laser42
@PanagiotisKanavos i added code as well as some investigations' results. — Laser42

Laser42 Laser42 · Accepted Answer · 2017-06-24T19:07:08

I decided to change method declaration to

protected override void

and rewrite DataService class to make async -> sync as follows:

public class DataService
{
    public static object ReceivedData(string queryString)
    {

        // Exec the query
        dynamic data = null;
        using (HttpClient client = new HttpClient())
        {
            var response = client.GetAsync(queryString).Result;

            // If result is not null, deserialize json
            if (response != null)
            {
                string json = response.Content.ReadAsStringAsync().Result;
                data = JsonConvert.DeserializeObject(json);
            }

        }

        // Return json result
        return data;

    }
}

In my case, all works (sync) in a thread of intent service, and then successfully inserted to local sqlite db (in OnDestroy() method of intentservice.

Multiple async Ado.net sql inserts in intent service

1 Answers