locked
Sql Lite Dispaly All item RRS feed

  • Question

  • How to Display All Data from sql. I want to display All data to DispalyAllPage Listbox . i am close the app rerun then previous data not showing . 

    public class MyModel
        {
            [PrimaryKey]
            public String Name { get; set; }
    
            public int Age { get; set; }
    
            public String LastName { get; set; }
        }


    public class MyViewModel : ViewModelBase
        {
            private ObservableCollection<MyModel> mymodelList;
            public ObservableCollection<MyModel> MymodelList
            {
                get
                {
                    return mymodelList;
                }
                set
                {
                    mymodelList = value;
                    RaisePropertyChanged("MymodelList");
                }
            }
    
    
            private MyModel addtoMyModel;
            public MyModel AddtoMyModel
            {
                get
                {
                    return addtoMyModel;
                }
                set
                {
                    addtoMyModel = value;
                    RaisePropertyChanged("AddtoMyModel");
                }
            }
    
            private MyModel selectedModel;
            public MyModel SelectedModel
            {
                get
                {
                    return selectedModel;
                }
                set
                {
                    selectedModel = value;
                    RaisePropertyChanged("SelectedModel");
                }
            }
            private async void createTable()
            {
                try
                {
                    var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
                    using (var db = new SQLite.SQLiteConnection(dbpath))
                    {
                        // Create the tables if they don't exist
                        db.CreateTable<MyModel>();
                        db.Commit();
    
                        db.Dispose();
                        db.Close();
                    }
                    var line = new MessageDialog("Table Created");
                    await line.ShowAsync();
                }
                catch
                {
    
                }
            }
    
            #region Adding Data
            private async void AddMyModel()
            {
                var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
                using (var db = new SQLite.SQLiteConnection(dbpath))
                {
                    // Create the tables if they don't exist
    
                    var newMyModel = new MyModel()
                    {
                       Age = AddtoMyModel.Age,
                       Name = AddtoMyModel.Name,
                       LastName = AddtoMyModel.LastName,
                    };
                    db.Insert(newMyModel);
                    MymodelList.Add(newMyModel);
                    db.Commit();
                    db.Dispose();
                    db.Close();
                    var line = new MessageDialog("Records Inserted");
                    await line.ShowAsync();
                }
            }
            public RelayCommand AddMyModelCommand { get; set; }
    
            private static MyViewModel _myViewModel;
    
            #endregion
    
            #region Delete Selected Item
            private void Delete()
            {
                if (SelectedModel != null)
                {
                    MymodelList.Remove(SelectedModel);
                }
            }
            public RelayCommand DeleteCommand { get; set; }
            #endregion
    
            #region Delete All Data
            private async void DeleteAll()
            {
                var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
                using (var db = new SQLite.SQLiteConnection(dbpath))
                {
                    db.DeleteAll<MyModel>();
    
                }
                var line = new MessageDialog("All Records Deleted");
                await line.ShowAsync();
                MymodelList.Clear();
            }
            public RelayCommand ClearAllCommand { get; set; }
            #endregion
    
            #region Navigate To Edit Page
            private void Edit()
            {
                if (SelectedModel != null)
                {
                    var rootFrame = Window.Current.Content as Frame;
                    rootFrame.Navigate(typeof(EditPage2));
    
                }
            }
            public RelayCommand EditCommand { get; set; }
            #endregion
    
            #region Selected Item Edit And Update
            private void Update()
            {
                var rootFrame = Window.Current.Content as Frame;
                rootFrame.Navigate(typeof(MainPage));
            }
            public RelayCommand UpdateCommand { get; set; }
            #endregion
    
            #region Display All
            private async void displayAll()
            {
                var rootFrame = Window.Current.Content as Frame;
                rootFrame.Navigate(typeof(DispalyallPage));
    
                var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
                using (var db = new SQLite.SQLiteConnection(dbpath))
                {
                    var d = from x in db.Table<MyModel>() select x;
                    
                    foreach (var sd in d)
                    {
    
                    }
                    db.Dispose();
                    db.Close();
                }
                var line = new MessageDialog("All Records Listed");
                await line.ShowAsync();
            }
            public RelayCommand DisplayAllCommand { get; set; }
            #endregion
    
            #region MAinPAge
            private async void gotoMainPage()
            {
                var rootFrame = Window.Current.Content as Frame;
                rootFrame.Navigate(typeof(MainPage));
                MessageDialog msg = new MessageDialog("Main Page");
                await msg.ShowAsync();
            }
    
            public RelayCommand GotoMainPAgeCommand { get; set; }
            #endregion
            #region Constructs
            public MyViewModel()
            {
                AddtoMyModel = new MyModel();
                SelectedModel = new MyModel();
                MymodelList = new ObservableCollection<MyModel>();
                AddMyModelCommand = new RelayCommand(AddMyModel);
                ClearAllCommand = new RelayCommand(DeleteAll);
                DeleteCommand = new RelayCommand(Delete);
                EditCommand = new RelayCommand(Edit);
                UpdateCommand = new RelayCommand(Update);
                DisplayAllCommand = new RelayCommand(displayAll);
                GotoMainPAgeCommand = new RelayCommand(gotoMainPage);
            }
            #endregion
    
            public static MyViewModel GetInstance()
            {
                return _myViewModel ?? (_myViewModel = new MyViewModel());
            }
    
    
        }

    Code On OneDrive Sql

     tell me how add Id variable on MyModel(Table) and how to Auto Increment

     #region Display All
            private async void displayAll()
            {
                var rootFrame = Window.Current.Content as Frame;
                rootFrame.Navigate(typeof(DispalyallPage));
    
                var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");
                using (var db = new SQLite.SQLiteConnection(dbpath))
                {
                    var d = from x in db.Table<MyModel>() select x;
                    
                    foreach (var sd in d)
                    {
    //// code Please
                    }
                    db.Dispose();
                    db.Close();
                }
                var line = new MessageDialog("All Records Listed");
                await line.ShowAsync();
            }
            public RelayCommand DisplayAllCommand { get; set; }
            #endregion


    Code Please All item Display  on

    DispalyallPage Listview .

    I want to display All data to DispalyAllPage Listbox . i am close the app rerun then previous data not showing .  How to Show that

    Click on Display all  Button then Navigate to Display all page list view show all data from MyModel Table

    • Edited by luna_luna Friday, June 6, 2014 11:23 AM missed
    Friday, June 6, 2014 9:04 AM

Answers

  • See webpage below.  The autominmcrement is normally done in the database application and not in the VS code.  With SQL Lite you have to specify the column that is being used for the auto increment when the database table is created.  Normally with databases user unique ID Variable are done in a procedure in the database and then VS called the procedure in the database to returns the custom AutoIncrement ID.

    http://www.sqlite.org/autoinc.html


    jdweng

    • Marked as answer by luna_luna Wednesday, July 2, 2014 3:14 AM
    Friday, June 6, 2014 9:46 AM