Use LINQ to SQL

Part 1

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 released along with. NET Framework orcas.O/RM(Object relationship ing), which 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 on how to use this designer.

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 followingCodeIt 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 method for your application.ProgramTo create a data layer. 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.

Part 2: defining our data model classes

Part 3: querying our database

Part 4: updating our database

Part 5: Binding UI using the ASP: linqdatasource Control

Part 6: retrieving data using Stored Procedures

Part 7: updating our database using Stored Procedures

Part 8: executing custom SQL expressions

Part 9: using a custom LINQ expression with Control