Click here to Skip to main content
Click here to Skip to main content

Using LINQ to SQL in Visual Basic

, 6 Jun 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
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 proce

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).

image001.jpg

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:

image002.jpg

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:

image003.png

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:

image004.png

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

image005.png

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:

image006.jpg

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

image007.png

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:

image008.png

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:

image009.jpg

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.

image010.jpg

Figure 10: Settings and the Connection String

image011.jpg

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.

image012.jpg

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.

''' <span class="code-SummaryComment"><summary></span>
''' This class defines functions used to
''' select, insert, update, and delete data
''' using LINQ to SQL and the defined
''' data context
''' <span class="code-SummaryComment"></summary></span>
''' <span class="code-SummaryComment"><remarks></remarks></span>
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

    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Employee Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetEmployeeTable() As  _
    System.Data.Linq.Table(Of Employee)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Employee)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Shipper Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetShipperTable() As  _
    System.Data.Linq.Table(Of Shipper)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Shipper)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Order Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetOrderTable() As  _
    System.Data.Linq.Table(Of Order)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Order)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Employee Territory Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetEmployeeTerritoryTable() As  _
        System.Data.Linq.Table(Of EmployeeTerritory)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of EmployeeTerritory)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Territory Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetTerritoryTable() As  _
        System.Data.Linq.Table(Of Territory)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Territory)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Region Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetRegionTable() As  _
        System.Data.Linq.Table(Of Region)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Region)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Customer Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetCustomerTable() As  _
        System.Data.Linq.Table(Of Customer)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Customer)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full CustomerCustomerDemo Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetCustomerCustomerDemoTable() As  _
        System.Data.Linq.Table(Of CustomerCustomerDemo)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of CustomerCustomerDemo)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Customer Demographic Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetCustomerDemographicTable() As  _
        System.Data.Linq.Table(Of CustomerDemographic)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of CustomerDemographic)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Order_Detail Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Product Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetProductTable() As  _
        System.Data.Linq.Table(Of Product)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Product)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Supplier Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Public Shared Function GetSupplierTable() As  _
    System.Data.Linq.Table(Of Supplier)

        Dim dc As New NorthwindDataClassesDataContext()
        Return dc.GetTable(Of Supplier)()

    End Function



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Returns the Full Category Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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

    ''' <span class="code-SummaryComment"><summary></span>
    ''' Example:  Where Clause
    ''' Returns an employee where the
    ''' employee ID matches the value
    ''' passed in as empID
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="empId"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Example:  Select to a single returned object
    ''' using a Where Clause
    '''
    ''' Returns the first matching order
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="ordId"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Example:  Select to a typed List
    ''' using a Where Clause
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="ordId"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Example:  Return an ordered list
    '''
    ''' Converts the returned value to a List
    ''' of type Employee; the list is ordered
    ''' by hire date
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Defined to support following function:
    ''' GetOrderAndPricingInformation - this class
    ''' supplies the return type for that function
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="ordId"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="orderId"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="SkipNumber"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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

    ''' <span class="code-SummaryComment"><summary></span>
    ''' Insert a customer if the customer does not exist, or
    ''' update the customer if it does exist
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="customerId"></param></span>
    ''' <span class="code-SummaryComment"><param name="companyName"></param></span>
    ''' <span class="code-SummaryComment"><param name="contactName"></param></span>
    ''' <span class="code-SummaryComment"><param name="contactTitle"></param></span>
    ''' <span class="code-SummaryComment"><param name="address"></param></span>
    ''' <span class="code-SummaryComment"><param name="city"></param></span>
    ''' <span class="code-SummaryComment"><param name="region"></param></span>
    ''' <span class="code-SummaryComment"><param name="postalCode"></param></span>
    ''' <span class="code-SummaryComment"><param name="country"></param></span>
    ''' <span class="code-SummaryComment"><param name="phone"></param></span>
    ''' <span class="code-SummaryComment"><param name="fax"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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''' <span class="code-SummaryComment"><summary></span>
    ''' Delete a customer by customer ID
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="customerID"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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"


    ''' <span class="code-SummaryComment"><summary></span>
    ''' Stored Procedure:  Sales By Year
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="beginningDate"></param></span>
    ''' <span class="code-SummaryComment"><param name="endingDate"></param></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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



    ''' <span class="code-SummaryComment"><summary></span>
    ''' Stored Procedure:  Ten Most Expenisve Products
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><returns></returns></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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
      • Exit
    • Read
      • Tables
      • Queries
      • Stored Procedures
    • Insert/Update/Delete

image013.png

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.

