[Android] Chapter 13.0 create and access the SQLite database & mdash; This Chapter provides an example of the main interface, androidsqlite

[Android] Chapter 13.0 create and access the SQLite database-This Chapter provides an example of the main interface, androidsqlite

Category: C #, Android, VS2015;

Created on: 1. Introduction

Android has three built-in data access methods: SQLite database, file, and SharedPreferences.

This chapter describes how to use the SQLite database to access data.

1. What kind of database is SQLite?

SQLite is a free and open-source database. Because of its unique design (converting various data types into five internal processing types), it occupies very little memory, therefore, many projects like to use it.

Android integrates SQLite with built-in APIs for SQLite operations, so developers can directly use it in mobile apps.

If we compare large databases (Oracle, SQL Server, DB2, etc.) with SQLite, we can regard the former as a "large supermarket", and there are everything from low-end to high-end products, however, the occupied space is also large, while the latter is a "small supermarket", which only sells the most basic and most commonly used products that everyone like, so it consumes less space.

However, if you want it to have everything like a large supermarket, go directly to the large supermarket.

2. Access to the SQLite Database

When compiling Android applications in C #, there are many ways to create and access the SQLite database, which can be downloaded free of charge through NuGet. This chapter only describes several basic methods.

(1) using Android built-in APIs

This method is used in [Example 13-1] and [Example 13-2] in this chapter.

Namespace to be referenced:

Using Android. Database;

Using Android. Database. Sqlite;

It should be noted that it is the most primitive implementation method to construct an SQL statement directly using SQL syntax, which may be preferred if you are familiar with SQL syntax. However, writing SQL statements in this way is too difficult, not only to remember various SQL syntaxes, but also to find errors in SQL strings. Especially for beginners, programs written in this way often have loopholes and a bunch of bugs.

(2) using the API provided by Xamarin

This is the built-in API when Xamarin for VS is installed. Earlier versions (1.0.66) allow you to access the sqlite database through ADO. NET. In the previous chapter (chapter 12th), the simple notepad function is implemented in this way.

Namespaces to be referenced in earlier versions:

Using Mono. Data. Sqlite;

The new version (1.0.99) supports. various versions of. NET, including. NET 4.5, 4.6, as well as LINQ, Entity Framework, etc., but it is not yet mature. It is still being improved. Do not use it for the time being. It is not too late to play after it is perfected.

The namespace to be referenced in the new version:

Using System. Data. SQL;

Using System. Data. SqlClient;

(3) implement with sqlite-net

Sqlite-net is a lightweight and open-source package written in C # To operate SQLite databases. The C # source program of this package can be directly downloaded to your project through NuGet. This package was originally designed to operate SQlite in an iPhone application, but can also be used in Android applications.

The main goal of sqlite-net is to design a fast and convenient database access layer. It designs the library according to the following principles:

• Easy to integrate with existing and MonoTouch projects.
• Fast and efficient lightweight packaging for SQLite (no query performance bottleneck caused by using it ).
• A simple method is provided to securely perform CRUD operations (I .e., create, read, update, and delete), and to perform parameter-based queries by searching for a strong type of search, and to quickly return results.
• It contains a lightweight ORM reflection driver layer, allowing you to fully use your own data model.

Note: sqlite-net does not provide the complete implementation of ADO. NET, nor does it provide the complete driver of SQLite. If you need this, use Mono. Data. SQLite (introduced in section 13.1) or csharp-sqlite.

You can use NuGet to download the sqlite-net Package for free:

• Sqlite-net: The capacity is small (only 143KB after decompression in the latest version 1.0.8)
• SQLiteNetExtensions: This is an ORM extension package of sqlite-net (the latest version is 1.3.0). It supports 1-to-1, 1-to-n, n-to-1, and n-to-n association operations.

However, since later there was an improved SQLite. NET-PCL, so this early version of sqlite-net written in C #3.0 became useless.

(4) implementation with SQLite. NET-PCL and SQLite. NET. Async-PCL

In this chapter, examples 13-3 and 13-4 are implemented in this way.

SQLite. net-PCL is an improved package based on sqlte-net (the latest version is version 3.1.1). In addition to synchronous and asynchronous operations, it also supports cross-platform, such as Xamarin. android, Xamarin. iOS (Classic), Xamarin. iOS (uniied), Windows Phone 8.1, Win8, Win10 ,......) .

