ASP. NET 3.5 core programming learning notes (23): LINQ-to-SQL data updates, transactions, stored procedures, and functions

Once an object is loaded into the memory, it is very easy to insert a new order. It is nothing more than creating an order class instance and adding it to the orders set of the customer object. LINQ-to-SQL will track changes in the data context and send those changes back to the database when the submitchanges of the data context object is called. The following example shows a data change process:

String id = "alfki ";
Customer c = datacontext. Customers. singleordefault (C => C. customerid = ID );
If (C! = NULL)
{
// Modify data
C. Address = "123 flowers Streat );
// Submit the changes to the database
Datacontext. submitchanges ();
}

Add and delete objects

The data context object will automatically track the objects added to the context and track the objects that have been updated, deleted, or added. Calling the submitchanges method instructs the data context class to convert the pending changes to the update statement for the underlying database.

Example of adding a new record:

Customer customer = new Customer();
customer.CustomerID = "KOEN2";
customer.CompanyName = "...";
...
dataContext.Customers.InsertOnSubmit(customer);
dataContext.SubmitChanges();

Deletion example:

Customer koen2 = dataContext.Customers.SingleOrDefault(c => c.CustomerID == "KOEN2");
if(koen2 != null)
{
    dataContext.Customers.DeleteOnSubmit(koen2);
    dataContext.SubmitChanges();
}

After successful submitchanges execution, the deleted object will be marked as deleted in the data context and will not be deleted from the internal cache.

Cross-Table update

LINQ-to-SQL allows us to model the relationships between tables in the context of data, and also supports cross-Table update. All we need to do is remove the object from the corresponding table object, and the rest of the work is done by submitchanges.

// Create a customer
Customer mands = new customer ();
// Assign values to the properties of mands
...
Datacontext. Customers. insertonsubmit (mands );

// Add order
Order order = New Order ();
// Assign values to the order attribute
...
// Add the order to the orders attribute of mands
Mands. Orders. Add (order );

// Submit the changes to the database
Datacontext. submitchanges ();

Delete the customers and orders added in the previous example:

// Retrieve the customer's order
VaR orders = from o in datacontext. Orders
Where o. customerid = "mands"
Select O;

// Delete the order
Foreach (VAR o in orders)
Datacontext. Orders. deleteonsubmit (O );

// Retrieve the customer object
Customer mands = datacontext. Customers. singleordefault (C => C. customerid = "mands ");
If (mands! = NULL)
{
// Delete the customer and submit the changes to the database
Datacontext. Customers. deleteonsubmit (mands );
Datacontext. submitchanges ();
}

Note: We should first Delete the customer order and then delete the customer. If you delete a customer first, an exception is thrown because the customer table and order table have table constraints.

Open concurrency

For example, if we use the SQL profile tool to track the statements actually executed by SQL when deleting an order, we can find that it runs the following statements:

Delete From dbo.Customers
Where CustomerID = 'MANDS'
and CompanyName = 'Managed Design'
and ContactName = 'Dino'
and ContactTitle is NULL
and Address = 'Via dei Tigli'
and City = 'Milan'
and Region is NULL
and PostalCode is NULL
and Country = 'Italy'
and Phone is NULL
and Fax is NULL

This technology is called "Open concurrency". It requires that you check whether other transactions have modified the record before updating or deleting the record. When "concurrent" is used to update or delete a record, it locks the record to avoid conflicts. By default, LINQ-to-SQL uses open concurrency.

We can pass a parameter to submitchanges to indicate that it ignores the conflict exception and continues to execute:

dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);

The default value is conflictmode. failonfirstconflict. If you choose to continue updating, how do you know which commands fail?

Therefore, we can traverse the changeconflicts set of the data context:

try
{
    dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch(ChangeConflictException e)
{
foreach(ObjectChangeConflict occ in dataContext.ChangeConflicts)
    {}
}

The attributes defined in the objectchangeconflict class enable us to understand what happened, where the table occurred, and the entity object involved.

Update operation Customization

To delete all orders of a customer without using LINQ, we may do this:

Delete From orders Where customerid = 'MANDS'

LINQ-to-SQL provides a way to change the implementation of object update operations. Because LINQ-to-SQL is built on the branch class, we only need to add a branch method.

The following is the data context class of the customer entity. You can extend the list using the division method:

partial void InsertCustomer(Customer instance);
partial void UpdateCustomer(Customer instance);partial void DeleteCustomer(Customer instance);

These methods are in the form of insertxxx, updatexxx, and deletexxx. xxx represents the name of the entity in the data context. In this way, when deleting a customer record, we can do this:

partial void DeleteCustomer(Customer instance)
{
this.ExecuteCommand("Delete From customers Where customerid={0}", instance.CustomerID);
}

After using this extension method, the order is automatically deleted when the customer record is deleted.

We can also use the custom features of update operations to execute database operations with stored procedures instead of T-SQL commands.

Use transactions

All updates submitted through submitchanges are performed in a transaction. After this method is called, LINQ-to-SQL will verify whether the current call is within the scope of other transactions and ensure that the transactions initiated by the user are not explicitly bound to the data context. If there is no existing transaction, LINQ-to-SQL starts a local transaction and uses it to execute all T-SQL commands. After all the commands are executed, this transaction is committed by LINQ-to-SQL.

In our code, we can use the transcationscope object to encapsulate any database operations caused by the submitchanges method. For example, through the external transcationscope object, we can introduce other non-database resources into transactions, send messages to MSMQ, or update the file system. If submitchanges detects that it is in the scope of an existing transaction, it will not only avoid creating new transactions, but also avoid closing connections.

You can also initiate a transaction to associate multiple submitchanges commands with the transaction. Therefore, we can use the transaction attribute of the data context object. The submission or rollback depend entirely on the developer. If the connection string of the transaction does not match the data context, an exception is thrown.

Use stored procedures

We can use the executecommand method in the division method to execute the stored procedure. Example:

partial void DeleteCustomer(Customer instance)
{
this.ExecuteCommand("exec delete_customer {0}", instance.CustomerID);
}

In addition, we can add the stored procedure to the data context in the O/R designer of vs2008 to call the stored procedure as a data context method.

Assume that the Stored Procedure named custorderhist has been added to the data context. The call method is as follows:

var orders = dataContext.CustOrderHist(customerID);

We can use the VaR keyword to deduce the result type. If a stored procedure returns multiple values or different types of values, the encapsulation of the stored procedure in the data context will return the imultipleresults type. In this case, we can use getresult <t> to access the given result. T is used to specify the type of a specific result.

Use User-Defined Functions

Similar to the stored procedure, user-defined functions in the database can also be attached to the data context and called as methods on the page.