Windows Phone local database (sqlce): 2. LINQ to SQL)

A newbie recently wants to learn about the Windows Phone database and find some short tutorials. Because it is in English, it is translated by the way. The English level is not good. It is estimated that there are many mistakes in the text. If you have any children's shoes that are unfortunately read, please keep in doubt about the translation quality and give me more advice.

 

 

This is the original address: http://windowsphonegeek.com/tips/Windows-Phone-Mango-Local-Database-SQL-CE--Linq-to-SQL

The text is as follows:

This is the second article in the Windows Phone mango local database (sqlce) series. To get you started using databases in Windows Phone mango, this series of short clips will cover all the things you need to know. I will talk about the related LINQ to SQL when using a local database in Windows Phone mango.

This series includes the following parts:

• Windows Phone mango local database (SQL CE): Introduction
• Windows Phone mango local database (SQL ce): LINQ to SQL
• Windows Phone mango local database (SQL CE): [Table] attribute
• Windows Phone mango local database (SQL CE): [column] attribute
• Windows Phone mango local database (SQL CE): [Association] attribute
• Windows Phone mango local database (SQL CE): [Index] attribute
• Windows Phone mango local database (SQL CE): database mapping
• Windows Phone mango local database (SQL CE): datacontext
• Windows Phone mango local database (SQL CE): connection strings
• Windows Phone mango local database (SQL CE): creating the database
• Windows Phone mango local database (SQL CE): database queries with LINQ
• Windows Phone mango local database (SQL CE): how to insert data
• Windows Phone mango local database (SQL CE): How to update data
• Windows Phone mango local database (SQL CE): How to delete data

First of all, it should be mentioned that the basic database function in Windows Phone 7.1 is an implementation of SQL compact on mango, and uses LINQ to SQL to access data stored in the database. 1. What is LINQ to SQL?

LINQ to SQL is An ORM (Object link ing) framework that is part of the. NET Framework. It allows you to map your business object (model class) to tables in the database, and then you can access or query data in the database without writing a single SQL statement. With the update of mango, LINQ to SQL can now be used for Windows Phone.

You can use LINQ to SQL: 1. Map your Business Object) to table 2 in the database, use LINQ to query the database 3, use the API of LINQ to SQL to insert data to the database 4, use the API of LINQ to SQL to update data 5, use although Windows Phone supports most of the features of LINQ to SQL, but it also has some limitations. You can take a look at the complete msdn document http://msdn.microsoft.com/zh-cn/library/hh202872 (V = vs.92). aspx. Some of the following are provided: 1. executecommand is not supported: Windows Phone does not support executing the original Transact-SQL, Data Definition Language (DDL), or Data Modeling Language (DML) statements. 2. ADO is not supported. net (for example, datareader): All data queried by LINQ to SQL will be returned in the type set specified by datacontext. 3. Only Microsoft SQL Server compact edition (SQL CE) data types are supported: sqlce is the basic database technology for local databases. For a complete list of sqlce data types, see http://msdn.microsoft.com/zh-cn/library/ms172424 (V = SQL .110). aspx

4. Table. ilistsource. getlist is not supported. to bind all content to a table, query the entire table and bind it to the query. Use business logic to process insertion and deletion. 5. binaryformatter (binary type) is not supported. to convert a custom data type to an SQL Server binary or varbinary data type, you can use the data context attribute to implement the following functions, or it can be byte [] or system. data. LINQ. binary type. To implement LINQ to SQL customtype, first create a custom class that implements tostring () and parse (), and then use the class as an attribute in your data context. LINQ mtype can be mapped from LINQ mtype to any SQL Server String type, such as char, nchar, nvarchar, text, and XML. 6. Take () must use a constant value in a LINQ query: SQL ce does not support using the query value in a Transact-SQL top session. If you want to use a variable value in the take method, calculate this value in other queries that do not use the take method. 7. Skip () and take () must use the sorting list: these methods depend on the sorting to return consistent results. 2. How does it work?

In short, LINQ is a set of extension methods that allow you to use special query syntax in C # Or VB to query data in the database. The basic query syntax is a convenient declarative shorthand for using standard LINQ query operators to represent queries. Before you start, you need to know some basic things:

1. The LINQ to SQL query expression usually starts with the "from" clause, end with "select" clause 2. "from" clause indicates the data you want to query (you usually query data from collection/datacontext) 3. The "select" clause indicates the data you want to return, what format should it be? 4. Whenever you want to filter data, you can use the "where" clause. 5. Sort data using the "orderby" clause. Note: LINQ to SQL completes all filtering and sorting at the database layer, which makes it very efficient. To this end, you can write a LINQ query in the C # code, which will be automatically translated into an SQL statement, and then the SQL runtime will execute the statement. Therefore, when you execute the following LINQ Query

1 var query = from p in context.Persons where p.Age > 18 select p;

Before actual execution, the LINQ to SQL runtime will automatically translate it into the following SQL query

 
1 SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName], [t0].[Age] 
2 FROM [dbo].[People] AS [t0] 
3 WHERE [t0].[Age] > @p0

Note: For more information about the complete LINQ syntax you can take a look at the official documentation: http://msdn.microsoft.com/zh-cn/library/bb308959.aspx