When using SQLite. Net-PCL, you do not need to download sqlte-net. However, to distinguish the original sqlte-net, this package is named SQLlite. NET-PCL again.

PCL is the abbreviation of the English "Portable Class Library". It indicates that the Library designed with it can run in Win8, Win10, Win32, Window Phone, monotouch, MonoAndroid ,...... And other platforms.

SQLite. Net-PCL made some modifications to the sqlte-net API. Specific modifications include:

• The SQLiteConnection class is modified so that it can be used across multiple platforms.
• SQliteAsyncConnection is provided to support asynchronous operations across multiple platforms. Note that in this class, SQLiteAsyncConnection is used to manage the program in Task. Run mode.
• Modified the serialization Implementation of DateTime so that it can support multiple regions. To obtain a localized DateTime, call dateTime. ToLocalTime ().

In general, if you want to write ,...... We recommend that you use the public operation class for databases on multiple platforms.

3. Other Instructions

No matter which database you use or which technology you use, you must always remember that a mobile app is a client program, without the foundation of network programming, it is difficult for you to fully understand it. In particular, don't expect to directly plug the database into your mobile phone in actual projects. You won't confuse using a mobile phone as a server to provide services to many people. In actual large applications, databases are dedicated servers (the number of servers required depends on the scale of applications ), the client only indirectly deals with database servers through the network to access a small part of the data needed by the mobile phone. WHO directly establishes a connection through the network? Is a Service or interface that is made public by applications on the application server, such as Web Service, Web API ,...... .

However, as an example, the database is also stored on the mobile phone or in your own program, so that you can debug and understand the database, and add, delete, and copy the database, after all, it is a learning and play stage. Remember one sentence. The tall building is not built out of thin air. You only have learned and understood its most basic usage, first, learn to build a small pop-up house, and then study how to increase the floor height at to be reliable. 2. Observe the packages referenced and downloaded in this project.

1. Observe the referenced. dll file namespace

By now, the MyDemos project has added the following references:

2. packages. config file

Now let's see which packages have been downloaded through NuGet in this project. The packages. config file in the root directory of the MyDemos project is as follows:

<?xml version="1.0" encoding="utf-8"?><packages>  <package id="SQLite.Net.Async-PCL" version="3.1.1" targetFramework="monoandroid60" />  <package id="SQLite.Net.Core-PCL" version="3.1.1" targetFramework="monoandroid60" />  <package id="SQLite.Net-PCL" version="3.1.1" targetFramework="monoandroid60" />  <package id="Xamarin.Android.Support.v4" version="23.1.1.0" targetFramework="monoandroid60" />  <package id="Xamarin.Android.Support.v7.AppCompat" version="23.1.1.0" targetFramework="monoandroid60" />  <package id="Xamarin.Android.Support.v7.CardView" version="23.1.1.0" targetFramework="monoandroid60" />  <package id="Xamarin.Android.Support.v7.RecyclerView" version="23.1.1.0" targetFramework="monoandroid60" /></packages>

Iii. Main Interface of this Chapter

1. Run

2. The code corresponding to this chapter in the MainActivity. cs File

ChItems. add (new Chapter () {ChapterName = "Chapter 13th SQLite database access", ChapterItems = new ChItem [] {new ChItem {type = typeof (ch1301MainActivity ), title = "example 13-1 basic usage of SQLite 1-SimpleCursorAdapter", Desc = "demonstrate how to use SimpleCursorAdapter to access SQLite Database"}, new ChItem {type = typeof (ch1302MainActivity ), title = "example 13-2 SQLite basic usage 2-custom CursorAdapter", Desc = "demonstrate how to access SQLite database with custom CursorAdapter "}, new ChItem {type = typeof (ch1303MainActivity), Title = "example 13-3 SQLite basic usage 3-SQLite.NET-PCL", Desc = "demonstrate how to use SQLite. NET-PCL access SQLite Database "}, new ChItem {type = typeof (ch1303MainActivity), Title =" example 13-4 SQLite basic usage 4-SQLite.NET.Async-PCL ", Desc =" demonstrate how to use SQLite. NET. async-PCL accessing SQLite Database "},}});