- •Contents
- •Lab 1: linq Project: Unified Language Features for Object and Relational Queries
- •Lab Objective
- •Exercise 1 –linq for In-Memory Collections
- •Task 1 – Creating the “linq Overview” Solution
- •Task 2 – Querying a Generic List of Integers
- •Task 3 – Querying Structured Types
- •Task 2 – Querying by Reading in an xml File
- •Task 3 – Querying an xml File
- •Exercise 2 – linq to xml: linq for xml documents
- •Task 1 – Adding linq to xml Support
- •Task 4 – Transforming xml Output
- •Exercise 3 – linq to DataSet: linq for DataSet Objects
- •Task 1 – Adding DataSet Support
- •Task 2 – Creating a DataSet – Add Designer File
- •Task 3 – Creating a DataSet – Using the Designer
- •Task 4 – Querying a DataSet
- •Exercise 4 – linq to sql: linq for Connected Databases
- •Task 1 – Adding linq to sql Support
- •Task 2 – Creating Object Mapping – Creating an Object and Providing Attributes
- •Task 3 – Creating Object Mapping – Using the Designer – Add Designer File
- •Task 4 – Creating Object Mapping – Using the Designer – Create the Object View
- •Task 5 – Querying using Expressions
- •Task 6 – Modifying Database Data
- •Task 7 – Calling Stored Procedures
- •Task 8 – Expanding Query Expressions
- •Exercise 5 – Understanding the Standard Query Operators [Optional]
- •Task 1 – Querying using the Standard Query Operators
- •Task 2 – Working with the Select Operator
- •Task 3 – Working with the Where Operator
- •Task 4 – Working with the Count Operator
- •Task 5 – Working with the Min, Max, Sum, and Average Operators
- •Task 6 – Working with the All and Any operators
- •Task 7 – Working with the ToArray and ToList Operators
- •Lab Summary
Task 6 – Modifying Database Data
In this task, you 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 see how LINQ to SQL makes CRUD operations simple and intuitive. This task shows how to use the create and update operations.
Create a new method that modifies the database data as well as a call from Main:
static void ModifyData()
{
var db = new NorthwindDataContext();
Console.WriteLine("Number Created Before: {0}",
db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());
var newCustomer = new Customer
{
CompanyName = "AdventureWorks Cafe",
CustomerID = "ADVCA"
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();
Console.WriteLine("Number Created After: {0}",
db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());
}
static void Main(string[] args)
{
ModifyData();
}
Press Ctrl+F5 to build and run the application. Notice that the two lines written to the screen are different after the database is updated. Now press any key to terminate the application.
Notice that after the Add method is called, the changes are then submitted to the database using the SubmitChanges method. Note that once the customer has been inserted, it cannot be inserted again due to the primary key uniqueness constraint. Therefore this program can only be run once.
Now update and modify data in the database. Change ModifyData to the following code that updates the contact name for the first customer retrieved.
static void ModifyData()
{
var db = new NorthwindDataContext();
Console.WriteLine("Number Updated Before: {0}",
db.Customers.Where( c => c.ContactName == "New Contact" ).Count());
var existingCustomer = db.Customers.First();
existingCustomer.ContactName = "New Contact";
db.SubmitChanges();
Console.WriteLine("Number Updated After: {0}",
db.Customers.Where( c => c.ContactName == "New Contact" ).Count());
}
Now press Ctrl+F5 to build and run the application. Notice again the number of contacts with the name “New Contact” changes. Press any key to terminate the application.
Task 7 – Calling Stored Procedures
Recall that when using the designer, you added a stored procedure along with the tables. In this task you will call tha stored procedure.
Create a new method that prints the results of a call to the Ten Most Expensive Products stored procedure that was added to the NorthwindDataContext in the designer:
static void InvokeSproc()
{
var db = new NorthwindDataContext();
foreach (var r in db.Ten_Most_Expensive_Products())
Console.WriteLine(r.TenMostExpensiveProducts + "\t" + r.UnitPrice);
}
Now call this method from the Main method:
static void Main(string[] args)
{
InvokeSproc();
}
Press Ctrl+F5 to build and run the application. After viewing the results, press any key to terminate the application.
When the database cannot be accessed through dynamic SQL statements, you can use C# 3.0 and LINQ to run stored procedures to access the data.