Walkthrough: Manipulating Data (C#)

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.

This walkthrough was written by using Visual C# Development Settings.

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. 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 SqlMetal.exe (Code Generation Tool).

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.

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

1. On the Visual Studio File menu, point to New, and then click Project.

2. In the Project types pane in the New Project dialog box, click Visual C#.

3. In the Templates pane, click Console Application.

4. In the Name box, type LinqDataManipulationApp.

5. In the Location box, verify where you want to store your project files.

6. Click OK.

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:

1. In Solution Explorer, right-click References, and then click Add Reference.

2. 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.

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

   C# Copy

   using System.Data.Linq;
   using System.Data.Linq.Mapping;
   

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.

1. On the Project menu, click Add Existing Item.

2. 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.

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.

1. Type or paste the following code into the Main method in the Program class:

   C# Copy

   // Use the following connection string.
   Northwnd db = new Northwnd(@"c:\linqtest6\northwnd.mdf");
   
   // Keep the console window open after activity stops.
   Console.ReadLine();
   

2. 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 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.

1. Create a new Customer by adding the following code before Console.ReadLine(); in the Main method:

   C# Copy

   // 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);
   }
   

2. Press F5 to debug the solution.

3. Press Enter in the Console window to stop debugging and continue the walkthrough.

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

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

  C# Copy

  // 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";
  

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:

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

  C# Copy

  // 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);
  

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.

1. Insert the following code just above Console.ReadLine:

   C# Copy

   db.SubmitChanges();
   

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

   C# Copy

   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);
   }
   

3. Press F5 to debug the solution.

4. Press Enter in the Console window to close the application.

• Learning by Walkthroughs