Database != Table. SQLite to separate database to table declarations

MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

Hello,
I have read today this guide : https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/databases/

And, I have a question with they examples on this page. The Database and the alone table have the same name, but in a real project, this is not the case. So, how to separate SQL request on a single file and the database in an other file? It's not explain.

Thanks for your answer. Sorry for my english.

Best Answers

  • MarlonRibeiroMarlonRibeiro US ✭✭✭
    edited July 2017 Accepted Answer

    @MaxenceSAUNIER Of course you can separate it in files, but I'd rather go on generics. You don't need to write the same operations a million times if you write it once using generics.
    Make sure of inherit all your model classes from a base model class.

    For example:

    public Task<List<T>> GetItemsAsync<T>() where T : BaseModel
    {
      return database.Table<T>().ToListAsync();
    }
    

    Then you just need to call
    var todoItems = await GetItemsAsync<TodoItem> ();
    or
    var whateverItems = await GetItemsAsync<Whatever>();
    :)

  • MarlonRibeiroMarlonRibeiro US ✭✭✭
    Accepted Answer

    @MaxenceSAUNIER If you're interested, I have this class which I include in basically all my projects.
    There are some operations from SQLite-Extensions which allows relationship properties (take a look: https://bitbucket.org/twincoders/sqlite-net-extensions).

     public class DataService
        {
    
            #region Fields
    
            private static object locker = new object();
            private SQLiteConnection database;
    
            #endregion
    
            #region Constructors
    
            public DataService(SQLiteConnection connection)
            {
                database = connection;
                CreateTables();
            }
    
            #endregion
    
            #region Setup
    
            private void CreateTables()
            {
                //database.CreateTable<Agreement>();
                //database.CreateTable<Company>();
        //add your tables here
    
            }
    
            #endregion
    
            #region Database Operations
    
            /// <summary>
            /// Insert or update a single object considering only foreign keys, ignoring relationship properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            public void Save(PersistentObject obj)
            {
                lock (locker)
                {
                    var persisted = database.InsertOrReplace(obj);
                    Debug.WriteLine("Object inserted or replaced in local database. Table = {0}; Id = {1}", obj.GetType(), obj.Id);
                }
            }
    
            /// <summary>
            /// Insert or update an object considering the relationship properties and ignoring foreign key properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void SaveWithChildren(PersistentObject obj, bool recursively = true)
            {
                try
                {
                    database.InsertOrReplaceWithChildren(obj, recursively);
                    Debug.WriteLine("Object inserted or replaced with children in local database. Table = {0}; Id = {1}", obj.GetType(), obj.Id);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            /// <summary>
            /// Insert or update an object considering the relationship properties and ignoring foreign key properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void SaveAllWithChildren<T>(List<T> list, bool recursively = true) where T : PersistentObject
            {
                try
                {
                    foreach (var item in list)
                    {
                        SaveWithChildren(item, recursively);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
    
            /// <summary>
            /// Get single object with children.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="id">Object identifier.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            /// <returns></returns>
            public T Get<T>(int id, bool recursively = true) where T : class
            {
                if (id != 0)
                    return database.GetWithChildren<T>(id, recursive: recursively);
                else
                    return null;
            }
    
            /// <summary>
            /// Retrieves all data from table <typeparamref>T</typeparamref>.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            /// <param name="includeInactiveObjects">Include object marked as inactive?</param>
            public IEnumerable<T> GetAll<T>(bool recursively = true, bool includeInactiveObjects = false) where T : PersistentObject
            {
                var list = database.GetAllWithChildren<T>(recursive: recursively);
                if (!includeInactiveObjects)
                    return list.Where(o => !o.IsDeleted);
                return list;
            }
    
            /// <summary>
            /// Executes a query on table <typeparamref>T</typeparamref>.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="query">Query.</param>
            /// <param name="args">Arguments.</param>
            public IEnumerable<T> Query<T>(string query, params object[] args) where T : PersistentObject
            {
                var list = database.Query<T>(query, args);
                return list;
            }
    
            /// <summary>
            /// Executes a query.
            /// </summary>
            /// <param name="mapping">Table Mapping.</param>
            /// <param name="query">Query.</param>
            public IEnumerable Query(SQLite.Net.TableMapping mapping, string query)
            {
                var list = database.Query(mapping, query);
                return list;
            }
    
            /// <summary>
            /// Mark as inactive.
            /// </summary>
            /// <param name="obj">Object.</param>
            public void SetInactive(PersistentObject obj)
            {
                lock (locker)
                {
                    obj.DeletedDate = DateTime.Now;
                    this.SaveWithChildren(obj);
                }
            }
    
            /// <summary>
            /// Deletes specified object.
            /// </summary>
            /// <param name="obj">Object to delete.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void HardDelete(PersistentObject obj, bool recursively = false)
            {
                lock (locker)
                {
                    database.Delete(obj, recursively);
                }
            }
    
            /// <summary>
            /// Retrieves Table Mapping for specified type.
            /// </summary>
            /// <param name="type">Type.</param>    
            public SQLite.Net.TableMapping GetMapping(Type type)
            {
                return database.GetMapping(type);
            }
    
            #endregion
    
        }
    
  • NMackayNMackay GB mod
    Accepted Answer

    Have you installed SQLite in your platform specific projects?

Answers

  • MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

    And, for CRUD (Create, Read, Update, Delete) are all on the same file? So, if I have 20 Tables I write 1xxxx lines on one file only? Do you have a solution to divide operations?

    public TodoItemDatabase(string dbPath)
    {
        database = new SQLiteAsyncConnection(dbPath);
        database.CreateTableAsync<TodoItem>().Wait();
        database.CreateTableAsync<OtherTable>().Wait();
        database.CreateTableAsync<Whatever>().Wait();
    }
    

    /appfolder/TablesRequests/TodoItemDatabase.cs
    /appfolder/TablesRequests/OtherTable.cs
    /appfolder/TablesRequests/Whatever.cs

    With her respective CRUD operation and other request. Thanks you.

  • MarlonRibeiroMarlonRibeiro USMember ✭✭✭
    edited July 2017 Accepted Answer

    @MaxenceSAUNIER Of course you can separate it in files, but I'd rather go on generics. You don't need to write the same operations a million times if you write it once using generics.
    Make sure of inherit all your model classes from a base model class.

    For example:

    public Task<List<T>> GetItemsAsync<T>() where T : BaseModel
    {
      return database.Table<T>().ToListAsync();
    }
    

    Then you just need to call
    var todoItems = await GetItemsAsync<TodoItem> ();
    or
    var whateverItems = await GetItemsAsync<Whatever>();
    :)

  • MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

    Perfect ! You answered another question that I was wondering ! :)

  • MarlonRibeiroMarlonRibeiro USMember ✭✭✭
    Accepted Answer

    @MaxenceSAUNIER If you're interested, I have this class which I include in basically all my projects.
    There are some operations from SQLite-Extensions which allows relationship properties (take a look: https://bitbucket.org/twincoders/sqlite-net-extensions).

     public class DataService
        {
    
            #region Fields
    
            private static object locker = new object();
            private SQLiteConnection database;
    
            #endregion
    
            #region Constructors
    
            public DataService(SQLiteConnection connection)
            {
                database = connection;
                CreateTables();
            }
    
            #endregion
    
            #region Setup
    
            private void CreateTables()
            {
                //database.CreateTable<Agreement>();
                //database.CreateTable<Company>();
        //add your tables here
    
            }
    
            #endregion
    
            #region Database Operations
    
            /// <summary>
            /// Insert or update a single object considering only foreign keys, ignoring relationship properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            public void Save(PersistentObject obj)
            {
                lock (locker)
                {
                    var persisted = database.InsertOrReplace(obj);
                    Debug.WriteLine("Object inserted or replaced in local database. Table = {0}; Id = {1}", obj.GetType(), obj.Id);
                }
            }
    
            /// <summary>
            /// Insert or update an object considering the relationship properties and ignoring foreign key properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void SaveWithChildren(PersistentObject obj, bool recursively = true)
            {
                try
                {
                    database.InsertOrReplaceWithChildren(obj, recursively);
                    Debug.WriteLine("Object inserted or replaced with children in local database. Table = {0}; Id = {1}", obj.GetType(), obj.Id);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            /// <summary>
            /// Insert or update an object considering the relationship properties and ignoring foreign key properties.
            /// </summary>
            /// <param name="obj">Object to save.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void SaveAllWithChildren<T>(List<T> list, bool recursively = true) where T : PersistentObject
            {
                try
                {
                    foreach (var item in list)
                    {
                        SaveWithChildren(item, recursively);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
    
            /// <summary>
            /// Get single object with children.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="id">Object identifier.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            /// <returns></returns>
            public T Get<T>(int id, bool recursively = true) where T : class
            {
                if (id != 0)
                    return database.GetWithChildren<T>(id, recursive: recursively);
                else
                    return null;
            }
    
            /// <summary>
            /// Retrieves all data from table <typeparamref>T</typeparamref>.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            /// <param name="includeInactiveObjects">Include object marked as inactive?</param>
            public IEnumerable<T> GetAll<T>(bool recursively = true, bool includeInactiveObjects = false) where T : PersistentObject
            {
                var list = database.GetAllWithChildren<T>(recursive: recursively);
                if (!includeInactiveObjects)
                    return list.Where(o => !o.IsDeleted);
                return list;
            }
    
            /// <summary>
            /// Executes a query on table <typeparamref>T</typeparamref>.
            /// </summary>
            /// <typeparam name="T">The type of object.</typeparam>
            /// <param name="query">Query.</param>
            /// <param name="args">Arguments.</param>
            public IEnumerable<T> Query<T>(string query, params object[] args) where T : PersistentObject
            {
                var list = database.Query<T>(query, args);
                return list;
            }
    
            /// <summary>
            /// Executes a query.
            /// </summary>
            /// <param name="mapping">Table Mapping.</param>
            /// <param name="query">Query.</param>
            public IEnumerable Query(SQLite.Net.TableMapping mapping, string query)
            {
                var list = database.Query(mapping, query);
                return list;
            }
    
            /// <summary>
            /// Mark as inactive.
            /// </summary>
            /// <param name="obj">Object.</param>
            public void SetInactive(PersistentObject obj)
            {
                lock (locker)
                {
                    obj.DeletedDate = DateTime.Now;
                    this.SaveWithChildren(obj);
                }
            }
    
            /// <summary>
            /// Deletes specified object.
            /// </summary>
            /// <param name="obj">Object to delete.</param>
            /// <param name="recursively">Perform cascade operations specified in object?</param>
            public void HardDelete(PersistentObject obj, bool recursively = false)
            {
                lock (locker)
                {
                    database.Delete(obj, recursively);
                }
            }
    
            /// <summary>
            /// Retrieves Table Mapping for specified type.
            /// </summary>
            /// <param name="type">Type.</param>    
            public SQLite.Net.TableMapping GetMapping(Type type)
            {
                return database.GetMapping(type);
            }
    
            #endregion
    
        }
    
  • MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

    Thanks you for this discovery ! The relationship with objects is very important.

  • MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

    I have install today SQLite-Extension but I have a problem. This package used SQLite.Net-PCL and this package implement a second parameter on the SQLiteConnection(ISQLitePlatform, path). What is the ISQLitePlatform ? I cant create this object. I have search many hours today and I despair.

    Thanks you very much :)

  • NMackayNMackay GBInsider, University mod
    Accepted Answer

    Have you installed SQLite in your platform specific projects?

  • MaxenceSAUNIERMaxenceSAUNIER USMember ✭✭✭

    Why SQLiteNetExtensions add SQLite.Net-PCL package if he prefer the SQLite-Net-PCL package ?
    Thanks

Sign In or Register to comment.