locked
How to show data from database sqlite in grid windows store RRS feed

  • Question

  • hello

    I am developing a windows app store, and I am not able to display data in a grid view, so with a listview:

    example:  People.ItemsSource = db.Table<Person>().Take(3);

    thanks for helping me

    Tuesday, October 1, 2013 3:43 PM

Answers

  • Hi Mota ,

    Remove the where clause from you sql statement and it will bring you everything.

    Change it to something like this

    "select * from Person"

    And then to get access

    foreach(Person Item in Mylist) //you don't need mylist2

    {

    //your code

    Item.FristName  .......bla bla

    }

    Don't forget to mark me as answered and  vote for me.

    thank you


    • Edited by zakkar Friday, October 4, 2013 4:58 AM
    • Marked as answer by Aurelien Mota Friday, October 4, 2013 10:06 AM
    Friday, October 4, 2013 4:58 AM

All replies

  • Hello Aurelien Mota,

    Try to get all the detail using sqliteasyncconnection for connect database and queryasync for execute query. Than store this all data in collection (ObservableCollection object). Than bind to your listview or gridview.

    Tuesday, October 1, 2013 4:35 PM
  • Hi ,

    Take a look at here

    http://social.msdn.microsoft.com/Forums/windowsapps/en-US/72025262-9351-4215-9a5a-46f82c5f630b/windows-store-apps-storing-datetimenow-into-sqlite-database

    Don't mind that it is talking about the datetime. The concept is the same

    Also see here . 

    http://social.msdn.microsoft.com/Forums/windowsapps/en-US/254c2d6b-e2ff-4474-9732-23f2cbb6fea1/store-blob-in-sqlite-database

    When you query your Sqlite db it returns a list of your class. This list you are assigning in the listview

    this.MyListiview.ItemsSource = Mylist //you get it from the querry in the db.

    string sql_statement="";

    sql_statement = "Select * from tbtodolist  where   Datetime(DateCreated) >= '1953-09-16 12:00:00' "

     var db = new SQLite.SQLiteConnection(this.dbpath);

    db.RunInTransaction(() =>
                    {
                       
                        MyList= db.Query<Myapp.App.tbtodolist>(sql_statement);


                    });
                    db.Dispose();

    this.MyListiview.ItemsSource = Mylist ;

    the dbpath is a string and it's the path of your database.

     private string m_dbpath;

       public string dbpath
            {
                get { return m_dbpath; }
                set { m_dbpath = value; }

            }

     this.dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydatabase.db");

    thank you

    Wednesday, October 2, 2013 5:14 AM
  • thank for this, but i can an error here,

    MylistView= db.Query<Sqlite2.App.Person>(sql_Statement);

    And my table is this:

    public class Person
            {
                [AutoIncrement, PrimaryKey]
                public int ID { get; set; }

                public string FristName { get; set; }
                public string LastName { get; set; }


                public override string ToString()
                {
                    return string.Format("{0} {1} {2}", ID, FristName, LastName);
                }
            }

    and this part: "this.MyListiview.ItemsSource = Mylist"  mylist is my listview? and Mylistview?

    thank you 


    Wednesday, October 2, 2013 5:26 PM
  • Hi ,

    You have in your XAML a Listview where inside it has a data template.

    In there you are binding your class

    For example

    In the page resources 

     <Page.Resources>
            <CollectionViewSource
                x:Name="itemsViewSource"
                Source="{Binding Items}"/>
            <!-- TODO: Delete this line if the key AppName is declared in App.xaml -->
            <x:String x:Key="AppName">Sales Per Customer</x:String>
        </Page.Resources>

                                                                                                                                                           

      <ListView
              x:Name="itemListView"
                AutomationProperties.AutomationId="ItemsListView"
                AutomationProperties.Name="Items"
                TabIndex="1"
                Visibility="Collapsed"
                Margin="0,130,0,0"
                Padding="10,0,0,60"

             
                ItemsSource="{Binding Source={StaticResource itemsViewSource}}" Grid.ColumnSpan="3" Grid.RowSpan="2">
                <ListView.ItemsPanel>
                    <ItemsPanelTemplate>
                        <WrapGrid Orientation="Horizontal" MaximumRowsOrColumns="5">
                        </WrapGrid>
                    </ItemsPanelTemplate>
                </ListView.ItemsPanel>

                <ListView.ItemTemplate >
                    <DataTemplate >
                        <Grid   Margin="6" x:Name="DatagridEmployeesTemplate" >
                            <Grid.ColumnDefinitions>
                                       <ColumnDefinition Width="Auto"/>
                                
                            </Grid.ColumnDefinitions>
                            <Grid.RowDefinitions>
                                <RowDefinition Height="140"/>
                            </Grid.RowDefinitions>

                          
                            <StackPanel Grid.Column="1" VerticalAlignment="Top" Margin="10,0,0,0" Width="170" >
                                <TextBlock Text="{Binding FirstName}" Style="{StaticResource TitleTextStyle}" TextWrapping="NoWrap"/>
                                <TextBlock Text="{Binding LastName}" Style="{StaticResource CaptionTextStyle}" TextWrapping="NoWrap"/>
                              
                            </StackPanel>

                          
                        </Grid>
                    </DataTemplate>
                </ListView.ItemTemplate>
            </ListView>

    and now in your code

    this.itemListView.ItemsSource = //whatever you bring as a list from your querry   .

    In your case now

    List<Person> MyList = New List<Person>();

    I suppose the person class is a table in your sqlite db and also you know how to create the sqlite db and tables so let's not focus in there.

     var db = new SQLite.SQLiteConnection(this.dbpath);

    db.RunInTransaction(() =>
                    {
                       
                        MyList= db.Query<Person>("Select * from Person");


                    });
                    db.Dispose();

    db=null;

    If (MyList.Count > 0 )

    {

    this.itemListView.ItemsSource = MyList;

    }

    In order for your binding to work you must be careful in the binding section of the XAML.

    What I mean is that if you have in your class this FristName 

    this is what you are suppose todo in your XAML

     <TextBlock Text="{Binding FristName }" Style="{StaticResource TitleTextStyle}" TextWrapping="NoWrap"/>

    It is case senstive.

    So change it if you like in the XAML above if that's the case. Although I believe that it's a typo.

    Do you know how to connect in sqlite and add it in your solution ? Because if you don't you must read the links and search for sqlite connections , help etc...

    Hope that gave you a start.

    thank you


    • Edited by zakkar Thursday, October 3, 2013 5:58 AM
    Thursday, October 3, 2013 5:57 AM
  • thanks zakkar, 


    Thursday, October 3, 2013 5:40 PM
  • thanks

    it's work, but so also show one data and gridview. how to show more gridview?

    i try this:

          List<Person> Mylist = new List<Person>();
                List<Person> Mylist2 = new List<Person>();
                Mylist = db.Query<Person>("Select FristName From Person where ID=1 ");
                Mylist2 = db.Query<Person>("Select LastName From Person where ID=3 ");

       this.DataGrid1.ItemsSource = Mylist;
               this.DataGrid1.ItemsSource = Mylist2;

    thanks for the response

    Thursday, October 3, 2013 10:02 PM
  • Hi Mota ,

    Remove the where clause from you sql statement and it will bring you everything.

    Change it to something like this

    "select * from Person"

    And then to get access

    foreach(Person Item in Mylist) //you don't need mylist2

    {

    //your code

    Item.FristName  .......bla bla

    }

    Don't forget to mark me as answered and  vote for me.

    thank you


    • Edited by zakkar Friday, October 4, 2013 4:58 AM
    • Marked as answer by Aurelien Mota Friday, October 4, 2013 10:06 AM
    Friday, October 4, 2013 4:58 AM