Walkthrough: Manipulating Data (Visual Basic) (LINQ to SQL)

Article
11/16/2012

This walkthrough provides a fundamental end-to-end LINQ to SQL scenario for adding, modifying, and deleting data in a database. You will use a copy of the sample Northwind database to add a customer, change the name of a customer, and delete an order.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

This walkthrough was written by using Visual Basic Development Settings.

Prerequisites

This walkthrough requires the following:

This walkthrough uses a dedicated folder ("c:\linqtest2") to hold files. Create this folder before you begin the walkthrough.
The Northwind sample database.

If you do not have this database on your development computer, you can download it from the Microsoft download site. For instructions, see Downloading Sample Databases (LINQ to SQL). After you have downloaded the database, copy the northwnd.mdf file to the c:\linqtest2 folder.
A Visual Basic code file generated from the Northwind database.

You can generate this file by using either the Object Relational Designer or the SQLMetal tool. This walkthrough was written by using the SQLMetal tool with the following command line:

sqlmetal /code:"c:\linqtest2\northwind.vb" /language:vb "C:\linqtest2\northwnd.mdf" /pluralize

For more information, see Code Generation Tool (SqlMetal.exe).

Overview

This walkthrough consists of six main tasks:

Creating the LINQ to SQL solution in Visual Studio.
Adding the database code file to the project.
Creating a new customer object.
Modifying the contact name of a customer.
Deleting an order.
Submitting these changes to the Northwind database.

Creating a LINQ to SQL Solution

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQL project.

To create a LINQ to SQL solution

On the Visual Studio File menu, click New Project.
In the Project types pane in the New Project dialog box, click Visual Basic.
In the Templates pane, click Console Application.
In the Name box, type LinqDataManipulationApp.
Click OK.

Adding LINQ References and Directives

This walkthrough uses assemblies that might not be installed by default in your project. If System.Data.Linq is not listed as a reference in your project (click Show All Files in Solution Explorer and expand the References node), add it, as explained in the following steps.

To add System.Data.Linq

In Solution Explorer, right-click References, and then click Add Reference.
In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

The assembly is added to the project.
In the code editor, add the following directives above Module1:
```
Imports System.Data.Linq
Imports System.Data.Linq.Mapping
```

Adding the Northwind Code File to the Project

These steps assume that you have used the SQLMetal tool to generate a code file from the Northwind sample database. For more information, see the Prerequisites section earlier in this walkthrough.

To add the northwind code file to the project

On the Project menu, click Add Existing Item.
In the Add Existing Item dialog box, navigate to c:\linqtest2\northwind.vb, and then click Add.

The northwind.vb file is added to the project.

Setting Up the Database Connection

First, test your connection to the database. Note especially that the name of the database, Northwnd, has no i character. If you generate errors in the next steps, review the northwind.vb file to determine how the Northwind partial class is spelled.

To set up and test the database connection

Type or paste the following code into Sub Main:

' Use a connection string, but connect to 
'     the temporary copy of the database. 
Dim db As New Northwnd _
    ("C:\linqtest2\northwnd.mdf")

' Keep the console window open after activity stops.
Console.ReadLine()

Press F5 to test the application at this point.

A Console window opens.

Close the application by pressing Enter in the Console window, or by clicking Stop Debugging on the Visual Studio Debug menu.

Creating a New Entity

Creating a new entity is straightforward. You can create objects (such as Customer) by using the New keyword.

In this and the following sections, you are making changes only to the local cache. No changes are sent to the database until you call SubmitChanges toward the end of this walkthrough.

To add a new Customer entity object

Create a new Customer by adding the following code before Console.ReadLine in Sub Main:

' Create the new Customer object. 
Dim newCust As New Customer()
newCust.CompanyName = "AdventureWorks Cafe"
newCust.CustomerID = "A3VCA" 

' Add the customer to the Customers table.
db.Customers.InsertOnSubmit(newCust)

Console.WriteLine("Customers matching CA before insert:")

Dim custQuery = _
    From cust In db.Customers _
    Where cust.CustomerID.Contains("CA") _
    Select cust

For Each cust In custQuery
    Console.WriteLine("Customer ID: " & cust.CustomerID)
Next

Press F5 to debug the solution.

The results that are shown in the console window are as follows:

Customers matching CA before insert:

Customer ID: CACTU

Customer ID: RICAR

Note that the new row does not appear in the results. The new data has not yet been submitted to the database.
Press Enter in the Console window to stop debugging.

Updating an Entity

In the following steps, you will retrieve a Customer object and modify one of its properties.

To change the name of a Customer

Add the following code above Console.ReadLine():

Dim existingCust = _
    (From cust In db.Customers _
    Where cust.CustomerID = "ALFKI" _
    Select cust).First()

' Change the contact name of the customer.
existingCust.ContactName = "New Contact"

Deleting an Entity

Using the same customer object, you can delete the first order.

The following code demonstrates how to sever relationships between rows, and how to delete a row from the database.

To delete a row

Add the following code just above Console.ReadLine():

' Access the first element in the Orders collection. 
Dim ord0 As Order = existingCust.Orders(0)

' Access the first element in the OrderDetails collection. 
Dim detail0 As OrderDetail = ord0.OrderDetails(0)

' Display the order to be deleted.
Console.WriteLine _
    (vbCrLf & "The Order Detail to be deleted is: OrderID = " _
    & detail0.OrderID)

' Mark the Order Detail row for deletion from the database.
db.OrderDetails.DeleteOnSubmit(detail0)

Submitting Changes to the Database

The final step required for creating, updating, and deleting objects is to actually submit the changes to the database. Without this step, your changes are only local and will not appear in query results.

To submit changes to the database

Insert the following code just above Console.ReadLine:
```
db.SubmitChanges()
```

Insert the following code (after SubmitChanges) to show the before and after effects of submitting the changes:

Console.WriteLine(vbCrLf & "Customers matching CA after update:")
Dim finalQuery = _
    From cust In db.Customers _
    Where cust.CustomerID.Contains("CA") _
    Select cust

For Each cust In finalQuery
    Console.WriteLine("Customer ID: " & cust.CustomerID)
Next

Press F5 to debug the solution.

The console window appears as follows:

Customers matching CA before update:
Customer ID: CACTU
Customer ID: RICAR

The Order Detail to be deleted is: OrderID = 10643

Customers matching CA after update:
Customer ID: A3VCA
Customer ID: CACTU
Customer ID: RICAR

Press Enter in the Console window to stop debugging.