Forum Xamarin.Forms
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.

Create Multiple Table with SQLite Database

SinghsumitSinghsumit INMember ✭✭

hello, xamarians,
I am creating a project and I want to need to more than 3 tables but I don't understand how to create
please help me out.

Posts

  • AlessandroCaliaroAlessandroCaliaro ITMember ✭✭✭✭✭
    If you have created 3 tables you can create the fourth
  • SinghsumitSinghsumit INMember ✭✭
    edited May 2018

    @AlessandroCaliaro
    I mean to say, how to use the database query for inserting, retrieving and joining table data, with using different tables.
    Sorry for my English.

  • MarcoTronconeMarcoTroncone GBUniversity ✭✭✭

    Hi @Singhsumit
    you can follow the tutorial I wrote about SqLite and Xamarin:
    http://www.xamarinexpert.it/2018/03/01/sqlite-made-easy/

    There you can see how to create a connection to the database, how to create a table and how to insert items in it.

    I hope it can help.
    If you have questions, feel free to ask.

  • amirvenusamirvenus USMember ✭✭✭

    This is the best plugin I found:

    http://www.xamarinexpert.it/2018/03/01/sqlite-made-easy/

    It does all the job for you and a db can be created/loaded with one line of code!

    It also supports LINQ queries so why would I use EntityFramework!?

    I highly recommend it and I do hope the author continues to maintain this lovely library.

  • gr8ozgr8oz Member ✭✭

    OK< I have read all the links. I followed the example in docs.microsoft.com/en-us/xamarin/xamarin-forms/app-fundamentals/databases to create my code but it only shows one table and my project requires multiple tables. None of other links answers this question.

  • ShantimohanElchuriShantimohanElchuri USMember ✭✭✭✭✭

    @gr8oz said:
    OK< I have read all the links. I followed the example in docs.microsoft.com/en-us/xamarin/xamarin-forms/app-fundamentals/databases to create my code but it only shows one table and my project requires multiple tables. None of other links answers this question.

    I suppose, the following code be repeated for the other tables by replacing the ToDoItem model with others.

                if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(TodoItem).Name))
                {
                    await Database.CreateTablesAsync(CreateFlags.None, typeof(TodoItem)).ConfigureAwait(false);
                    initialized = true;
                }
    
  • gr8ozgr8oz Member ✭✭

    I tried that and it might be working but I am having other issues seeing the results. Thank for reading my post and answering. I will press with debugging.

  • ShantimohanElchuriShantimohanElchuri USMember ✭✭✭✭✭

    @gr8oz said:
    I tried that and it might be working but I am having other issues seeing the results. Thank for reading my post and answering. I will press with debugging.

    Can you discuss your issue here?

  • gr8ozgr8oz Member ✭✭

    I am adapting the model code to fit my App but don't really understand what it is doing so I feel like I should spend more time trying to understand before posting my adaption with questions. BTW, your suggestion for multiple tables seems to work. Thanks

  • gr8ozgr8oz Member ✭✭

    I need your help. When I run the program, debug gives me the attached message.

    Here is the Members table.
    using SQLite;
    namespace NorthBayHLAA.ViewModel
    {
    public class Members
    {
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

        public string Email { get; set; } // Email address
        public string FirstName { get; set; } // First name
        public string LastName { get; set; } // Last Name}
        public int Rating { get; set; } // Member rating *** not used ***
        public bool CI { get; set; } // Cochlear Implant Yes/ No
        public string Notes { get; set; } // Notes about member
    }
    

    }

    Here is the db connection code

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using NorthBayHLAA.ViewModel;
    using SQLite;
    namespace NorthBayHLAA.Data
    {
    public class NorthBayDatabase
    {
    static readonly Lazy lazyInitializer = new Lazy(() =>
    {
    return new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
    });

        static SQLiteAsyncConnection Database => lazyInitializer.Value;
        static bool initialized = true;
    
        public NorthBayDatabase()
        {
            InitializeAsync().SafeFireAndForget(false);
        }
    
        async Task InitializeAsync()
        {
            if (!initialized)
            {
                if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(Members).Name))
                {
                    await Database.CreateTablesAsync(CreateFlags.None, typeof(Members)).ConfigureAwait(false);                
                }
                if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(Meetings).Name))
                {
                    await Database.CreateTablesAsync(CreateFlags.None, typeof(Meetings)).ConfigureAwait(false);
                }
                if (!Database.TableMappings.Any(m => m.MappedType.Name == typeof(Attendees).Name))
                {
                    await Database.CreateTablesAsync(CreateFlags.None, typeof(Attendees)).ConfigureAwait(false);
                }
                initialized = true;
            }
        }
    
        public Task<List<Members>> GetMembersAsync()
        {
    
            return Database.Table<Members>().ToListAsync();
        }
        public Task<ViewModel.Members> GetMembersAsync(int id)
        {
            return Database.Table<Members>().Where(i => i.ID == id).FirstOrDefaultAsync();
        }       
        public Task<int> SaveMemberAsync(Members item)
        {
            if (item.ID != 0)
            {
                return Database.UpdateAsync(item);
            }
            else
            {
                return Database.InsertAsync(item);
    
            }
        }
        public Task<List<Meetings>> GetMeetingsAsync()
        {
    
            return Database.Table<Meetings>().ToListAsync();
        }
        public Task<ViewModel.Meetings> GetMeetingsAsync(int id)
        {
            return Database.Table<Meetings>().Where(i => i.ID == id).FirstOrDefaultAsync();
        }
        public Task<int> SaveMeetingsAsync(Meetings item)
        {
            if (item.ID != 0)
            {
                return Database.UpdateAsync(item);
            }
            else
            {
                return Database.InsertAsync(item);
    
            }
        }
        public Task<List<Meetings>> GetAttendeesAsync()
        {
    
            return Database.Table<Meetings>().ToListAsync();
        }
        public Task<ViewModel.Attendees> GetAttendeesAsync(int id)
        {
            return Database.Table<Attendees>().Where(i => i.ID == id).FirstOrDefaultAsync();
        }
        public Task<int> SaveAttendeesAsync(Attendees item)
        {
            if (item.ID != 0)
            {
                return Database.UpdateAsync(item);
            }
            else
            {
                return Database.InsertAsync(item);
    
            }
        }
        /*
        public Task<int> DeleteItemAsync(ViewModel.Members item)
        {
            return Database.DeleteAsync(item);
        }
        */
    }
    

    }

Sign In or Register to comment.