Scottgu blog-Part 2-Use custom SQL statements

Thanks to Dudu for bringing us a server with faster running speed! Celebrate the launch of the new server!

In the last few weeks, I wrote a series of posts that gave an overview of LINQ to SQL. LINQ to SQL is the implementation of O/RM (Object relationship ing) integrated in. Net framework3.5. It makes it easy for you to use the. NET class to generate a relational database model. Then you can query, update, insert, and delete it using a LINQ expression. Below are links to the first seven posts in my series:

• Part 1: Introduction to 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

In the first two posts (Part 6 and Part 7), I described how to use the LINQ to SQL data model to query and retrieve data through database stored procedures and user-defined functions. In today's blog, I will discuss how to use stored procedures to update, insert, and delete data.

Since I wrote those posts, one of the questions that many people have asked me is: "If I want to fully control the SQL expressions of LINQ to SQL, and I don't want to use stored procedures-what should I do?" Today's blog will talk about this issue-and discuss how to use custom SQL expressions to control your LINQ to SQL data model class, insert, update, and delete it.

Use a LINQ query expression together with LINQ to SQL

To complete the content to be described in today's post, let's assume that the following data model class has been defined for the northwind database using the LINQ to SQL ORM designer in vs2008: (Part 2 of Part 2 of this series describes how to use the designer to define this data model class):

 

In Part 3 of this series, I talked about how to use the new LINQ language supported in VB and C # to query the preceding data model class, and an object that represents the row/column relationship in the database is returned.

 

For example, we can add a "getproductsbycategory" Help method to the datacontext class of our data model. This method is used to query data from the database using the LINQ query and return a collection of product objects:

VB:

C #:

 

Once we define the LINQ help method, we can write the following code, use the help method of LINQ to retrieve the product and traverse the result:

VB:

 

When the "getproductsbycategory" method is called, LINQ to SQL orm runs dynamic SQL to retrieve product data. You can check in the LINQ to SQL debug visualizer to see how the LINQ expression is generated.

Using custom SQL queries with LINQ to SQL Use custom SQL queries together with LINQ to SQL

In the above example, we did not write any SQL code to query the database and retrieve strong product objects. Instead, the LINQ to SQL orm automatically translates the LINQ expression into an SQL statement and uses the generated SQL statement to operate the database.

However, what should we do if we want to completely control the SQL statements used to operate our database and do not want to make the SQL to SQL statements automatically generated for us? One way to achieve this is to use the stored procedure described in section 6 (Part 6) and 7 (Part 7) of this series. Another method is to use the "executequery" Help method in the datacontext base class and the custom SQL expression we provide.

Use the executequery Method

 

The executequery method uses the SQL query expression as a parameter, and a series of parameters can be used. With this function, we can execute the original SQL statements (including custom connections between multiple tables) on the database ).

What makes the executequery method really useful is how it allows you to declare the format of the value returned by the SQL expression. You can use either of the following methods: pass a type object as a parameter to the method, or use the generic-based version of this method.

For example, we can modify the original getproductsbycategory () to replace the executequery method, execute our original SQL expression to operate the database and return the "product" object:

VB:

C #:

 

Then we can call the getproductbycategory () Help method. The code can be exactly the same as the previous Code:

 

However, unlike the previous one, operations on the database are performed using custom SQL expressions instead of dynamic SQL statements generated by the LINQ expressions.

Update custom SQL expressions and Object Tracking

By default, when you use LINQ to SQL to retrieve data from a data model object, it will track all changes and update these changes when you update the object. If you call the "sbumitchanges ()" method in datacontext, it starts a transaction and updates all the changes to the database. In Part 4 of this series, I deeply explain this point.

One of the cool ones of executequery () is that it is fully involved in tracking and updating models. For example, we can write the following code to query products of a certain type and discount their prices by 10%:

Because we convert the result set of the getproductbycategory () method called by the executequery () method to the type of "product", the LINQ to SQL knows the product object returned by the trail. When we call the "submitchanges ()" method in the context object, they will be saved to the database.

Custom SQL expressions in a custom class

The executequery () method allows you to specify any class as the return value type of an SQL query. This class does not have to be generated using the LINQ to SQL ORM designer or implemented some custom interfaces-you can specify the old class to it as its return value type.

For example, I can define a new productsummary class, which contains a subset of the properties of the product class, includes the following attributes (note the new automatic attributes of C ):

 

Then, we can generate a getproductsummariesbycatetory () Help method that uses this class as the return value type in the northwinddatacontext. Note that the following SQL statement is used to request the executequery () method to automatically process the returned productsummary object only for the value of the property we need in the product:

 

Then we can execute this help method and use the following code to traverse the result set:

 

Custom SQL expressions used for insertion, update, and deletion

In addition to using custom SQL expressions for queries, we can execute them to insert, update, and delete the logic.

We can achieve this by using the following method: generate an appropriate local method for insertion, update, and deletion in a local class of datacontext. Then, use the executecommand method to write the SQL statement that we need to execute. For example, to overwrite the deletion method for the product class, we can define the following eproduct local method:

 

Then, if we write the following code to remove a specific product instance from the database, LINQ to SQL will call the deleteproduct Method -- this method will cause custom SQL statements to replace the automatically generated SQL statements:

  Summary

The system automatically generates and runs dynamic SQL statements to query, update, insert, and delete databases.

In some advanced scenarios, or when you want to fully control the executed SQL statements, you can still customize the ORM or use stored procedures, alternatively, you can use your own defined SQL expression to replace the original SQL statement automatically generated. This gives you great flexibility when creating and expanding your data layer.

In the next series of posts, I will describe some of the remaining LINQ to SQL concepts, including: Single-Table inheritance, latency/early loading, optimized concurrency conflicts, and handling multiple scenarios.

I hope this will help you,

Scott