Entries:
Comments:
Posts:

Loading User Information from Channel 9

Something went wrong getting user information from Channel 9

Latest Achievement:

Loading User Information from MSDN

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Loading Visual Studio Achievements

Something went wrong getting the Visual Studio Achievements

Using SQLite in Windows Store Apps

Download

Right click “Save as…”

In this episode, Robert shows you how to add SQLite support to your Windows Store apps. SQLite is a free open source library that provides a self-contained transactional SQL database engine. Watch as Robert demonstrates how to setup a project to use SQLite as well as how to query, add, update,  and delete items in the database.

Check out Robert's blog post to see how to create the sample app he uses.

Tags:

Follow the Discussion

  • Jason CartwrightJason Cartwright

    Thanks for the video. I have just finished a new app (currently going through submission) called NewHouse. I used Sqlite for the data source. It's fantastic, especially when combined with RoamingStorage for cross-device apps.

  • H GH G

    Can we create portable class library that refer SQLITE and act as model (MVVM) for both Win8 & WP8 app?

  • How does this work with all async/await statements ? 

    I can see that you are opening/close db each time you take something from it and was wondering how this will work if we will use async/awaits and at some point two different function will try to manipulate on db. 

    kind give us your thoughts around that

    thanks

    M.

  • LexLex

    Since SQLite.dll is native dll, existing for x32/x64/arm, you have to make/test/publish 3 different versions of your app - for each platform.

    Welcome to native hell ;)

  • Can the SQLLite data be encrypted ?

  • @Lex: You will need to build 3 different app packages, one for each architecture and submit each of them to the Store. You don't need different code bases, unless you have architecture specific code, so you should be able to fully test one architecture and then smoke test the others.

    Robert

  • Araz kAraz k

    can I make an invoice to print and reports like pdf with SQLite and can I using a network Metro application with SQLite.
    Thansk

  • Good tool I use for SQLite http://sqliteexpert.com/

  • Excellent!  This is exactly what I was looking for.  Thanks Robert!

  • ALIZizigolo Check This Out ;)

    Thanks For This Video

  • Seriously guys, I kind of remember there is a Server and Tools Division inside Microsoft, which happens to have a product called SQL Server, and something like SQL Express, SQL Embeded, SQL Compact, SQL Whatever. Are they all dead?

  • Clay ShannonClay Shannon

    Wecarpool.com: That link (http://sqliteexpert.com/) says, "Supported platforms: Windows 2000, XP, Vista, 7." So it doesn't support Windows 8?

  • Neil GlastonburyNeil Glastonbury

    Best tool for SQLite:
    SQLite Administrator
    http://sqliteadmin.orbmu2k.de

    (Supported
    Win 2000
    Win XP
    Win Vista)

    Had ZERO issues using it with Windows 8.

  • I just uploaded the sample to the MSDN Code Samples. You will have to add the references and get the SQLite-net package, but you won't have to write any of the code.

  • CetiCeti

    Hi Guys, any idea why SQLite3.Open(path, out handle) method can not open any file out side ApplicationData.Current\...?
    I am trying to unittest my class library which uses sqlite but I want
    to keep the data for other tests. (the point is that the ApplicationData.Current will remove after each test because my library is not a user interface). and SQLite3.Open(path, out handle) can not open any file in DocumentsLibrary nor DownloadsFolder.

    Thanks in advance

  • @Clay Shannon: Windows 8 is Windows 7 + RT.

    It works on Desktop in Win8 no problem

  • Yeah, seriously, the presenter is writing an APP to manage his APPS.  Great.  That's a real world example of why someone would need a robust FULLY SUPPORTED RDBMS on a device lol.  Well Robert, here in the real world, where we have apps written that, as an example, would do the following.

    1.) Route sheet with over 50 calls per day/per driver handling sales for a major international Energy drink distributor.  That's 70 routes x 50 sales calls/invoices per day.

    2.) Sales history for each customer (over 1000 customers per device) for the last 24 months that can be recalled and printed while at the store dealing with the customer

    3.) New invoices from the route created that day replicated in real time to the SQL Server at head office

    4.) New route sheet for the next day (and 14 day peak into the future for driver planning)

    5.) Multiple "Surveys" regarding shelving and presentation for every store on the route.  Data is collected while the driver is at the store and immediately replicated to head office for analysis

    6.) Real time "chatting" with the drivers with entire dialogue stored on the LOCAL SQL CE DATABASE.

    blah, what a disaster.  Try the above with SQL Lite.

    If I/We were in the business of writing 99 cent apps, we would be coding for android or IOS.  We chose MS because we code APPs for businesses, not 18 year old college students.

    I am inspired... to code for the android.  Unfortunately, I agreed with the client, and moved them over to an iPhone web based app as I saw (and now confirmed) no use for MS in the device market place.

    what a gong show

  • MustansirMustansir

    Hello Robert i'm new to application development this was really nice explanation of sqlite, i got the answers of many doubts i was looking for, however in the MSDN Code Samples provided when i compiled it gave two errors

    Error 1 The name 'ex' does not exist in the current context C:\Users\Downloads\New folder\Using SQLite in a Windows Store App\C#\SQLiteDemo\SQLite.cs 722 19 SQLiteDemo

    Error 2 The name 'ex' does not exist in the current context C:\Users\Downloads\New folder\Using SQLite in a Windows Store App\C#\SQLiteDemo\SQLite.cs 767 18 SQLiteDemo



    is there something that i missed ??

  • @Mustansir: I just uploaded a new version of this. Download that and follow the Building the Sample instructions. Let me know if that doesn't work.

    Robert

  • DevendraDevendra

    When i try run your Simple SqLite demo application it is getting an exception as displaying 'System.DllNotFoundException' occured, & Unable to load DLL 'sqlite3'
    What is Team foundation server, Why i have to integrate with it. When i start your sample app it is asking for Team Foundation server Version control "https://microsoftdpe.tfspreview.com/defaultcollection".
    Please help me, Thanks in advance.

  • @Devendra: Did you follow the Building the Sample instructions? You need to download the SQLite for Windows Runtime extension for Visual Studio.

    And you can safely ignore the prompt for Team Foundation Server.

    Robert

  • Hi Robert,

    Thank you for your tutorial...

    i am a beginer and i am interested in the development of Winsdows Stores application with MVVM-pattern. I have download your SQLiteDemo and wanted to tested but there is not "start-app". Which template may i select (empty store template)?.

    I want to develope some store-application with my own page like login-page.

    Thank,

    Jibyz

  • @jibyz: You can look at my blog post, where I show how to build this app from scratch. You can also look at Create your first Windows Store app walkthroughs on MSDN. They have them for XAML and for JavaScript apps.

    Robert

  • Hi Robert,

    i have retried, but i don't receive the information: Picture5

    and the there was no file added into the Common folder. I just have the StandardStyle.xaml in this folder.

    Maybe i do something wrong... please can you help me? I have your tutorial step by step...

    Thanks,

    Jibyz

  • @jibyz: If you add a new XAML page using the Basic Page item template, you should see that dialog. If you add a new XAML page using the Blank Page item template, you won't see that. So make sure you use the Basic Page template. And let me know if that doesn't work.

    Robert

  • Antoine GamondAntoine Gamond

    @rogreen How can I make relationship?

    I used to do that with the entity framework :
    public virtual ICollection<KCExample> examples { get; set; }
    public virtual KCTrade trade { get; set; }

    But it seems to be unsupported... What's the right syntax?

  • Silvio Ribeirosilvioribei​ro Silvio Ribeiro

    Hi Robert, great video tutorial.

    Is it possible to create a Windows Store App using SQLite in Visual Basic? I'm having difficulties with the "Imports.SQLite" statement.

    Thanks

  • @silvioribeiro:Hi Silvio.

    Bill Burrows has an example of using SQLite with VB. He has a VB front end that talks to the C# code in the SQLite-net NuGet package. Looks pretty straightforward.

    Robert

  • Silvio Ribeirosilvioribei​ro Silvio Ribeiro

    @rogreen that's exactly what I needed to get started.

    I'm definitely heading in the right direction now.

    Thank you for the link and for the great tutorial! 

  • Thanks for the video Robert!

    Is it a good idea to instantiate a new SQLiteConnection each time your interact with the database or could I re-use a single connection over and over again?

    Does anyone have a best-practice? Thanks.

  • Thanks Robert, it is just what I need for my Windows 8 project.

    Others had told me to us XML and I was banging my head against the wall. This will let me do exactly what I need for my app.

    Thanks again Big Smile

    Dave

  • Thanks for the video Robert!

    Can we create portable class library that refer SQLITE and act as model (MVVM) for both Win8 & WP8 app?

  • http://msdn.microsoft.com/en-us/magazine/hh335063.aspx

    Perfect!

  • Luke LucasLuke Lucas

    Thank you for this tutorial...

    Can someone please tell me from where I can locate the database on my Disk so I can modify it using Mozilla Fire Fox?

    Thanks

  • job mwanikijob mwaniki

    Thank you for the video I learnt a lot,
    can I get the source code for the app?

  • @job mwaniki: You can find the sample at http://code.msdn.microsoft.com/Using-SQLite-in-a-Windows-c16aea77.

    Let me know if you have any questions or comments.

    Robert

  • PodsterPodster

    Thanks again for the project. Is there a way to automatically sync between devices through cloud and SQLite? What if one device adds data while no internet (offline sync) is available how would it sync back to other devices once internet connection is restored...

  • @Podster: There are some people who have updated the open source Microsoft Sync Framework to work with SQLite on Windows 8. That would be a good place to start. Two good examples are:

    Robert

  • Joabe TavaresJoabe Tavares

    Why when I change the data and run the app again the data saved are not saved?

  • @Joabe Tavares: Check in the OnLaunched method in the App.xaml.cs file. If there is a call to ResetData, comment it out. ResetData populates the tables the first time you run the app. But if you leave it in there, it will do this every time.

    Robert

     

  • Hi Robert, Please can you help. Still on SQLite with Windows Phone. I have found a solution to the type or namespace error with Community

    However, There are few issues with SQLite and Windows Phone. Please are you able to do a show on Using SQLite with Windows Phone 8. It will be much appreciated. Thanks

  • how i can protect my sqlite file from getting stolen?

  • GhadaGhada

    Hi,
    Thanks for the video.
    After you permission, What about windows 8.1 and Visual Studio 2013?

  • @Ghada: There is a Windows 8.1 version of the SQLite for Windows Runtime extension for use with Visual Studio 2013. Check out http://visualstudiogallery.msdn.microsoft.com/1d04f82f-2fe9-4727-a2f9-a2db127ddc9a.

    Robert

    X

  • GhadaGhada

    @Robert Thank you very much.
    Please, how I can get the microsoft visual c++ runtime package for visual studio 2013?

  • @Ghada:You need the C++ 2013 Runtime Package (version 12), which targets .NET Framework 4.5.1. The C++ Runtime Package (version 11) targets .NET Framework 4.5 and is greyed out when you are on Windows 8.1.

  • GhadaGhada

    Thanks again Robert

    I am very confused.
    My app required (cloud not local) database that contains images and tables.........,to let people see the updated images and data in the database and enabling them to insert their data.

    My Question:
    Is SQLite can help me?

  • @Ghada: As we discussed offline, you should look into Azure Mobile Services for this scenario. Check out https://www.windowsazure.com/en-us/develop/mobile/tutorials/get-started/

    Robert

  • JoeJoe

    Robert, first thanks for this! I am changing a program from a WPF desktop app to a Windows 8.1 app and I have been killing myself with Data since my previous app used a relation database (SQLCE). My question to you is this (and this is my first time using SQLite): Where does the app store the data, I know it is in local storage, but I noticed when I first start up the app, there is already data populated in it. So what is populating the data? I didn't see anything in the code creating the database. The reason I am asking is I want to script my SQLCe database (which i did through SQLite command line) so I have data in the database I can manipulate and test with. Thanks!

  • @Joe: In the OnLaunched method in App.xaml.cs, you will find a call to ResetData. This populates the database with the sample data.

    Robert

  • JoeJoe

    @Rogreen Thanks for pointing that out! Now its time to play with this and see if I can get it to work!

  • GhadaGhada

    According to
    @Ghada: As we discussed offline, you should look into Azure Mobile Services for this scenario. Check out https://www.windowsazure.com/en-us/develop/mobile/tutorials/get-started/.

    Thank you so much.

Remove this comment

Remove this thread

close

Comments Closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.