How do I handle data in a Windows 10 UWP app
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 133 | |
| Author | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/48802 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
00:00
Software developerData storage deviceMobile WebMobile appInternet service providerSoftware frameworkPlastikkarteProtein foldingComputer filePhysical systemOpen setVideoconferencingDirected setLimit (category theory)Local ringDemosceneMultiplication signRevision controlSystem callPasswordQuicksortData dictionaryAreaOrder (biology)Software developerBuildingMobile WebWindowDatabaseContent (media)VideoconferencingMusical ensembleCuboidComplex (psychology)MereologyInstallation artDemo (music)BitGastropod shellSlide ruleEncryptionPlastikkarteCharacteristic polynomialComputer filePhysical systemMobile appSoftwareGoodness of fitInternet service providerOpen setCompact spacePoint cloudCartesian coordinate systemData storage deviceApplication service providerFront and back endsSynchronizationSet (mathematics)Web pageClient (computing)Context awarenessValidity (statistics)Computing platformLocal ringNormal (geometry)Mechanism designFile systemReading (process)Filesharing-SystemSuite (music)Object (grammar)Software frameworkExtension (kinesiology)Group actionString (computer science)Address spaceLatent heat1 (number)Uniform resource locatorLaptopPerspective (visual)Intelligent NetworkVirtual machineSequelView (database)Graph coloringRight anglePrandtl numberLetterpress printingDirection (geometry)Dependent and independent variablesWave packetCausalityDiscrete element methodSurfaceServer (computing)Directed graphDirectory serviceJSONXMLUMLComputer animation
09:21
Data storage deviceLimit (category theory)Local ringSoftware developerProtein foldingShared memoryMobile appHill differential equationData typeElectric currentPoint cloudInstallation artSuite (music)Physical systemDatabase transactionACIDCrash (computing)DatabaseMiniDiscView (database)Table (information)Computing platformAndroid (robot)DatabaseComputer fileSerial portTheory of relativityString (computer science)Key (cryptography)WindowSet (mathematics)Complex (psychology)Message passingPhysical systemMobile WebObject (grammar)Database transactionAttribute grammarComputing platformTable (information)Row (database)QuicksortDemo (music)Different (Kate Ryan album)NumberSlide ruleMultiplication signProjective planeCustomer relationship managementGroup actionCodeLine (geometry)Data storage deviceSemiconductor memoryControl flowStatement (computer science)Interior (topology)Cross-platformOffice suiteType theorySoftware developerSynchronizationPoint (geometry)Software frameworkPasswordMechanism designMobile appRule of inferencePoint cloudLimit (category theory)CASE <Informatik>Default (computer science)LogicSource codeView (database)Standard deviationMereologyCharacteristic polynomialIdentity managementGoodness of fitException handlingOpen sourceRelational databaseAndroid (robot)ACIDWater vaporCivil engineeringLevel (video gaming)Video gameSequelDirected graphInformation securitySystem callRight angleFamilyPearson product-moment correlation coefficientSocial classHypermediaInjektivitätArithmetic meanComputer animation
18:34
CodeBuildingWindows SDKSoftware development kitComputing platformLibrary (computing)EmailConfiguration spaceLink (knot theory)outputLinker (computing)Euclidean vectorWrapper (data mining)Extension (kinesiology)Visual systemInstallation art.NET FrameworkSoftware developerStatement (computer science)Object-relational mappingAxiom of choiceSpherical capDatabaseTable (information)Reading (process)String (computer science)Moving averageError messageElectric currentKey (cryptography)IntegerContent (media)Source codeFunction (mathematics)Cellular automatonComputer fileNormal (geometry)Wrapper (data mining)Visualization (computer graphics)Library (computing)Statement (computer science)Connectivity (graph theory)CodeExtension (kinesiology)Projective planeMobile appSoftware developerTable (information)Link (knot theory)DatabaseContext awarenessKey (cryptography)Theory of relativityDifferent (Kate Ryan album)Decision theoryMathematicsQuicksortConnected spaceMoment (mathematics)State of matterComputing platformInstance (computer science)Client (computing)Order (biology)Revision controlSurjective functionWindowProgramming paradigmNormal (geometry)Slide ruleBitUniqueness quantificationOcean currentSimilarity (geometry)Attribute grammarFlagObject (grammar)EmailData storage deviceBuildingServer (computing)Compact spaceComputer fileCartesian coordinate systemRelational databaseMultiplication signPoisson-KlammerVideo gameSequelOpen sourceType theoryNeuroinformatikProcess (computing)Lie groupIdeal (ethics)CASE <Informatik>Power (physics)Point (geometry)Computer programmingVotingAxiom of choiceNatural numberPhysical lawSoftware frameworkData managementArmGoodness of fitStandard deviationView (database)Coefficient of determinationSemiconductor memoryPhysical systemAdditionComputer animation
27:48
Software developerVisual systemEnterprise architectureMaxima and minimaComputer configurationRevision controlCASE <Informatik>.NET FrameworkPoint (geometry)Computer fileSequelWindowMoment (mathematics)1 (number)CurveProjective planeSpherical capComputing platformData managementMobile appSource codeComputer animation
29:07
Software developer.NET FrameworkCohen's kappaMobile appComputer fileReal number1 (number)Revision controlObject (grammar)NamespaceOrder (biology)Projective planeMobile appStatement (computer science)Greatest elementMultiplication sign.NET FrameworkSequelEqualiser (mathematics)Spherical capExtension (kinesiology)Computer animation
31:05
Software testingSoftware developer.NET FrameworkInclusion mapSoftware development kitSequelInternet service providerBitCodeComputing platformSelf-organizationWindow1 (number)Extension (kinesiology)Library (computing)Universe (mathematics)Computer fileWrapper (data mining)Computer animation
32:21
Software developerUniverse (mathematics)Sampling (statistics)Computing platformSequelBlogComa BerenicesAutonomous System (Internet)EmailLibrary (computing)Internet forumTask (computing)Demo (music)Mobile appComputer animation
33:23
Software developerQuery languageExecution unitContent (media)Order (biology)Sampling (statistics)Natural numberInstance (computer science)Web pageCASE <Informatik>Multiplication signObject (grammar)Set (mathematics)Connected spaceResultantLogicSocial classComputer fileMobile appQuery languageTable (information)Endliche ModelltheorieDatabaseStructural loadQuicksortSequelProjective planeState observerInsertion lossCore dumpObject-relational mappingView (database)Inheritance (object-oriented programming)Reading (process)Computer animation
35:23
Software developerQuery languageSample (statistics)Demo (music)PlanningDifferent (Kate Ryan album)CodeRevision controlPattern languageSampling (statistics)Projective planeComputer animation
36:38
Software developerQuery languageProjective planeMobile appRevision controlSingle-precision floating-point formatComputing platformUniverse (mathematics)Multiplication signWindowData storage deviceElectronic mailing listMoment (mathematics)Extension (kinesiology)Special unitary groupPoint (geometry)Client (computing)Data managementStatement (computer science)Rule of inferenceMachine visionContrast (vision)Different (Kate Ryan album)SequelComputer animation
38:07
String (computer science)Software developerElectronic mailing listPointer (computer programming)Convex hullVirtual memoryKey (cryptography)Connected spaceDatabaseObject (grammar)State observerStress (mechanics)Table (information)Constraint (mathematics)Proper mapCodeSequelDirected graphBitSemiconductor memoryStatement (computer science)Exception handlingInsertion lossProjective planeInstance (computer science).NET FrameworkSocial classSimilarity (geometry)Wrapper (data mining)Row (database)Mobile appStructural loadComputer animation
40:20
DatabaseSoftware developerParameter (computer programming)Execution unitElectronic program guideLocal ringInformationSoftware frameworkTable (information)Data storage deviceInternet service providerAbstractionExtension (kinesiology)SQL ServerRelational databaseSoftware testingRead-only memory.NET FrameworkRevision controlComputing platformData modelBlogContext awarenessOvalString (computer science)Content (media)Internet service providerDatabase.NET FrameworkEndliche ModelltheorieSoftware frameworkDifferent (Kate Ryan album)Relational databaseApplication service providerSurface of revolutionReverse engineeringContext awarenessComputer programmingSoftware developerData storage deviceData modelExtension (kinesiology)Modeling languageCategory of beingStructural loadProjective planeInsertion lossBlogDemo (music)VideoconferencingComputer configurationAxiom of choiceQuery languageStatement (computer science)Wrapper (data mining)Parameter (computer programming)Keyboard shortcutSelectivity (electronic)CodeBitRow (database)Link (knot theory)WindowInformationTable (information)NumberQuicksortObject (grammar)Sampling (statistics)Server (computing)Multiplication signSemiconductor memoryVariable (mathematics)Directed graphOpen setWechselseitige InformationExistenceTerm (mathematics)CausalityGroup actionPlanningOcean currentRevision controlLocal ringState of matterCASE <Informatik>Normal (geometry)SequelSoftware testingRight anglePhysical systemStability theorySeries (mathematics)Line (geometry)Observational studyExterior algebraBuildingTheory of relativityComputer animation
47:08
Hill differential equationSoftware developerInterior (topology)Moment (mathematics)Computer fileGroup actionComputer clusterSampling (statistics)Mobile app.NET FrameworkType theorySoftware frameworkMultiplication signReflection (mathematics)CompilerDefault (computer science)Data managementPhysical systemVideo game consoleDirectory serviceCASE <Informatik>Computer animation
49:13
Software developerSoftware frameworkInstallation artSequelMetropolitan area networkFlagInternet service providerProjective planeComputer animation
50:12
Software developerVisual systemEndliche ModelltheorieSocial classSoftware frameworkData managementDemo (music)CodeSelectivity (electronic)2 (number)Right angleVisualization (computer graphics)Computer animation
53:09
Software developerVisual systemExecution unitComputer configurationOvalSet (mathematics)Endliche ModelltheorieHuman migrationField (computer science)BitData managementMathematicsInheritance (object-oriented programming)Category of beingQuicksortSubject indexingProjective planeContext awarenessVideo game consoleScripting languageSocial classKey (cryptography)WordDatabaseConstraint (mathematics)View (database)Attribute grammarReading (process)Mobile appComputer fileVisualization (computer graphics)Source codeComputer animation
56:31
Video game consoleSoftware developerExecution unitHill differential equationCausalityPRINCE2CodeComputer fileTable (information)Human migrationProjective planeStatement (computer science)Endliche ModelltheorieCartesian coordinate systemElectronic mailing listContext awarenessMultiplication signOrder (biology)Mobile appSoftware frameworkSubject indexingInheritance (object-oriented programming)DatabaseObject (grammar)Revision controlNavigationCustomer relationship managementStructural loadConstraint (mathematics)Field (computer science)MathematicsKey (cryptography)Coefficient of determinationSemiconductor memoryComputer animation
58:34
Software developerMobile appMobile WebInternet service providerPoint cloudSynchronizationClient (computing)Wrapper (data mining)CodeQuery languageSoftware framework.NET FrameworkObject-relational mappingDigital rights managementElectronic meeting systemDatabaseObject-relational mappingData storage deviceSequelSoftware developerRevision controlInternet service providerMultiplication signLine (geometry).NET FrameworkStatement (computer science)MathematicsMobile WebRoutingComputing platformComputer fileClient (computing)Axiom of choiceSet (mathematics)Server (computing)CuboidDirectory serviceRow (database)SoftwareSocial classMobile appHypermediaReal numberPoint cloudOrder (biology)Human migrationDirected graphPhase transitionBitComputer configurationCross-platformDecision theoryTable (information)Software frameworkTerm (mathematics)CodeSynchronizationTrailDifferent (Kate Ryan album)Android (robot)Electric generatorEnterprise architectureConnected spaceRiemann hypothesisFront and back endsProper mapoutputWrapper (data mining)Computer animation
Transcript: English(auto-generated)
00:06
I'm going to start bang on time because hopefully we'll get a few more. We'll wander in, but the first bit is kind of scene setting anyway. So we'll get started on this. So I'm Andy Wigley, I work for Microsoft. I work for a group in Redmond, although I'm resident in the UK.
00:23
So I work from home most of the time, which is very nice. So I work for the group in Redmond that's responsible for things like the build conference and for developer outreach and education. And I do a lot of video training, videos some of you may have seen on Channel 9. And I'm going to be responsible for some of the content at Build.
00:43
Any of you planning to go to Build? All right, cool, good, all right. Yeah, so this tool, it's a new one. I haven't given this one before, but it's almost like my favorite topic, actually. Over the years, I've been doing talks for ages, mainly on Windows Phone and
01:02
Compact Framework, well, Windows Mobile going back far enough, and Compact Framework, and we used to have SQL Server CE, SQL Server Compact Edition. Which is a fantastic small database with fantastic characteristics and good encryption and a great sync story to the back end. And then for unfathomable reasons, we killed it.
01:22
So we don't have that anymore. Well, you do, you can use it on the server side. So you can use it for ASP.NET and that kind of thing. So, but I've always been interested in cool ways of handling your data and syncing it up to the back end and all this sort of thing. And so I wanted to look at that with the context of UWP clients.
01:43
So obviously that spans, does span small devices, embedded devices. Cuz we have, all the demos I'm gonna show you will work and do work fine on like a Raspberry Pi 2 running UWP. But obviously, it's also we got phone mobile devices, desktop devices,
02:02
Surface Hub, presumably HoloLens and everything. All of this will apply to any of those clients where we have got the UWP platform running on them. So I'm just gonna start by having a quick look at data storage, just to kind of make sure we're all on the same page about where a UWP app can actually store stuff.
02:21
Very briefly look at kind of plain old CLR serialization, just cuz it's part of the story, but that's kind of the least interesting part of the story. Then I'm gonna talk about SQLite. I'm gonna do a bit more detail on entity framework, cuz that's coming along and that's really interesting. And then round off with looking at storing your data in the cloud
02:40
with a quick demo of Azure App Service mobile apps. So first of all, data storage in UWP, it's an amazingly complex story actually. You think, okay, how complicated can it be? We've got an app and the user downloads it from the store and it runs in a sandbox on your machine. How much complexity could there be in there?
03:01
So there's our app. And obviously when the user installs your app from the store, you ship it with some assets which could include some data files. But they'd be static data files, they're part of the app package. You could have a SQLite database in there or a JSON file or whatever. Less useful because obviously those files are read only.
03:22
But that can be useful as a kind of a seed, getting started initial seed data. Because what you can do in your app is then copy it up to one of the read writeable folders, which is the normal local storage area that an app has. And there's three flavors of that, local, roaming, and temp.
03:42
I'll talk about them in a little bit more detail just on the next slide. But those are folders, which is just like a, it's a sandbox, looks like a file system. You can create folders in it, you can store files in it. It's your storage for your app. Also with UWP, we brought in a new thing, which is this publisher's shared folder.
04:02
And you can think of this as a bit like a file share. So it's a common storage area that's file structured and apps from the same publisher can all read and write to that folder. So you can have a suite of apps and they have this common folder that they can all use. And that's one way of sharing data between apps,
04:23
if you are the publisher of all of those apps. We can obviously use storage cards, that's another place you can read and write to, that becomes an extension of the app storage. Credential lock is an interesting one. Again, you read and write, it's a string keyed dictionary.
04:40
But it's specifically for storing credential objects. So usernames and passwords, but that's obviously all encrypted. And one interesting thing about the credential locker as well is that anything that you store in that credential locker dictionary is actually roamed across all the user's devices. So the system will roam that, as it does with the roaming folder,
05:03
clues in the name, the system will take care, UWP system will take care of roaming that data across all the user's devices. So it's a nice way of sharing some data, particularly credential locker. It allows you to have these scenarios like getting a user to log in once to
05:22
some kind of resource, and you need to store the username and password to reuse it in future. You store it in credential locker, then they run your app on one of their other devices, and they don't have to enter that username and password all over again because it's already been roamed across. So it's nice little things that make users happy and this sort of thing. So all that stuff on the left is kind of directly addressable stuff and the SD card.
05:45
We also have a bunch of APIs in UWP called the File, Open, and Save Pickers. So these are APIs that you can call in your app, and they're basically, we've had kind of file open pickers in every kind of UI framework forever.
06:00
And these are just the UWP versions of those. The idea with these is because you actually make a call and say, okay, we asked the user to identify where to save this file. And then the shell pops up an explorer kind of window that allows the user to go and select the folder where they can save a file or
06:21
where they can open a file. Now, the reason why this is interesting is just because those folders are outside of the app sandbox. So it can be off to the My Documents or Downloads or any kind of folder, public folder on the device. And also with these File, Open, Save, or Pickers,
06:41
we can actually, you could write a specific provider for a pickers, which opens up some other kind of storage, such as OneDrive. So it kind of extends beyond file system to other, any file, any storage-oriented bits of the system, the network that you can get at. So those ones, because the user gets involved,
07:03
permission is implied that it's all right to save stuff to that folder, because the user has said that that's okay. The complementary to that is there's a bunch of known folders APIs, which again, for getting to common areas such as pictures, videos, music. Those, you don't directly ask the user through a File, Open, Picker,
07:24
so they don't pop up a UI when you use them. But in order to use the known folders, you've actually gotta put in the manifest. You gotta tick one of those check boxes to say, I want to use the pictures, or I want to use the user's music. So this is when the user installs the app. They get that dialog up saying, this app requires access to your pictures.
07:43
Are you okay with that? And of course, everybody says, yeah, of course I am, I want the app. But you're asking for the user's permission there. So again, then your app can read and write to those locations. And finally, of course, is the cloud. So cloud is a very valid data storage. And I'm actually gonna close this talk about talking more about the cloud.
08:02
Cuz it's more important, I think, than people realize from a user perspective. And this whole thing about having one app, one UWP app can run across all the user's devices. And I'm sure all of you in here, you've probably got, you'll have a laptop, you may have a tablet, you've got one or two phones.
08:20
That's becoming more and more common for all of us. We've got all this plethora of devices, but you want to be able to have access to your data on all of those devices. And the only way you're gonna do that is having an app that's using a cloud backend for storage. So again, from a continuous mobile experience,
08:40
a good mobile experience for the user, having a cloud backend for your data or some kind of sync mechanism through the cloud is pretty crucial for that, to enable that scenario. So a little bit more about these direct folders. Windows storage application data is the API. We have the local folder, which is just store files and folders in it.
09:02
There's also a settings dictionary that is entirely local to your app. So it's local to your app on that device. So that's not shared, not roamed or anything like that. There is the roaming folder, which is exactly the same. Except stuff that you store into that or the roaming dictionary does get roamed across the user's other devices.
09:22
So they're signed in with their Microsoft account, for example. Or if it's a business Windows 10 device, they might be authenticated against Azure Active Directory or Office 365 account. But either way, roaming goes on and the system takes care of moving stuff that you store in there across the user's other devices.
09:41
There's one very big caveat and restriction to that, and that is as soon as the roaming folder gets to be more than 100K in size, roaming stops. It ends up getting treated like a local folder. So it's not super useful. It's useful for some setting stuff, and there's no sync mechanism. You know, last writer wins is the rule.
10:00
So it's got some use, but use carefully. Then we've got the temp folder, as the name suggests. You can store stuff in it. The system will just blow it away whenever it feels the need. And we have that publisher shared folder that I mentioned, which you just have to declare up in the app manifest if you want to use this.
10:21
And then you can read and write to this just as if it was a local folder. And then there was that windows.securitycredentials, the password vault, which is the secure storage, and that one is roamed across the devices. There may well be a size limit on it, but frankly, if you were getting more than 100K of encrypted credentials, you've probably got too many credentials.
10:44
Right, so that's kind of the background. Let's look at, I'm now going to just run through a number of different ways for an app to think about storing its data. So the starting premise for all of these demos is that we've got two collections of objects. So most of these demos are kind of a very simple CRM kind of scenario.
11:03
So I've got a collection of customer objects, and I've got a collection of projects. And there's a one-to-many relationship between a customer and, you know, one or more projects. So straightaway, this kind of where you've got relationships between groups of entities, that can cause some difficulties
11:24
if you just want to serialize stuff. So serializing to a file, you take a collection of objects in memory, and you stringify it. You turn it into a string. Typically, that string is XML or JSON. Increasingly more JSON these days is more preferred. So this is an example of serializing some JSON.
11:43
Now, this is absolutely not the only way. This is using the windows.data.json classes, which are in the UWP APIs. In actual fact, the vast majority of the world, including myself, prefers JSON.net. Any hands for JSON.net? Yeah, it's kind of almost the de facto standard.
12:00
Maybe I should change this slide next time I do it, to use JSON.net. You can... You don't have to... No, that's right. There's different ways of doing it. But also, what's kind of interesting about this is actually, normally, if you're going to serialize a collection of objects, in JSON.net, it's a one-line, two lines of code, actually. And there probably is a way of doing this and this.
12:22
But this is kind of... Sometimes, simple serialization breaks down, and you have to start manually doing your own serialization. This is why I've done this example here. So here we've got an example of school objects, and we've got an array of school objects that you want to serialize away. So first of all, you could store... You could create a JSON array of these things,
12:43
which is serializing a collection of objects. And then the top-level object, we can new up a JSON object, and then we can actually store attributes into it, give it a key name of ID, in this case, and create a string value. So you can do this kind of low-level serialization if you need to. And sometimes, you do have to with serialization.
13:01
Just the one-liner serializes collection doesn't work for whatever reason. It's not easily serialized data. So you're gonna sometimes have to do this manual stuff. And then you can store, in the top-level object, you can store that JSON array of objects. So here, we've managed to serialize relational data into simple files.
13:21
And the key message here is, I'm gonna show you increasingly more complex, but probably more interesting ways of storing your data. But sometimes, simple is the best, and sometimes we don't need all this complexity. This may well be good enough. And taking it the other way, of course, deserialization, you can just parse a string of JSON
13:40
using the JSON object.parse, and then you can pull out particular attributes from that record that is now sort of parsed into memory. And you can use special logic again to handle specific cases that the default serialization. If you've got some specific needs in your serialization,
14:01
different ways of handling a null value, for example, then you can do it. We've got all the tools available. But it's kind of, you end up potentially writing quite a lot of code for this sort of thing. And the other way of using local storage is just to use the settings dictionary. So local settings or roaming settings is just a dictionary.
14:20
So you can give it a string key value, like example setting, and you can store simple objects into it. You could store serializable objects into it. You could store a JSON string into it if you like. But this again is kind of composite value. So for really simple storage, actually a settings dictionary may well be enough. You don't need anything more complicated.
14:41
And its brother, the roaming settings, this is the one I was talking about, use it exactly the same way, except in this case, as long as it's less than 100K, then it will be automatically synced for you by Windows through the cloud, either through the user's Microsoft account, which is like what we call public OneDrive,
15:02
or OneDrive for Business, which is where you have got a business device that's where the user's primary logon is to a corporate or education identity. But essentially working exactly the same way, getting synced through the cloud. And these are all kind of nice simple
15:22
data storage techniques that will actually fit a great number of situations. But that's all kind of boring really, isn't it? Files are kind of boring. But databases are actually inherently more interesting and actually have a lot of characteristics that make them perhaps better suited
15:41
for storing this kind of relational data where you've got a relationship between one collection of data and another one. So we have good support for SQLite in UWP. It's not been great support. It's getting better all the time. So why even think of using SQLite? Well, it is the world's most popular database.
16:03
And increasingly, as developers, if we were doing cross-platform work, it's nice to work with the same tools on each of the different platforms. So it's widely used on iOS, Android, Python, Mono. It is open source. You can go up to SQLite.org. You can download the source code.
16:21
You can build your own SQLite database. It's really well proven. And it's been around for a long, long time. So it's very well tested. It's got all the kind of features that you'd expect in a relational database and some that are kind of interesting that you might not expect.
16:41
Just an example of that, I'm not gonna talk about it because I did used to do a talk just on SQLite. And there's an interesting thing. If you do a create table statement in SQLite and you say, okay, I want a string column and I want an int column and a blob column, SQLite will go and create it for you.
17:01
But actually, column types in SQLite are actually kind of just, they're not hard and fast. They're kind of hints to the system. So if you take your int column and try and store a string in it, SQLite will go, really? Well, okay then, all right, I'll do that. And it stores it. It just stores it in there. So it's kind of, it will try and do the right thing.
17:23
So if you've got an int column and you try and store a string in it, the first thing it will do is see if it's actually a stringified integer and try and convert, do an int32.parse on it effectively. And then if it can convert it to an integer, it will store it in there. But if it can't, well, it just, it carries on and does it.
17:42
That's interesting but not actually terribly helpful from a developer's point of view. But it does explain why SQLite tends to turn up as the data storage, the persistent data storage underneath other frameworks, such as Entity Framework 7, as I'm gonna talk about, and also in Azure mobile apps,
18:00
which I'm gonna talk about. Both of them use underneath SQLite. And it's this kind of flexibility that makes it really nice as part of an infrastructure, part of a bigger solution. But it is very reliable. You know, your system can crash and its transactions are acid. So even if interrupted, it won't end up with corruption. So it's great.
18:20
It's got all the standard features you might expect, tables, indices, triggers, and views. So it's a great tool. It is up on SQLite.org. Like I said, you can go and download the source if you wish. Nobody really would unless you've got a particular specialist need to because the tools that you need to use it are actually provided for you, as I'm gonna show.
18:41
Now, the first thing is, yeah, we've kind of been, as Microsoft, we used to have SQLite Compact, SQL Server Compact Edition for client-side data storage. And that was great. And then we've moved away from that and we've been trying, sort of moving towards
19:01
a more standards-based approach. And SQLite was kind of, with Windows 8.1 store apps, SQLite was kind of supported and people like me were doing talks showing how it was done. But it was kind of, well, yeah, you can use it, but we weren't actively sort of mandating and saying, yeah, this is the one we should use. And we're now kind of,
19:21
we're getting a little bit more clarity, if you like, in our messaging as Microsoft say, yeah, look, this is what we support. It is strategic going forward. It is open source, but we love open source. And one manifestation of that is in the very latest UWP update, the one that's just come out just before Christmas,
19:40
the Windows 10 1511 version, which is, and the SDK for that is Build 10.5.86. So this is the second release of our Windows 10 UWP platform, if you like, after the 10.2.40 build that we had back in the summer. So 10.5.86. So now SQLite is included in our SDK.
20:04
Now what that means is that you can actually reference it without having to download something from SQLite.org. There's a restriction to that. It's actually, SQLite is written in C. It exposes a C API. That doesn't make it directly easily usable
20:22
from C sharp, for example. So we have to kind of put wrappers around it to make it usable from C sharp. But if you are working in native code, you can reference the SDK version of SQLite just by including that header and linking to the WinSQLite3.lib in the additional dependencies.
20:42
Why would you want to do that? So you could download SQLite3.dll, and I'm gonna show you in a minute, we still need to do that for C sharp. You could still do that with native code, but if you can directly link to it, the advantage of that is that you won't have to worry about updates in the future. One minor irritation about SQLite
21:01
is that you get a new update comes down from SQLite.org. It feels like every month, and you update your extension SDKs, and then you have to update your code and this sort of thing, or you just say, okay, I'm sticking with this version for this project. So if you link to the SDK version, you can just let Windows update the platform on the client platform and push the latest updates to SQLite
21:23
whenever we do a refresh of our SDK. You might also see, so you're relying on the platform to update it, but you might also see a slight performance advantage just because other bits of the system, a lot of our first-party apps, for example, are using SQLite under the wrappers,
21:41
and if it's already been updated for them, and the user's already running one of those, it'll already be in memory, and you'll get, not perceptibly, not hugely faster, but you will get some faster launch times. But for managed code, we need to do something else. So for managed code solutions,
22:03
we need to rely on some kind of a wrapper. Like I said, it's a C API to work directly with SQLite 3.dll, which is the actual library that you will ship down. So if you install this Visual Studio extension, SQLite for Universal App Platform, it's still using the old name,
22:21
but that's the UWP SQLite, what it will do is it will put into your extensions and updates SQLite for Universal App Platform, and your managed code apps can now reference that SDK. And actually, all that you are doing by doing that is adding SQLite 3.dll, which is the SQLite DLL, into your project.
22:44
Again, that's not quite enough to actually then go ahead and write an application that uses it. So you need to use one of the wrappers that are in NuGet. So we have to provide a WinRT component that each exposes unique ways of working with SQLite.
23:01
SQLite PCL and SQLite WinRT are fairly similar in that they work with old school, if you like, SQL statements. So if you have grown up with relational databases and you love working with select, star, from, whatever, where, order by kind of statements, or maybe you've got some legacy code that does that,
23:20
one of those two will feel quite nice to you. The other one, SQLite-net, in brackets, or in quotes in the stars says, doesn't work currently. I've got a slide coming up to show you how you can get it to work. The NuGet package doesn't work, and I'll tell you why in a minute. But you need to use one of these wrappers
23:41
in order to actually program against SQLite. So this is the difference, this is your choice, really, as a managed code developer in using SQLite. Do you prefer link statements, or do you prefer SQL statements? It's just a simple choice, really.
24:00
For new development, I mean, actually, I mean, obviously, I've been doing, as we all have probably, database programming for years, and I grew up with actual SQL statements, but it does seem a little bit weird to be writing a text, piece of text, and then processing it, I don't know. So I kind of tend to move towards this one
24:20
on any new development, or Entity Framework, which gives a similar kind of programming model. There are a whole bunch of these things. So to give you an example of the style when you're programming, this is SQLite.net,
24:40
which is the kind of the link to SQL kind of one. And this one, if you want to, you create yourself a, get the path to where you want to store it, you want to store it in the local folder, in this case, you create yourself a SQLite connection object, you might give it some flags on how you want to use it, and then you create your tables by calling the,
25:01
on your context object, create table of type, and then you define your types, kind of like that. So it's an object relational mapper, what you, it's a lightweight ORM, this is, so you're working, conceptually, you're working with entities like this, which is nice. And you put attributes on it to say,
25:21
it's a primary key and it's auto increment and that kind of stuff. The difference is if you want to work with old school SQL statements, then you are very much into old school SQL statements. And here, you would, you create, open a connection to your database, and then you execute the statement. So this is just the same as if you were in a desktop tool
25:41
as one of the SQLite tools where you can actually work with a database and create tables and columns and this sort of thing. That's what you're doing. You're sending it to, you're sending that SQL statement to the database engine and it's processing it. Actually, interestingly, the one I showed you before, db.create table, underneath the wrappers does exactly the same,
26:00
but it just hides all that, the SQL statements. SQLite, SQLite database is, works on SQL statements under the wrappers. So that's kind of the two decisions you've got to make. Now, I said I was gonna tell you how to use SQLite.net, because this is very popular. Have any of you used SQLite.net?
26:20
Lightweight ORM, it's very, very popular. It doesn't work in UWP because we made a change with UWP from NuGet version two to NuGet version three. NuGet version three, they rewrote a lot of the way it works, for good reasons. But one of the shortcomings of it at the moment
26:42
is that when you add a NuGet package, it can't add additional files into your project. NuGet version two could do that. And SQLite.net, actually the wrapper consists of two C sharp files that does P invoke state calls out to SQLite3.dll in order to do its magic. So you need those two files in your project,
27:02
but if you add the SQLite.net NuGet package, it doesn't do anything because it can't add in content files. So the workaround is actually a bit kludgy, but you just need to go create a new project targeting Windows 8.1, which is still NuGet version two,
27:21
add the NuGet package onto that, and then just whip the two files out of it. And you also need to reference the extension SDK as normal. And I'll let him show you how that works.
27:43
let's go, let's start a new instance of this, Visual Studio. So, file a new project, and I wanna show you how to use SQLite.net.
28:04
So, first of all, I need to have those files, so I'm actually gonna create a Windows 8.1 blank app, app two, and create that first.
28:23
And we are working on NuGet version three, so this problem will be solved over time, but at the moment, this is what you have to do. And then you have to go off to manage NuGet packages, browse for SQLite-net,
28:48
and the other thing you'll find is when you search NuGet for SQLite wrappers, there are gazillions of them. So clearly, you want to choose one that's got lots of downloads because people obviously like that one. But this downloads figure, 120K, is not UWP,
29:01
is not 8.1, it's kind of all platforms. But that will do, that one is great, it's supported for this platform, 8.1. And then, here we are, we get those two files. This is the SQLite-net wrapper, which is a very interesting example of how to p-invoke out to a native DLL,
29:21
if you're ever interested in that, but most of us are not, we need to have that. So let's just save all those files. I'm gonna grab those files from app two in a minute, and now I'm gonna create the real object of this, new project, and this time I want a UWP blank app, which we'll call, imaginatively, app three.
29:47
So there we go. And so there's a couple of things we need to do in order to support SQLite-net, well, .net, I never know what to call it, to be honest. I'm gonna add some existing files, existing item.
30:05
So go off to our app two, and I wanna grab those guys. So those were the ones that were inserted by the NuGet version two package. I don't have to worry about namespace,
30:21
because they are, they're just, you know, their namespace is SQLite. And then I can start going ahead and start writing some SQL statements. There's one other thing I have to do, though. Like this is, down the bottom of this, SQLite-3, are the imported, DLL imports for the different methods
30:46
that are on the C API that is in SQLite3.dll. So we need to have a copy of SQLite3.dll in our project. So the way you do that is you add, well, first of all, tools, extension, and updates,
31:05
up online, you will find SQLite, various flavors of SQLite, and SQLite for Universal App Platform
31:22
is one of the ones you can find online through the extensions and updates. Or you can go to SQLite.org and just download the VSIX file. That's all that this is doing, is pulling down that VSIX and installing into your dev platform. I've already obviously added, I've already installed that,
31:40
as you can see by the green ticks. So I can now reference it in here. So I can just simply add a reference, and I go to Universal Windows, Extensions, and then all of the libraries that are available to me are in here, including SQLite for Universal App Platform. You have to tick it. It's a bit of a weird whiz at this. You think just selecting it and clicking OK would be enough,
32:02
but you have to actually tick it, otherwise it doesn't actually add it. And that's added in that reference, and now if I build it, SQLite3.dll will be there, and the wrapper is all of that. So that's kind of the plumbing bit of it, and then we can go ahead. I'm not gonna write the code. What I'm gonna do instead is move to
32:22
some existing samples, which are up on my blog. In fact, while I was preparing this talk, somebody pinged me on email, because there was a question online on the forums from somebody who was failing to get SQLite working on IoT,
32:40
which was timely, because I was working on demos for this talk. So I developed all of these samples. I was in the middle of developing them, and checked them all out. These all run on the Raspberry Pi as well. But what's interesting, these are all up on my blog, which is andiwiggly.com. SQLite-dotnet-sample, that data access library is used
33:02
for accessing SQLite from a background task, so these samples show that as well. I'm not gonna talk about that. But now this one, this SQLite-dotnet-sample has got the SQLite for universal app platform in it, and of course we've got those two files, SQLite-cs and SQLite-async-dot-cs.
33:20
Now how do we then use it? In app-dot-xaml-dot-cs, in this particular sample I, here we go. Here's the logic to actually create our, get a reference to it, create a SQLite connection, create our tables,
33:41
and then here's this, I've got this reset data which actually kind of clears it all out and sets it. So this is how you then do from a SQLite-dotnet how you insert new entities into your database. Quite easy, you knew up an instance of the entity, the class, the customer or whatever, and you, using your SQLite connection,
34:02
you call the insert method on it, and you insert it into the database. Insert, loads of inserts, projects and this sort of thing. And that's actually, that's all you need to do, is in order to put those into the database. In order to, then if you go and look at main-page-dot-xaml, in order to read stuff out,
34:25
so that was seeding the database, in order to read stuff out, all you need to do, well, I've got a customer's view model class. Let's go and have a look at that. So here's our, and that exposes a collection,
34:41
observable collection of customer view models, and this is how, this is my method to get the customers and unsurprisingly, we just knew up a SQLite connection, the path to the database file, and then this is where you do your query. Query is db.table customer, you can put an order by clause on it, and then read through the results for each customer in query.
35:03
We're newing up an object. In this case, it's a customer view model, and then we're adding it to our collection of customers and returning the customers. So it's very natural. It's a lightweight ORM way of working with data, and honestly, it's, the app itself is not super exciting,
35:21
but I'll show you because you'll see this a few times. Yeah. No, that's a great question, so.
35:50
No, no, no. And it's, as somebody doing demos, I'm always conflicted by the need to do it right, and the need to keep the demos simple
36:01
so that people can understand. So this is a simple. Now I would put a repository pattern in. Yeah, and yeah, yeah, so. And the nice thing about a repository pattern is you're just working against entities, and it fits very nicely, and underneath, it doesn't really, we don't care what the data access code is, so. Yep, yep, yep.
36:21
So this is my, you know, this is the sample, and I've got various different versions of this, so it's just got a relationship between a customer, Mr. Adventureworks customer, and has got three projects, and Contoso has got one project, and so we've got this relational thing going on, and the access code is as I've shown you there,
36:40
that kind of stuff. By contrast, the old school way of doing things, also in this project, so we're done with that one. By contrast, this one, SQLite WinRT. So this one, if I go into the,
37:02
this one does use the NuGet. So first of all, we've got a reference already. I've added a reference to SQLite for Universal App Platform. That's the extension SDK. That's your SQLite3.dll, and I've also got, I'm gonna just bring up the NuGet package manager,
37:23
and installed into this project, I have got SQLite WinRT, which is one that I support, which at the moment for UWP is a pre-release. It's on my work list to actually merge it with the old one, which supports Windows Phone 8,
37:45
Windows Phone 8.1, Windows Store 8.1, everything under the sun. This one at the moment is separate just because of the NuGet version 2 to NuGet version 3, and there is a way of creating a single package in NuGet that supports both the clients, but I haven't had time to do the work yet.
38:01
So that one has been added, and this one supports SQL statements, so a somewhat different way of doing things. And if we look at our, let's first of all look at our entities. Well, these are just, these are our entities, but these are actually not used directly, so I've just got, this is just a class in memory object
38:24
to represent these objects that I'm going to be storing to my database and getting out of my database. But you're very much closer to the old way of doing things here, and you're writing a lot of, too much code really, and SQLite-net avoids you writing a lot of the code you've got here. So let's have a look at app.xaml.cs,
38:41
which is where, similar to the other example, this is where we create our database, where we create our tables. But this time, if I can find it, okay, here we have our load database method, where we knew up a database object,
39:00
which is in my wrapper. We open a connection to it. It's fully async, this one, and here we go. It's an old-fashioned SQL statement, where we are creating tables using SQL statements, and you execute the statement, so that does it to the database. Similarly, here's the project. But this is nice, because actually one thing that SQLite.net
39:22
is not very good at is foreign key relationships. So here we've actually got a foreign key we can reference. Interesting thing, and I was saying that SQLite has some interesting stuff about it. It's been around so long that foreign key support was added after the initial releases of it. So it's not turned on. This is constraints in the database.
39:42
It's not turned on by default. If you want to turn on, the observance of foreign key constraints, and by that I mean it stops you. You actually get an exception if you try and store a project record that's got an invalid customer ID in it. So it's got no proper foreign key relationship. So if you want to enable those constraints in the database, you actually have to execute a pragma.
40:02
You have to execute this. Pragma foreign keys equals on. We are much closer to the database, but you do get, as always, you've got a bit less abstraction. You've got a bit more control. So there you go. And you execute it. How do we add some stuff into this database?
40:21
Well, let's go and find, there's some stuff down here. Again, it's all great SQL statements. Yeah, here we go. This is a bit weird. Insert, this is a parameterized query. Insert into customer, into the name, city, and contact columns, three values represented here by question marks,
40:42
anonymous values. And then you have to actually plug the real values in. So this is what we call, you take your statement, your prepared statement, and you add in the values, which in this case, these are variables. Customer name, city, and contact. And then you call stepAsync to execute it. This is very close to the C API
41:02
that is exposed from SQLite3.dll with no kind of embellishments. You're really very close to the database. So if that's what you want to do, you can do it with this database. But it's very much a choice for you. And so it goes on. And actually reading things
41:20
is a bit of an eye-opener as well. Oh yeah, no, actually this one, look. So this select row ID from customer where name equals anonymous and contact equals anonymous. Then you have to bind these parameters. But look at this. It's a one-based collection. Gosh, when was the last time you saw a one-based collection? It's just insane.
41:41
Huh? Okay. Yeah, it's still around. Not logical for most of us. So you can see the style of things. And these samples are up in my blog. And you can have a look at the study, how it all works. But I'm gonna move on now to the next topic.
42:06
Oh yeah, there's a bit more information. Also, if you want to watch one of the videos I've got up on channel nine, one that I did last summer, actually looks at yet another wrapper. She also uses SQL statements, which is up on channel nine.
42:20
In the developers guide to Windows 10 series, there's one on SQLite local database. So a bit more information for you if you're interested in that. But I wanted to, this is actually quite exciting. So this is one that has caught my eye recently. They've been announcing it forever, Entity Framework team. And then you've seen in the Entity Framework talks, they first started talking about EF7
42:41
back in the mists of time, 2014 actually. At TechEd 2014, where they said they were developing this. And they are building this. Any of you using Entity Framework today? Entity Framework six, okay. Six and seven, all right. So Entity Framework seven is not a replacement for six.
43:01
It's an alternative. It's a new framework that is very portable. The key thing here is the old Entity Framework needed a full .NET framework. And it's been developed over years. And it's got this huge legacy of stuff. And it's a rich framework. And lots of developers are very happy with it.
43:22
But the fact that it had this dependency on the full .NET framework and all this legacy code means that it wasn't very appropriate for new scenarios. It wasn't appropriate for cross-platform dev and this sort of thing. So Entity Framework seven is this new lightweight and extensible version, which shares a lot of the same coding methods, the same techniques.
43:41
But it's intended to be much more flexible and much more applicable to loads of different kind of scenarios. So obviously it'll work with relational databases. We've got all these different providers, which are actually the link between the Entity Framework programming layer and the underlying storage. So yeah, we can actually talk to SQL Server,
44:01
SQLite, Postgres and others. We can talk to things like Azure table storage. So non-relational data storage is also supported by this as well, which is kind of interesting. So they're expanding the number of scenarios where it can be used. But you keep your code, basically your data access code, the same. So if you are doing a lot of development
44:21
across a lot of different platforms, this is definitely one you wanna look at. That being said, all these different providers, so if you're doing ASP.NET 5, for example, you can use the SQL Server provider and talk directly to SQL Server. Can't do that on UWP, not yet. I mean, they're working on it all the time
44:41
and they're releasing as fast as they can. We are close to RTM. We are close to RTM, not quite. I wonder if they'll, they might announce that bill. I don't know. But actually for UWP, as she says, SQLite is the only supported provider. That's not quite true. You can actually use the in-memory one as well. But the in-memory one is purely for testing.
45:01
So as the name suggests, it just creates entities in memory. It's used for trying stuff out. But for UWP, actually, SQLite is the only one. It uses, this is kind of a controversial term as well, this thing called code-first data model. So the idea here is that you define your context
45:22
and you define your entities in code and Entity Framework will create the underlying data storage for you based on your data model. This is the only model supported by, so we haven't got support for these EDMX data diagrams, this modeling language that you have got in EF6.
45:41
And when this was first announced by the Entity Framework team, there was like a revolution in the streets, an uproar, and everybody was very upset about it. But what you have to remember is you can still take a database, an existing database, and you can reverse engineer the plain old CLR objects. You can still come back to what is a code model of your database
46:02
if you want to kind of reverse engineer an existing database. So a lot of the expected shortcomings of this actually aren't there. So it's a different way of working, though, and this is the way it works. I'll talk about this in a bit more, actually, because I've got a demo coming up which is a more interesting way of talking about it. But you have a context which you must inherit from DB context.
46:22
You define some properties for your entities and then you can kind of say, okay, I'm gonna use, here's where you select the provider. The Entity Framework 7 providers are just through NuGet packages. So you select the provider you want and they come with extension methods that allow you to say, for example, in the DB context options builder,
46:42
yeah, I'm gonna use the SQLite database. And then you can set some attributes. And then you define your entities as an example of a blog and a post. But I'm actually gonna show you a demo of this which is rather more interesting. All right, file, new project.
47:16
I'm gonna create a UWP blank app and I'm gonna call it EF7 sample.
47:35
Now, so we are close to RTM. There is a slight little thing that you have to do at the moment
47:41
which is a workaround that they will fix in time. But at the moment, if you were gonna take this and actually deploy it out, an app using this, you have to go into your default.rd.xml file which is the file that is runtime directory is used by .NET native.
48:01
So EF7, it turns out, and .NET native don't sit very comfortably together. They are working on it. But most of the time you never, most people never have to go into this file. But in this case, for Entity Framework, you actually have to add in an additional directive into here. You have to put the time, name is equal to,
48:24
and the name is the system.collections. So here you're telling the .NET native compiler that it needs to, I could spell that right, it needs to support, it needs to support, dyslexic today, name, there we go,
48:43
needs to support this type which is not getting just from reflection on the types in here. So EF7 does work, throws out ArrayList, and we need, let's just put required all on it. This will probably go away, but at the moment this is what you have to do.
49:01
Now then, we need to add in Entity Framework 7 to this solution. So for that I'm gonna bring up the NuGet package manager console which is down here, and it's just initializing. And then I need to install the package that I wanna use.
49:22
So I'm gonna call the install package command, and the name is the Entity Framework, Framework.SQLite, and this is a pre-release,
49:41
so I need to put the pre-release flag on it, and that will install the packages in to my project for Entity Framework for the SQLite provider for Entity Framework 7. And I'm also gonna add in, I'm gonna use some commands as well in a little while. So there are some Entity Framework commands, so I wanna add in those as well.
50:05
Okay, so that's my, I've now got my packages in. So if we look in here in references, we've got Entity Framework stuff in here in my references. So that gets us started. I now wanna create my model.
50:20
I'm gonna define my model. So I'm gonna add a folder called models, and in there, I want to add a class. I'm gonna add a class for our customers. So I'm gonna use the same example, add,
50:44
and I'm gonna have a snippet. Oh, that's not what I wanted. Ah, my snippets have gone wrong.
51:06
This is going to rather torpedo my demo. That's disappointing.
51:28
Oh, that is weird. Where's it gone?
51:42
Where have these gone? It's not gonna be there. Other? No. Excuse me, one second. And if I can't find them quickly, I'll just go, I've got a finished one available.
52:04
Where's my code snippets? Yeah. Well, they are. Okay, that's weird.
52:22
Code snippets manager. I haven't created a basic thing, have I? No, C sharp, that's rather weird.
52:42
Documents, 2015, code snippets, visual C sharp, my code snippets. Select folder. Okay. EF7, right. Let me just, let's just start this again.
53:06
Yes. And if this doesn't work, I'll just switch to the,
53:23
sorry? Yeah, Visual Studio still has a few rough edges, doesn't it, yeah. Seven, hey, there we go. There we go, right. We're back in business. So, now this is my customer entity.
53:40
What's interesting in here is I've got my primary ID name, city, and contact, but I've also included in here a collection of its child objects, its projects, which I haven't added yet. So, let's add another class to this.
54:01
You're reading my script. It does exactly that, yes, it does that, yeah. It does, there's some clever stuff built into it. And this is my project class, so nothing particularly exciting, a bunch of attribute properties on that. So, that's my model.
54:21
So, I now need to add in here a new class, which is my context. So, I'm just gonna call this the CRM context, because it's kind of a CRME, sort of an app.
54:41
And in here, then we've got, this is where we define all the bits and pieces of it. So, and what's interesting here is that I actually need to resolve a couple of things. In some of the models.
55:02
So, I've got my DB set of customer and this sort of thing. I've got, I just have to data.entity. I've got a completely broken snippet here. That's not good. Okay, we're running a bit out of time,
55:22
so let's just, I'm gonna go switch to my finished one. So, in this on configuring, you can actually define
55:41
a lot of the attributes of our database with the use SQLite, the file name where it's gonna be stored. You can then define a lot of, you can constraints on your model. So you can say that I'm gonna define GUIDs for my keys. I don't want the database to generate them,
56:00
so I can say actually for the ID property on customer, I'll take care of that. But it is a primary key ID. Similarly for the project, I'm gonna take care of it and the ID is the primary key. I've also got an index on customer ID, which is the foreign key field in a project entity that points back to its parent customer. And you can also do things like say,
56:21
okay, the name is required. And you define all of this. You then build it. And then what you do is you go into your package manager console and you use a command there which is add-migration and you give it a name.
56:41
And then that will generate in your application, in your project, these files. And what it does is it's analyzing all that code you just defined your model and it turns it into statements to create your table. Here's the migration to create table. There are all the columns for that table. It's got a constraint on it, a primary key,
57:02
which is the ID field. And it's just derived all of that by looking at the code that I've defined, defining my model and that DB context. And yeah, just by having that list of projects in the customer, it knew to create that foreign key relationship in there. So it knew just by looking at my code
57:22
that that was intended. That's the index I added to make lookups on that customer ID efficient, which you should always do on the child side of a foreign key relationship. And anytime you change your model, you can add a new migration. And up in your app.xaml.cs, in order to actually create your database,
57:41
you just call db.database.migrate and that will create it the first time it runs. And subsequently, if you ship an update of your app, it will actually change the database as well using the data migrations in order to apply the new schema to it. Then to actually go ahead and read stuff
58:01
from this entity framework thing. For example, here we go on navigator two, we have load data where we knew up a CRM in context and then you just get db.customers, turn it to a list. And then you can actually get the child objects out of it as well. We can actually set the projects,
58:21
which is that list of projects in a customer object. You can go off to the, go and get the db.projects where the customer ID is equal to the ID of the parent. So we can populate our in-memory version of this and you won't be surprised to see that all that works just great.
58:44
So it's very much an object relational mapper. It's a nice way of working with data. We can reset our data. Crates are in the same data as we had before but it works in exactly the same way. Underneath, it's storing it in a SQLite database but actually you don't need to know that. It's just a data store that works really great.
59:09
So that was that. Because I'm messing around with snippets, I've unfortunately run out of time so I'm just gonna talk about this one quickly. So, and I opened with this,
59:21
talking about the importance of creating a mobile experience where the user, mobile user is the center of this, of your app, not the device. We've kind of moved from a world where mobile app development was all focused on a phone or something or a mobile device. That's not what it's about. Mobile app development is creating an experience that flows across a user's devices.
59:43
And UWP obviously is ideal for that. So whatever the user's device, they need to be able to get at that data. So your app needs to be linked up to some kind of cloud storage. And one of the best, there are various different mobile backend as a service solutions. You could roll your own in Asha, but we give you one out.
01:00:00
a box that is really easy to use. Azure App Service mobile apps, which has all the features you need for a mobile backend as a service. It's really easy to generate. You can store in SQL database, in table storage, in blob storage, MongoDB. You can authenticate against Azure Active Directory or any of the social media networks. You can do push notifications.
01:00:22
And most of all, it's completely cross-platform. So you can have a true backend in Azure that will support your clients, whichever client platform they're running on. It's really easy to configure. We actually used Entity Framework Data Migrations as well to manage changes for the database.
01:00:41
So as you're doing the development phase, you can either just drop the database and apply a new schema, or you can actually update it as you go. It's really easy to configure it during the development phase. But the key thing I really wanted to talk about, unfortunately I'm not gonna be able to show you, is its support for offline sync. So this is really great.
01:01:02
It uses SQLite on the client underneath the wrappers again as the local storage. And it's got built into it change tracking. So you can actually have your mobile app running completely offline. When you get a connection back again, it will resync back with the backend and apply the changes.
01:01:21
So you could make your apps really resilient against network connectivity. Not necessarily important for every app, but think about your app, particularly maybe critical line of business apps, you might want to have this. And it's got full support for detecting conflicts, which is important. You can roll your own solutions for sync, but actually it's really hard to get right.
01:01:43
So really this kind of solution out of the box is highly recommended if you wanna do this properly. If you've got a real need for robust enterprise class synchronization. It's what SQL Server Compact Edition used to do so well. But we've now got a solution that works with SQLite, actually works cross-platform,
01:02:01
because this all works with, of course it's using SQLite that runs, the client SDK stuff runs on iOS and Android, on Xamarin, and on UWP, and on HTML5. So this is really the latest generation of these solutions. And you can choose, if you get an update that's a conflict, like two clients have tried to update the same record,
01:02:22
you can choose in your solution whether you handle that conflict on the server, just you choose in code running in the REST service on the server how to handle that conflict and just choose who wins. Or you can bounce it back to the client and say, I can't apply your update, and let the app kind of do whatever it needs to do
01:02:41
in order to resolve that conflict. Maybe ask the user what they wanna do or something like that. So in summary, that really supports all the features that you need for Azure Mobile App Offline Sync. There's a talk on Channel 9 where I go into this in a lot more detail. I was about to show you a demo, but I'm not gonna be able to, unfortunately.
01:03:02
But I will just summarize everything I've shown you in this talk. In terms of the data options for UWP, keep it simple if you can. If you only need to serialize into a few files or a settings dictionary, well, go for it, you know? However, if you wanna get something a bit more sophisticated in terms of
01:03:21
the data storage on your device, SQLite is a great, great choice for that, and we are supporting it very strongly these days. You've got choices of wrappers. You can use SQL statements, or you can use an ORM kind of SQLite-net solution. Increasingly, though, particularly if you're supporting,
01:03:42
you're veering towards that later one, I would recommend that Entity Framework 7 is gonna be the way to go. And that gives you, again, a cross-platform, a cross-target coding story. Gives you all the same advantage you get with SQLite-net with the difference that it's a fully supported Microsoft framework.
01:04:00
That's about to get to RTM. We're close to that, and it's a very good solution. And then finally, the mobile back-end-of-the-service cloud storage, really important if you wanna create that connected experience across different devices. I'm over time. Thanks very much for listening. I'm gonna be, obviously, here
01:04:20
if you wanna come and ask me any questions about anything more. I'm really, really happy to answer them. Thanks very much for coming. Thanks.