Use the first part of LINQ to SQL

Use LINQ to SQL (Part 1)

[Original address] using LINQ to SQL (Part 1)
[Original article publication date] Saturday, May 19,200 7 AM

In the past few months, I have written a series of posts covering some new features in Vs and. NET Framework orcas. Below are links to these posts:

• Automatic attributes, object initializing, and set initializing
• Extension Method
• Lambda expressions
• Query syntax
• Anonymous type

Help with the above language featuresData QueryBecome a concept of first-class programming. We call this general query Programming Model "LINQ" -- it refers. Net Language-level integrated query.

Developers can use LINQ on any data source. They can express efficient query behavior in the programming language they select, and choose to convert the query results or construct any format they want, and then operate the result set very conveniently. Languages with the ability to implement the LINQ function can provide full types of security and query expression compilation check, while development tools can provide full intelligent awareness and debugging when writing LINQ code, and rich refactoring support.

LINQ supports a rich range of extensibility models. This model will help generate different efficient computing factors (domain-specific operators) for different data sources ).. . NET Framework's orcas version will release some built-in libraries that provide support for object (objects), XML, and database-based LINQ.

What is LINQ to SQL?

LINQ to SQL is an O/RM (Object relationship ing) Implementation released along with. NET Framework orcas. It allows you to use. Net classes to model a relational database. Then you can use LINQ to query databases and update, insert, and delete data.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides a convenient way to integrate data verification and business logic rules into your data model.

Use LINQ to SQL to model a database:

Visual Studio orcas provides a LINQ to SQL designer, which provides a simple way to visually convert a database to a LINQ to SQL object model. In my next blog, I will go into more details about how to use the designer (you can watch the video I recorded in February about how to create the LINQ to SQL model from scratch ).

Through the LINQ to SQL designer, I can easily design the example model of the northwind database as follows:

Four entity classes are defined: product, category, order, and orderdetail. The attributes of each class are mapped to the fields of the corresponding table in the database. each instance of the class object represents a row of records in the data table.

In, the arrows between the four object classes represent the associations/relationships between different objects. They are mainly generated based on the primary key/foreign key relationship in the database. The arrow points on the designer indicate whether the relationship is one-to-one or one-to-many. Based on this, strong attributes will be added to the object class. For example, there is a one-to-multiple relationship between the category class and the product class above. This means that the category class will have a "Products" attribute, which represents the set of all product objects in this class. The product class will have a "category" attribute to point to an instance of the category class. The instance of this category class indicates the category of the product.

To the right of the SQL designer in the list of stored procedures that interact with our database model. In the above example, I added a "getproductsbycategory" stored procedure. It has a categoryid as the input parameter, and returns a product entity sequence as the result set. The following code shows how to call the stored procedure.

Understanding datacontext

When you click the "save" button on the LINQ to SQL designer, Visual Studio will save the classes we have created that represent the relationship between entities and databases. A custom datacontext class is also generated for each LINQ to SQL designer file added to our solution. This datacontext class is the main channel for us to query entities from the database or perform changes. The generated datacontext class will contain some attributes, which correspond to each data table created in the database as well as some methods, corresponding to each stored procedure we add.

For example, the northwinddatacontext class is generated based on the model we designed above:

Examples of code written using LINQ to SQL

After modeling our database with the LINQ to SQL designer, we can easily write code to operate the database. Below are some examples of code that demonstrate common database operations:

1) query products from the database

The following code uses the LINQ to SQL query syntax to obtain the ienumerable sequence of the product object. Note how the Code uses the product/Category relationship to identify only products with the "beverages" category:

C #:

VB:

2) update a product record in the database

The following code demonstrates how to query a single product record from the database, update its price, and save the changes to the database:

C #:

VB:

Note: VB does not support Lambda in orcas beta1. But in beta2, it will support-then the code will be more concise.

3) Insert a new category and two new products to the database.

The following code demonstrates how to generate a new category, then generate two products associated with the category, and then save the three records to the database.

Note that I do not need to manually maintain the primary/foreign key relationship. Instead, I only add two product records to the "Products" set of the category object, then, add the category object to the "categories" set of datacontext, and LINQ to SQL will know that the relationship between PK and FK will automatically last for me.

C #

VB:

4) Delete products from the database

The following code demonstrates how to delete all toys from the database:

C #:

VB:

5) Call the Stored Procedure

The following code demonstrates how to query the product entity by calling the "getproductsbycategory" stored procedure we added to the data model without using the LINQ query syntax. Note: Once I query the product result set, I can update/delete them, and then call dB. submitchanges () to submit these updates to the database.

C #:

VB:

6) query products by page on the server

The following code demonstrates how to use the LINQ query syntax to implement efficient paging queries for server-side databases. By using the following SKIP () and take () operators, we can query only 10 records starting from row 200 in the database:

C #:

VB:

Summary

LINQ to SQL provides a great and clean way to build a data layer for your application. Once you define a data model, you can query, insert, update, and delete it conveniently and effectively.

I hope that the above introduction and code examples will stimulate your appetite and learn more. In the next few weeks, I will discuss more specifically in this series about LINQ to SQL.

I hope this article will help you,

Scott