212Microsoft Visual Studio 2010: A Beginner’s Guide

VB:

Sub UpdateCustomer(ByVal custID As Integer)

Dim myShop As New MyShopDataContext

Dim customers =

From cust In myShop.Customers

Where cust.CustomerID = custID

Select cust

Dim firstCust As Customer = customers.SingleOrDefault()

If (firstCust IsNot Nothing) Then

firstCust.Name = "James"

End If

myShop.SubmitChanges() End Sub

In the previous queries for the customer whose name was Jim, change the object to James and saves changes. The call to SingleOrDefault was necessary because the result of a LINQ to SQL query is a collection, but we only want the first or only record returned. There is also an operator method named Single, but using SingleOrDefault is favorable because it returns a default value if no records are returned, whereas Single will throw an exception. The code uses an if statement to protect against the possibility of an exception; otherwise, the code would throw a NullReferenceException when firstCust is null (Nothing in VB) and the code tries to access the Name property of a null object. Remember to call SubmitChanges; otherwise the updates won’t be made.

You can now query, insert, and update. Your final skill to learn is deleting data.

Deleting Data with LINQ to SQL

To delete a record from the database, you get a reference to the object for that record, call a method to delete that object, and save changes. Here’s an example that deletes a record:

C#:

private static void DeleteCustomer(int custID)

{

var myShop = new MyShopDataContext();

var customers =

from cust in myShop.Customers where cust.CustomerID == custID select cust;

Page 235
Image 235
Microsoft 9GD00001 manual Deleting Data with Linq to SQL