SQLite. net orm and androidsqliteorm of Xamarin. Android

SQLite. net orm and androidsqliteorm of Xamarin. Android
I. Preface

Through the study of "SQLiteOpenHelper of Xamarin. Android" and "ContentProvider of Xamarin. Android", we have learned how to use database operations specific to this platform. However, this is against the cross-platform interface declared by Xamarin, because we need to write different code for different platforms, and this chapter uses the open-source project SQLite on Github. NET to solve this problem, so as to implement cross-platform, reduce code duplication.

For details about this open-source project, click me

 

2. Preparations

Because this library is large, we only need one of them. the cs file is enough, so I have encapsulated a library, which not only contains this library, but also contains cross-platform network connections.

Click to download

Because the Library also uses a json library, the reader also needs to download and reference

Click to download

 

Create an Android Application Project (. net 4.0) and set the SDK version to 14 (Android 4.0). Now we can start to learn about it.

 

Iii. Text

 

1. Reference namespace

OpenMainActivityClass and add the following reference to the top

1 using NSCPAndroid.SQLite;

 

 

2. Create a ing Class

Those who have some development experience will be familiar with ORM. What I usually use most isEF,Castle Active Record. Of course there are many other better frameworks. It doesn't matter if you are not familiar with it. Next we just create a new class, and the structure of this class corresponds to the table in the database. For example, the author createsStockTable:

1 [Table("Stock")]2 public class StockTable3 {4      [PrimaryKey, AutoIncrement, Column("_id")]5      public int Id { get; set; }6 7      [MaxLength(8)]8      public string Symbol { get; set; }9 }

Here, we can see that the Table annotation attribute indicates that this class corresponds to the Table name in the database. Do not mistakenly assume that the primary key is the primary key in the following two fields according to the class name, because SQLite requires that the primary key must be maintained by itself, so here we use the PrimaryKey annotation attribute to indicate that this attribute is the primary key, AutoIncrement indicates that this primary key is auto-incrementing, 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 MaxLength to indicate that the field can be saved up to eight characters.

For more annotation attributes, see the following introduction.

[PrimaryKey]: Indicates the primary key of the table. It can only be used for Int type attributes.

[AutoIncrement]: Used for attributes that require auto-increment. This field is auto-incrementing every time a new data is inserted. It can only be used for Int type.

[Column (name)]: Indicates the name of the field corresponding to the specified attribute in the table. If this attribute is not added, the field name in the table is the same as the attribute name.

[Table (name)]: Indicates the name of the table corresponding to the specified class to the database. If this attribute is not added, the table name in the database is the same as the name of this class.

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

[Ignore]: Ignore this attribute, so that the corresponding fields are not generated in the table.

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

 

3. Create a database connection

You must specify the path to store the database and open the database. If the database does not exist, the file is automatically created. The following code retrieves the database path and opens the database:

1 string dbPath = Path.Combine(System.Environment.2 GetFolderPath(System.Environment.SpecialFolder.Personal),"ormdemo.db3");3 var db = new SQLiteConnection(dbPath);

 

Note that we useSQLiteConnectionOpen the database, insteadSqlConnection. After the database is successfully opened, we can easily complete the remaining work by using the database. So far, we have created only an empty database with no tables in it.

 

4. Create a table

In step 2, we have created a table structure. Next we will create a corresponding table in the database. We only need to use the following code to easily create a table:

1 db.CreateTable<StockTable>();

You can also use the following method:

1 db.CreateTable(typeof(StockTable));

After the creation, the table is still empty. Therefore, we need to insert several data records to introduce other operations.

 

Note: in practice, some people may want to find out how to determine whether the table has been created. In fact, there is no need to find it. Even if you perform this operation repeatedly, it will not affect anything, once a table is created, it will not be re-created after execution.

 

5. Insert data

Here we createStockTableFor example, the following code inserts a data entry into the database:

1 var newStock = new StockTable();2 newStock.Symbol = "First";3 db.Insert(newStock);

Is it very simple, just need to callInsertThis method also returns the primary key of the inserted data. But this is just to insert a piece of data. What if we need to insert multiple pieces of data? Of course not usableForeach, Another method is provided.InsertAllFor example, the following example inserts multiple data records at the same time:

 1 List<StockTable> stocks = new List<StockTable> 2 { 3     new StockTable{ 4             Symbol = "First" 5     }, 6     new StockTable{ 7             Symbol = "Second" 8     } 9 };10 db.InsertAll(stocks);

If you need to verify whether the table is inserted, you can obtain the data of the table, for example, the following code:

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

Now that the table has data, we need to query it.

 

6. query data

First, we will introduce the simplest query method, that is, directly obtaining the query by id. Of course, we do not need to splice any SQL statements. We only need to use the following code to obtain the data with id 1:

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

 

 

However, in most cases, we need to use conditional statements to query data, so we need to use SQL statements. For example, in the following query statement, we will query data starting with "F" with Symbol:

1 var items = db.Query<StockTable>(" select * from Stock where Symbol like ? ", "F%");

 

 

I believe that those who are more interested in technology will surely knowLinqIt frees us from the embarrassment of splicing SQL statements, making queries simple and interesting (For complex queries, you still need to use SQL, or even stored procedures.), Of course, we can still use this feature here. Next we will rewrite the above CodeLinq:

