Language Integrated Query (LINQ) to SQL
Querying Data
The following example code uses LINQ syntax to query datafrom the database and then loop through it to print it tothe console. You'll notice how the query expression syntaxis the same as what you would find whether it be LINQ to XMLor another form of LINQ. Also notice how theNorthwindDataClassesDataContext object is wrapped within ausing construct.TheNorthwindDataClassesDataContext
is an object thatextends the DataContext class and represents a stronglytyped connection to our database. TheNorthwindDataClassesDataContext
is a disposabletype, so wrapping it in a using block ensures it is properlydisposed after use.
using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) { var results = from customers in context.Customers where customers.City == "London" orderby customers.CompanyName select customers; foreach (var customer in results) { Console.WriteLine("Company is {0} and contact is {1}", customer.CompanyName, customer.ContactName); } // Pause to see the output Console.ReadLine(); }
Modifying Data Using the Created Object Model
The previous example focused on the retrieval of data.We'll now examine the basic data manipulation, create,update, and delete operations and how LINQ to SQL makes themsimple. The object model used to manipulate the data is themodel generated in one of the earlier sections. After eachoperation we'll use the SubmitChanges() method to save thechanges to the database. We'll execute some simple queryexpressions with theCount()
method to verify themodifications were made as desired. As the example willshow, you can use instance of objects, make modifications tothem, and the changes that you make are tracked in a changeset and pushed back to the database.
using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) { // Add a new record and verify it exists through Count var customer = new Customer() { CompanyName = "Drama Cafe", CustomerID = "DRACA", ContactName = "Tom Smith", City = "Beverly Hills", Address = "123 Melrose Place", PostalCode = "90210" }; context.Customers.InsertOnSubmit(customer); context.SubmitChanges(); Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count()); // Modify the record and verify it is changed through Count customer.ContactName = "Joe Smith"; context.SubmitChanges(); Console.WriteLine("Number of Joe Smith records: {0}", context.Customers.Where(c => c.ContactName == "Joe Smith").Count()); // Delete a record and verify it is removed through Count context.Customers.DeleteOnSubmit(customer); context.SubmitChanges(); Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count()); // Pause to see the output Console.ReadLine();
Page 2 of 3
This article was originally published on April 28, 2009