Forum Cross Platform with Xamarin

Performing a lot of queries fails (cross platform)

I'm having a problem with database access on xamarin, testing on iOS but I believe it applies cross platform.

It is very similar to the below post:

I have worked through every possible scenario over the past few days to address this, in essence the code is :-

using (SqliteConnection connection = new SqliteConnection ("Data Source=" + pathToDatabaseFile))
connection.Open ();

I've put this into a static class to do all my database query stuff, and as I understand it this is tight and secure (i.e. no connections left open). However, it always fails at the 'connection.Open' stage when I'm running a large number of queries. I'm totally out of ideas as to how to solve this, I'm very new to Xamarin but have released apps which are using this code which work fine, its only once the number of queries (I assume) goes over a certain point.

I've looked into singleton design patterns but can't find any explicit samples of one for database access, can anyone please help as I'm totally at a loss as to how to fix this.



  • AndyFlisherAndyFlisher GBBeta, University ✭✭✭

    Not Xamarin specific, but does this help?

    Personally, if possible, I would try to reuse an existing connection with appropriate check exists / connect logic, and thus reducing the number of specific connect / disconnects (let the underlying SQLite handlers manager that)

    As an aside, have you tried iterating a counter to see how many connections / queries it manages before bombing out - that value may mean something to a better person than me?

  • softlionsoftlion FRBeta ✭✭✭

    Don't use Mono.Data.Sqlite, use pcl instead.

  • JohnChapmanJohnChapman GBMember

    thanks to both of you for taking the time to reply, yes Andy I did try maintaining a single correction as you suggested as a workaround but this didn't help.

    I'll have a go with the alternative library and get back to you with progress, reading round I'm not the first person to have a problem with this
    thanks for your help

  • JohnChapmanJohnChapman GBMember
    edited December 2013

    Sorry but still having the same problem. I've done the most explicit memory clearing I can think of and its still falling over after a large number of queries, here is the relevant code

        public static SqliteConnection conn; // connection to use throughout
        if ( conn!=null) conn.Dispose ();
        conn = new SqliteConnection("Data Source=" + dbPath);
            conn.Open ();

    // do stuff, run queries etc
    catch(Exception e){
    // oh dear, can't open

    This example, although I realise isn't the prettiest / most efficient, should work no matter if the connection is being reused, reset etc etc, however this code falls over after a large number of queries and is indicative that somewhere a memory leak is still going on.

    The exception is caught at the stage.

    I'm inclined to think therefore this is a major bug with xamarin, does anyone have any example code / suggestions to make this work as the documentation for the sqllite stuff is pretty basic and doesn't address this area at all, I've no idea about how to create a Singleton etc as there is no documentation for it, so could someone post how they would change this code to make it work? didn't work either;), same problem


  • JohnChapmanJohnChapman GBMember

    Fixed now - did an edit to to allow generic queries to return List<Dictionary<string,string>> instead of having to specify the objects etc as this was a faff. I've tried to attached the modified sqllite library but it isn't allowed so I'll paste in the relevant code, the call is

    List <Dictionary<string,string>> results = connection.Query2("Select something from table where something, "");

    and in

        public List<Dictionary<string, string>> Query2(string query, params object[] args)
            var cmd = CreateCommand(query, args);
            List<Dictionary<string,object>> results = cmd.ExecuteQuery();
            List<Dictionary<string,string>> op = new List<Dictionary<string,string>> ();
            foreach(Dictionary<string,object> row in results){
                Dictionary<string, string> outputRow = row.ToDictionary(k => k.Key, k => k.Value == null ? "" : k.Value.ToString());
            return op;

    thanks for your help. By the sounds of it Xamarin need to update their documentation to ensure people don't use Mono.Data.Sqllite as its got a memory leak issue, as it still fails even with the singleton pattern etc.

  • softlionsoftlion FRBeta ✭✭✭
    edited January 2014

    The open source project site is here:

    Could you create a pull request there ?

    The relevant file is


    So you say that this one leaks ?

       public List<T> Query<T>(string query, params object[] args) where T : new()
                SQLiteCommand cmd = CreateCommand(query, args);
                return cmd.ExecuteQuery<T>();
  • JohnChapmanJohnChapman GBMember

    no, I don't think the SQLlite.Net leaks, but Mono.Data.Sqlite does. The problem with is that it only returns specific configured objects, whereas I need something more flexible - having List implemented as List<Dictionaty<string,string>> for example didn't work.

    More than happy to do a pull request, however I suspect my relative inexperience with xamarin in general would mean that I wouldn't be the best person to do this as I'll probably do more harm than good. The 'Query2' function works very well but I'm sure someone else with more expertise could improve it, but for anyone having similar problems I'd just suggest adding it in ad-hoc to as and when you need it.

Sign In or Register to comment.