
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;