Avenuelucky

Using SQLite.NET with Android.; 6 minutes to read +1; In this article. The SQLite.NET library that Xamarin recommends is a very basic ORM that lets you easily store and retrieve objects in the local SQLite database on an Android device.

1. Get Generated Key Sqlite Android Free
2. Get Generated Key Sqlite Android Free

The SQLite.NET library that Xamarin recommends is a very basic ORM thatlets you easily store and retrieve objects in the local SQLite databaseon an Android device. ORM stands for Object Relational Mapping – anAPI that lets you save and retrieve 'objects' from a databasewithout writing SQL statements.

To include the SQLite.NET library in a Xamarin app, add the following NuGet package to your project:

• Package Name: sqlite-net-pcl
• Author: Frank A. Krueger
• Id: sqlite-net-pcl
• Url:nuget.org/packages/sqlite-net-pcl

Tip

There are a number of different SQLite packages available – be sure to choose the correct one (it might not be the top result in search).

Once you have the SQLite.NET library available, follow these three steps to use it to access a database:

1. Add a using statement – Add the following statement to the C#files where data access is required:

2. Create a Blank Database – A database reference can becreated by passing the file path the SQLiteConnection classconstructor. You do not need to check if the file already exists– it will automatically be created if required, otherwise theexisting database file will be opened. The dbPath variable shouldbe determined according the rules discussed earlier in thisdocument:

3. Save Data – Once you have created a SQLiteConnectionobject, database commands are executed by calling its methods, suchas CreateTable and Insert like this:

4. Retrieve Data – To retrieve an object (or a list ofobjects) use the following syntax:

Basic Data Access Sample

The DataAccess_Basic sample code for this document looks like thiswhen running on Android. The code illustrates how to perform simpleSQLite.NET operations and shows the results in as text in theapplication's main window.

Android

The following code sample shows an entire database interaction usingthe SQLite.NET library to encapsulate the underlying database access.It shows:

1. Creating the database file

2. Inserting some data by creating objects and then saving them

3. Querying the data

You'll need to include these namespaces:

The last one requires that you have added SQLite to your project. Notethat the SQLite database table is defined by adding attributes to aclass (the Stock class) rather than a CREATE TABLE command.

Using the [Table] attribute without specifying a table name parameterwill cause the underlying database table to have the same name as theclass (in this case, 'Stock'). The actual table name is importantif you write SQL queries directly against the database rather than usethe ORM data access methods. Similarly the [Column('_id')] attributeis optional, and if absent a column will be added to the table with thesame name as the property in the class.

SQLite Attributes

Common attributes that you can apply to your classes to control howthey are stored in the underlying database include:

• [PrimaryKey] – This attribute can be applied to aninteger property to force it to be the underlying table's primarykey. Composite primary keys are not supported.

• [AutoIncrement] – This attribute will cause an integerproperty's value to be auto-increment for each new object insertedinto the database

• [Column(name)] – The name parametersets the underlying database column's name.

• [Table(name)] – Marks the class as being able to bestored in an underlying SQLite table with the name specified.

• [MaxLength(value)] – Restrict the length of a textproperty, when a database insert is attempted. Consuming codeshould validate this prior to inserting the object as thisattribute is only 'checked' when a database insert or updateoperation is attempted.

• [Ignore] – Causes SQLite.NET to ignore this property.This is particularly useful for properties that have a type thatcannot be stored in the database, or properties that modelcollections that cannot be resolved automatically by SQLite.

• [Unique] – Ensures that the values in the underlyingdatabase column are unique.

Most of these attributes are optional. You should always specify an integerprimary key so that selection and deletion queries can be performedefficiently on your data.

More Complex Queries

The following methods on SQLiteConnection can be used to perform other data operations:

• Insert – Adds a new object to the database.

• Get<T> – Attempts to retrieve an object using theprimary key.

• Table<T> – Returns all the objects in the table.

• Delete – Deletes an object using its primary key.

