Walkthrough: Manipulating Data (C#) (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 C# Development Settings.

Prerequisites

This walkthrough requires the following:

This walkthrough uses a dedicated folder ("c:\linqtest6") 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:\linqtest6 folder.
A C# 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:\linqtest6\northwind.cs" /language:csharp "C:\linqtest6\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, point to New, and then click Project.
In the Project types pane in the New Project dialog box, click Visual C#.
In the Templates pane, click Console Application.
In the Name box, type LinqDataManipulationApp.
In the Location box, verify where you want to store your project files.
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, 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.

Add the following directives at the top of Program.cs:

using System.Data.Linq;
using 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:\linqtest6\northwind.cs, and then click Add.

The northwind.cs file is added to the project.

Setting Up the Database Connection

First, test your connection to the database. Note especially that the database, Northwnd, has no i character. If you generate errors in the next steps, review the northwind.cs 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 the Main method in the Program class:

// Use the following connection string.
Northwnd db = new Northwnd(@"c:\linqtest6\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.

You can 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 the Main method:

// Create the new Customer object.
Customer newCust = new Customer();
newCust.CompanyName = "AdventureWorks Cafe";
newCust.CustomerID = "ADVCA";

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

Console.WriteLine("\nCustomers matching CA before insert");

foreach (var c in db.Customers.Where(cust => cust.CustomerID.Contains("CA")))
{
    Console.WriteLine("{0}, {1}, {2}",
        c.CustomerID, c.CompanyName, c.Orders.Count);
}

Press F5 to debug the solution.
Press Enter in the Console window to stop debugging and continue the walkthrough.

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();:

// Query for specific customer. 
// First() returns one object rather than a collection. 
var existingCust =
    (from c in db.Customers
     where c.CustomerID == "ALFKI" 
     select c)
    .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. Add the following code before Console.ReadLine to see how objects can be deleted:

To delete a row

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

// Access the first element in the Orders collection.
Order ord0 = existingCust.Orders[0];

// Access the first element in the OrderDetails collection.
OrderDetail detail0 = ord0.OrderDetails[0];

// Display the order to be deleted.
Console.WriteLine
    ("The Order Detail to be deleted is: OrderID = {0}, ProductID = {1}",
    detail0.OrderID, detail0.ProductID);

// 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("\nCustomers matching CA after update");
foreach (var c in db.Customers.Where(cust =>
    cust.CustomerID.Contains("CA")))
{
    Console.WriteLine("{0}, {1}, {2}",
        c.CustomerID, c.CompanyName, c.Orders.Count);
}

Press F5 to debug the solution.
Press Enter in the Console window to close the application.