EdgeDB is designed to support modern and emerging approaches to building highly reactive and flexible applications. Thanks to its elegant data model and expressive query language, EdgeDB greatly simplifies working with complex application data. The AACRAO Electronic Database for Global Education (EDGE) is a dynamic database and credential advice is subject to change. Credential advice is modified when new studies of foreign countries' education systems have been undertaken. Based upon the research collected and analyzed, new placement recommendations are reviewed and approved by an.
-->You can use SQLite to store and retrieve data in a light-weight database on the user's device. This guide shows you how.
Some benefits of using SQLite for local storage
✔️ SQLite is light-weight and self-contained. It's a code library without any other dependencies. There's nothing to configure.
✔️ There's no database server. The client and the server run in the same process.
✔️ SQLite is in the public domain so you can freely use and distribute it with your app.
✔️ SQLite works across platforms and architectures.
You can read more about SQLite here.
Choose an abstraction layer
We recommend that you use either the Entity Framework Core or the open-source SQLite library built by Microsoft.
Entity Framework Core
Entity Framework (EF) is an object-relational mapper that you can use to work with relational data by using domain-specific objects. If you've already used this framework to work with data in other .NET apps, you can migrate that code to a UWP app and it will work with appropriate changes to the connection string.
To try it out, see Getting started with EF Core on Universal Windows Platform (UWP) with a New Database.
SQLite library
The Microsoft.Data.Sqlite library implements the interfaces in the System.Data.Common namespace. Microsoft actively maintains these implementations, and they provide an intuitive wrapper around the low-level native SQLite API.
The rest of this guide helps you to use this library.
Set up your solution to use the Microsoft.Data.SQlite library
We'll start with a basic UWP project, add a class library, and then install the appropriate Nuget packages.
The type of class library that you add to your solution, and the specific packages that you install depends on the minimum version of the Windows SDK that your app targets. You can find that information in the properties page of your UWP project.
Use one of the following sections depending on the minimum version of the Windows SDK that your UWP project targets.
The minimum version of your project does not target the Fall Creators Update
If you're using Visual Studio 2015, click Help->About Microsoft Visual Studio. Then in the list of installed programs, make sure that you have NuGet package manager version of 3.5 or higher. If your version number is lower than that, install a later version of NuGet here. On that page, you'll find all of the versions of Nuget listed beneath the Visual Studio 2015 heading.
Next, add class library to your solution. You don't have to use a class library to contain your data access code, but we'll use one our example. We'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.
Right-click the solution, and then click Manage NuGet Packages for Solution.
If you're using Visual Studio 2015, Choose the Installed tab, and make sure that the version number of the Microsoft.NETCore.UniversalWindowsPlatform package is 5.2.2 or higher.
If it isn't, update the package to a newer version.
Choose the Browse tab, and search for the Microsoft.Data.SQLite package. Install version 1.1.1 (or lower) of that package.
Move onto the Add and retrieve data in a SQLite database section of this guide.
The minimum version of your project targets the Fall Creators Update
There's a couple of benefits to raising the minimum version of your UWP project to the Fall Creators update.
First off, you can use .NET Standard 2.0 libraries instead of regular class libraries. That means that you can share your data access code with any other .NET-based app such as a WPF, Windows Forms, Android, iOS, or ASP.NET app.
Secondly, your app does not have to package SQLite libraries. Instead, your app can use the version of SQLite that comes installed with Windows. This helps you in a few ways.
✔️ Reduces the size of your application because you don't have to download the SQLite binary, and then package it as part of your application.
✔️ Prevents you from having to push a new version of your app to users in the event that SQLite publishes critical fixes to bugs and security vulnerabilities in SQLite. The Windows version of SQLite is maintained by Microsoft in coordination with SQLite.org.
✔️ App load time has the potential to be faster because most likely, the SDK version of SQLite will already be loaded into memory.
Lets start by adding a .NET Standard 2.0 class library to your solution. It's not necessary that you use a class library to contain your data access code, but we'll use one our example. We'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.
Right-click the solution, and then click Manage NuGet Packages for Solution.
At this point, you have a choice. You can use the version of SQLite that is included with Windows or if you have some reason to use a specific version of SQLite, you can include the SQLite library in your package.
Let's start with how you use the version of SQLite that included with Windows.
To use the version of SQLite that is installed with Windows
Choose the Browse tab, and search for the Microsoft.Data.SQLite.core package, and then install it.
Search for the SQLitePCLRaw.bundle_winsqlite3 package, and then install it only to the UWP project in your solution.
To include SQLite with your app
You don't have to do this. But if you have a reason to include a specific version of SQLite with your app, choose the Browse tab, and search for the Microsoft.Data.SQLite package. Install version 2.0 (or lower) of that package.
Add and retrieve data in a SQLite database
We'll do these things:
1️⃣ Prepare the data access class.
2️⃣ Initialize the SQLite database.
3️⃣ Insert data into the SQLite database.
4️⃣ Retrieve data from the SQLite database.
5️⃣ Add a basic user interface.
Prepare the data access class
From your UWP project, add a reference to the DataAccessLibrary project in your solution.
Add the following
using
statement to the App.xaml.cs and MainPage.xaml.cs files in your UWP project.Open the DataAccess class in your DataAccessLibrary solution and make that class static.
Note
While our example will place data access code in a static class, it's just a design choice and is completely optional.
Add the following using statements to the top of this file.
Initialize the SQLite database
Add a method to the DataAccess class that initializes the SQLite database.
This code creates the SQLite database and stores it in the application's local data store.
In this example, we name the database
sqlliteSample.db
but you can use whatever name you want as long as you use that name in all SqliteConnection objects that you instantiate.In the constructor of the App.xaml.cs file of your UWP project, call the
InitializeDatabase
method of the DataAccess class.Insert data into the SQLite database
Add a method to the DataAccess class that inserts data into the SQLite database. This code uses parameters in the query to prevent SQL injection attacks.
Retrieve data from the SQLite database
Add a method that gets rows of data from a SQLite database.
The Read method advances through the rows of returned data. It returns true if there are rows left, otherwise it returns false.
The GetString method returns the value of the specified column as a string. It accepts an integer value that represents the zero-based column ordinal of the data that you want. You can use similar methods such as GetDataTime and GetBoolean. Choose a method based on what type of data the column contains.
The ordinal parameter isn't as important in this example because we are selecting all of the entries in a single column. However, if multiple columns are part of your query, use the ordinal value to obtain the column you want to pull data from.
Add a basic user interface
In the MainPage.xaml file of the UWP project, add the following XAML.
This basic user interface gives the user a
TextBox
that they can use to type a string that we'll add to the SQLite database. We'll connect the Button
in this UI to an event handler that will retrieve data from the SQLite database and then show that data in the ListView
.In the MainPage.xaml.cs file, add the following handler. This is the method that we associated with the
Click
event of the Button
in the UI.That's it. Explore the Microsoft.Data.Sqlite to see what other things you can do with your SQLite database. Check out the links below to learn about other ways to use data in your UWP app.
Next steps
Connect your app directly to a SQL Server database
See Use a SQL Server database in a UWP app.
Share code between different apps across different platforms
See Share code between desktop and UWP.
Add master detail pages with Azure SQL back ends
See Customer Orders Database sample.
-->Azure Cosmos DB supports Apache Tinkerpop's graph traversal language, known as Gremlin. You can use the Gremlin language to create graph entities (vertices and edges), modify properties within those entities, perform queries and traversals, and delete entities.
In this article, we provide a quick walkthrough of Gremlin and enumerate the Gremlin features that are supported by the Gremlin API.
Compatible client libraries
The following table shows popular Gremlin drivers that you can use against Azure Cosmos DB:
Download | Source | Getting Started | Supported connector version |
---|---|---|---|
.NET | Gremlin.NET on GitHub | Create Graph using .NET | 3.4.0-RC2 |
Java | Gremlin JavaDoc | Create Graph using Java | 3.2.0+ |
Node.js | Gremlin-JavaScript on GitHub | Create Graph using Node.js | 3.3.4+ |
Python | Gremlin-Python on GitHub | Create Graph using Python | 3.2.7 |
PHP | Gremlin-PHP on GitHub | Create Graph using PHP | 3.1.0 |
Gremlin console | TinkerPop docs | Create Graph using Gremlin Console | 3.2.0 + |
Supported Graph Objects
TinkerPop is a standard that covers a wide range of graph technologies. Therefore, it has standard terminology to describe what features are provided by a graph provider. Azure Cosmos DB provides a persistent, high concurrency, writeable graph database that can be partitioned across multiple servers or clusters.
The following table lists the TinkerPop features that are implemented by Azure Cosmos DB:
Category | Azure Cosmos DB implementation | Notes |
---|---|---|
Graph features | Provides Persistence and ConcurrentAccess. Designed to support Transactions | Computer methods can be implemented via the Spark connector. |
Variable features | Supports Boolean, Integer, Byte, Double, Float, Integer, Long, String | Supports primitive types, is compatible with complex types via data model |
Vertex features | Supports RemoveVertices, MetaProperties, AddVertices, MultiProperties, StringIds, UserSuppliedIds, AddProperty, RemoveProperty | Supports creating, modifying, and deleting vertices |
Vertex property features | StringIds, UserSuppliedIds, AddProperty, RemoveProperty, BooleanValues, ByteValues, DoubleValues, FloatValues, IntegerValues, LongValues, StringValues | Supports creating, modifying, and deleting vertex properties |
Edge features | AddEdges, RemoveEdges, StringIds, UserSuppliedIds, AddProperty, RemoveProperty | Supports creating, modifying, and deleting edges |
Edge property features | Properties, BooleanValues, ByteValues, DoubleValues, FloatValues, IntegerValues, LongValues, StringValues | Supports creating, modifying, and deleting edge properties |
Gremlin wire format: GraphSON
Azure Cosmos DB uses the GraphSON format when returning results from Gremlin operations. Azure Cosmos DB currently supports 'GraphSONv2' version. GraphSON is the Gremlin standard format for representing vertices, edges, and properties (single and multi-valued properties) using JSON.
For example, the following snippet shows a GraphSON representation of a vertex returned to the client from Azure Cosmos DB.
The properties used by GraphSON for vertices are described below:
Property | Description |
---|---|
id | The ID for the vertex. Must be unique (in combination with the value of _partition if applicable). If no value is provided, it will be automatically supplied with a GUID |
label | The label of the vertex. This property is used to describe the entity type. |
type | Used to distinguish vertices from non-graph documents |
properties | Bag of user-defined properties associated with the vertex. Each property can have multiple values. |
_partition | The partition key of the vertex. Used for graph partitioning. |
outE | This property contains a list of out edges from a vertex. Storing the adjacency information with vertex allows for fast execution of traversals. Edges are grouped based on their labels. |
And the edge contains the following information to help with navigation to other parts of the graph.
Property | Description |
---|---|
id | The ID for the edge. Must be unique (in combination with the value of _partition if applicable) |
label | The label of the edge. This property is optional, and used to describe the relationship type. |
inV | This property contains a list of in vertices for an edge. Storing the adjacency information with the edge allows for fast execution of traversals. Vertices are grouped based on their labels. |
properties | Bag of user-defined properties associated with the edge. Each property can have multiple values. |
Each property can store multiple values within an array.
Property | Description |
---|---|
value | The value of the property |
Gremlin steps
Now let's look at the Gremlin steps supported by Azure Cosmos DB. For a complete reference on Gremlin, see TinkerPop reference.
step | Description | TinkerPop 3.2 Documentation |
---|---|---|
addE | Adds an edge between two vertices | addE step |
addV | Adds a vertex to the graph | addV step |
and | Ensures that all the traversals return a value | and step |
as | A step modulator to assign a variable to the output of a step | as step |
by | A step modulator used with group and order | by step |
coalesce | Returns the first traversal that returns a result | coalesce step |
constant | Returns a constant value. Used with coalesce | constant step |
count | Returns the count from the traversal | count step |
dedup | Returns the values with the duplicates removed | dedup step |
drop | Drops the values (vertex/edge) | drop step |
executionProfile | Creates a description of all operations generated by the executed Gremlin step | executionProfile step |
fold | Acts as a barrier that computes the aggregate of results | fold step |
group | Groups the values based on the labels specified | group step |
has | Used to filter properties, vertices, and edges. Supports hasLabel , hasId , hasNot , and has variants. | has step |
inject | Inject values into a stream | inject step |
is | Used to perform a filter using a boolean expression | is step |
limit | Used to limit number of items in the traversal | limit step |
local | Local wraps a section of a traversal, similar to a subquery | local step |
not | Used to produce the negation of a filter | not step |
optional | Returns the result of the specified traversal if it yields a result else it returns the calling element | optional step |
or | Ensures at least one of the traversals returns a value | or step |
order | Returns results in the specified sort order | order step |
path | Returns the full path of the traversal | path step |
project | Projects the properties as a Map | project step |
properties | Returns the properties for the specified labels | properties step |
range | Filters to the specified range of values | range step |
repeat | Repeats the step for the specified number of times. Used for looping | repeat step |
sample | Used to sample results from the traversal | sample step |
select | Used to project results from the traversal | select step |
store | Used for non-blocking aggregates from the traversal | store step |
TextP.startingWith(string) | String filtering function. This function is used as a predicate for the has() step to match a property with the beginning of a given string | TextP predicates |
TextP.endingWith(string) | String filtering function. This function is used as a predicate for the has() step to match a property with the ending of a given string | TextP predicates |
TextP.containing(string) | String filtering function. This function is used as a predicate for the has() step to match a property with the contents of a given string | TextP predicates |
TextP.notStartingWith(string) | String filtering function. This function is used as a predicate for the has() step to match a property that doesn't start with a given string | TextP predicates |
TextP.notEndingWith(string) | String filtering function. This function is used as a predicate for the has() step to match a property that doesn't end with a given string | TextP predicates |
TextP.notContaining(string) | String filtering function. This function is used as a predicate for the has() step to match a property that doesn't contain a given string | TextP predicates |
tree | Aggregate paths from a vertex into a tree | tree step |
unfold | Unroll an iterator as a step | unfold step |
union | Merge results from multiple traversals | union step |
V | Includes the steps necessary for traversals between vertices and edges V , E , out , in , both , outE , inE , bothE , outV , inV , bothV , and otherV for | vertex steps |
where | Used to filter results from the traversal. Supports eq , neq , lt , lte , gt , gte , and between operators | where step |
The write-optimized engine provided by Azure Cosmos DB supports automatic indexing of all properties within vertices and edges by default. Therefore, queries with filters, range queries, sorting, or aggregates on any property are processed from the index, and served efficiently. For more information on how indexing works in Azure Cosmos DB, see our paper on schema-agnostic indexing.
Next steps
- Get started building a graph application using our SDKs
- Learn more about graph support in Azure Cosmos DB