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, Jun 21 '17 at 14:34
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, Jun 21 '17 at 14:36
SqlBulkCopy uses *minimal* logging - instead of logging each statement, it logs the modified data pages only. — Panagiotis Kanavos, Jun 21 '17 at 14:39
@PanagiotisKanavos Thanks! I'll research bulk insert or at least executing one insert values construction — Laser42, Jun 21 '17 at 15:13
@PanagiotisKanavos i added code as well as some investigations' results. — Laser42, Jun 21 '17 at 20:54
The code doesn't help - those aren't ADO.NET methods and classes. The only thing one can say is that `async void` is a very bad idea. It's meant only for event handlers. You can't await an `async void` method. Asynchronous *methods* should return `async Task`. Asynchronous functions, `async Task`. Most likely, the code that calls `OnHandleIntent` fails to await for the method to execute — Panagiotis Kanavos, Jun 22 '17 at 07:35
PS constructing queries by concatenating strings just begs for SQL Injection attacks and conversion issues. Why don't you use an ORM like EF or a micro-ORM like Dapper? You are trying to reimplement functionality that is already implemented — Panagiotis Kanavos, Jun 22 '17 at 07:36
OK, now I'm using string generated insert just for debug. I'll take a look of libs you have wrote. Now, the problem is that I call asynchronously OnHandleIntent. So, I will remove async - await from all IntentService, and try to overwrite DataService not as async task, but usual synchronous method. Something like answer from here: https://stackoverflow.com/questions/14435520/why-use-httpclient-for-synchronous-connection — Laser42, Jun 22 '17 at 08:12
No, that's not a solution. The problem is the method, not `async/await`. Change `async void` to `async Task`, *don't* remove `async/await`. There is nothing wrong with it at all. If `DataService`, whatever it is, depends on events and synchronous methods, it needs rewriting — Panagiotis Kanavos, Jun 22 '17 at 08:13
And the link you provided is irrelevant - nothing to do with ADO.NET at all. *All* the answers stress that you *shouldn't* block asynchronous calls — Panagiotis Kanavos, Jun 22 '17 at 08:15
The link I provided show how to overwrite my another class (DataService), which takes httpQuery, parses json and return result to IntentService. — Laser42, Jun 22 '17 at 08:36

score 1 · Accepted Answer · answered Jun 24 '17 at 19:07

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 Answers1