• Query<T> – Perform an SQL query that returns a number ofrows (as objects).

• Execute – Use this method (and not Query) when youdon't expect rows back from the SQL (such as INSERT, UPDATE andDELETE instructions).

Getting an object by the primary key

SQLite.Net provides the Get method to retrieve a single object based on its primary key.

Selecting an object using Linq

Methods that return collections support IEnumerable<T> so you can useLinq to query or sort the contents of a table. The following code showsan example using Linq to filter out all entries that begin with theletter 'A':

Get Generated Key Sqlite Android Free

Selecting an object using SQL

Even though SQLite.Net can provide object-based access to your data,sometimes you might need to do a more complex query than Linq allows(or you may need faster performance). You can use SQL commands with theQuery method, as shown here:

Note

When writing SQL statements directly you create adependency on the names of tables and columns in your database, whichhave been generated from your classes and their attributes. If youchange those names in your code you must remember to update anymanually written SQL statements.

Deleting an object

Get Generated Key Sqlite Android Free

The primary key is used to delete the row, as shown here:

You can check the rowcount to confirm how many rows were affected(deleted in this case).

Using SQLite.NET with Multiple Threads

SQLite supports three different threading modes: Single-thread,Multi-thread, and Serialized. If you want to access the databasefrom multiple threads without any restrictions, you can configureSQLite to use the Serialized threading mode. It's important to setthis mode early in your application (for example, at the beginning ofthe OnCreate method).

To change the threading mode, call SqliteConnection.SetConfig. Forexample, this line of code configures SQLite for Serialized mode:

The Android version of SQLite has a limitation that requires a few moresteps. If the call to SqliteConnection.SetConfig produces a SQLiteexception such as library used incorrectly, then you must use thefollowing workaround:

1. Link to the native libsqlite.so library so that thesqlite3_shutdown and sqlite3_initialize APIs are madeavailable to the app:

2. At the very beginning of the OnCreate method, add this codeto shutdown SQLite, configure it for Serialized mode, andreinitialize SQLite:

This workaround also works for the Mono.Data.Sqlite library. For moreinformation about SQLite and multi-threading, seeSQLite and Multiple Threads.

Related Links

The SQLite.NET library that Xamarin recommends is a very basic ORM thatlets you easily store and retrieve objects in the local SQLite databaseon an Android device. ORM stands for Object Relational Mapping – anAPI that lets you save and retrieve 'objects' from a databasewithout writing SQL statements.

To include the SQLite.NET library in a Xamarin app, add the following NuGet package to your project:

• Package Name: sqlite-net-pcl
• Author: Frank A. Krueger
• Id: sqlite-net-pcl
• Url:nuget.org/packages/sqlite-net-pcl

Tip

There are a number of different SQLite packages available – be sure to choose the correct one (it might not be the top result in search).

Once you have the SQLite.NET library available, follow these three steps to use it to access a database:

1. Add a using statement – Add the following statement to the C#files where data access is required:

2. Create a Blank Database – A database reference can becreated by passing the file path the SQLiteConnection classconstructor. You do not need to check if the file already exists– it will automatically be created if required, otherwise theexisting database file will be opened. The dbPath variable shouldbe determined according the rules discussed earlier in thisdocument:

3. Save Data – Once you have created a SQLiteConnectionobject, database commands are executed by calling its methods, suchas CreateTable and Insert like this:

4. Retrieve Data – To retrieve an object (or a list ofobjects) use the following syntax:

Basic Data Access Sample

The DataAccess_Basic sample code for this document looks like thiswhen running on Android. The code illustrates how to perform simpleSQLite.NET operations and shows the results in as text in theapplication's main window.

Android

The following code sample shows an entire database interaction usingthe SQLite.NET library to encapsulate the underlying database access.It shows:

1. Creating the database file

2. Inserting some data by creating objects and then saving them

3. Querying the data

You'll need to include these namespaces:

