Sqlite best practice - single DB connection for app or open/close connection for each operation?

AndyAndy Member ✭✭

Hi,
I've seen various coding samples where people either open and hold a SqliteConnection for the life of the app and others were the SqliteConnection is opened and closed. The Sqlite documentation indicates that there is a SERIALIZED threading mode that makes sharing a connection between threads safe.
Does anyone have experience using that in Xamarin Forms? I'll have background threads updating the database at the same time as the UI threads.
Should I obtain SqliteConnection and open/close it with every operation or leave the SqliteConnection open the life of the app?

Answers

  • JiriMatejkaJiriMatejka CZMember ✭✭✭

    I would definitely go the way having separate connection on UI thread and background thread. The database runs locally, there is no issue to have two connections opened.

  • knasherknasher USMember ✭✭

    I tend to set-up my DI container set-up to create and close connections as needed, which can mean that multiple connections are open at once. Entity Framework seems to prefer individual connections instead of using a singleton too.

  • AndyAndy Member ✭✭

    Thanks - So that means I have to pass my Sqlite connection and Sqlite transaction into all of my data access layer API's as a parameter and manually handle transaction enrollment (method get's called with an existing transaction or without an existing transaction).

    SaveSomeData(string MyData, SqliteConnection SQLConn = null, SqliteTransaction SQLTran = null)
    {
    if(SQLConn == null) SQLConn = new SqliteConnection(...);
    if(SQLTran == null) SQLTran = SQLConn.StartTransaction();

    // now save MyData to the database 
    
    ....
    
    // Now before I leave I need to add logic checking if if the SQlConn and SQLTran were passed in and if no I need to 
    // commit the transaction and close the connection (SQLConn.Close())
    

    }

  • rlaskerrlasker Member ✭✭

    I'm still trying to understand this myself. There doesn't seem to be a "Best Practice" that is immediately available. According to appcelerator it is "best practice" to open / close per operation but I really don't see much to corroborate this.

    https://wiki.appcelerator.org/display/guides2/Working+with+a+SQLite+Database#WorkingwithaSQLiteDatabase-Closethedatabaseandresultsetwitheachoperation

  • rlaskerrlasker Member ✭✭

    In fact I see the opposite in some occasions so it's hard to say has the most compelling solution.

    http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

Sign In or Register to comment.