How to connect a database to a Xamarin.Forms application?

Question

PHP

1. I have a SQLite database on a server on the internet, using PHP I can interact with the database in Unity - is this a good way to access the database?
2. What is the best local server to host the SQLite database?
3. The SQLite database will be located on the same local server. (Directly accessing the database in Xamarin.Forms, as I understand it, may not be a good option either) do I need to use PHP here again in between?
4. How to perform various queries about creating, editing and deleting database tables in Xamarin.Forms (some code examples please)?

PS Xamarin.Forms app for both Android and Windows.

Answer 1

I will describe one of mine:
A backend written in PHP a long time ago.
Xamarin.FORM application displaying sections, subsections, and articles.
The application can save all articles for offline access.
Accordingly, there is a service that gives the same data for both modes.
When trying to go offline, the user is prompted to download all resources approx. 60 MB.
Then it works offline.
Here are the base classes

using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Polly;
using SQLite;
using Xamarin.Essentials;

namespace SpecialForcesDirectory.Code
{
    public abstract class BaseContentDatabase
    {
        public static readonly string DatabasePath = Path.Combine(FileSystem.AppDataDirectory, Settings.DataBaseName);
        static readonly Lazy<SQLiteAsyncConnection> DatabaseConnectionHolder = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(DatabasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));

        private static SQLiteAsyncConnection DatabaseConnection => DatabaseConnectionHolder.Value;


        protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
        {
            if (DatabaseConnection.TableMappings.Any(x => x.MappedType == typeof(T))) return DatabaseConnection;
            // On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database execution
            await DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false);
            await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
            return DatabaseConnection;
        }

        public static Task<T> AttemptAndRetry<T>(Func<Task<T>> action, int numRetries = 10)
        {
            return Policy.Handle<SQLiteException>().WaitAndRetryAsync(numRetries, PollyRetryAttempt).ExecuteAsync(action);

            static TimeSpan PollyRetryAttempt(int attemptNumber) => TimeSpan.FromMilliseconds(Math.Pow(2, attemptNumber));
        }

        protected static async Task Close()
        {
            await DatabaseConnection.CloseAsync();
        }
    }
public class ContentDatabase : BaseContentDatabase
    {
        #region Category repository
        private async Task<SQLiteAsyncConnection> GetCategoryConn()
        {
            return await GetDatabaseConnection<Category>().ConfigureAwait(false);
        }
        public async Task<IEnumerable<Category>> GetCategories()
        {
            var conn = await GetCategoryConn();
            return await AttemptAndRetry(() => conn.Table<Category>().ToListAsync()).ConfigureAwait(false);
        }
        public async Task<Category> GetCategory(int id)
        {
            var conn = await GetCategoryConn();
            return await AttemptAndRetry(() => conn.GetAsync<Category>(id)).ConfigureAwait(false);
        } 
        #endregion

        #region Content Repository
        private async Task<SQLiteAsyncConnection> GetCtxConn()
        {
            return await GetDatabaseConnection<Content>().ConfigureAwait(false);
        }
        
        public async Task<IEnumerable<Content>> GetItems()
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.Table<Content>().ToListAsync()).ConfigureAwait(false);
        }

        public async Task<Content> GetItem(int id)
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.GetAsync<Content>(id)).ConfigureAwait(false);
        }

        public async Task<int> DeleteItem(int id)
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.DeleteAsync<Content>(id)).ConfigureAwait(false);
        }

        public async Task SaveItem(Content item)
        {
            var conn = await GetCtxConn();
            if (item.Id == 0) await AttemptAndRetry(() => conn.InsertAsync(item)).ConfigureAwait(false);
            await AttemptAndRetry(() => conn.UpdateAsync(item)).ConfigureAwait(false);
        }

        public async Task<IEnumerable<SRawItem>> GetItemsByQuery(string q, int lim)
        {
            var qu = q.ToUpper();
            var list = new List<SRawItem>();
            var conn = await GetCtxConn();
            var result = conn.Table<Content>()
                .Where(x => x.EnableTab1 == 1 && x.Tab1.ToUpper().Contains(qu)
                            || x.EnableTab2 == 1 && x.Tab2.ToUpper().Contains(qu)
                            || x.EnableTab3 == 1 && x.Tab3.ToUpper().Contains(qu)).Take(lim);

            var r = await result.ToListAsync();
            foreach (var content in r)
            {
                var st = GetSt(content, qu);
                var title = await conn.Table<Category>().Where(x => x.Id == content.CatId).FirstAsync();
                var ni = new SRawItem
                {
                    Body = st.Body,
                    CatId = content.CatId.ToString(),
                    Title = title.Title,
                    Id = content.Id.ToString(),
                    MType = "a",
                    Tab = st.Tab
                };
                list.Add(ni);
            }

            return list;
        }

        private static TabStruct GetSt(Content content, string q)
        {
            if (content.Tab1.ToUpper().Contains(q))
                return new TabStruct { Tab = "1", Body = content.Tab1 };
            if (content.Tab2.ToUpper().Contains(q))
                return new TabStruct { Tab = "2", Body = content.Tab2 };
            return new TabStruct { Tab = "3", Body = content.Tab3 };
        }

        private struct TabStruct
        {
            public string Body { get; set; }
            public string Tab { get; set; }
        }

        #endregion

        public async Task Clear()
        {
            await BaseContentDatabase.Close();
        }
    }
[Table("quote")]
    public class Quote
    {
        [PrimaryKey]
        [AutoIncrement]
        [Column("id")] 
        public int Id { get; set; }

        [Column("author")] public string Author { get; set; }

        [Column("body")] public string Body { get; set; }

        [Column("image")] public string Image { get; set; }

        [Column("created")] public DateTime Created { get; set; }
    }

public class QuoteRepository : BaseUserDatabase
    {
        private async Task<SQLiteAsyncConnection> GetConn()
        {
            return await GetDatabaseConnection<Quote>().ConfigureAwait(false);
        }
        public async Task<IEnumerable<Quote>> GetItems()
        {
            var conn = await GetConn();
            return await AttemptAndRetry(() => conn.Table<Quote>().ToListAsync()).ConfigureAwait(false);
        }

        public async Task<Quote> GetItem(int id)
        {
            var conn = await GetConn();
            return await AttemptAndRetry(() => conn.GetAsync<Quote>(id)).ConfigureAwait(false);
        }

        public async Task<int> DeleteItem(int id)
        {
            var conn = await GetConn();
            return await AttemptAndRetry(() => conn.DeleteAsync<Quote>(id)).ConfigureAwait(false);
        }

        public async Task<int> SaveItem(Quote item)
        {
            var conn = await GetConn();
            return await AttemptAndRetry(() => conn.InsertOrReplaceAsync(item)).ConfigureAwait(false);
        }

        public async Task DeleteAll()
        {
            var conn = await GetConn();
            await AttemptAndRetry(() => conn.DeleteAllAsync<Quote>()).ConfigureAwait(false);
        }
    }


}

Answer 2

1. It is not reasonable to use sqlite in a loaded environment.
2. Local)
The usual use case for sqlite is on the client device, not on the server.
3.

Directly accessing the database in Xamarin.Forms, as I understand it, may also not be a good option

No. This is quite ok, in the case of sqlite. Why is PHP in the middle at all?
4. google: Entity Framework Core sqlite
PS: I got the feeling that you have some kind of mess there, not an application.
If possible, describe the task in more detail in a comment.