LINQ to sqldatabase operations

Translate Part 4: updating our database of scottgu about LINQ to SQL. This post explains how to update a database using LINQ to SQL, and how to integrate the business logic and custom verification logic. The development environment is. NET Framework 3.5 beta2 and the development tool is Visual Studio 2008 beta2.

Use LINQ to SQL to model the northwind database

Before that, I learned how to use the LINQ to SQL designer. The following data model is used:

When using the LINQ to SQL designer to design the five defined classes (product, category, customer, order, and orderdetail, the attributes in each class are mapped to the columns in the corresponding database table, and the instances of each class represent a record in the database table. In addition, when defining data models, the LINQ to SQL designer will also create a custom datacontext class to serve as the main channel for database queries and application updates/changes. The datacontext class defined in the preceding data model is named "northwinddatacontext ". This class contains attributes that represent each modeling database table.

Using the LINQ syntax expression, you can easily use the northwinddatacontext class to query and retrieve data in the database. At run time, LINQ to SQL automatically converts the LINQ expression to the appropriate SQL code for execution. For example, write the following LINQ expression to retrieve a single product object based on the product name:

You can also use a LINQ expression to retrieve all products that do not exist in order details and whose unitprice is greater than 100:

Change tracking and datacontext. submitchanges ()

When you query and retrieve objects such as product instances, LINQ to SQL automatically keeps track of any changes or updates to these objects. We can perform queries of any number of times and update the datacontext class using LINQ to SQL, and all these changes will be tracked.

Note: The change tracking of LINQ to SQL occurs on the caller end-not in the database. This means that the use of tracking will not consume any database resources, nor need to change/install any component modules in the database.

After making changes to the objects retrieved from LINQ to SQL, we can call the submitchange () method on datacontext to apply the changes and return them to the database. This will cause the dynamic computation of LINQ to SQL and execute appropriate SQL code to update the database. For example, compile the following code to update unitprice and unitsinstock on the product whose product name is "chai" in the database:

When the northwind. submitchanges () method is called in the above Code, the LINQ to SQL will dynamically construct and execute an SQL "Update" code module that updates the property values of these two products.

In the following code, we will traverse the less popular and expensive products and set their reorderlevel attribute to 0:

When the northwind. submitchanges () method is called in the above Code, LINQ to SQL calculates and executes a set of appropriate update code modules to modify the products whose recorderlevel attribute has changed.

Note: If the property of a product does not change because it is specified, the object will not be considered to be changed, in addition, LINQ to SQL does not update the object back to the database. For example, if the unitprice of the "chai" object is still $2 and unitsinstock is still 4, it will not cause execution of any database update code module when submitchange () is called. Similarly, in the second example, only products whose recorderlevel is not 0 will be updated when submitchange () is called.

Insert and delete example

In addition to updating existing rows in the database, LINQ to SQL also supports data insertion and deletion. You can add/remove data objects from the datacontext table set and call the submitchange () method to insert or delete data objects in the database. The addition and removal operations are also tracked by the LINQ to SQL statement. When submitchange () is called, the insert or delete code module in the appropriate SQL statement is automatically executed.

Insert Product

You can create a new product instance to add a new product to the database, set its properties, and add it to the product set in the datacontext class:

When the northwind. submitchanges () method is called in the above Code, a new record is created in the product table of the database.

Delete Product

Similar to adding, you can remove a product from the product set in the datacontext class to indicate that you want to delete the corresponding records from the database:

In the above code, you can use LINQ to query and retrieve a series of products that will not be ordered, and then pass them to the removeall () method on the product set in the datacontext class. When the northwind. submitchanges () method is called in the above Code, these product records will be deleted from the product table in the database.

Update by link Association

A flexible o/R ing tool such as LINQ to SQL allows us to easily model data models through association between tables. For example, you can model each product to a category. Each order contains multiple orderdetail details, and each orderdetail detail is associated with a product, each customer has a group of associated orders.

These relational associations can be used in both query and update data. For example, write the following code to create a new product and associate it with an existing "beverages" category in a database:

Note how to add a product object to the product set of this category. In this way, the relationship between the two objects will be indicated, and the primary/foreign key relationship association between the two objects will be automatically maintained when the submitchange () method is called by LINQ to SQL.

Another example of how to manage cross-Table link association is provided by using another SQL statement. Let's take a look at how to create a new order for an existing customer. After setting orderdate, requiredate, and freight of order, create orderdetail related to product ordered by two customers, add it to order, and associate order with customer, finally, update all changes back to the database:

As we can see, the programming model for executing these jobs is very clear and object-oriented.

Transactions

Transactions are services provided by databases (or other resource managers) to ensure that a series of independent operations occur atomically. This means that they are either all successful, either all of them fail, and no changes will occur before these operations are automatically executed. When the sumbitchange () method on datacontext is called, these update operations are encapsulated into a transaction. This means that when multiple changes are executed, the database will never be in an inconsistent state-these changes are either saved together or no changes are saved.

Verification and business logic

One of the most important things to consider for data processing is how to integrate data verification and Business Rule logic. LINQ to SQL provides developers with a variety of ways to easily integrate these into the data model. The addition of the data validation logic ensures that the data model can be applied anytime and anywhere. This avoids repeated definitions in multiple places and ensures the maintainability and code cleanliness of the data model.

Architecture verification support

When you use the LINQ to SQL designer in Visual Studio 2008 to define a data model class, some validation rules inferred from the database table architecture will be appended by default. The data type of the attribute in the data model class will match the data type of the database architecture. This means that compilation errors will occur if you try to specify a Boolean value to decimal or implicitly convert the numeric type. If the column in the database can be null, the corresponding attribute created in the data model by using the LINQ to SQL designer will be of the nullable type. If you try to specify a null value for a property not marked as nullable, an exception is automatically thrown. Similarly, LINQ to SQL ensures that the identity/unique column values in the database are correctly verified.

Of course, you can also use the LINQ to SQL designer to override the verification settings of these default architecture drivers-but by default, We can automatically obtain them without additional work. As a result, the system automatically processes the escaped SQL values, so that you do not have to worry about SQL injection attacks.

Support for custom Property Verification

Architecture-driven data verification is only the first step for applications, but it is not enough for practical applications. Consider a situation in the northwind -- the phone attribute in the customer class, which is defined as the nvarchar type in the database. Developers can use the following code to update a valid phone number using LINQ to SQL:

 

However, you may encounter such questions in the application. For example, the following code is legal in terms of simple SQL architecture:

To prevent fake phone numbers from being added to the database, we can add a custom Property Verification rule to the customer data model class. Using the local class feature, you can simply add rules to verify the phone number. You only need to add a new local class defined by the following method to our project:

 

The above code utilizes the two features of LINQ to SQL:

1. Therefore, classes created through the LINQ to SQL designer are declared as local classes-this means that developers can easily add additional methods, attributes, and events to these classes. In this way, the data model class and datacontext class created through the LINQ to SQL designer can easily expand self-defined verification rules and additional custom auxiliary methods. There are no settings or subsequent Code requirements.

2. LINQ to SQL exposes some custom extensible points in the data model class and datacontext class to add verification logic before and after an event occurs. Many of these extensions use the new language features called "partial methods (local method.

In the preceding verification example, we use the onphonechanging method that will be executed when setting the phone attribute of the customer class. This method is used to verify the input. If the verification is successful, the input is returned from this method and the value is determined to be valid by LINQ to SQL, otherwise, an exception is generated in the verification method to prevent value assignment.

Support for custom object verification

Property-level verification is very effective for verifying independent properties in the data model class. However, you sometimes need to verify multiple attributes on an object at the same time. In this scenario, set the orderdate and requireddate attributes on the order object at the same time:

The above code is legal from the perspective of a simple SQL architecture-even if the specified delivery date is the day before the order release date is meaningless. The good news is that currently in beta2 (. NET Framework 3.5), LINQ to SQL can easily add custom entity-level validation rules to prevent similar errors. You can add a local class for the Order object class and implement the onvalidate () local method. This method is called when the object value is to be recorded in the database. You can use this verification method to access and verify all data model class attributes:

 

With this verification method, we can check any attribute values in the object class (even if only read-only access is obtained for its management object). If the verification value is incorrect, an exception may occur. Any exceptions generated from the onvalidate () method cause the database to exit from the update operation and roll back the changes in the transaction where the operation is located.

Validation of custom object insertion/update/deletion Methods

In practical applications, we sometimes need to add verification logic for specific insertion, update, or deletion scenarios. In beta2 (. in the. NET Framework 3.5), you can add local types to extend the datacontext class, and then implement local methods to customize the insertion, update, and deletion logics for Data Model entities. When you call the submitchanges () methods in the datacontext class, these methods are automatically called.

After you use these methods to add the appropriate verification logic, if the verification passes, you will be notified to the LINQ to SQL to continue the database update operation (by calling the "executedynamicxyz" method in the datacontext class ):

After you add the preceding methods as appropriate, these methods are automatically called for any creation, update, and deletion operations on the data object. For example, to consider such a scenario, create a new order and associate it with an existing customer:

 

When you call northwind. submitchanges () in the preceding sample code, LINQ to SQL checks whether a new order object needs to be inserted and automatically calls the insertorder local method.

Advanced: View entity changes for transactions

In practical applications, you cannot simply add verification logic by looking at individual insert/update/insert operations. Instead, you should view a list of changes to the entity Operations that occur in transactions. Starting from. NET Framework 3.5 beta2, we can access the change list by calling the public datacontext. getchangeset () method. This method returns a Changeset object that exposes each added, modified, and removed operation.

Another alternative method is to write a subclass of datacontext and override the submitchanges () method. Then you can retrieve Changeset for the update operation and perform any custom data verification:

Use optimistic concurrent execution to handle simultaneous changes

One of the things that developers need to consider in a multi-user database system is how to handle the simultaneous updates of data in the database. For example, if two users retrieve the same product object in the same application, one user updates recorderlevel to 0, and the other updates to 1. If both users attempt to save product updates to the database, developers need to decide how to deal with change conflicts.

One way is to "win the later Updater"-this means that the value submitted by the first user will be lost without being noticed by major users. This is generally considered a manifestation of lack of application development experience.

Another method supported by LINQ to SQL is to use the optimistic concurrent execution model-how to execute other update operations on the original values in the database in advance, LINQ to SQL automatically detects locations. LINQ to SQL provides developers with a list of conflicting values to change, and can reconcile their differences, or notify end users of applications through the UI to determine how to handle them.

Use stored procedures or custom SQL logic for insert/update/delete scenarios

The good news for database developers is that LINQ to SQL provides a flexible development model that allows developers to overwrite dynamic SQL automatically executed through LINQ to SQL, instead of calling custom insert, update, and delete stored procedures.

At the beginning, we can define the data model and use LINQ to SQL to automatically process insertion, update, deletion, and other logic. Later, you can customize the stored procedure or SQL used to update the data model. You do not need to make any changes to the application logic using the data model, there is no need to make any changes to the corresponding data verification or business rule logic. This provides a lot of flexibility for application construction.

Summary

In addition to general insert, update, delete operations, and data verification, we should also pay attention to the following points through this post:

1. Be good at using association between tables for data update

2. Application Levels of data verification and business logic verification include architecture level, entity level, attribute level, update operation verification, and transaction-based verification.

3. LINQ to SQL can also effectively handle SQL injection attacks

4. LINQ to SQL can also automatically process transactions.

5. Use the optimistic concurrent execution model to handle update conflicts in the LINQ to SQL statements.