Introduction
This article provides an introduction to employing LINQ to SQL within a Windows Forms application; the article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures. Select query examples will demonstrate ordering, filtering, aggregation, returning typed lists, returning single objects and values, and how to query across entity sets (which are essentially related tables associated by foreign keys).
Figure 1: Application Main Form
The demonstration project included with the article is a simple win forms application; this example contains a datagridview control and a menu; the menu is used to execute each example query contained in the demonstration. The application provides the following functionality:
- Return Full Tables
- Return Typed Lists
- Return Single Typed Values
- Insert Data
- Update Data
- Delete Data
- Execute Stored Procedures
- Select Filtered Lists
- Select Ordered Lists
- Perform Aggregate Functions
There is a great deal more that one can do with LINQ to SQL that is not contained in this demonstration however, the demonstration was geared towards the mechanics of performing the most typical types of queries that might be required within a data driven application.
LINQ to SQL Statements
This section will discuss some of the common techniques used in LINQ to SQL statement construction. In a nutshell, LINQ to SQL provides the developer with the means to conduct queries against a relational database through a LINQ to SQL database model and related data context.
Data Context
The data context provides the mapping of all entities (essentially tables) to the database. It is through the data context that the application can query the database and it is through the data context that changes to the database can be executed.
Anatomy of LINQ to SQL Statements
Example 1 – A Simple Select
This is an example of a very simple LINQ to SQL statement:
Public Sub SampleQ1()
Dim dc As New NwindDataClassesDataContext()
Dim q = _
From a In dc.GetTable(Of Order)() _
Select a
DataGridView1.DataSource = q
End Sub
In the example, an instance of the data context is created and then a query is formed to get all of the values in the table; once the query runs, the result is used as the data source of a datagridview control and the results are displayed in the grid:
Dim q = _
From a In dc.GetTable(Of Order)() _
Select a
Since the GetTable function in the data context returns the entire table, this query is pretty useless but it does work and it is representative of a simple select query. You could accomplish the same task using this code:
Public Sub SampleQ2()
Dim dc As New NwindDataClassesDataContext()
DataGridView1.DataSource = dc.GetTable(Of Order)()
End Sub
If you were to create a project, add either bit of code to a method and run it, the results would look like this:
Figure 2: Query Results
Example 2 – Select with a Where Clause
The next example shows a LINQ to SQL query that incorporates a where clause. In this example, we get a data context to work with first, and then query the Orders table to find a customer with the customer ID of starts with the letter “A”, the results are then bound to a datagridview control.
Public Sub SimpleQ3()
Dim dc As New NwindDataClassesDataContext()
Dim q = _
From a In dc.GetTable(Of Order)() _
Where a.CustomerID.StartsWith("A") _
Select a
DataGridView1.DataSource = q
End Sub
If you were to run the query, the results would appear as follows:
Figure 3: Query Results
Example 3 – Select with a Where Clause
In a slight variation to the previous query, this example looks for an exact match in its where clause:
Public Sub SimpleQ4()
Dim dc As New NwindDataClassesDataContext()
Dim q = _
From a In dc.GetTable(Of Order)() _
Where a.CustomerID = "VINET" _
Select a
DataGridView1.DataSource = q
End Sub
Running this code will display this result:
Figure 4: Query Results
Example 4 – Generating an Ordered List
In this query, the list of orders is ordered (using “orderby a.OrderDate ascending”):
Public Sub SimpleQ5()
Dim dc As New NwindDataClassesDataContext()
Dim q = _
From a In dc.GetTable(Of Order)() _
Where a.CustomerID.StartsWith("A") _
Order By a.OrderDate Ascending _
Select a
DataGridView1.DataSource = q
End Sub
Figure 5: Query Results
Example 5 – Working with a Custom Type
In this example a query is built to return a list of a custom type (CustomerOrderResult).
Public Sub GetCustomerOrder()
Dim dc As New NwindDataClassesDataContext()
Dim q = (From orders In dc.Orders _
From orderDetails In orders.Order_Details _
From prods In dc.Products _
Where ((orderDetails.OrderID = orders.OrderID) And _
(prods.ProductID = orderDetails.ProductID) And _
(orders.EmployeeID = 1)) _
Order By (orders.ShipCountry) _
Select New CustomerOrderResult With { _
.CustomerID = orders.CustomerID, _
.CustomerContactName = orders.Customer.ContactName, _
.CustomerCountry = orders.Customer.Country, _
.OrderDate = orders.OrderDate, _
.EmployeeID = orders.Employee.EmployeeID, _
.EmployeeFirstName = orders.Employee.FirstName, _
.EmployeeLastName = orders.Employee.LastName, _
.ProductName = prods.ProductName _
}).ToList()
dataGridView1.DataSource = q
End Sub
The “select new” in the query defines the result type and then sets each of the properties in the type to a value returned by the query. At the end of the query, the output is converted to a List of the CustomerOrderResult type.
The displayed results of running the query are:
Figure 6: Query Results
The CustomerOrderResult class used in as the type behind the parts list is as follows:
Public Class CustomerOrderResult
Private mCustomerID As String
Private mCustomerContactName As String
Private mCustomerCountry As String
Private mOrderDate As Nullable(Of DateTime)
Private mEmployeeID As Int32
Private mEmployeeFirstName As String
Private mEmployeeLastName As String
Private mProductName As String
Public Property CustomerID() As String
Get
Return mCustomerID
End Get
Set(ByVal value As String)
mCustomerID = value
End Set
End Property
Public Property CustomerContactName() As String
Get
Return mCustomerContactName
End Get
Set(ByVal value As String)
mCustomerContactName = value
End Set
End Property
Public Property CustomerCountry() As String
Get
Return mCustomerCountry
End Get
Set(ByVal value As String)
mCustomerCountry = value
End Set
End Property
Public Property OrderDate() As Nullable(Of DateTime)
Get
Return mOrderDate
End Get
Set(ByVal value As Nullable(Of DateTime))
mOrderDate = value
End Set
End Property
Public Property EmployeeID() As Int32
Get
Return mEmployeeID
End Get
Set(ByVal value As Int32)
mEmployeeID = value
End Set
End Property
Public Property EmployeeFirstName() As String
Get
Return mEmployeeFirstName
End Get
Set(ByVal value As String)
mEmployeeFirstName = value
End Set
End Property
Public Property EmployeeLastName() As String
Get
Return mEmployeeLastName
End Get
Set(ByVal value As String)
mEmployeeLastName = value
End Set
End Property
Public Property ProductName() As String
Get
Return mProductName
End Get
Set(ByVal value As String)
mProductName = value
End Set
End Property
End Class
Example 6 – Searching an Existing List (Of Type) Using LINQ to Objects
In this example, a typed list is created (as in the previous example) using LINQ to SQL, populated, and then the returned typed list is queried using LINQ to Objects. In this case, the query includes a where clause that only returns matches were the customer ID begins is equal to “RICAR”:
Public Sub GetCustomerOrder2()
Dim dc As New NwindDataClassesDataContext()
Dim q = (From orders In dc.Orders _
From orderDetails In orders.Order_Details _
From prods In dc.Products _
Where ((orderDetails.OrderID = orders.OrderID) And _
(prods.ProductID = orderDetails.ProductID) And _
(orders.EmployeeID = 1)) _
Order By (orders.ShipCountry) _
Select New CustomerOrderResult With { _
.CustomerID = orders.CustomerID, _
.CustomerContactName = orders.Customer.ContactName, _
.CustomerCountry = orders.Customer.Country, _
.OrderDate = orders.OrderDate, _
.EmployeeID = orders.Employee.EmployeeID, _
.EmployeeFirstName = orders.Employee.FirstName, _
.EmployeeLastName = orders.Employee.LastName, _
.ProductName = prods.ProductName _
}).ToList()
Dim matches = (From c In q _
Where c.CustomerID = "RICAR" _
Select c).ToList()
DataGridView1.DataSource = matches
End Sub
Figure 7: Query Results
Example 7 – Searching an Existing List (Of Type) Using LINQ to Objects and Returning a Single Result
In this example, a typed list is created (as in the previous example), populated, and then queried using LINQ to Objects. In this case, returns a single result of type “Parts”:
Public Sub GetEmployeeLastName()
Dim dc As New NwindDataClassesDataContext()
Dim query = (From orders In dc.GetTable(Of Order)() _
Select orders)
Dim matches = (From c In query _
Where c.OrderID = 10248 _
Select c.Employee.LastName).SingleOrDefault()
MessageBox.Show(matches, "Employee 10248 - Last Name")
End Sub
The results are displayed as:
Figure 8: Returning a Single Result
The preceding examples were intended to provide a simple overview as to how to conduct some basic queries against collections using LINQ to SQL and LINQ to Objects; there are certainly a great number of more complex operations that can be executed using similar procedures (groups and aggregation, joins, etc.) however, the examples provided are representative of some of the more common types of queries.
Getting Started
There is a single solution included with this download, the solution contains a Win Forms project called “L2S_Northwind_VB”; this project contains one form (the main form used to display the results of the demonstration queries (frmMain) , and LINQ to SQL database model (NorthwindDataClasses.dbml) along with the models designer code and layout file, and a class entitled, “Accessor” which contains code used to perform the LINQ to SQL queries used in the demonstration.
If you open the attached project into Visual Studio 2008, you should see the following in the solution explorer:
Figure 9: Solution Explorer
The demonstration relies upon an instance of the Northwind database running in SQL Server 2005. The database can be downloaded from here; the database was created for SQL Server 2000 but you can install the database and attach to it using a local instance of SQL Server 2005.
Once the database is installed, you will want to update the connection string found in the “MyProject” settings. Open the settings and click on the button (showing an ellipsis) to set the connection string.
Figure 10: Settings and the Connection String
Figure 11: Adding LINQ to SQL Classes to a Project
When starting from scratch, in order to add LINQ to SQL to a project, open the “Add New Item” dialog and select the LINQ to SQL Classes item (Figure 11); name the data classes and then select the “Add” button to add the item to the project. Once set, set the connection string for the data classes, then open the server explorer to drag tables and stored procedures onto the designer (dragging the tables into the left hand side of the workspace and stored procedures into the right hand side of the workspace (Figure 12)). Once that is done, build the project to generate the LINQ to SQL code.
Figure 12: Model of Northwind Data Class (tables on left, stored procedures on right)
This project is intended for Visual Studio 2008 with the .NET framework version 3.5.
Code: Accessor.vb
The Accessor class is used to the store all of the functions used to execute LINQ to SQL queries against the database. The functions contained in this class are all static and include a mixed bag of selects, inserts, updates, deletes, and stored procedure evocations. You may wish to implement some business logic in the extensibility methods defined in the auto-generated code contained in the designer file but I chose not to in this example.
The class begins with the normal and default imports:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Data.Linq
Imports System.Text
The next section contains the class declaration.
Public Class Accessor
Next up is a region containing all of the functions used to return full tables from the database through the data context. All of the functions work essentially the same way; the data context includes a function called GetTable (Of Type) which is used to return a table of the indicated type. Each example gets a data context and then evokes the GetTable function to return the full table of the indicated type.
#Region "Full Table Queries"
Public Shared Function GetEmployeeTable() As _
System.Data.Linq.Table(Of Employee)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Employee)()
End Function
Public Shared Function GetShipperTable() As _
System.Data.Linq.Table(Of Shipper)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Shipper)()
End Function
Public Shared Function GetOrderTable() As _
System.Data.Linq.Table(Of Order)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Order)()
End Function
Public Shared Function GetEmployeeTerritoryTable() As _
System.Data.Linq.Table(Of EmployeeTerritory)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of EmployeeTerritory)()
End Function
Public Shared Function GetTerritoryTable() As _
System.Data.Linq.Table(Of Territory)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Territory)()
End Function
Public Shared Function GetRegionTable() As _
System.Data.Linq.Table(Of Region)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Region)()
End Function
Public Shared Function GetCustomerTable() As _
System.Data.Linq.Table(Of Customer)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Customer)()
End Function
Public Shared Function GetCustomerCustomerDemoTable() As _
System.Data.Linq.Table(Of CustomerCustomerDemo)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of CustomerCustomerDemo)()
End Function
Public Shared Function GetCustomerDemographicTable() As _
System.Data.Linq.Table(Of CustomerDemographic)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of CustomerDemographic)()
End Function
Public Shared Function GetOrderDetailTable() As _
System.Data.Linq.Table(Of Order_Detail)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Order_Detail)()
End Function
Public Shared Function GetProductTable() As _
System.Data.Linq.Table(Of Product)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Product)()
End Function
Public Shared Function GetSupplierTable() As _
System.Data.Linq.Table(Of Supplier)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Supplier)()
End Function
Public Shared Function GetCategoryTable() As _
System.Data.Linq.Table(Of Category)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.GetTable(Of Category)()
End Function
#End Region
That next region contained in the Accessor class is Queries region; this region contains examples of different types of select queries that may be performed using LINQ to SQL. Each query is described in the annotation:
#Region "Queries"
Public Shared Function GetEmployeeById(ByVal empId As Integer) As Employee
Dim dc As New NorthwindDataClassesDataContext()
Dim retVal = (From e In dc.GetTable(Of Employee)() _
Where (e.EmployeeID = empId) _
Select e).FirstOrDefault()
Return retVal
End Function
Public Shared Function GetOrderById(ByVal ordId As Integer) As Order
Dim dc As New NorthwindDataClassesDataContext()
Dim retVal = (From ord In dc.GetTable(Of Order)() _
Where (ord.OrderID = ordId) _
Select ord).FirstOrDefault()
Return retVal
End Function
Public Shared Function GetOrdersById(ByVal ordId As Integer) As List(Of Order)
Dim dc As New NorthwindDataClassesDataContext()
Dim retVal = (From ord In dc.GetTable(Of Order)() _
Where (ord.OrderID = ordId) _
Select ord).ToList()
Return retVal
End Function
Public Shared Function GetEmployeesByHireDate() As List(Of Employee)
Dim dc As New NorthwindDataClassesDataContext()
Dim retVal = (From emp In dc.GetTable(Of Employee)() _
Order By emp.HireDate Ascending _
Select emp).ToList()
Return retVal
End Function
Public Class OrdersAndDetailsResult
Private mCustomerID As String
Private mOrderDate As Nullable(Of DateTime)
Private mRequiredDate As Nullable(Of DateTime)
Private mShipAddress As String
Private mShipCity As String
Private mShipCountry As String
Private mShipZip As String
Private mShippedTo As String
Private mOrderID As Integer
Private mNameOfProduct As String
Private mQtyPerUnit As String
Private mPrice As Nullable(Of Decimal)
Private mQtyOrdered As Int16
Private mDiscount As Single
Public Property CustomerID() As String
Get
Return mCustomerID
End Get
Set(ByVal value As String)
mCustomerID = value
End Set
End Property
Public Property OrderDate() As Nullable(Of DateTime)
Get
Return mOrderDate
End Get
Set(ByVal value As Nullable(Of DateTime))
mOrderDate = value
End Set
End Property
Public Property RequiredDate() As Nullable(Of DateTime)
Get
Return mRequiredDate
End Get
Set(ByVal value As Nullable(Of DateTime))
mRequiredDate = value
End Set
End Property
Public Property ShipAddress() As String
Get
Return mShipAddress
End Get
Set(ByVal value As String)
mShipAddress = value
End Set
End Property
Public Property ShipCity() As String
Get
Return mShipCity
End Get
Set(ByVal value As String)
mShipCity = value
End Set
End Property
Public Property ShipCountry() As String
Get
Return mShipCountry
End Get
Set(ByVal value As String)
mShipCountry = value
End Set
End Property
Public Property ShipZip() As String
Get
Return mShipZip
End Get
Set(ByVal value As String)
mShipZip = value
End Set
End Property
Public Property ShippedTo() As String
Get
Return mShippedTo
End Get
Set(ByVal value As String)
mShippedTo = value
End Set
End Property
Public Property OrderID() As Integer
Get
Return mOrderID
End Get
Set(ByVal value As Integer)
mOrderID = value
End Set
End Property
Public Property NameOfProduct() As String
Get
Return mNameOfProduct
End Get
Set(ByVal value As String)
mNameOfProduct = value
End Set
End Property
Public Property QtyPerUnit() As String
Get
Return mQtyPerUnit
End Get
Set(ByVal value As String)
mQtyPerUnit = value
End Set
End Property
Public Property Price() As Nullable(Of Decimal)
Get
Return mPrice
End Get
Set(ByVal value As Nullable(Of Decimal))
mPrice = value
End Set
End Property
Public Property QtyOrdered() As Int16
Get
Return mQtyOrdered
End Get
Set(ByVal value As Int16)
mQtyOrdered = value
End Set
End Property
Public Property Discount() As Single
Get
Return mDiscount
End Get
Set(ByVal value As Single)
mDiscount = value
End Set
End Property
End Class
Public Shared Function OrdersAndDetails() As List(Of OrdersAndDetailsResult)
Dim dc As New NorthwindDataClassesDataContext()
Dim rtnVal = (From ords In dc.GetTable(Of Order)() _
Join dets In dc.GetTable(Of Order_Detail)() _
On ords.OrderID Equals dets.OrderID _
Order By ords.CustomerID Ascending _
Select New OrdersAndDetailsResult With { _
.CustomerID = ords.CustomerID, _
.OrderDate = ords.OrderDate, _
.RequiredDate = ords.RequiredDate, _
.ShipAddress = ords.ShipAddress, _
.ShipCity = ords.ShipCity, _
.ShipCountry = ords.ShipCountry, _
.ShipZip = ords.ShipPostalCode, _
.ShippedTo = ords.ShipName, _
.OrderID = ords.OrderID, _
.NameOfProduct = dets.Product.ProductName, _
.QtyPerUnit = dets.Product.QuantityPerUnit, _
.Price = dets.UnitPrice, _
.QtyOrdered = dets.Quantity, _
.Discount = dets.Discount}).ToList()
Return rtnVal
End Function
Public Class OrderAndPricingResult
Private mOrderID As Int32
Private mCompany As String
Private mOrderCountry As String
Private mProductName As String
Private mUnitPrice As Nullable(Of Decimal)
Private mUnitsOrder As Int16
Private mShipperName As String
Private mSalesFirstName As String
Private mSalesLastName As String
Private mSalesTitle As String
Public Property OrderID() As Int32
Get
Return mOrderID
End Get
Set(ByVal value As Int32)
mOrderID = value
End Set
End Property
Public Property Company() As String
Get
Return mCompany
End Get
Set(ByVal value As String)
mCompany = value
End Set
End Property
Public Property OrderCountry() As String
Get
Return mOrderCountry
End Get
Set(ByVal value As String)
mOrderCountry = value
End Set
End Property
Public Property ProductName() As String
Get
Return mProductName
End Get
Set(ByVal value As String)
mProductName = value
End Set
End Property
Public Property UnitPrice() As Nullable(Of Decimal)
Get
Return mUnitPrice
End Get
Set(ByVal value As Nullable(Of Decimal))
mUnitPrice = value
End Set
End Property
Public Property UnitsOrder() As Int16
Get
Return mUnitsOrder
End Get
Set(ByVal value As Int16)
mUnitsOrder = value
End Set
End Property
Public Property ShipperName() As String
Get
Return mShipperName
End Get
Set(ByVal value As String)
mShipperName = value
End Set
End Property
Public Property SalesFirstName() As String
Get
Return mSalesFirstName
End Get
Set(ByVal value As String)
mSalesFirstName = value
End Set
End Property
Public Property SalesLastName() As String
Get
Return mSalesLastName
End Get
Set(ByVal value As String)
mSalesLastName = value
End Set
End Property
Public Property SalesTitle() As String
Get
Return mSalesTitle
End Get
Set(ByVal value As String)
mSalesTitle = value
End Set
End Property
End Class
Public Shared Function GetOrderAndPricingInformation() As List(Of
OrderAndPricingResult)
Dim dc As New NorthwindDataClassesDataContext()
Dim rtnVal = (From ords In dc.Orders _
From dets In ords.Order_Details _
Select New OrderAndPricingResult With { _
.OrderID = ords.OrderID, _
.Company = ords.Customer.CompanyName, _
.OrderCountry = ords.Customer.Country, _
.ProductName = dets.Product.ProductName, _
.UnitPrice = dets.Product.UnitPrice, _
.UnitsOrder = dets.Quantity, _
.ShipperName = ords.Shipper.CompanyName, _
.SalesFirstName = ords.Employee.FirstName, _
.SalesLastName = ords.Employee.LastName, _
.SalesTitle = ords.Employee.Title}).ToList()
Return rtnVal
End Function
Public Shared Function GetOrderAndPricingInformationByOrderId(ByVal ordId As
Integer) As List(Of OrderAndPricingResult)
Dim dc As New NorthwindDataClassesDataContext()
Dim rtnVal = (From ords In dc.Orders _
From dets In ords.Order_Details _
Where ords.OrderID = ordId _
Select New OrderAndPricingResult With { _
.OrderID = ords.OrderID, _
.Company = ords.Customer.CompanyName, _
.OrderCountry = ords.Customer.Country, _
.ProductName = dets.Product.ProductName, _
.UnitPrice = dets.Product.UnitPrice, _
.UnitsOrder = dets.Quantity, _
.ShipperName = ords.Shipper.CompanyName, _
.SalesFirstName = ords.Employee.FirstName, _
.SalesLastName = ords.Employee.LastName, _
.SalesTitle = ords.Employee.Title}).ToList()
Return rtnVal
End Function
Public Shared Function GetOrderValueByOrderId(ByVal orderId As Integer)
As Decimal?
Dim dc As New NorthwindDataClassesDataContext()
Dim rtnVal = (From od In dc.GetTable(Of Order_Detail)() _
Where od.OrderID = orderId _
Select (od.Product.UnitPrice * od.Quantity)).Sum()
Return rtnVal
End Function
Public Shared Function GetTopFiveOrdersById(ByVal SkipNumber As Integer)
Dim dc As New NorthwindDataClassesDataContext()
Dim rtnVal = (From ord In dc.GetTable(Of Order)() _
Order By ord.OrderID Ascending _
Select ord).Skip(SkipNumber).Take(5).ToList()
Return rtnVal
End Function
#End Region
The next region is “Insert Update Delete”; it contains examples of how to insert or update data, and an example showing how to delete data from the database. Each function is described in its annotation:
#Region "Insert Update Delete"
Public Shared Sub InsertOrUpdateCustomer(ByVal customerId As String, _
ByVal companyName As String, _
ByVal contactName As String, _
ByVal contactTitle As String, _
ByVal address As String, _
ByVal city As String, _
ByVal region As String, _
ByVal postalCode As String, _
ByVal country As String, _
ByVal phone As String, _
ByVal fax As String)
Dim dc As New NorthwindDataClassesDataContext()
Dim matchedCustomer = (From c In dc.GetTable(Of Customer)() _
Where c.CustomerID = customerId _
Select c).SingleOrDefault()
If (matchedCustomer Is Nothing) Then
Try
Dim customers As Table(Of Customer) = Accessor.GetCustomerTable()
Dim cust As New Customer With { _
.CustomerID = customerId, _
.CompanyName = companyName, _
.ContactName = contactName, _
.ContactTitle = contactTitle, _
.Address = address, _
.City = city, _
.Region = region, _
.PostalCode = postalCode, _
.Country = country, _
.Phone = phone, _
.Fax = fax}
customers.InsertOnSubmit(cust)
customers.Context.SubmitChanges()
Catch ex As Exception
Throw ex
End Try
Else
Try
matchedCustomer.CompanyName = companyName
matchedCustomer.ContactName = contactName
matchedCustomer.ContactTitle = contactTitle
matchedCustomer.Address = address
matchedCustomer.City = city
matchedCustomer.Region = region
matchedCustomer.PostalCode = postalCode
matchedCustomer.Country = country
matchedCustomer.Phone = phone
matchedCustomer.Fax = fax
dc.SubmitChanges()
Catch ex As Exception
Throw ex
End Try
End If
End Sub Public Shared Sub DeleteCustomer(ByVal customerID As String)
Dim dc As New NorthwindDataClassesDataContext()
Dim matchedCustomer = (From c In dc.GetTable(Of Customer)() _
Where c.CustomerID = customerID _
Select c).SingleOrDefault()
Try
dc.Customers.DeleteOnSubmit(matchedCustomer)
dc.SubmitChanges()
Catch ex As Exception
Throw ex
End Try
End Sub
#End Region
The last region of the class contains the code used to execute stored procedures. The stored procedures, once added to the project may be immediately accessed through the data context; to access a stored procedure just get an instance of the data context and call the stored procedure, passing along any required parameters as arguments to the function call:
#Region "Stored Prodedures"
Public Shared Function SalesByYear(ByVal beginningDate As DateTime?, ByVal
endingDate As DateTime?) _
As List(Of Sales_by_YearResult)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.Sales_by_Year(beginningDate, endingDate).ToList()
End Function
Public Shared Function TenMostExpensiveProducts() As List(Of
Ten_Most_Expensive_ProductsResult)
Dim dc As New NorthwindDataClassesDataContext()
Return dc.Ten_Most_Expensive_Products().ToList()
End Function
#End Region
That concludes the description of the Accessor class.
Code: Main Application Form (frmMain.vb)
This is the main form of the application; this form is used to provide a test harness for testing each of the functions defined in the Accessor class; all functions defined in the Accessor class have a corresponding menu item and the click event handler for each menu item executes an Accessor class function; supplying any arguments necessary as canned values.
The structure for the main form’s menu is as follows:
- Menu
- File
- Read
- Tables
- Queries
- Stored Procedures
- Insert/Update/Delete
Figure 13: frmMain.vb
The class begins with the normal and default imports:
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
The next section contains the class declaration.
Public Class frmMain
Next is the definition of a private variable used to maintain the position within the orders table; it used in an example showing how to make use of the Skip and Take functions.
Private OrderPosition As Integer
The next region of code in the class contains the constructor. The constructor sets the Order Position integer value to zero.
#Region "Constructor"
Public Sub New()
InitializeComponent()
OrderPosition = 0
End Sub
#End Region
The next code region is called ‘Full Table Requests’. Each of the functions operates in a similar manner in that the function creates a list of the type returned by the Accessor class function called, evokes the Accessor function and then assigns the returned list to the data source property of the datagridview control contained in the main form.
#Region "Full Table Requests"
Private Sub employeesToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles employeesToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetEmployeeTable()
End Sub Private Sub shippersToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles shippersToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetShipperTable()
End Sub Private Sub ordersToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ordersToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetOrderTable()
End Sub Private Sub employeeTerritoryToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles employeeTerritoryToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetEmployeeTerritoryTable()
End Sub Private Sub territoryToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles territoryToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetTerritoryTable()
End Sub Private Sub regionToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles regionToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetRegionTable()
End Sub Private Sub customerToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles customerToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetCustomerTable()
End Sub Private Sub customerDemoToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles customerDemoToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetCustomerCustomerDemoTable()
End Sub Private Sub customerDemographicToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles customerDemographicToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetCustomerDemographicTable()
End Sub Private Sub orderDetailsToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles orderDetailsToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetOrderDetailTable()
End Sub Private Sub productToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles productToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetProductTable()
End Sub Private Sub supplierProductToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles supplierProductToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetSupplierTable()
End Sub Private Sub categoToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles categoToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetCategoryTable()
End Sub
#End Region
The next region contains the menu item click event handlers used to execute each of the queries described in the queries region of the Accessor class. Each function is annotated to describe what it does and what it is intended to demonstrate.
#Region "Queries"
Private Sub employeeByIDToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles employeeByIDToolStripMenuItem.Click
Dim emp As New Employee
emp = Accessor.GetEmployeeById(1)
Dim sb As New StringBuilder()
sb.Append("Employee 1: " + Environment.NewLine)
sb.Append("Name: " + emp.FirstName + " " + emp.LastName +
Environment.NewLine)
sb.Append("Hire Date: " + emp.HireDate + Environment.NewLine)
sb.Append("Home Phone: " + emp.HomePhone + Environment.NewLine)
MessageBox.Show(sb.ToString(), "Employee ID Search")
End Sub Private Sub orderByIDToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles orderByIDToolStripMenuItem.Click
Dim ord As New Order()
ord = Accessor.GetOrderById(10248)
Dim sb As New StringBuilder()
sb.Append("Order: " + Environment.NewLine)
sb.Append("Order ID: " + ord.OrderID.ToString() + Environment.NewLine)
sb.Append("Date Shipped: " + ord.ShippedDate + Environment.NewLine)
sb.Append("Shipping Address: " + ord.ShipAddress + Environment.NewLine)
sb.Append("- City: " + ord.ShipCity + Environment.NewLine)
sb.Append("- Region: " + ord.ShipRegion + Environment.NewLine)
sb.Append("- Country: " + ord.ShipCountry + Environment.NewLine)
sb.Append("- Postal Code: " + ord.ShipPostalCode + Environment.NewLine)
sb.Append("Shipping Name: " + ord.ShipName + Environment.NewLine)
MessageBox.Show(sb.ToString(), "Shipping Information")
End Sub Private Sub employeesByHireDateToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles employeesByHireDateToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetEmployeesByHireDate()
End Sub Private Sub ordersByIdToolStripMenuItem_Click( _
ByVal sender As _
System.Object, _
ByVal e As System.EventArgs) _
Handles ordersByIdToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetOrdersById(10248)
End Sub Private Sub ordersAndDetailsToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ordersAndDetailsToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.OrdersAndDetails()
End Sub Private Sub ordersAndDetailsEntityRefToolStripMenuItem_Click( _
ByVal sender _
As System.Object, _
ByVal e As System.EventArgs) _
Handles ordersAndDetailsEntityRefToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.GetOrderAndPricingInformation()
End Sub Private Sub ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ordersAndDetailsByOrderIDEntityRefToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrderAndPricingInformationByOrderId(10248)
End Sub Private Sub orderValueByOrderIDToolStripMenuItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
orderValueByOrderIDToolStripMenuItem.Click
Dim d As Decimal? = Accessor.GetOrderValueByOrderId(10248)
Dim dollarValue As String = String.Format("{0:c}", d)
MessageBox.Show("The total dollar value of order 10248 is " & _
dollarValue, "Order 10248 Value")
End Sub Private Sub getTopFiveOrdersToolStripMenuItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
getTopFiveOrdersToolStripMenuItem.Click
Try
dataGridView1.DataSource = Accessor.GetTopFiveOrdersById(OrderPosition)
OrderPosition += 5
getTopFiveOrdersToolStripMenuItem.Text = "Get Next Five Orders"
Catch
MessageBox.Show("Cannot increment an higher, starting list over.")
OrderPosition = 0
End Try
End Sub
#End Region
The next region contains methods used to insert, update, or delete data from the database; these click event handlers evoke the corresponding functions contained in the Accessor class:
#Region "Insert Update Delete"
The Insert or Update Customer menu item click event handler calls the Accessor class Insert or Update Customer function, passing in some canned arguments to populate the last. If you look at the customer table before and after executing this click event handler you will see the customer added to the table.
Private Sub insertOrUpdateCustomerToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles insertOrUpdateCustomerToolStripMenuItem.Click
Try
Accessor.InsertOrUpdateCustomer("AAAAA", "BXSW", _
"Mookie Carbunkle", "Chieftain", _
"122 North Main Street", "Wamucka", _
"DC", "78888", "USA", _
"244-233-8977", "244-438-2933")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
End Try
End Sub
The Delete Customer menu item click event handler is used to delete the customer created by running the previous function; again, checking the table before and after running this click event handler will allow you to see the added customer deleted from the table.
Private Sub deleteCustomerToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles deleteCustomerToolStripMenuItem.Click
Try
Accessor.DeleteCustomer("AAAAA")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
End Try
End Sub
#End Region
The next region in this class is used to execute a couple of the stored procedures made available through the data context.
#Region "Stored Procedures"
Private Sub salesByYearToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles salesByYearToolStripMenuItem.Click
Dim startDate As New DateTime(1990, 1, 1)
Dim endDate As New DateTime(2000, 1, 1)
dataGridView1.DataSource = Accessor.SalesByYear(startDate, endDate)
End Sub Private Sub tenMostExpensiveProductsToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles tenMostExpensiveProductsToolStripMenuItem.Click
dataGridView1.DataSource = Accessor.TenMostExpensiveProducts()
End Sub
#End Region
The last region contained in the class is the housekeeping region; in this region there is only one click event handler which is used to exit the application.
#Region "Housekeeping"
Private Sub exitToolStripMenuItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles exitToolStripMenuItem.Click
Application.Exit()
End Sub
#End Region
Summary
The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
TY