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 Get Table 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 (http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en);
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.
'''
<summary>
''' This class defines functions
used to
''' select, insert, update, and
delete data
''' using LINQ to SQL and the
defined
''' data context
'''
</summary>
'''
<remarks></remarks>
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"
' This section
contains examples of
' pulling back
entire tables from
' the database
'''
<summary>
''' Returns the
Full Employee Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetEmployeeTable() As _
System.Data.Linq.Table(Of
Employee)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Employee)()
End
Function
'''
<summary>
''' Returns the
Full Shipper Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetShipperTable() As _
System.Data.Linq.Table(Of
Shipper)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Shipper)()
End
Function
'''
<summary>
''' Returns the
Full Order Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetOrderTable() As _
System.Data.Linq.Table(Of
Order)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Order)()
End
Function
'''
<summary>
''' Returns the
Full Employee Territory Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetEmployeeTerritoryTable() As _
System.Data.Linq.Table(Of
EmployeeTerritory)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of EmployeeTerritory)()
End
Function
'''
<summary>
''' Returns the
Full Territory Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetTerritoryTable() As _
System.Data.Linq.Table(Of
Territory)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Territory)()
End
Function
'''
<summary>
''' Returns the
Full Region Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetRegionTable() As _
System.Data.Linq.Table(Of
Region)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Region)()
End
Function
'''
<summary>
''' Returns the
Full Customer Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetCustomerTable() As _
System.Data.Linq.Table(Of
Customer)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Customer)()
End
Function
'''
<summary>
''' Returns the
Full CustomerCustomerDemo Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetCustomerCustomerDemoTable() As _
System.Data.Linq.Table(Of
CustomerCustomerDemo)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of CustomerCustomerDemo)()
End
Function
'''
<summary>
''' Returns the
Full Customer Demographic Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetCustomerDemographicTable() As _
System.Data.Linq.Table(Of
CustomerDemographic)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of CustomerDemographic)()
End
Function
'''
<summary>
''' Returns the
Full Order_Detail Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
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
'''
<summary>
''' Returns the
Full Product Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetProductTable() As _
System.Data.Linq.Table(Of
Product)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Product)()
End
Function
'''
<summary>
''' Returns the
Full Supplier Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public
Shared Function
GetSupplierTable() As _
System.Data.Linq.Table(Of
Supplier)
Dim dc
As New
NorthwindDataClassesDataContext()
Return
dc.GetTable(Of Supplier)()
End
Function
'''
<summary>
''' Returns the
Full Category Table
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
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"
' This region contains examples of some
' of the sorts of queries that can be
' executed using LINQ to SQL
''' <summary>
''' Example: Where Clause
''' Returns an employee where the
''' employee ID matches the value
''' passed in as empID
''' </summary>
''' <param
name="empId"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetEmployeeById(ByVal
empId As Integer)
As Employee
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' get the first Employee with and employee ID
' matching the employee ID passed in as an
' argument to this function
Dim retVal = (From
e In dc.GetTable(Of
Employee)() _
Where (e.EmployeeID = empId) _
Select e).FirstOrDefault()
Return retVal
End Function
''' <summary>
''' Example: Select to a single returned object
''' using a Where Clause
'''
''' Returns the first matching order
''' </summary>
''' <param
name="ordId"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetOrderById(ByVal
ordId As Integer)
As Order
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' return a single value from the orders table
' where the order Id match the ordId argument
' passed to this function
Dim retVal = (From
ord In dc.GetTable(Of
Order)() _
Where (ord.OrderID = ordId) _
Select ord).FirstOrDefault()
Return retVal
End Function
''' <summary>
''' Example: Select to a typed List
''' using a Where Clause
''' </summary>
''' <param
name="ordId"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetOrdersById(ByVal
ordId As Integer)
As List(Of
Order)
' get the context
Dim dc As
New NorthwindDataClassesDataContext()
' get a list of Orders where the Order ID matches
' the ordId argument and return the collection as
' a list of type Order
Dim retVal = (From
ord In dc.GetTable(Of
Order)() _
Where (ord.OrderID = ordId) _
Select ord).ToList()
Return retVal
End Function
''' <summary>
''' Example: Return an ordered list
'''
''' Converts the returned value to a List
''' of type Employee; the list is ordered
''' by hire date
''' </summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetEmployeesByHireDate()
As List(Of
Employee)
'
get the context
Dim dc As
New NorthwindDataClassesDataContext()
' get the Employee table, order it by HireDate
' and return the result as a list of type Employee
Dim retVal = (From
emp In dc.GetTable(Of
Employee)() _
Order
By emp.HireDate
Ascending _
Select emp).ToList()
Return retVal
End Function
''' <summary>
''' This class is used to define the return type
''' for the next function - OrdersAndDetails
'''
''' When results are extracted from multiple tables
''' you can either return the results as anonymous
''' or as a type; this class defines the return
''' type used by OrdersAndDetails
''' </summary>
'''
<remarks></remarks>
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
''' <summary>
''' Example: Joins
''' Joining using the join keyword
'''
''' The values are set to each of the
''' properties contained in the
''' OrdersAndDetailsResult class
'''
''' The value returned is converted
''' to a list of the specified type
''' </summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function OrdersAndDetails()
As List(Of
OrdersAndDetailsResult)
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' join Orders on Order_Details, order the list
' by CustomerID and select the results into a new
' instance of OrdersAndDetailsResults, return the
' collection as a list of that type
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
''' <summary>
''' Defined to support following function:
''' GetOrderAndPricingInformation - this class
''' supplies the return type for that function
''' </summary>
'''
<remarks></remarks>
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
''' <summary>
''' Example: Query across an entity ref
''' This example collections information from the
orders table
''' and the order_details table through the orders
table
''' entity association to the orders_details table.
'''
''' An entity is a representation in the model of a
table
''' in the database, foreign key relationships are
maintained
''' as entity references to the related tables in the
model.
''' It is possible to query across tables through
this
''' relationship in LINQ to SQL
''' </summary>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetOrderAndPricingInformation()
As List(Of
OrderAndPricingResult)
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' select values from the Orders and Order_Details
' tables into a new instance of OrderAndPricingResult
' and return the collection as a list of that type
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
''' <summary>
''' Example: Query across entity ref with Where
class
''' Same as previous function with added where clause
'''
''' An entity is a representation in the model of a
table
''' in the database, foreign key relationships are
maintained
''' as entity references to the related tables in the
model.
''' It is possible to query across tables through
this
''' relationship in LINQ to SQL
''' </summary>
''' <param
name="ordId"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function
GetOrderAndPricingInformationByOrderId(ByVal
ordId As
Integer) As
List(Of OrderAndPricingResult)
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' select values from the Orders and Order_Details
' tables into a new instance of OrderAndPricingResult
' and then return the collection as a list of
' that type
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
''' <summary>
''' Example: Aggregation
'''
''' Returns the total sum of the order
''' selected by order ID by selecting
''' unit price multiplied by quantity
''' ordered and then calling sum for
''' the total
''' </summary>
''' <param
name="orderId"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetOrderValueByOrderId(ByVal
orderId As Integer)
As Decimal?
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' get the order with a matching order ID and then
' multiply the unit price by the quantity, when
' all matching order items have been calculated
' individually into a collection, sum the total of
' that collection and return the value
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
''' <summary>
''' Example: Using Take to get a limited
''' number of returned values for display and
''' using Skip to sequence to a different
''' starting point within the returned values -
''' can be used to navigate through a large
''' list
''' </summary>
''' <param
name="SkipNumber"></param>
'''
<returns></returns>
'''
<remarks></remarks>
Public Shared
Function GetTopFiveOrdersById(ByVal
SkipNumber As Integer)
' get the data context
Dim dc As
New NorthwindDataClassesDataContext()
' order the table by Order ID
' and then skip down the SkipNumber of records and
' take the next file records, covert that to
' a list and return it
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"
' This sections
contains examples of
' inserting,
updating, and deleting data
'''
<summary>
''' Insert a
customer if the customer does not exist, or
''' update the
customer if it does exist
'''
</summary>
'''
<param name="customerId"></param>
'''
<param name="companyName"></param>
'''
<param name="contactName"></param>
'''
<param name="contactTitle"></param>
'''
<param name="address"></param>
'''
<param name="city"></param>
'''
<param name="region"></param>
'''
<param name="postalCode"></param>
'''
<param name="country"></param>
'''
<param name="phone"></param>
'''
<param name="fax"></param>
'''
<remarks></remarks>
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()
' Look for an
existing customer with the
' customer ID
Dim
matchedCustomer = (From c
In dc.GetTable(Of
Customer)() _
Where c.CustomerID = customerId _
Select c).SingleOrDefault()
If
(matchedCustomer Is
Nothing) Then
' there was
not matching customer
Try
'
create a new customer record since the customer ID
' does
not exist
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}
' add
the new customer to the database
customers.InsertOnSubmit(cust)
customers.Context.SubmitChanges()
Catch
ex As Exception
Throw
ex
End
Try
Else
' the
customer already exists, so update
' the
customer with new information
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
'
submit the changes to the database
dc.SubmitChanges()
Catch
ex As Exception
Throw
ex
End
Try
End
If
End
Sub
'''
<summary>
''' Delete a
customer by customer ID
'''
</summary>
'''
<param name="customerID"></param>
'''
<remarks></remarks>
Public
Shared Sub
DeleteCustomer(ByVal customerID
As String)
' get the data
context
Dim dc
As New
NorthwindDataClassesDataContext()
' find the
customer with a matching customer ID
Dim
matchedCustomer = (From c
In dc.GetTable(Of
Customer)() _
Where c.CustomerID = customerID _
Select c).SingleOrDefault()
Try
' delete
the matching customer
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"
'''
<summary>
''' Stored
Procedure: Sales By Year
'''
</summary>
'''
<param name="beginningDate"></param>
'''
<param name="endingDate"></param>
'''
<returns></returns>
'''
<remarks></remarks>
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
'''
<summary>
''' Stored
Procedure: Ten Most Expenisve Products
'''
</summary>
'''
<returns></returns>
'''
<remarks></remarks>
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.
'''
<summary>
''' Demonstration Application - this
form
''' class is used to test each of
the functions
''' and subroutines defined in the
Accessor
''' class
'''
</summary>
'''
<remarks></remarks>
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.
' used to support
take/skip example
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()
' This call is
required by the Windows Form Designer.
InitializeComponent()
' Add any
initialization after the InitializeComponent() call.
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"
'''
<summary>
''' Display full
employee table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub employeesToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
employeesToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetEmployeeTable()
End
Sub
'''
<summary>
''' Display full
shippers table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub shippersToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
shippersToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetShipperTable()
End
Sub
'''
<summary>
''' Display full
orders table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub ordersToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
ordersToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrderTable()
End
Sub
'''
<summary>
''' Display full
employee territory table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub employeeTerritoryToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
employeeTerritoryToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetEmployeeTerritoryTable()
End
Sub
'''
<summary>
''' Display the
full territory table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub territoryToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
territoryToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetTerritoryTable()
End
Sub
'''
<summary>
''' Display full
region table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub regionToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
regionToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetRegionTable()
End
Sub
'''
<summary>
''' Display full
customer table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub customerToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
customerToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetCustomerTable()
End
Sub
'''
<summary>
''' Display the
full customer customer demo table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub customerDemoToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
customerDemoToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetCustomerCustomerDemoTable()
End
Sub
'''
<summary>
''' Display the
full customer demographic table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub customerDemographicToolStripMenuItem_Click(
_
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
customerDemographicToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetCustomerDemographicTable()
End
Sub
'''
<summary>
''' Display the
full order_detail table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub orderDetailsToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
orderDetailsToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrderDetailTable()
End
Sub
'''
<summary>
''' Display the
full product table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub productToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
productToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetProductTable()
End
Sub
'''
<summary>
''' Display the
full supplier table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub supplierProductToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
supplierProductToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetSupplierTable()
End
Sub
'''
<summary>
''' Display the
full category table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
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"
'''
<summary>
''' Find and
display an employee by
''' the employee's
ID
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
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
'''
<summary>
''' Gets an Order
by the order ID and
''' displays
information about the first
''' single matching
order.
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
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
'''
<summary>
''' Displays a list
of employeess ordered by
''' their dates of
hire
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub employeesByHireDateToolStripMenuItem_Click(
_
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
employeesByHireDateToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetEmployeesByHireDate()
End
Sub
'''
<summary>
''' Displays all
orders that match
''' on Order ID
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub ordersByIdToolStripMenuItem_Click( _
ByVal
sender As _
System.Object, _
ByVal
e As System.EventArgs) _
Handles
ordersByIdToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrdersById(10248)
End
Sub
'''
<summary>
''' Returns values
based on joining the Order and
''' Order_Details
tables
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub ordersAndDetailsToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
ordersAndDetailsToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.OrdersAndDetails()
End
Sub
'''
<summary>
''' Query across
entity set
''' This example
collections information from the orders table
''' and the
order_details table through the orders table
''' entity
reference to orders_details.
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub
ordersAndDetailsEntityRefToolStripMenuItem_Click( _
ByVal
sender _
As
System.Object, _
ByVal
e As System.EventArgs) _
Handles
ordersAndDetailsEntityRefToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrderAndPricingInformation()
End
Sub
'''
<summary>
''' Retrieves
values across an entity set to
''' display both
order and pricing information
''' by filtering
for an order ID
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub
ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
ordersAndDetailsByOrderIDEntityRefToolStripMenuItem.Click
dataGridView1.DataSource =
Accessor.GetOrderAndPricingInformationByOrderId(10248)
End
Sub
'''
<summary>
''' Displays to
total dollar value of the selected order
''' by multiplying
each order product's unit cost by
''' the units
ordered, and then summing the total of each
''' individual
cost.
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub orderValueByOrderIDToolStripMenuItem_Click(ByVal
sender As
System.Object, ByVal
e As System.EventArgs)
Handles
orderValueByOrderIDToolStripMenuItem.Click
' get the
dollar value
Dim d
As Decimal? =
Accessor.GetOrderValueByOrderId(10248)
' convert the
decimal value to currency
Dim
dollarValue As String
= String.Format("{0:c}",
d)
' display the
dollar value
MessageBox.Show("The
total dollar value of order 10248 is " & _
dollarValue,
"Order 10248 Value")
End
Sub
'''
<summary>
''' Displays the
top five orders in the order table
''' on first
selection and then increments up by
''' five orders to
show the list five orders
''' at a time
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub getTopFiveOrdersToolStripMenuItem_Click(ByVal
sender As
System.Object, ByVal
e As System.EventArgs)
Handles
getTopFiveOrdersToolStripMenuItem.Click
Try
' get the
top five orders starting at the current position
dataGridView1.DataSource =
Accessor.GetTopFiveOrdersById(OrderPosition)
' increment
the formwide variable used to
' keep
track of the position within the
' list of
orders
OrderPosition += 5
' change
the text in the menu strip item
' to show
that it will retrieve the next
' five
values after the current position
' of the
last value shown in the grid
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.
'''
<summary>
''' Insert or
Update a Customer into
''' the Customer
Table
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub
insertOrUpdateCustomerToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
insertOrUpdateCustomerToolStripMenuItem.Click
Try
' insert or
update customer
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.
'''
<summary>
''' Delete an
existing customer from
''' the customer
table if the customer
''' ID matches
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
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"
'''
<summary>
''' Execute the
Sales by Year stored
''' procedure and
display the results
''' in the datagrid
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
Private
Sub salesByYearToolStripMenuItem_Click( _
ByVal
sender As System.Object, _
ByVal
e As System.EventArgs) _
Handles
salesByYearToolStripMenuItem.Click
' define a
starting and ending date
Dim
startDate As New
DateTime(1990, 1, 1)
Dim
endDate As New
DateTime(2000, 1, 1)
dataGridView1.DataSource =
Accessor.SalesByYear(startDate, endDate)
End
Sub
'''
<summary>
''' Execute the Ten
Most Expensive Products
''' stored
procedure and display the
''' results in the
datagri
'''
</summary>
'''
<param name="sender"></param>
'''
<param name="e"></param>
'''
<remarks></remarks>
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.