Xamarin.android using Sqliteopenhelper for database operations

First, preface

Making a network connection in a phone is not only time consuming but also draining power, while power consumption is deadly. So we need a database to help us store offline data so that users can use some of the app's features without the use of the network, and be prompted on the features that require network connectivity to let users decide whether to turn on the network. In this section we will learn how to access the database and provide basic additions and deletions, and make them as decoupled as possible.

Second, the database

Xamarin.android Create a local database in the same way as in Java , and we have to master the use of sqliteopenhelper, because this class simplifies the steps we take to create the data, Let's just focus on creating the tables in the database and manipulating the database version when it needs to be updated. Where we must implement the OnCreate method and the Onupgrade method, the OnCreate method executes only if the database does not exist, so it is not repeated. For example, the following code.

1         class Locationsqliteopenhelper:sqliteopenhelper 2         {3 public             override void OnCreate (Sqlitedatabase db ) 4             {5             } 6  7 public             override void Onupgrade (sqlitedatabase db, int oldversion, int newversion) 8
   
    {9             }10         }
   

But we also need to use the constructor of the parent class to specify the name of the database and the initial version. For example, the following code we will create a data named "Test", and the initial version is 1.

1         class Locationsqliteopenhelper:sqliteopenhelper2         {3 public             Locationsqliteopenhelper (context context ) 4                 : Base (context, "test", null,1) 5             {6             }7         }

Learn the above, we can create a database named Test , and the database contains a USER table (the SQLite database under the primary key needs to be an integer type, and is self-increasing).

1 Public     class Testsqliteopenhelper:sqliteopenhelper 2     {3 public         Testsqliteopenhelper (context context) 4             : Base (context, "Test", NULL, 1) 5         {6         } 7  8 public         override void OnCreate (Sqlitedatabase db) 9         {ten             db. Execsql ("CREATE TABLE USER (id INTEGER PRIMARY KEY not null,uname text not null,upwd text not NULL)");         }12         P ublic override void Onupgrade (sqlitedatabase db, int oldversion, int newversion) (+-             db). Execsql ("DROP TABLE IF EXISTS USER"),             OnCreate (db),         }18}

Creating the Database object, we can use this object to manipulate the database, first we need to initialize the database object in the OnCreate method in mainactivity .

Testsqliteopenhelper dbhelper = new Testsqliteopenhelper (this);

However, we cannot access the database directly using dbhelper , we must obtain the database Access object with its writabledatabase property or readabledatabase property. Writabledatabase can perform all operations on the database,readabledatabase can read the database. All of their return types are sqlitedatabase. So we have to get their objects as needed.

Sqlitedatabase db = Dbhelper.writabledatabase;

This allows us to operate with the Insert,Update,Query , and Delete of the DB, and of course we can use the execsql execute our SQL statement directly. Here we will describe the use of these methods.

1. Add (Insert)

The first is the definition of the method:

Public virtual long Insert (string table, String nullcolumnhack, contentvalues values);

Where the parameters have the following meanings:

Table: The names of the tables that need to be inserted.

nullcolumnhack: When values are empty or the value inside is empty, the database is not allowed to insert a blank line, if you need to insert a blank row, you need to specify a field name, so that when the situation occurs, the field will be set to null and then try to insert.

values: The data that needs to be inserted.

About the first two parameters is very simple do not introduce too much, if you want to introduce the last parameter, it is a contentvalues type, through which we can greatly simplify the concatenation of their own insert statements, such as the following we can set the value of the Uname field is YZF, The value of UPWD is 123.

1 contentvalues CV = new Contentvalues (); 2 cv. Put ("uname", "YZF"); 3 cv. Put ("Upwd", "123");

The key is the Put method, which has the following overloaded methods.

1 public void put (string key, bool value), 2 public void put (string key, byte[] value), 3 public void put (string key, double Value), 4 public void put (string key, float value), 5 public void put (string key, int value), 6 public void put (string key,  Long value); 7 public void put (string key, SByte value), 8 public void put (string key, short value), 9 public void put (string Key, string value);

With these overloaded methods we can insert different types of parameters, of course, we can also remove by removing the method, if we need to insert a null value for a field can use the putnull method, determine whether a field exists can be used ContainsKey method, the last is the corresponding to get the values of the different fields.

1 Public Object Get (string key); 2 public bool Getasboolean (string key); 3 Public sbyte Getasbyte (string key); 4 public byte[] Getasbytearray (string key); 5 Public double getasdouble (string key); 6 Public float Getasfloat (string key); 7 public int Getasinteger (string key); 8 Public long Getaslong (string key); 9 Public short Getasshort (string key), public string getasstring (string key);

After a brief introduction of the use of contentvalues , we will use it to add a piece of data, such as the following code will add a piece of data to the user table.

Long id = db. Insert ("User", NULL, CV);

The return value is the primary key for the inserted data.

2. Queries (query)

The first is the definition of the method:

1 public virtual ICursor Query (string table, string[] columns, string selection, string[] Selectionargs, string groupBy, S Tring having, string-by-clause); 2 public virtual ICursor Query (string table, string[] columns, string selection, string[] se Lectionargs, String groupBy, String having, string-by-clause, string limit), 3 public virtual icursor Query (bool distinct, ST Ring table, string[] columns, string selection, string[] Selectionargs, String groupBy, String having, string-by-clause, str ing limit);

Where the parameters have the following meanings:

table: Names of tables that need to be queried

columns: The field to get, if passing in null means getting all fields

selection: Conditional statements, where we can be practical "?" Placeholder as a parameter (different from the @ in SQL Server)

Selectionargs: Condition parameter, used to replace "?" in a query statement

groupBy: Grouping statements

having: grouping conditions

ORDER BY: Sort statement

Limit: A paging statement (such as "1,3" means to get 1th to 3rd data Total 3 Records)

Before inserting the data through insert , we can get it from the database by using the Query method, such as the following code

ICursor IC = db. Query ("User", new string[] {"id", "uname", "Upwd"}, "id =?", new string[] {ID. ToString ()}, NULL, NULL, NULL);

The method finally returns an object that implements the ICursor interface, which we can use to get the data, and then we get the username and password.

1 IC. Movetofirst (); 2 string uname = IC. GetString (IC. Getcolumnindex ("uname")); 3 String upwd = IC. GetString (IC. Getcolumnindex ("Upwd"));

Because icursor is for a result set, we need to navigate to the first data, so we take the Movetofirst method and get the parameters by GetString . But we also need to pass the location of a field, so we also need to use Getcolumnindex to get the location of the specified field name.

Here is an introduction to the ICursor method

Count: How many data gets

isafterlast: is currently after the last data

Isbeforefirst: is currently before the first data

IsClosed: Whether it is turned off

IsFirst: Is the first piece of data

islast: Is the last piece of data

Position: Current Location

Getcolumnindex: Gets the position based on the field name and returns 1 if the field does not exist

getcolumnname: Get field names based on location

Movetofirst: Move to the first piece of data

Movetofirst: Move to the last piece of data

MoveToNext: Move to the next piece of data

movetoposition: Move the specified position

movetoprevious: Move to previous data

Here's how to get the corresponding type of data based on location

getdouble,getfloat,GetInt,getlong,getshort,GetString

3. Updates (update)

The first is the definition of the method:

Update (string table, contentvalues values, String whereclause, string[] whereargs);

Where the parameters have the following meanings

table: tables where the data that needs to be updated

values: The value of the updated field

whereclause: Query statement

Whereargs: Parameters required in a query statement

With the help of inserting and querying data, we can update this data after inserting the data, and then get the data through query to see if the data changes.

1 contentvalues NCV = new Contentvalues (); 2 NCv. Put ("Uname", "Zn"); 3 NCv. Put ("Upwd", "456"); 4 db. Update ("User", NCV, "id =?", new string[] {ID. ToString ()});

The conditional statement here is similar to the one in the query, and then we look at the obtained data to see that the changes have indeed occurred.

4. Deletion (delete)

The first is the definition of the method:

public virtual int Delete (string table, String Whereclause, string[] whereargs);

The description of the parameter is the same as Update , so the practical way is not introduced here.

All the code for all instances is as follows:

TestSQLiteOpenHelper.cs

1 Public     class Testsqliteopenhelper:sqliteopenhelper 2     {3 public         Testsqliteopenhelper (context context) 4             : Base (context, "Test", NULL, 1) 5         {6         } 7  8 public         override void OnCreate (Sqlitedatabase db) 9         {ten             db. Execsql ("CREATE TABLE USER (id INTEGER PRIMARY KEY not null,uname text not null,upwd text not NULL)");         }12         P ublic override void Onupgrade (sqlitedatabase db, int oldversion, int newversion) (+-             db). Execsql ("DROP TABLE IF EXISTS USER"),             OnCreate (db),         }18}

In the OnCreate of mainactivity

1             testsqliteopenhelper dbhelper = new Testsqliteopenhelper (this); 2             sqlitedatabase db = Dbhelper.writabledatabase; 3              4             contentvalues cv = new Contentvalues (); 5             CV. Put ("uname", "YZF"); 6             CV. Put ("Upwd", "123"); 7             Long id = db. Insert ("User", NULL, CV); 8  9             contentvalues NCV = new Contentvalues ();             NCv. Put ("Uname", "Zn");             NCv. Put ("Upwd", "456");             Update ("User", NCV, "id =?", new string[] {ID. ToString ()});             icursor IC = db. Query ("User", new string[] {"id", "uname", "Upwd"}, "id =?", new string[] {ID. ToString ()}, NULL, NULL, NULL);             Movetofirst ();             string uname = ic. GetString (IC. Getcolumnindex ("uname")); the             string upwd = ic. GetString (IC. Getcolumnindex ("Upwd"));

Xamarin.android using Sqliteopenhelper for database operations