''' <span class="code-SummaryComment"><summary></span>
''' Demonstration Application - this form
''' class is used to test each of the functions
''' and subroutines defined in the Accessor
''' class
''' <span class="code-SummaryComment"></summary></span>
''' <span class="code-SummaryComment"><remarks></remarks></span>
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"


    ''' <span class="code-SummaryComment"><summary></span>
    ''' Display full employee table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub employeesToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles employeesToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetEmployeeTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display full shippers table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub shippersToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles shippersToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetShipperTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display full orders table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub ordersToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles ordersToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetOrderTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display full employee territory table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub employeeTerritoryToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles employeeTerritoryToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetEmployeeTerritoryTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full territory table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub territoryToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles territoryToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetTerritoryTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display full region table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub regionToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles regionToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetRegionTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display full customer table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub customerToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles customerToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetCustomerTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full customer customer demo table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub customerDemoToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles customerDemoToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetCustomerCustomerDemoTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full customer demographic table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub customerDemographicToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles customerDemographicToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetCustomerDemographicTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full order_detail table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub orderDetailsToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles orderDetailsToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetOrderDetailTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full product table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub productToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles productToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetProductTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full supplier table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub supplierProductToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles supplierProductToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetSupplierTable()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Display the full category table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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"


    ''' <span class="code-SummaryComment"><summary></span>
    ''' Find and display an employee by 
    ''' the employee's ID
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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''' <span class="code-SummaryComment"><summary></span>
    ''' Gets an Order by the order ID and
    ''' displays information about the first
    ''' single matching order.
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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''' <span class="code-SummaryComment"><summary></span>
    ''' Displays a list of employeess ordered by
    ''' their dates of hire
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub employeesByHireDateToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles employeesByHireDateToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetEmployeesByHireDate()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Displays all orders that match
    ''' on Order ID
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub ordersByIdToolStripMenuItem_Click( _
            ByVal sender As  _
            System.Object, _
            ByVal e As System.EventArgs) _
            Handles ordersByIdToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetOrdersById(10248)

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Returns values based on joining the Order and
    ''' Order_Details tables
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub ordersAndDetailsToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles ordersAndDetailsToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.OrdersAndDetails()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' 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.
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub ordersAndDetailsEntityRefToolStripMenuItem_Click( _
            ByVal sender _
            As System.Object, _
            ByVal e As System.EventArgs) _
            Handles ordersAndDetailsEntityRefToolStripMenuItem.Click

        dataGridView1.DataSource = Accessor.GetOrderAndPricingInformation()

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' Retrieves values across an entity set to 
    ''' display both order and pricing information 
    ''' by filtering for an order ID
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    Private Sub ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
            Handles ordersAndDetailsByOrderIDEntityRefToolStripMenuItem.Click

        dataGridView1.DataSource = 
        Accessor.GetOrderAndPricingInformationByOrderId(10248)

    End Sub''' <span class="code-SummaryComment"><summary></span>
    ''' 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.
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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''' <span class="code-SummaryComment"><summary></span>
    ''' 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
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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.

    ''' <span class="code-SummaryComment"><summary></span>
    ''' Insert or Update a Customer into
    ''' the Customer Table
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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.

    ''' <span class="code-SummaryComment"><summary></span>
    ''' Delete an existing customer from
    ''' the customer table if the customer
    ''' ID matches
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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"


    ''' <span class="code-SummaryComment"><summary></span>
    ''' Execute the Sales by Year stored
    ''' procedure and display the results
    ''' in the datagrid
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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''' <span class="code-SummaryComment"><summary></span>
    ''' Execute the Ten Most Expensive Products
    ''' stored procedure and display the
    ''' results in the datagri
    ''' <span class="code-SummaryComment"></summary></span>
    ''' <span class="code-SummaryComment"><param name="sender"></param></span>
    ''' <span class="code-SummaryComment"><param name="e"></param></span>
    ''' <span class="code-SummaryComment"><remarks></remarks></span>
    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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

salysle
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey3-May-13 23:29 
GeneralMy vote of 5 PinmemberVitorHugoGarcia22-Jan-13 4:44 
QuestionHi There, Pinmembersalman_s22-Dec-12 22:26 
GeneralLINQ Example Pinmembersanabil6-Jun-12 2:38 
GeneralMy vote of 4 PinmemberShridhar Kulkarni8-Aug-11 19:58 
QuestionHow can I have all that using a Dataset? Pinmemberpaladinkerb9-Apr-11 16:19 
GeneralMy vote of 5 PinmemberWizard76-Jul-10 18:37 
Questionhow to return dataset Pinmemberpraveenkumar palla19-Jun-09 3:40 
Generalquestion and thnx Pinmemberel3ashe212-May-09 23:37 
GeneralThanks! Pinmemberla2texascoder1-Jan-09 10:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 6 Jun 2008
Article Copyright 2008 by salysle
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid