Why does Android software use SQLite

Using SQLite.NET with Android

  • 6 minutes to read

The SQLite.NET library that Xamarin recommends is a very basic ORM that allows you to easily store and retrieve objects in the local SQLite database on an Android device very basic ORM that lets you easily store and retrieve objects in the local SQLite database on an Android device.ORM stands for relational object mapping - an API that allows you to store and retrieve "objects" from a database without requiring SQL statements ORM stands for Object Relational Mapping - an API that lets you save and retrieve "objects" from a database without writing SQL statements.

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

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 in C # to the files where data access is requiredAdd a using statement - Add the following statement to the C # files where data access is required:

  2. Create an empty database - A database reference can be created by passing the file path of the sqliteconnection class constructor.Create a blank database - A database reference can be created by passing the file path the SQLiteConnection class constructor. You do not need to check whether the file already exists, - It is created automatically if necessary, otherwise the existing database file is opened check if the file already exists - it will automatically be created if required, otherwise the existing database file will be opened. The variable should be determined according to the rules discussed earlier in this document:

  3. Saving data - After you have created a sqliteconnection object, database commands are executed by calling the associated methods, such as: b. CreateTable and INSERT as follows:Save data - Once you have created a SQLiteConnection object, database commands are executed by calling its methods, such as CreateTable and Insert like this:

  4. Retrieving data - to get an object (or a list of objects) use the following syntax:Retrieve data - To retrieve an object (or a list of objects) use the following syntax:

Basic Data Access Sample

The DataAccess_Basic Sample code for this document looks like this when run on Android DataAccess_Basic sample code for this document looks like this when running on Android. The code illustrates how to perform simple SQLite.net operations and shows the results in as text in the application's main window.

AndroidAndroid

The following code sample shows an entire database interaction using the 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. Note that adding attributes to a class (the class) instead of a CREATE TABLE command Note that the SQLite database table is defined by adding attributes to a class (the class) rather than a CREATE TABLE command.

Using the attribute without specifying a table name parameter will cause the underlying database table to have the same name as the class (in this case, "Stock"). The actual table name is important if you are writing SQL queries directly against the database rather than using the ORM data access methods SQL queries directly against the database rather than use the ORM data access methods. Accordingly, the attribute is optional, and if it doesn't exist, a column is added to the table with the same name as the property in the class is optional, and if absent a column will be added to the table with the same name as the property in the class.

SQLite Attributes

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

  • [PrimaryKey] - this attribute can be applied to an integer property to enforce the primary key of the underlying table.[PrimaryKey] - This attribute can be applied to an integer property to force it to be the underlying table's primary key. Composite primary keys are not supported.

  • [AUTOINCREMENT] - this attribute has the effect that the value of an integer property is automatically increased for each new object that is inserted into the database.[AutoIncrement] - This attribute will cause an integer property's value to be auto-increment for each new object inserted into the database

  • [Column (Name)] - the parameter defines the name of the underlying database column.[Column (name)] - The parameter sets the underlying database column's name.

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

  • [MaxLength (value)] - limit the length of a text property when attempting a database insert.[MaxLength (value)] - Restrict the length of a text property when a database insert is attempted. Using code, this should be verified prior to inserting the object as this attribute is only checked when attempting a database insert or update "istConsuming code should validate this prior to inserting the object as this attribute is only 'checked' when a database insert or update operation is attempted.

  • [To ignore] - causes SQLite.net to ignore this property.[Ignore] - Causes SQLite.NET to ignore this property. This is particularly useful for properties that are of a type that cannot be stored in the database or properties that model collections that SQLite cannot automatically resolve useful for properties that have a type that cannot be stored in the database, or properties that model collections that cannot be resolved automatically by SQLite.

  • [Clearly] - ensures that the values ​​in the underlying database column are unique.[Unique] - Ensures that the values ​​in the underlying database column are unique.

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

More Complex Queries

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

  • By Insert - a new object is added to the database.Insert - Adds a new object to the database.

  • Get - Tries to get an object using the primary key.Get - Attempts to retrieve an object using the primary key.

  • Table - returns all objects in the table.Table - Returns all the objects in the table.

  • Delete - deletes an object using its primary key.Delete - Deletes an object using its primary key.

  • Query - Execute an SQL query that returns a series of rows (as objects).Query - Perform an SQL query that returns a number of rows (as objects).

  • Execute - Use this method (and not) if you do not expect any rows from the SQL statement (e.g. insert, update and DELETE statements).Execute - Use this method (and not) when you don't expect rows back from the SQL (such as INSERT, UPDATE and DELETE 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 so you can use Linq to query or sort the contents of a table. The following code shows an example of the The following code shows an example using Linq to filter out all entries that begin with the letter "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 the Query method, as shown here :

Note

When writing SQL statements directly you create a dependency on the names of tables and columns in your database, which have been generated from your classes and their attributes. If you change those names in your code you must remember to update any manually written SQL statements.

Deleting an object

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

You can check the 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, Multithreadand serialized.SQLite supports three different threading modes: Single thread, Multi-thread, and SerializedIf you want unlimited access to the database from multiple threads, you can configure SQLite to use the serialized Threading mode. If you want to access the database from multiple threads without any restrictions, you can configure SQLite to use the Serialized It's important to set this mode early in your application (for example, at the beginning of the method).

To change the threading mode, call. For example, this line of code configures sqlite for the serialized Mode: For example, this line of code configures SQLite for Serialized Fashion:

The Android version of SQLite has a limitation that requires a few more steps. If the calls throw a SQLite exception like this, you must use the following workaround : If the call to produces a SQLite exception such as, then you must use the following workaround:

  1. Link the native libsqlite.so Library so that the and APIs are made available to the app: Link to the native libsqlite.so library so that the and APIs are made available to the app:

  2. At the beginning of the method, add this SQLite shutdown code, configure it for the serialized Mode and reinitialize SQLite: At the very beginning of the method, add this code to shutdown SQLite, configure it for Serialized mode, and reinitialize SQLite:

This workaround also works for the library.For more information about SQLite and multi-threading, see SQLite and Multiple Threads.

Related Links