locked
ListView with SQLite as the data provider RRS feed

  • Question

  • I'm looking for an example how to use the listview together with an SQLite database as the data provider.
    Monday, September 24, 2012 8:47 PM

Answers

  • Many thanks to all of you helping me. I have now a working example and I like to share it. The datasource API from github has been taken and it works perfect. Following are the two files:

    contact.html

    <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <meta charset="utf-8" /> <title>contact</title> <!-- WinJS-Verweise --> <link href="//Microsoft.WinJS.1.0/css/ui-light.css" rel="stylesheet" /> <script src="//Microsoft.WinJS.1.0/js/base.js"></script> <script src="//Microsoft.WinJS.1.0/js/ui.js"></script> <link href="contact.css" rel="stylesheet" /> <script src="contact.js"></script> <script src="/js/SQLite3.js"></script>

    </head> <body> <div class="contact fragment"> <header aria-label="Header content" role="banner"> <button class="win-backbutton" aria-label="Back" disabled type="button"></button> <h1 class="titlearea win-type-ellipsis"> <span data-win-res="{innerText: 'ContactTitle'}"></span> </h1> </header> <section aria-label="Main content" role="main"> <div id="imageTextCollectionTemplate" data-win-control="WinJS.Binding.Template"> <div class="imageTextCollection"> <img class="imageTextCollectionImage" data-win-bind="src: imagename" /> <div class="imageTextCollectionDetail"> <h2><span class="imageTextCollectionTextStrong" data-win-bind="innerText: namecomp"></span> <span class="imageTextCollectionTextStrong" data-win-bind="innerText: surname"></span> </h2> <div> <span class="imageTextCollectionText" data-win-bind="innerText: postcode"></span> <span class="imageTextCollectionText" data-win-bind="innerText: place"></span> </div> </div> </div> </div> <div id="contactListView" data-win-control="WinJS.UI.ListView" data-win-options="{ itemDataSource : proIT.contact.contactDataSource, itemTemplate: select('#imageTextCollectionTemplate'), layout: { type: WinJS.UI.GridLayout } }"> </div> </section> </div> </body> </html>

    contact.js

    (function () {
        "use strict";
    
        var WinRes = WinJS.Resources;
    
        // fields
    
        var _db = null;
        var contactDataSource = null;
    
        // Methods
    
        var _openDB = function() {
            /// <summary> open the database and get itemDataSource </summary>
            if (_db == null) {
                //open database first
                var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + "\\" + WinRes.getString("dbFileName").value;
                SQLite3JS.openAsync(dbPath)
                    .then(function (myDB) {
                        console.log('DB opened');
                        _db = myDB;
    
                        WinJS.UI.processAll().done(function () {
                            var listview = document.getElementById("contactListView").winControl;
                            
                            // create the data source
                            contactDataSource = _db.itemDataSource("SELECT * FROM perscomp", "perscompid");
    
                            // set the properties on the list view to use itemDataSource
                            listview.itemDataSource = contactDataSource;
                        });
                    });
            }
        }
    
        var onReady = function (element, options) {
            WinRes.processAll();
            _openDB();
        };
    
        var onUnload = function () {
            // TODO: Auf Navigationen weg von dieser Seite reagieren.
            _db.close();
            _db = null;
        }
    
        var onUpdateLayout = function (element, viewState, lastViewState) {
            // TODO: Auf Änderungen in viewState reagieren.
        }        
    
        WinJS.UI.Pages.define("/pages/contact/contact.html", {
            // Diese Funktion wird immer aufgerufen, wenn ein Benutzer zu dieser Seite wechselt. Sie
            // füllt die Seitenelemente mit den Daten der App auf.
            ready: onReady,
            unload: onUnload,
            updateLayout: onUpdateLayout
        });
    
        WinJS.Namespace.define("proIT.contact", {
            contactDataSource : contactDataSource
        });
                
    })();


    Sunday, October 7, 2012 5:14 PM