In this article, I talked about how to use LINQ to SQL in the Windows Phone mango local database. Continue to pay attention to the following articles.

 

This is the original address: http://windowsphonegeek.com/tips/Windows-Phone-Mango-Local-Database-SQL-CE--Linq-to-SQL

The text is as follows:

This is the second article in the Windows Phone mango local database (sqlce) series. To get you started using databases in Windows Phone mango, this series of short clips will cover all the things you need to know. I will talk about the related LINQ to SQL when using a local database in Windows Phone mango.

This series includes the following parts:

• Windows Phone mango local database (SQL CE): Introduction
• Windows Phone mango local database (SQL ce): LINQ to SQL
• Windows Phone mango local database (SQL CE): [Table] attribute
• Windows Phone mango local database (SQL CE): [column] attribute
• Windows Phone mango local database (SQL CE): [Association] attribute
• Windows Phone mango local database (SQL CE): [Index] attribute
• Windows Phone mango local database (SQL CE): database mapping
• Windows Phone mango local database (SQL CE): datacontext
• Windows Phone mango local database (SQL CE): connection strings
• Windows Phone mango local database (SQL CE): creating the database
• Windows Phone mango local database (SQL CE): database queries with LINQ
• Windows Phone mango local database (SQL CE): how to insert data
• Windows Phone mango local database (SQL CE): How to update data
• Windows Phone mango local database (SQL CE): How to delete data

First of all, it should be mentioned that the basic database function in Windows Phone 7.1 is an implementation of SQL compact on mango, and uses LINQ to SQL to access data stored in the database. 1. What is LINQ to SQL?

LINQ to SQL is An ORM (Object link ing) framework that is part of the. NET Framework. It allows you to map your business object (model class) to tables in the database, and then you can access or query data in the database without writing a single SQL statement. With the update of mango, LINQ to SQL can now be used for Windows Phone.

You can use LINQ to SQL: 1. Map your Business Object) to table 2 in the database, use LINQ to query the database 3, use the API of LINQ to SQL to insert data to the database 4, use the API of LINQ to SQL to update data 5, use although Windows Phone supports most of the features of LINQ to SQL, but it also has some limitations. You can take a look at the complete msdn document http://msdn.microsoft.com/zh-cn/library/hh202872 (V = vs.92). aspx. Some of the following are provided: 1. executecommand is not supported: Windows Phone does not support executing the original Transact-SQL, Data Definition Language (DDL), or Data Modeling Language (DML) statements. 2. ADO is not supported. net (for example, datareader): All data queried by LINQ to SQL will be returned in the type set specified by datacontext. 3. Only Microsoft SQL Server compact edition (SQL CE) data types are supported: sqlce is the basic database technology for local databases. For a complete list of sqlce data types, see http://msdn.microsoft.com/zh-cn/library/ms172424 (V = SQL .110). aspx

4. Table. ilistsource. getlist is not supported. to bind all content to a table, query the entire table and bind it to the query. Use business logic to process insertion and deletion. 5. binaryformatter (binary type) is not supported. to convert a custom data type to an SQL Server binary or varbinary data type, you can use the data context attribute to implement the following functions, or it can be byte [] or system. data. LINQ. binary type. To implement LINQ to SQL customtype, first create a custom class that implements tostring () and parse (), and then use the class as an attribute in your data context. LINQ mtype can be mapped from LINQ mtype to any SQL Server String type, such as char, nchar, nvarchar, text, and XML. 6. Take () must use a constant value in a LINQ query: SQL ce does not support using the query value in a Transact-SQL top session. If you want to use a variable value in the take method, calculate this value in other queries that do not use the take method. 7. Skip () and take () must use the sorting list: these methods depend on the sorting to return consistent results. 2. How does it work?

In short, LINQ is a set of extension methods that allow you to use special query syntax in C # Or VB to query data in the database. The basic query syntax is a convenient declarative shorthand for using standard LINQ query operators to represent queries. Before you start, you need to know some basic things:

1. The LINQ to SQL query expression usually starts with the "from" clause, end with "select" clause 2. "from" clause indicates the data you want to query (you usually query data from collection/datacontext) 3. The "select" clause indicates the data you want to return, what format should it be? 4. Whenever you want to filter data, you can use the "where" clause. 5. Sort data using the "orderby" clause. Note: LINQ to SQL completes all filtering and sorting at the database layer, which makes it very efficient. To this end, you can write a LINQ query in the C # code, which will be automatically translated into an SQL statement, and then the SQL runtime will execute the statement. Therefore, when you execute the following LINQ Query

1 var query = from p in context.Persons where p.Age > 18 select p;

Before actual execution, the LINQ to SQL runtime will automatically translate it into the following SQL query

1 SELECT [t0].[ID], [t0].[FirstName], [t0].[LastName], [t0].[Age] 
2 FROM [dbo].[People] AS [t0] 
3 WHERE [t0].[Age] > @p0

Note: For more information about the complete LINQ syntax you can take a look at the official documentation: http://msdn.microsoft.com/zh-cn/library/bb308959.aspx

In this article, I talked about how to use LINQ to SQL in the Windows Phone mango local database. Continue to pay attention to the following articles.