1 var items = (from item in db.Table<StockTable>()2                 where item.Symbol.StartsWith("F")3                 select item).GetEnumerator();

I usedGetEnumeratorIs not required in actual use. If you prefer the most primitive query, you can useExecuteScalarMethod.

 

Note: the query of linq is a delayed query, which means that the query results are not queried at the moment, but are not queried until you use it.

 

Recommended:

1. Jesse Liu's essay "Happy lambda expressions", I personally feel very interesting and useful.

2. Article on how to implement dynamic query in Linq: dynamic query can be considered as a part of the SQL statement that concatenates a string with a string. The purpose of this article is to solve the problem in some special cases.

As data query ends, we will learn how to update and delete the remaining two operations.

 

7. Update and delete data

First, update the data because the operation is the same as inserting data.UpdateAndUpdateAllOnly the primary key of the data requires data. Otherwise, you cannot locate the data to be updated. The following describes how to Update the data using Update:

1 var result = (from item in db.Table<StockTable>()2                   where item.Symbol.StartsWith("F")3                   select item).First();4 result.Symbol = "Third";5 db.Update(result);6 7 result = db.Get<StockTable>(result.Id);

Since it is an update, you must first obtain a piece of data and then update it, and finally take it out from the database to determine whether the update is successful. Next we will continue the deletion operation:

1 var result = (from item in db.Table<StockTable>()2           where item.Symbol.StartsWith("T")3           select item).First();4 5 db.Delete<StockTable>(result.Id);

If you want to delete the data of the entire table, you only need to useDeleteAllOf course, these operations are limited, so we still need to support the SQL method, so we can useExecuteYou can.

 

Finally, I would like to give you a favorite ORM, which is used for website development.

 

What is the aspnet framework? In addition to the mvc three-layer nhib.pdf, there are other well-developed orm or cmsl recommendations.

MVC (Modal View Controler) originally exists in the Desktop program, M is the exponential data model, V is the user interface, and C is the controller. The purpose of using MVC is to separate the implementation code of M and V, so that the same program can use different expressions. For example, you can use a column chart or pie chart to represent a batch of statistical data. The purpose of C is to ensure synchronization between M and V. Once M changes, V should be updated synchronously.

Model-View-controller (MVC) is a software design model invented by Xerox PARC for programming language Smalltalk-80 in 1980s. It has been widely used. Sun's J2EE platform design model has been recommended in recent years and is welcomed by more and more developers who use ColdFusion and PHP. Model-View-controller mode is a useful toolbox, which has many advantages but also has some disadvantages.

How MVC works

MVC is a design pattern that forcibly separates the input, processing, and output of an application. MVC applications are divided into three core components: model, view, and controller. They process their own tasks.

View
A view is the interface on which the user sees and interacts with it. For older Web applications, a view is an interface composed of HTML elements. In New Web applications, HTML still plays an important role in the view, however, some new technologies have emerged, including Macromedia Flash and some identification languages such as XHTML, XML/XSL, and WML and Web services.

How to process the application interface becomes increasingly challenging. One major benefit of MVC is that it can process many different views for your applications. In fact, there is no real processing in the view. Whether the data is stored online or an employee list, as a view, it is just a way to output data and allow users to manipulate it.

Model
The model represents enterprise data and business rules. Among the three components of MVC, the model has the most processing tasks. For example, it may use component objects such as EJBs and ColdFusion Components to process databases. The data returned by the model is neutral, that is, the model has nothing to do with the data format, so that a model can provide data for multiple views. Because the Code applied to the model can be reused by multiple views only once, code duplication is reduced.

Controller
The Controller accepts user input and calls models and views to fulfill user requirements. Therefore, when you click a hyperlink on a Web page and send an HTML form, the controller does not output anything or process anything. It only receives the request and determines which model component is called to process the request, and then determines which view is used to display the data returned by the model for processing.

Now let's summarize the MVC processing process. First, the Controller receives user requests and decides which model should be called for processing. Then, the model uses the business logic to process user requests and return data, finally, the controller uses the corresponding view to format the data returned by the model and presents it to the user through the presentation layer.
Why use MVC?

Most Web applications are created using procedural languages such as ASP, PHP, and CFML. They mix data-Layer Code such as database query statements and presentation-Layer Code such as HTML. Experienced developers will separate the data from the presentation layer, but this is usually not easy to do. It requires careful planning and continuous attempts. MVC strictly separates them. Although constructing an MVC application requires some additional work, the benefits it brings to us are unquestionable.

First, the most important thing is that multiple views can share a model. As I mentioned, you need to access your application in more and more ways. In this regard, one solution is to use MVC, whether your users want the Flash interface or WAP interface, they can be processed using a model. Since you have separated Data from business rules from the presentation layer, You can reuse your code to the maximum extent.

Because the data returned by the model is not formatted, the same component can be used on different interfaces. For example, a lot of data may be expressed in HTML, but they can also be... the remaining full text>

ORM in aspnet

Orm is a synonym of the Five-body projection.
Orz haha
Orm is a persistent layer framework
This interface is used to access databases. It maps relational algebra databases to object-oriented databases and uses object-oriented languages to perform operations such as addition, deletion, and modification of databases. -- Personal understanding
Generally, orm is implemented through reflection, but reflection and orm are two different things.
Reflection is A. net feature rather than a framework.