All replies

  • Hi Bruno,

    I am not aware of any.  You should be able to look at the data samples and quick starts for DataSource and Virtualized datasource however to get the feel of how to do this.

    Has anyone else on this forum done this?  Perhaps you could share with Bruno!

    -Jeff


    Jeff Sanders (MSFT)

    Tuesday, September 25, 2012 12:35 PM
    Moderator
  • We've done this, although not sure it is something I can explain in a few paragraphs.  But here are the basics.

    We found it easier to write our business layer in C# and then consume from javascript via WinRT projections.

    We used this package to provide a lot of the plumbing and give us a LINQ interface to SQLite. https://github.com/praeclarum/sqlite-net

    We created basic .NET objects corresponding to tables and views and then wrote a basic data layer to do the CRUD work against SQLite.

    We create C# data models that are appropriate for how the application is consuming the data and fill them from the data layer.

    We expose the data via an interface in a C# WinRT component as IAsyncOperation<IList<YourModelName>> GetYourModelAsync();

    You can then call the data from javascript.  We then put the data into local javascript view models inside a collection and bind that to a ListView.

    There are probably simpler ways of doing this, but we have a fairly large application and needed a fairly decoupled robust architecture.  I'm only hitting the high points and our various layers do a lot of other stuff, but maybe that will get you started.

    Tuesday, September 25, 2012 1:22 PM
  • Yes, we have done it over the last couple of months. Although, the datasource API still has limitations. Check out https://github.com/doo/SQLite3-WinRT

    It does not support transactions yet.

    Thursday, September 27, 2012 10:13 PM
  • I just put a solution template up in the gallery that shows a simplified version of the architecture we use.  You can find it in the Visual Studio 2012 online gallery by searching for SQLite or here is the direct link:

    http://visualstudiogallery.msdn.microsoft.com/ea310b3b-1ed2-424c-b6d6-40a9e30726f1

    Thursday, October 4, 2012 5:16 PM
  • Many thanks to all of you helping me. I have now a working example and I like to share it. The datasource API from github has been taken and it works perfect. Following are the two files:

    contact.html

    <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <meta charset="utf-8" /> <title>contact</title> <!-- WinJS-Verweise --> <link href="//Microsoft.WinJS.1.0/css/ui-light.css" rel="stylesheet" /> <script src="//Microsoft.WinJS.1.0/js/base.js"></script> <script src="//Microsoft.WinJS.1.0/js/ui.js"></script> <link href="contact.css" rel="stylesheet" /> <script src="contact.js"></script> <script src="/js/SQLite3.js"></script>

    </head> <body> <div class="contact fragment"> <header aria-label="Header content" role="banner"> <button class="win-backbutton" aria-label="Back" disabled type="button"></button> <h1 class="titlearea win-type-ellipsis"> <span data-win-res="{innerText: 'ContactTitle'}"></span> </h1> </header> <section aria-label="Main content" role="main"> <div id="imageTextCollectionTemplate" data-win-control="WinJS.Binding.Template"> <div class="imageTextCollection"> <img class="imageTextCollectionImage" data-win-bind="src: imagename" /> <div class="imageTextCollectionDetail"> <h2><span class="imageTextCollectionTextStrong" data-win-bind="innerText: namecomp"></span> <span class="imageTextCollectionTextStrong" data-win-bind="innerText: surname"></span> </h2> <div> <span class="imageTextCollectionText" data-win-bind="innerText: postcode"></span> <span class="imageTextCollectionText" data-win-bind="innerText: place"></span> </div> </div> </div> </div> <div id="contactListView" data-win-control="WinJS.UI.ListView" data-win-options="{ itemDataSource : proIT.contact.contactDataSource, itemTemplate: select('#imageTextCollectionTemplate'), layout: { type: WinJS.UI.GridLayout } }"> </div> </section> </div> </body> </html>

    contact.js

    (function () {
        "use strict";
    
        var WinRes = WinJS.Resources;
    
        // fields
    
        var _db = null;
        var contactDataSource = null;
    
        // Methods
    
        var _openDB = function() {
            /// <summary> open the database and get itemDataSource </summary>
            if (_db == null) {
                //open database first
                var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + "\\" + WinRes.getString("dbFileName").value;
                SQLite3JS.openAsync(dbPath)
                    .then(function (myDB) {
                        console.log('DB opened');
                        _db = myDB;
    
                        WinJS.UI.processAll().done(function () {
                            var listview = document.getElementById("contactListView").winControl;
                            
                            // create the data source
                            contactDataSource = _db.itemDataSource("SELECT * FROM perscomp", "perscompid");
    
                            // set the properties on the list view to use itemDataSource
                            listview.itemDataSource = contactDataSource;
                        });
                    });
            }
        }
    
        var onReady = function (element, options) {
            WinRes.processAll();
            _openDB();
        };
    
        var onUnload = function () {
            // TODO: Auf Navigationen weg von dieser Seite reagieren.
            _db.close();
            _db = null;
        }
    
        var onUpdateLayout = function (element, viewState, lastViewState) {
            // TODO: Auf Änderungen in viewState reagieren.
        }        
    
        WinJS.UI.Pages.define("/pages/contact/contact.html", {
            // Diese Funktion wird immer aufgerufen, wenn ein Benutzer zu dieser Seite wechselt. Sie
            // füllt die Seitenelemente mit den Daten der App auf.
            ready: onReady,
            unload: onUnload,
            updateLayout: onUpdateLayout
        });
    
        WinJS.Namespace.define("proIT.contact", {
            contactDataSource : contactDataSource
        });
                
    })();


    Sunday, October 7, 2012 5:14 PM