Forum Cross Platform with Xamarin
We are excited to announce that the Xamarin Forums are moving to the new Microsoft Q&A experience. Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

We encourage you to head over to Microsoft Q&A for .NET for posting new questions and get involved today.

DB Locked in Android Xamarin Forms

I'm working on a App that have more than 50 tables and can work without internet connection, so in background the app can sync up with the API and get all the information and make the CRUD operation in local.

Sometimes when the app is sync up with the API, I'm getting the error "database is LOCKED", when I'm making another operation on the App.

So I need help to solve this, I know that there are a lot of post about this problem, but base on my implamentation with my database, it seems not to be enough to solve my problem.

Nugets:
Xamarin.Forms 3.0.0 482510
sqlite-net-pcl 1.5.166-beta

I use a class DataService.cs where that class connect with the DataContext.cs and make the connection with database and methods CRUD.
All methods in the DataService have the same way to connect with DataContext

//This is a resume of DataService.cs
    public class DataService 
        {
            public T Insert<T>(T model)
            {
                try
                {
                    using (var da = new DataContext())
                    {
                        da.Insert(model);
                        return model;
                    }
                }
                catch (Exception error)
                {
                    error.ToString();
                    return model;
                }
            }
    }

In DataContext.cs we have the connection with the local database and all the methods with the local database .
All methods have the collisionLock (to avoid conflict with database) and cnn.Dispose() (To close connection with the database and avoid the error Fatal signal 11 (SIGSEGV));

DataContext.cs

public interface IBusinessEntity
    {
        int ID { get; set; }
    }

  //This is a resume of DataContext.cs
    public class DataContext : IDisposable
    {
        #region Attributes
        public SQLiteConnection cnn;
        private static object collisionLock = new object();
        #endregion

        #region Constructors
        public DataContext()
        {
            cnn = DependencyService.Get<IConfiguracion>().GetConnection();
...
} 
        #endregion

        #region MetodosGenericosZulu
        public void Insert<T>(T model)
        {
            try
            {
                // Use locks to avoid database collisions
                lock (collisionLock)
                {
                    cnn.Insert(model);
                    cnn.Dispose();
                }
            }
            catch (Exception error)
            {
                Application.Current.MainPage.DisplayAlert(
                    "Error",
                    "Un error a ocurrido con la DB (Insert): " + error.Message.ToString(),
                    "Ok");
            }
        }

        public void Update<T>(T model)
        {
            try
            {
                lock (collisionLock)
                {
                    cnn.Update(model);
                    cnn.Dispose();
                }
            }
            catch (Exception error)
            {
                Application.Current.MainPage.DisplayAlert(
                                    "Error",
                                    "Un error a ocurrido con la DB (Actualizar): " + error.Message.ToString(),
                                    "Ok");
            }
        }

        ...
        #endregion
}
}

Implentation on Android project.

public class Configuracion : IConfiguracion
    {
        public Configuracion(){ }

        public SQLite.SQLiteConnection GetConnection()
        {
                var sqliteFileName = "FloraNueva_Desarrollo.db3";
                string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                var path = Path.Combine(documentsPath, sqliteFileName);
              var  conn = new SQLite.SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache);

            return conn;
        }
    }

So I need your help guys to solve the problem (database locked) and review if my implementation with the SQLite is OK.

I'm hearing all the suggestions.

Thanks in advance.

Best Answer

Answers

Sign In or Register to comment.