Exercise 4 – Modifying Database Data

In this exercise, you will move beyond data retrieval and see how to manipulate the data. The four basic data operations are Create, Retrieve, Update, and Delete, collectively referred to as CRUD. You will see how LINQ to SQL makes CRUD operations simple and intuitive.

Task 1 – Modifying your object model

We are going to modify our object model by adding two more tables:

1. In Server Explorer, expand Data Connections.

2. Open the Northwind server (NORTHWND.MDF if you are using SQL Server Express).

3. Open the Northwind.dbml file double clicking it from the solution explorer

4. From the Tables folder drag the Order Details table onto the designer surface

5. From the Tables folder drag the Products table onto the designer surface

Task 2 – Creating a new Entity

1. Creating a new entity is straightforward. Objects such as Customer and Order can be created with the new operator as with regular C# Objects. Of course you will need to make sure that foreign key validations succeed. Change the Main method entering the following code to create a new customer:

2. Modify the Main method so that it appears as the following:

static void Main(string[] args)

{

NorthwindDataContext db = new NorthwindDataContext();

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

var customers = db.Customers

.Where(cust => cust.CustomerID.Contains("CA"));

foreach (var c in customers)

Console.WriteLine("{0}, {1}, {2}, {3}",

c.CustomerID, c.CompanyName, c.ContactName, c.Orders.Count);

Console.ReadLine();

}

3. Press F5 to debug the solution

Notice that the new row does not show up in the results. The data is not actually added to the database by this code yet.

Task 3 – Updating an Entity

1. Once you have a reference to an entity object, you can modify its properties like you would with any other object. Add the following code to modify the contact name for the first customer retrieved:

static void Main(string[] args)

{

NorthwindDataContext db = new NorthwindDataContext();

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

var customers = db.Customers

.Where(cust => cust.CustomerID.Contains("CA"));

foreach (var c in customers)

Console.WriteLine("{0}, {1}, {2}, {3}",

c.CustomerID, c.CompanyName, c.ContactName, c.Orders.Count);

Customer existingCust = customers.First();

// Change the contact name of the customer

existingCust.ContactName = "New Contact";

Console.ReadLine();

}

As in the last task, no changes have actually been sent to the database yet.