"Android" 13.0 the 13th chapter create and Access SQLite database-This chapter sample main interface

Category: C #, Android, VS2015;

Date Created: 2016-02-26 I. INTRODUCTION

Android contains three data access methods: SQLite database, file, sharedpreferences.

In this chapter we mainly learn how to use SQLite database to access data.

1, SQLite is a database of what grade

SQLite is a free, open-source database that has been used by many projects because of its unique design (converting various data types to the 5 types it handles internally), resulting in very little memory being consumed.

Android integrates SQLite and has built-in APIs specifically for SQLite operations, so developers can use it directly in the phone app.

If you compare large databases (Oracle, SQL Server, DB2, and so on) to SQLite, you can think of it as a "big supermarket" with everything from low to high, but with a lot of space, while the latter is a "small supermarket", It sells only the most basic and commonly used items you like, so it takes up less space.

But if you want it to be something like a big supermarket, go straight to the big supermarket.

2. How to access SQLite database

When writing Android applications in C #, there are a number of ways to create and access SQLite databases, which can be downloaded for free through NuGet. This chapter covers only a few of the most basic ways.

(1) implemented with Android built-in API

"Example 13-1" and "Example 13-2" in this chapter are all implemented in this way.

Namespaces that need to be referenced:

Using Android.database;

Using Android.Database.Sqlite;

It is important to note that it is the most primitive way to construct SQL statements directly with SQL syntax, and it is possible to be familiar with SQL syntax in this way. However, writing SQL statements in this way is too laborious, not only to remember the various SQL syntax, but also to find errors in SQL strings. Especially beginners, programs written in this way are often riddled with bugs and bug piles.

(2) Implementation of the APIs provided by Xamarin

This is the built-in API for the installation of Xamarin for VS, an earlier version (1.0.66), which allows you to access the SQLite database by using ADO. The "Example 12-5" simple Notepad function in the previous chapter (chapter 12th) is implemented in this way.

Previous versions need to refer to namespaces:

Using Mono.Data.Sqlite;

The new version (1.0.99) supports various versions of. NET, including. NET 4.5, 4.6, and LINQ, Entity framework, and so on, but is not yet mature, still in continuous improvement, temporarily do not use it, and so it perfect again play is not too late.

The new version needs to refer to the namespace:

Using SYSTEM.DATA.SQL;

Using System.Data.SqlClient;

(3) Implement with Sqlite-net

Sqlite-net is a lightweight, open-source package written in C # that operates the SQLite database, and the C # source program for that package can be downloaded directly to your project through NuGet. The package was originally designed to work with SQLite in the iphone app, but it can also be used in an Android app.

The main goal of Sqlite-net is to design a fast and convenient database access layer. It follows these principles to design this library:

• Easy integration of existing projects and Monotouch projects.
• Fast and efficient lightweight packaging for sqlite (not the performance bottleneck that caused the query because it was used).
• Provides a simple way to safely perform crud operations (that is, create, read, update, delete), and to execute a query with parameters by retrieving a strongly typed retrieval method, and to quickly return results.
• The interior contains a lightweight ORM reflector driver layer, allowing you to fully use your own defined data model.

Note: Sqlite-net does not provide the full implementation of ADO, nor does it provide the full driver of SQLite. If you need this, use Mono.Data.SQLite (that's what 13.1 describes) or csharp-sqlite.

Use NuGet to download the Sqlite-net package for free:

• Sqlite-net: Small capacity (latest 1.0.8 release only 143KB after decompression)
• Sqlitenetextensions: This is the Sqlite-net ORM expansion Pack (the latest version is 1.3.0), which supports 1-to-1, 1-N, N-to-1, n-to-N correlation operations.

However, the earlier version of Sqlite-net, written in C # 3.0, became useless because of the later improvements in SQLITE.NET-PCL.

(4) Realized with SQLITE.NET-PCL and SQLITE.NET.ASYNC-PCL

"Example 13-3" and "Example 13-4" in this chapter are all implemented in this way.

SQLITE.NET-PCL's improved package based on Sqlte-net (the latest version is the 3.1.1 version), which supports both synchronous and asynchronous operations, as well as support across multiple platforms such as Xamarin.android, Xamarin.ios (Classic), Xamarin.ios (Unified), Windows Phone 8.1, Win8, Win10 、...... such as

You do not need to download sqlte-net when using SQLITE.NET-PCL. However, in order to differentiate the original sqlte-net, the package is named SQLLITE.NET-PCL.

PCL is the abbreviation for the English "Portable Class Library", meaning: the library designed with it can run on Win8, Win10, Win32, Window Phone, Monotouch, Monoandroid 、...... and other platforms.

SQLITE.NET-PCL has made some changes to Sqlte-net's API. Specific changes to the place are:

• Modified the Sqliteconnection class so that it can span multiple platforms.
• Provides the sqliteasyncconnection to support asynchronous operations across multiple platforms. Note in this class, the program you write for the Task.run mode is managed by Sqliteasyncconnection.
• A serialization implementation of DateTime was modified so that it could support multiple regions. If you want to get a localized datetime, you need to call Datetime.tolocaltime ().

Overall, if you want to write across Android, IOS, Windows Phone 、...... And so on multiple platform database common operation class, it is recommended to use it to implement.

3. Other instructions

No matter what kind of database you use, and no matter what technology you adopt, always remember that the mobile app is a client-side program, and without the foundation of network programming it's hard to understand it completely. In particular, don't expect to plug the database directly into your phone in real-world projects, and you won't be confused about using a phone as a server to serve a lot of people. In actual large-scale applications, the database is a dedicated server (how many servers it takes to see the size of the application), and the client is only through the network indirectly to the database server to access the small amount of data needed in the phone. So, who is connecting directly through the network? is a service or interface that is exposed externally by programs in the application server, such as Web service, Web API 、...... such as

However, as an example, are the database also get to the phone or your own program, so you debug and understand convenient, add, delete, copy the database is also convenient, after all, is learning, play the stage, anyway, remember a word on the line: Lofty high-rise is not from the middle of a thin cover up, You only learn and understand its most basic usage, first learn to build a small PA house, and then study how to increase the height of the floor at 1.1 points. Ii. observing the packages referenced and downloaded by this project

1. Observe the referenced. dll file namespace

As of this chapter, the following references have been added to the Mydemos project:

2. packages.config file

Take a look at what packages the project has downloaded through NuGet in this chapter. The contents of the Packages.config file under the Mydemos project root directory are 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>
3. The sample 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 13 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 Basic usage of SQLite 2-Custom CursorAdapter", Desc = "Demonstrate how to use a custom CursorAdapter to access SQLite database"},
        new ChItem {type = typeof (ch1303MainActivity), Title = "Example 13-3 SQLite basic usage 3-SQLite.NET-PCL", Desc = "Demonstrate how to use SQLite.NET-PCL to access SQLite database"},
        new ChItem {type = typeof (ch1303MainActivity), Title = "Example 13-4 Basic usage of SQLite 4-SQLite.NET.Async-PCL", Desc = "Demonstrate how to use SQLite.NET.Async-PCL to access SQLite database"},
    }
});
[Android] 13.0 Chapter 13 Creating and Accessing SQLite Database—The Sample Main Interface of This Chapter