locked
Create Multiple Table with SQLite Database RRS feed

  • Question

  • User355664 posted

    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.

    Wednesday, May 2, 2018 5:15 PM

All replies

  • User2148 posted

    If you have created 3 tables you can create the fourth

    Wednesday, May 2, 2018 5:37 PM
  • User355664 posted

    @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.

    Tuesday, May 15, 2018 7:06 AM
  • User246900 posted

    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.

    Tuesday, May 15, 2018 8:09 AM
  • User330867 posted

    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.

    Friday, January 4, 2019 2:26 PM
  • User371474 posted

    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.

    Friday, July 3, 2020 7:27 PM
  • User42522 posted

    @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;
                }
    
    Sunday, July 5, 2020 4:46 PM
  • User371474 posted

    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.

    Sunday, July 5, 2020 5:11 PM
  • User42522 posted

    @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?

    Sunday, July 5, 2020 11:49 PM
  • User371474 posted

    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

    Monday, July 6, 2020 12:57 AM
  • User371474 posted

    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);
        }
        */
    }
    

    }


    Monday, July 6, 2020 7:13 PM