MAUI: Issue with saving values to local DB table

Question

I have integrated the local DB using SQLite for saving the favorites contacts and all of its functionalities are working fine.

I follow this blog and its codes are added below:

SqlHelper

public class SqlHelper
{
    static object locker = new object();
    SQLiteConnection database;
    public SqlHelper()
    {
        database = GetConnection();
        database.CreateTable<Favorites>();
    }

    public SQLite.SQLiteConnection GetConnection()
    {
        SQLiteConnection sqlitConnection;
        var sqliteFilename = "FavoritesContact.db3";
        string path = Path.Combine(Microsoft.Maui.Storage.FileSystem.Current.AppDataDirectory, sqliteFilename);
        sqlitConnection = new SQLite.SQLiteConnection(path);
        return sqlitConnection;
    }
    
    public List<Favorites> GetAllItems()
    {
        lock (locker)
        {
            return database.Table<Favorites>().ToList();
            //return (from i in database.Table<Favorites>() select i).ToList();
        }
    }

    public int SaveItem(Favorites item)
    {
        lock (locker)
        {
            if (item.ID != 0)
            {
                //Update Item  
                database.Update(item);
                return item.ID;
            }
            else
            {
                //Insert item  
                return database.Insert(item);
            }
        }
    }
}

App.xaml.cs

public static SqlHelper Database
{
    get
    {
        if (database == null)
        {
            database = new SqlHelper();
        }
        return database;
    }
}

Now I need to add a new table for saving call history. I did like below:

Updated SqlHelper

public class SqlHelper
{
    static object locker = new object();
    SQLiteConnection database;
    SQLiteConnection calllogdatabase;
    public SqlHelper()
    {
        database = GetConnection();
        calllogdatabase = GetCallLogConnection();
        // create the tables  
        database.CreateTable<Favorites>();
        calllogdatabase.CreateTable<CallLog>();
    }

    public SQLite.SQLiteConnection GetConnection()
    {
        SQLiteConnection sqlitConnection;
        var sqliteFilename = "FavoritesContact.db3";
        string path = Path.Combine(Microsoft.Maui.Storage.FileSystem.Current.AppDataDirectory, sqliteFilename);
        sqlitConnection = new SQLite.SQLiteConnection(path);
        return sqlitConnection;
    }

    public SQLite.SQLiteConnection GetCallLogConnection()
    {
        SQLiteConnection sqlitConnection;
        var sqliteFilename = "allcalllog.db3";
        string path = Path.Combine(Microsoft.Maui.Storage.FileSystem.Current.AppDataDirectory, sqliteFilename);
        sqlitConnection = new SQLite.SQLiteConnection(path);
        return sqlitConnection;
    }
    
    public List<Favorites> GetAllItems()
    {
        lock (locker)
        {
            return database.Table<Favorites>().ToList();
            //return (from i in database.Table<Favorites>() select i).ToList();
        }
    }

    public int SaveItem(Favorites item)
    {
        lock (locker)
        {
            if (item.ID != 0)
            {
                //Update Item  
                database.Update(item);
                return item.ID;
            }
            else
            {
                //Insert item  
                return database.Insert(item);
            }
        }
    }

    //call log functions
    public int SaveCallLogItem(CallLog item)
    {
        lock (locker)
        {
            if (item.ID != 0)
            {
                //Update Item  
                calllogdatabase.Update(item);
                return item.ID;
            }
            else
            {
                //Insert item  
                return calllogdatabase.Insert(item);
            }
        }
    }

    public List<CallLog> GetAllCallLogs()
    {
        lock (locker)
        {
            return calllogdatabase.Table<CallLog>().ToList();
        }
    }
}

When I try to save 20 call logs only the last item is getting saved, but I am getting success response for all the database save part.

Saving Data to local DB:

CallLog callLog = new CallLog();
foreach (var item in AllCallCollection)
{
    CallLog CallLogItem = App.Database.GetItemBySId(item.sid);
    if (CallLogItem == null && item != null)
    {
        callLog.toFormatted = item.toFormatted;
        callLog.fromFormatted = item.fromFormatted;
        callLog.from = item.from;
        callLog.to = item.to;
        callLog.toName = item.toName;
        callLog.fromName = item.fromName;
        callLog.dateCreated = item.dateCreated;
        callLog.TimeSent = item.TimeSent;
        callLog.FromDetails = fromlabel;
        callLog.sid = item.sid;
        callLog.CallRecord = item.CallRecord;

        int i = App.Database.SaveCallLogItem(callLog);
        if (i > 0)
        {
            Debug.WriteLine("***success***");
        }
        else
        {
            Debug.WriteLine("Already Saved.");
        }
    }
}

CallLog

public class CallLog
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string toFormatted { get; set; }
    public string fromFormatted { get; set; }
    public string from { get; set; }
    public string to { get; set; }
    public string toName { get; set; }
    public string fromName { get; set; }
    public string dateCreated { get; set; }
    public string TimeSent { get; set; }
    public string FromDetails { get; set; }
    public string sid { get; set; }
    public string CallRecord { get; set; }
}

I suspect the SQLiteConnection implementation in SqlHelper has some logical issues. Is multiple tables implementation logic I have done has any kind of issues?

Update

I have created a demo project and upload it here for the reference.

I have added 3 items into a collection and added all these items from collection to callog and favorite local db using a for loop. After saving when I check the count of local db data, only 1 is showing for calllog and 3 is showing for favorite.

I need to save all data to local db.

Answer 1

Hello,

For multi-table operations in a database, you need to create multiple tables for a database, not for the tables.

I changed a small amount of code in your sample, and after testing, 3 callogs were successfully saved and read.

public SqlHelper()
{
    database = GetConnection();
    // create the tables  
    database.CreateTable<Favorites>();
    database.CreateTable<CallLog>();
}

public SQLite.SQLiteConnection GetConnection()
{
    SQLiteConnection sqlitConnection; 
    // Create a database to manage all the tables.
    var sqliteFilename = "FavoritesContact_allcalllog.db3";
    string path = Path.Combine(Microsoft.Maui.Storage.FileSystem.Current.AppDataDirectory, sqliteFilename);
    sqlitConnection = new SQLite.SQLiteConnection(path);
    return sqlitConnection;
}

Best Regards,

Alec Liu.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.