The last one requires that you have added SQLite to your project. Notethat the SQLite database table is defined by adding attributes to aclass (the Stock class) rather than a CREATE TABLE command.

Using the [Table] attribute without specifying a table name parameterwill cause the underlying database table to have the same name as theclass (in this case, 'Stock'). The actual table name is importantif you write SQL queries directly against the database rather than usethe ORM data access methods. Similarly the [Column('_id')] attributeis optional, and if absent a column will be added to the table with thesame name as the property in the class.

SQLite Attributes

Common attributes that you can apply to your classes to control howthey are stored in the underlying database include:

• [PrimaryKey] – This attribute can be applied to aninteger property to force it to be the underlying table's primarykey. Composite primary keys are not supported.

• [AutoIncrement] – This attribute will cause an integerproperty's value to be auto-increment for each new object insertedinto the database

• [Column(name)] – The name parametersets the underlying database column's name.

• [Table(name)] – Marks the class as being able to bestored in an underlying SQLite table with the name specified.

• [MaxLength(value)] – Restrict the length of a textproperty, when a database insert is attempted. Consuming codeshould validate this prior to inserting the object as thisattribute is only 'checked' when a database insert or updateoperation is attempted.

• [Ignore] – Causes SQLite.NET to ignore this property.This is particularly useful for properties that have a type thatcannot be stored in the database, or properties that modelcollections that cannot be resolved automatically by SQLite.

• [Unique] – Ensures that the values in the underlyingdatabase column are unique.

Most of these attributes are optional. You should always specify an integerprimary key so that selection and deletion queries can be performedefficiently on your data.

More Complex Queries

The following methods on SQLiteConnection can be used to perform other data operations:

• Insert – Adds a new object to the database.

• Get<T> – Attempts to retrieve an object using theprimary key.

• Table<T> – Returns all the objects in the table.

• Delete – Deletes an object using its primary key.

• Query<T> – Perform an SQL query that returns a number ofrows (as objects).

• Execute – Use this method (and not Query) when youdon't expect rows back from the SQL (such as INSERT, UPDATE andDELETE instructions).

Getting an object by the primary key

SQLite.Net provides the Get method to retrieve a single object based on its primary key.

Selecting an object using Linq

Methods that return collections support IEnumerable<T> so you can useLinq to query or sort the contents of a table. The following code showsan example using Linq to filter out all entries that begin with theletter 'A':

Selecting an object using SQL

Even though SQLite.Net can provide object-based access to your data,sometimes you might need to do a more complex query than Linq allows(or you may need faster performance). You can use SQL commands with theQuery method, as shown here:

Note

When writing SQL statements directly you create adependency on the names of tables and columns in your database, whichhave been generated from your classes and their attributes. If youchange those names in your code you must remember to update anymanually written SQL statements.

Deleting an object

The primary key is used to delete the row, as shown here:

You can check the rowcount to confirm how many rows were affected(deleted in this case).

Using SQLite.NET with Multiple Threads

SQLite supports three different threading modes: Single-thread,Multi-thread, and Serialized. If you want to access the databasefrom multiple threads without any restrictions, you can configureSQLite to use the Serialized threading mode. It's important to setthis mode early in your application (for example, at the beginning ofthe OnCreate method).

To change the threading mode, call SqliteConnection.SetConfig. Forexample, this line of code configures SQLite for Serialized mode:

The Android version of SQLite has a limitation that requires a few moresteps. If the call to SqliteConnection.SetConfig produces a SQLiteexception such as library used incorrectly, then you must use thefollowing workaround:

1. Link to the native libsqlite.so library so that thesqlite3_shutdown and sqlite3_initialize APIs are madeavailable to the app:

2. At the very beginning of the OnCreate method, add this codeto shutdown SQLite, configure it for Serialized mode, andreinitialize SQLite:

This workaround also works for the Mono.Data.Sqlite library. For moreinformation about SQLite and multi-threading, seeSQLite and Multiple Threads.

Related Links