CRUD with LINQ

Database: Northwind

Visual Studio 2010

Setup:

1. Create a new Web or WinForm Application

2. Create a new Data Connection in Server Explorer to connect to Northwind database

3. Add LINQ to SQL Classes to your project

4. Drag all the Northwind tables to the designer surface left panel, drag stored procedures CustOrderHist, CustOrdersDetails, CustOrdersOrders to the designer surface right panel. Actually I did not use all of those in the application.

5. Create a new stored procedure in the Northwind, name it as UpdateCustomer. Afterwards, I'll show you how to use LINQ to call this SP to update database and get the returned value. The SP is as following:

ALTER PROCEDURE [dbo].[UpdateCustomer]          @CustomerID nchar(5),       @CompanyName nvarchar(40) AS BEGIN  SET NOCOUNT ON;  update dbo.Customers set CompanyName = @CompanyName where CustomerID = @CustomerID  return 1 -- for demo purpose END

The SP updates the CompanyName of the Customers table based on the CustomerID, it takes  two parameters: CustomerID and CompanyName. And always return value 1 for demo purpose here.

6. Refresh the database in Server Explorer and drag UpdateCustomer SP to the right panel of the Northwind.dbml designer surface.

7. Build the application, make sure it succeeded. The application looks like the following:

8. LINQ has generated the code to model the database, and we can start to enjoy the intellisense and strongly typed database object. The following is the code snippet:

Read Products with category name is "Beverages"

NorthwindDataContext db = new NorthwindDataContext(); var products = from p in db.Products where                    p.Category.CategoryName=="Beverages"                select p; foreach (Product p in products) {    lblResult.Text = lblResult.Text + p.ProductName + " | "; }

Update Products table

NorthwindDataContext db = new NorthwindDataContext(); Product product = db.Products.Single(p => p.ProductName == "Chai"); product.UnitPrice = 99; product.UnitsInStock = 5; db.SubmitChanges();

Insert Records

NorthwindDataContext db = new NorthwindDataContext(); Category cat = new Category(); cat.CategoryName = "TOY"; Product p1 = new Product(); p1.ProductName = "toy 1"; Product p2 = new Product(); p2.ProductName = "toy 2"; cat.Products.Add(p1); cat.Products.Add(p2); db.Categories.InsertOnSubmit(cat); db.SubmitChanges();

Delete

NorthwindDataContext db = new NorthwindDataContext(); var toyProd = from p in db.Products               where p.ProductName.Contains("toy")               select p; db.Products.DeleteAllOnSubmit(toyProd); db.SubmitChanges();

Run SP, return a collection

NorthwindDataContext db = new NorthwindDataContext(); var orderDetail = db.CustOrdersDetail(10248); foreach (CustOrdersDetailResult re in orderDetail) {     Console.WriteLine(re.ProductName); }

Note: When you use the object of CustOrdersDetailResult, you can see the intellisense and can access every Field the Stored Procedure CustOrdersDetailResult returns, that is ProductName, UnitPrice, Quantity, Discount and ExtendedPrice in this case. This SP is to return the order details based on the OrderID, which is passed as a parameter.

Run SP, Update table and return a value

NorthwindDataContext db = new NorthwindDataContext(); var result = db.UpdateCustomer("AAAAA", "BMW"); Console.WriteLine(result.ToString());

Note: UpdateCustomer is the new SP created at step 5. It sets the CompanyName to "BMW" for the CustomerID "AAAAA", Passing the parameter is like to call a regular method, and we do not need SubmitChanges here. In the SP, @CustomerID declared as nchar(5), @CompanyName declared as nvarchar(40), but in the intellisense, I see the signature changed to db.UpdateCustomer(string customerID, string companyName), need to watch not to exceed the width.

Server side Pagination

NorthwindDataContext db = new NorthwindDataContext(); var products = (from p in db.Products                 where p.Category.CategoryName.StartsWith("C")                 select p).Skip(200).Take(10);

Pagination made easy, only returns the collection we are looking for.

9. Save, compile and run the application. The above code has been tested.