Xamarin.android's Sqlite.net ORM

First, preface

Through the sqliteopenhelper of Xamarin.android and the contentprovider of xamarin.android, we have mastered how to use database operations that are specific to that platform. But this contradicts the cross-platform that Xamarin claims, because we need to write different code for different platforms, and this chapter will use the Open source project Sqlite.net on GitHub to solve this problem, so that you can achieve cross-platform and reduce code duplication.

For the Open source project please point me

Ii. preparatory work

Because this library is very large, and we only need one of the. cs file on it, so the author here has encapsulated a library, which not only contains the library, but also contains a cross-platform network connection.

Click I download

Because the library also uses a JSON library, the reader also downloads and references

Click I download

Then create a new Android Application project (. NET 4.0) and the SDK version is set to (Android 4.0), and we're ready to start a formal study.

Third, the text

1. Referencing namespaces

Open the mainactivity class and add the following reference at the top

1 using Nscpandroid.sqlite;

2. Create a mapping class

Have a certain development experience of people will be very familiar with ORM, I usually use the most is EF,Castle Active Record. There are, of course, many other better frameworks. Not familiar with the person also has no relationship, below we just create a new class, and the structure of this class is completely corresponding to the table in the database. For example, here I create a table called Stock :

1[Table ("Stock")]2  Public classstocktable3 {4[PrimaryKey, AutoIncrement, Column ("_id")]5       Public intId {Get;Set; }6 7[MaxLength (8)]8       Public stringSymbol {Get;Set; }9}

Where we can see this annotation property of table, meaning that this class corresponds to the table name in the database. Readers should not be mistaken for the class name, the following is the two fields where the key is the primary key, because SQLite rules the primary key must be maintained by itself, so here we use the PrimaryKey annotation property to indicate that the property is the primary key, autoincrement that the primary key is self-increment, Finally, column indicates that the field corresponds to the field name of the table (the name must be _id), the following is our own field, the author uses the MaxLength to indicate that the field can hold up to eight characters.

For more annotation properties, see the introduction below.

[PrimaryKey]: Represents the primary key of the table and can only be used for attributes of type int.

[AutoIncrement]: For properties that require self-increment, each time a new data is inserted the field is self-increment and can only be used for the int type.

[Column]: Used to indicate the name of the field specified in the table, and the field name in the table is the same as the property name if the attribute is not added.

[table]: Used to indicate that the specified class corresponds to a table name in the database, and if the attribute is not added, the table name in the database is the same as the class name.

[MaxLength (value)]: Used to limit the maximum length of characters a field can hold.

[Ignore]: Indicates that the property is ignored, so that the corresponding field is not generated in the table.

[Unique]: Indicates that the value of the property must be unique in the table.

3. Create a database connection

We need to specify the path that the database is saved in and open the database as well. If the database does not exist, the file is created automatically. The following code we will get the path to the database and open the database:

1 string DbPath = path.combine (system.environment. 2 GetFolderPath (System.Environment.SpecialFolder.Personal),"ormdemo.db3" ); 3 var New Sqliteconnection (DbPath);

Note here that we are using sqliteconnection to open the database instead of SqlConnection. The rest of the work after the successful opening of the database we only need to use the DB can be done easily, so far we have only created an empty database, there is no table.

4. Create a table

2nd step we have created a table structure, below we will create the corresponding table in the database, we just need to use the following code to easily create a table:

1 db. Createtable<stocktable> ();

Or the following is the same way you can:

1 db. CreateTable (typeof(stocktable));

The table is empty after it is created, so we need to insert a few data in order to introduce other actions.

Note: The actual use of someone will be looking for how to determine whether the table has been created method, in fact, there is no need to find, even if the operation is repeated, it will not affect what, once created, the table will not be re-created after the execution.

5. Inserting data

Here we directly insert into the database by creating an instance of stocktable , such as the following code we will insert a piece of data:

1 var New stocktable (); 2 "  First " ; 3 db. Insert (Newstock);

is not very simple, just need to call insert , the method also returns the primary key of the inserted data. But this is just inserting a piece of data, what if we need to insert more than one piece of data? Of course it is not possible to use foreach, which already provides another method Insertall, such as the following example will insert multiple data simultaneously:

1List<stocktable> stocks =NewList<stocktable>2 {3     Newstocktable{4Symbol =" First"5     },6     Newstocktable{7Symbol ="Second"8     }9 };TenDb. Insertall (stocks);

If the reader needs to verify that it is plugged in, we can get how much data the table has, such as the following code:

1 int count = db. Table<stocktable> (). Count ();

Now that the table already has the data, below we need to carry on the inquiry.

6. Querying data

First of all, the simplest way to query is to rely on the ID to get directly. Of course, we do not need to splice any SQL, just through the following code to get the ID 1 data:

1 var item = db. Get<stocktable> (1);

But most of the cases we need to query through conditional statements, so we need to use SQL statements, such as the following query statement, we will query the symbol beginning with "F" data:

1 var items = db. Query<stocktable> ("" "f%");

Believe that those who pursue the technology will certainly know that Linq, it let us get rid of the embarrassment of splicing SQL statements, make the query become simple and interesting ( complex query still need to use SQL, even stored procedures, etc. ), Of course we can still use this feature here, and we'll rewrite the code above into Linq:

1 var items = ( from in Db. Table<stocktable>()2                 where item. Symbol.startswith ("F")3                 Select Item). GetEnumerator ();

In order to be able to view the results faster, so the use of GetEnumerator, the actual use does not necessarily need. The executescalar method can be used if the reader likes the most primitive queries.

Note: LINQ queries are deferred queries, meaning that they are not queried at the moment of the query, but rather wait for you to use it.

Recommended:

1.Jesse Liu's essay, "Happy lambda expression," is a very interesting and useful personal feeling.

2. about how LINQ implements dynamic query article points I, a dynamic query can be thought of as a string stitching LINQ part of the statement, the purpose is to solve the use of certain special occasions.

About the query data to this end, we will learn the remaining two actions are update and delete.

7. Updating and deleting data

First we will update the data, because its operation is the same as the insertion of data is the presence of update and UpdateAll, only the primary key of the data need to have data, otherwise can not be positioned to update which data, We'll show you how to update the data with update:

1 varresult = ( fromIteminchDb. Table<stocktable>()2                   whereItem. Symbol.startswith ("F")3                   Selectitem). First ();4Result. Symbol ="Third";5 db. Update (result);6 7result = db. Get<stocktable> (result. ID);

Since the update is, of course, you need to get a piece of data and update it, and finally take it out of the database to determine if it has been updated successfully. Here we proceed with the delete operation:

1 var result = ( from in Db. Table<stocktable>()2           where item. Symbol.startswith ("T")3           Select  Item). First (); 4 5 db. Delete<stocktable> (result. ID);

If you need to delete the entire table data only need to use DeleteAll , of course, these operations are limited, so we also need to support the SQL method, then we can use Execute .

The final result for everyone a I prefer ORM, used in the development of the Web site

Xamarin.android's Sqlite.net ORM