30 Oktober 2008

pengertian data base

Pengertian database

DaTaBaSe adalah kumpulan informasi yang disusun berdasarkan cara tertentu dan merupakan suatu kesatuan yang utuh. Dengan sistem tersebut data yang terhimpun dalam suatu database dapat menghasilkan informasi yang berguna.

LangKah-LangKah:

1. Membuat Database Baru
Dalam Microsoft Access, tiap database tersimpan dalam sebuah file dengan ekstensi .mdb. Tiap objek baik itu tabel, form, report, query, data access page, atau lainnya yang Anda ciptakan dalam database itu juga disimpan dalam file ini. Jadi, satu file berisi semua objek dalam database Anda. Ikutilah langkah-langkah berikut ini untuk membuat database baru.
Untuk menjalankan Microsoft Access langkahnya sebagai berikut:
  • Klik Start
  • Klik All Programs
  • Klik Microsoft Access. Setelah itu segera tampil jendela Microsoft Access
  • Klik Blank Database, karena kita akan membuat database baru. Setelah Anda mengklik Blank Database, maka akan tampil kotak dialog Create database
  • Tentukan lokasi atau folder untuk menyimpan file Database baru Anda, misalnya di My Document
  • Ketikkan sebuah nama file untuk Database baru Anda, misalnya LATIHAN.
  • Klik Create. Access secara otomatis akan menambahkan ekstensi .mdb ke nama file itu.
  • Teks di bagian atas di batang judul jendela database berisi nama dalam contoh ini bernama LATIHAN. Dalam Microsoft Access 2002 (XP) jendela database mempunyai dua bagian, bagian sebelah kiri menampilkan tipe objek dan group, sedangkan bagian kanan berisi objek database dan shortcut untuk menciptakan objek baru
  • Di bawah heading objects, ada beberapa tipe objek yang bisa dipilih dan digunakan untuk berbagai keperluan, jika Anda mengklik Tables, maka Microsoft Access akan menampilkan semua tabel dalam database yang letaknya di bagian kanan jendela Database.


2. Merancang Tabel Baru
Anda bisa menyimpan bermacam-macam data dalam tabel yang sudah dibuat tergantung kebutuhan. Sebagai contoh seperti ditampilkan pada tabel 2.1. adalah tabel yang berisi informasi dari buku alamat penulis.
Nama Alamat Kota Telepon
Tutang Jalan Raya Bogor KM 46 Cibinong 0218761985
Idam Cubriadi Jalan Babakan Bogor 025165656
Yuniar Supriadi Jalan Ciawi Bogor 025132318


Sebuah tabel terdiri atas field dan record. Field mengkategorikan data dan record adalah data itu sendiri. Seperti Anda lihat yang ditampilkan pada tabel di atas terdiri atas empat field dan mempunyai tiga record. Sebelum Anda menggunakan tabel dalam Microsoft Access sebelumnya Anda harus merancang tabel tersebut.

Langkah pertama yang harus Anda lakukan adalah menentukan field-field yang diperlukan dan jenis data untuk setiap field.


Pengertian dan Kegunaan Query.

Jika anda telah membuat tabel-tabel dan telah memasukkan banyak data dalam tabel , suatu ketika Anda menginginkan untuk mengetahui/mencari data tertentu. Hal ini dapat anda lakukan dengan :

  • membuka tabel,
  • menelusuri data satu per satu,
  • lalu membuat tabel baru untuk menyimpannya.


Akan tetapi cara seperti ini sangat tidak efisien. Untuk kebutuhan tersebut, akan jauh lebih mudah jika anda menggunakan query untuk memenuhi permintaan data kita Pada dasarnya, query adalah merupakan bahasa untuk melakukan manipulasi terhadap database, yang telah distandarkan dan lebih dikenal dengan nama Structured Query Language (SQL).


Query dibedakan menjadi 2, yaitu
1. Untuk membuat/mendefinisikan obyek-obyek database seperti membuat tabel, relasi dan sebagainya. Biasanya disebut dengan Data Definition Language (DDL)
2. Untuk memanipulasi data, yang biasanya dikenal dengan Data Manipulation Language (DML). Manipulasi data bisa berupa: a. Menambah, mengubah atau menghapus data. b. Pengambilan informasi yang diperlukan dari database, yang mana datanya diambil dari tabel maupun dari query sebelumnya.


Tipe Data Field
Field-field dalam sebuah tabel haruslah ditentukan tipe datanya agar Access mengetahui bagaimana agar data tersebut dapat diolah dan memperlakukan data dalam field itu. Oleh karena itu sangat penting bagi kita untuk memahami berbagai tipe data field yang ada. Bahkan pengetahuan mengenai hal ini akan bermanfaat ketika Anda merancang berbagai macam tabel dalam sebuah database.


Text
Ini adalah tipe data yang paling umum digunakan. Tipe data ini bisa digunakan untuk field alfanumeric, seperti nama, alamat, kode pos, nomor telepon dan sebagainya. Microsoft Access memberi keleluasaan bagi Anda untuk memasukkan data sampai 255 karakter ke field dengan tipe data ini.


Memo
Tipe data ini mirip dengan teks, tetapi bisa menampung sampai dengan 64.000 karakter. Tipe data text lebih sering dugunakan karena memo tidak bisa diurutkan atau diindeks.


Number
Tipe data ini digunakan untuk menyimpan data numeric yang akan digunakan untuk perhitungan matematis. Anda tidak menggunakan tipe data ini untuk nomor telepon, misalnya, karena nomor telepon tidak perlu diproses secara matematis.


Date/Time
Tipe data ini digunakan untuk nilai tanggal dan waktu. Misalnya, Anda bisa menggunakan tipe data ini untuk tanggal lahir atau tanggal pembelian.


Currency
Tipe data ini terutama digunakan untuk nilai mata uang. Tetapi, Anda juga bisa menyertakan data numeric dalam perhitungan yang memerlukan satu sampai empat angka desimal. Tipe data ini, misalnya, bisa digunakan untuk nilai pembelian atau ongkos pengantaran.


Auto Number
Ini adalah angka yang secara otomatis akan terurut atau angka acak yang ditetapkan Microsoft Access untuk record baru yang ditambahkan ke sebuah tabel. Field AutoNumber tidak bisa diedit. Oleh karena itu jenis data ini digunakan untuk field yang harus unik, artinya tidak boleh ada nilai yang sama untuk field ini. Sebagai contoh Anda bisa menggunakan tipe data ini untuk NIM, NIP, KARPEG atau Nomor Induk Siswa.


Yes/No
Tipe data ini hanya bisa mempunyai dua nilai yaitu Yes atau No. Data ini digunakan untuk field yang hanya mempunyai dua nilai. Misalnya, sebuah tabel yang berisi informasi mengenai karyawan bisa menggunakan tipe data ini untuk field status perkawinan di mana hanya memiliki dua nilai yaitu Ya atau Tidak.


OLE Object
Tipe data ini digunakan untuk eksternal objek, seperti bitmap atau file suara. Misalnya, Anda bisa menggunakan tipe data ini untuk menyimpan gambar bitmap (foto) karyawan dalam tabel Karyawan. Meskipun file bitmap yang digunakan disimpan dalam tabel, tetapi file bitmap ini hanya bisa ditampilkan dalam form atau report.


Hyperlink
Ini merupakan gabungan dari teks dan bilangan yang disimpan sebagai teks dan digunakan sebagai alamat hyperlink. Hyperlink digunakan untuk menyimpan pointer ke sebuah situs web atau path ke sebuah file di harddisk. Bila Anda mengklik sebuah hyperlink, Microsoft Access akan mencoba membuka file tersebut dalam aplikasi asli file yang bersangkutan.


Lookup Wizard
Bila Anda menggunakan tipe data ini untuk sebuah field, Anda bisa memilih sebuah nilai dari tabel lain atau dari sebuah daftar nilai yang ditampilkan dalam sebuah kotak daftar atau kotak combo.


3.Menambahkan tabel

Sebagai gambaran dan latihan Anda berikut ini akan dijelaskan cara untuk membuat sebuah tabel baru dalam sebuah database. Sebagai contoh Anda akan membuat tabel buku alamat ke dalam sebuah database di Microsoft Access.

  • Dalam jendela Database bernama LATIHAN di bawah Objects pilih Tables.
  • Klik dua kali Create Table and Design View. Setelah itu segera tampil jendela untuk mendefinisikan tabel Anda.
  • Pada frame sebelah atas di jendela terdapat tiga kolom, yaitu Field Name, Data Type dan Description. Anda bisa mulai dengan memasukkan data dalam kolom Field dan Data Type.
  • Ketikkan NAMA di bawah FieldName, lalu klik Data Type dan pilih Text. Secara default nilai Field Size untuk tipe Data Text adalah 50. Ini artinya Anda hanya bisa memasukkan sampai dengan 50 karakter untuk tiap nilai dalam field ini.
  • Ketikkan ALAMAT, lalu klik Data Type dan pilih Text
  • Ketikkan KOTA, lalu klik Data Type dan pilih Text
  • Ketikkan NOMOR TELEPON, lalu klik Data Type dan pilih Text
  • Ketikkan TANGGAL LAHIR, lalu klik Data Type dan pilih Date/Time


Catatan:
Kolom Description digunakan untuk keterangan singkat tiap field, meskipun isi dari kolom ini bukan merupakan bagian dari data Anda dan tidak ditampilkan dalam tabel. Untuk memasukkan deskripsi sebuah field, klik sel dalam kolom Description dan ketikkan deskripsi yang akan Anda masukkan.

  1. Tutup tampilan Design dengan mengklik tombol Close Window di sebelah kanan atas tampilan Design.
  2. Microsoft Access akan bertanya apakah Anda ingin menyimpan desain tabel tersebut? Klik Yes untuk menyimpannya. Karena ini adalah tabel baru yang belum ada namanya, maka Microsoft Access akan meminta Anda mengetikkan sebuah nama. Ketikkan sebuah nama yang sesuai, misalnya Daftar Alamat.
  3. Klik OK.
  4. Microsoft Access akan mengingatkan bahwa tabel Anda belum mempunyai kunci primer (primary key). Pada bagian ini Anda tidak harus mengetahui terlebih dahulu mengenai kunci primer tersebut. Karena akan dibahas di bagian selanjutnya.
  5. Klik No. Pada jendela Database yang sudah Anda buat klik Tables di bawah Objects dan perhatikan sekarang sudah terdapat satu ikon tambahan bernama DAFTAR ALAMAT


4. Mengubah Desain Tabel
Jika Anda sedang membangun sebuah database, maka kadang tabel yang dibuat perlu diubah, umpamanya Anda perlu menambahkan field baru atau menghapus sebuah field dari tabel tersebut.
Untuk mengubah desain sebuah tabel, yang harus Anda lakukan adalah dengan membukanya dalam tampilan Design. Caranya klik nama tabel dalam tab Tables, kemudian klik tombol Design. Tiap kali Anda mengubah desain sebuah tabel, tekan tombol Ctrl+S untuk menyimpan desain tabel tersebut.


5. Menambahkan Field
Kadang-kadang kita perlu menambahkan field baru ke bagian akhir dari suatu tabel atau menyisipkannya ke tengah-tengah field-field yang ada. Hal ini sering terjadi bagi seorang developer database.

5.1. Menambahkan Field Baru ke Akhir Tabel
Untuk menambahkan field baru ke bagian akhir dari suatu tabel langkah yang harus Anda lakukan adalah sebagai berikut:

  • Dalam tampilan Design, klik sel kosong dalam kolom Field name sesudah entri Field Name yang terakhir
  • Ketikkan nama field yang baru, misalnya KODE POS, pilih Data Type dan klik Text
  • Ketikkan deskripsi untuk field yang baru dalam kolom Description jika diperlukan.
  • Tekan tombol Ctrl+S untuk menyimpan desain tabel tersebut.
  • Tutup tampilan Design tabel dengan mengklik tombol Close Window.


5.2. Menyisipkan Field ke Sebuah Tabel
Jika Anda perlu menambahkan field baru ke dalam sebuah tabel, maka tidak heran jika suatu saat Anda juga perlu menyisipkan sebuah field di tengah-tengah tabel. Adapun langkah yang harus Anda lakukan adalah sebagai berikut:

  • Field baru akan disisipkan di atas baris yang Anda klik. Dalam tampilan Design, tempatkan kursor Anda di baris dimana Anda akan menyisipkan sebuah field baru.
  • Pilih Rows dari menu Insert, atau klik tombol Insert Rows pada toolbar.
  • Access akan menyisipkan sebuah baris kosong. Isi field-field kosong tersebut.
  • Tekan tombol Ctrl+S untuk menyimpan desain tabel baru.
  • Tutup tampilan Design dengan mengklik tombol Close Window.


5.3. Menghapus Field dari Tabel
Untuk menghapus sebuah field dari desain sebuah tabel, langkah yang harus Anda lakukan adalah sebagai berikut:

  • Dalam tampilan Design, tempatkan kursor di field yang ingin Anda hapus
  • Pilih Delete Rows dari menu Edit, atau klik tombol Delete Rows di toolbar.
  • Jika field ini berisi data, Access akan meminta Anda mengkonfirmasi bahwa Anda benar-benar ingin meng-hapus field ini. Klik Yes di kotak dialog yang muncul.
  • Tekan tombol Ctrl+S untuk menyimpan desain tabel yang baru
  • Tutup tampilan Design tabel dengan mengklik tombol Close Window.


6. Mengubah Nama Tabel
Untuk mengubah nama sebuah tabel langkah yang harus Anda lakukan adalah sebagai berikut:
Dalam tab Tables, klik kanan mouse Anda. Setelah itu segera tampil Menu Popup
Klik Rename, lalu ketikkan nama tabel baru
Tekan tombol Enter.


7. Menghapus Tabel
Jika sebuah tabel tidak diperlukan lagi Anda bisa menghapus atau membuangnya. Cara yang harus Anda lakukan adalah sebagai berikut:

  • Dalam tab Tables, klik nama tabel
  • Tekan tombol Delete pada keyboard
  • Microsoft Access akan meminta konfirmasi. Klik Yes untuk menghapus tabel atau No bila Anda tidak jadi menghapus tabel.


8. Memasukkan Data
Untuk memasukkan data ke dalam sebuah tabel caranya adalah sebagai berikut:

  • Dalam tab Tables, klik nama tabel, dalam contoh ini adalah DAFTAR ALAMAT
  • Klik tombol Open. Microsoft Access akan menampilkan sebuah tabel
  • Untuk memasukkan data, tempatkan kursor ke sebuah sel lalu ketikkan data Anda. Perhatikan bahwa ikon bergambar seperti pensil di sebelah kiri akan muncul di baris itu.
  • Untuk berpindah ke sel lain, gunakan tombol panah atau mouse untuk memindahkan kursor. Untuk berpindah ke field berikutnya, gunakan tombol Tab atau Enter.


9. Menghapus Record
Untuk menghapus sebuah record, langkah yang harus Anda lakukan adalah:

  • Buka tabel dalam tampilan Datasheet
  • Tempatkan kursor di record yang ingin Anda hapus
  • Pilih Delete Record dari menu Edit
  • Klik Yes di kotak konfirmasi yang muncul.

Koneksi Database menggunakan Visual Basic 6 dan .NET

Koneksi SQL Server 2000 dengan Visual Basic 6 menggunakan ADODB

· Menggunakan SQL Server Client
Dim Conn as ADODB.ConnectionConn = “Provider=SQLOLEDB.1;User ID=;Password=” & _“;Initial Catalog=” & _“;Data Source=
SQL Server Client harus sudah terinstal terlebih dahulu.

· Menggunakan ODBC
Dim Conn as ADODB.ConnectionConn = “ODBC;DRIVER=SQL Server;DSN=” & _“;UID=;PWD=” & _“;SERVER=;DATABASE=

Set terlebih dahulu pada ODBC database yang akan dikoneksikan pada tab System DSN – Add.
Pilih driver SQL Server, untuk selanjutnya pilih alamat server dan databasenya.Untuk SQL Server 2005 gunakan driver SQL Native Client.



Koneksi MySQL dengan Visual Basic 6 menggunakan ADODB

· Menggunakan ODBC
Dim Conn as ADODB.ConnectionConn = “DRIVER={MySQL ODBC 3.51 Driver};SERVER=;” & _“DATABASE=;UID=;PWD=;”

Set terlebih dahulu pada ODBC database yang akan dikoneksikan pada tab System DSN – Add.
Pilih driver MySQL ODBC 3.51 Driver, untuk selanjutnya pilih alamat server dan databasenya.
MySQL ODBC drivernya harus diinstal dahulu. Untuk sourcenya dapat di download secara gratis pada www.mysql.com/products/connector/


Koneksi MySQL dengan Visual Basic .Net menggunakan ADO.Net

· Tanpa ODBC
Dim ConnString As StringDim Conn As MySqlConnectionConnString =“Server=;Database=;“ & _“Uid=;“ & _“Pwd=;Connect Timeout=30;“Conn = New Conn(ConnString)


Koneksi Microsoft Access dengan Visual Basic 6 menggunakan ADODB
· Tanpa ODBC
Dim Conn as ADODB.ConnectionConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _“;User Id=;Password=;”

· Menggunakan ODBC
Dim Conn as ADODB.ConnectionConn = “ODBC;DRIVER=MICROSOFT ACCESS DRIVER;DSN=” & _“;UID=;PWD=” & _“;SERVER=;DATABASE=
Set terlebih dahulu pada ODBC database yang akan dikoneksikan pada tab System DSN – Add.
Pilih driver Microsoft Access Driver (*.mdb), untuk selanjutnya pilih alamat server dan databasenya.


koneksi database dengan Visual basic beserta source codenya

Untuk dapat mengakses suatu database dari visual basic, Anda harus membuat koneksi ke database tersebut. Visual basic dapat menangani berbagai database, teks bakan worksheet excel. Terdapat beberapa cara yang tersedia untuk itu sesuai dengan database yang digunakan

Cara 1. Menggunakan Object ADO
Anda dapat menggunakan icon Adodc (Microsoft ActiveX Data Objects Data Control) pada toolbox. Jika pada toolbox tidak terdapat icon Adodc, Anda dapat menambahkannya dengan memilih menu Project, Components (Ctrl+T) lalu beri tanda pilih pada Microsoft ADO Data Control (OLEDB) dan klik Apply. Sekarang kita akan mencoba mengaitkan database Ms. Acess yang diberi nama Pembelian.mdb. Untuk itu, lakukan langkah-langkah sebagai berikut:
  1. Pilih menu File lalu New.
  2. Pilih VB Enterprise (Professional) Edition Controls.
  3. Klik OK.
  4. Klik ganda pada folder Forms pada jendela proyek lalu klik ganda pada Form 1 sampai muncul tampilan form.
  5. Klik icon Adodc pada toolbox.
  6. Klik dan seret pada jendela form, sehingga muncul tampilan seperti Gb. Objek yang tergambar dalam form disebut objek data.
  7. Tentukan properti data sebagai berikut:
    - Name db Pembelian
    - Caption Data Barang
    - EOFAcction 2
  8. Tentukan properti ConnectionString dengan cara mengklik tombol … pada kolom ConnectionString sehingga muncul tam pilan seperti pada Gb.
  9. Pilih OptionButton pada Use Connection String lalu klik Build sehingga muncul tampilan seperti pada Gb.
  10. Pilih Microsoft Jet 4.0 OLEDB Provider lalu klik Next.
  11. Pilih (dengan mengklik tombol …) atau ketik nama database yang dipakai pada kolom Select or enter a database name.
  12. Klik Test Connection jika perlu, lalu kllk OK dua kali sampai tampilan kembali ke jendela Form.
  13. Tentukan properti RecordSource dengan cara mengklik tombol pada kolom RecordSource sehingga muncul tampilan seperti pada Gb.
  14. Pada kolom Command Type pilih 2-adCmdTable. Setelah kolom ini diisi, maka Anda akan dapat mengisi kolom Table or Stored Procedure Name. lsilah dengan memilib tabel Barang seperti pada Gb.
  15. Klik OK.
  16. Simpan form dan proyek dengan nama VB-Lat-01.


Cara 2: Mendeklarasikan Objek Ado
Object ado dapat dideklarasi melalui coding. Tiga object bawaan ADO dalam pengembangan aplikasi adalah :


1. Obcet Connection
untuk menangani koneksi antara program dengan sumber data atau server. Deklarasi dapat bersifat lokal maupun global serta dapat diletakan pada level form, modul atau class modul. ADO paling umum membuka suatu koneksi dengan menggunakan obyek Connection Open metoda. Sintaks untuk Metoda Open ditunjukkan sbb:


Dim connection as New ADODB.Connection
connection.Open ConnectionString, UserID, Password, OpenOptions


Sebagai alternatif, anda dapat menggunakan teknik pintas, recordset.Open, untuk membuka koneksi yang tersembunyi dan mengeluarkan suatu perintah atas koneksi dalam satu operasi. Lakukan hal ini dengan mengaktivkan suatu koneksi yang valid seperti argumentasi ActiveConnection ke Metoda Open. Ini adalah sintaksis untuk masing-masing metoda di Visual Basic:


Dim recordset as ADODB.Recordset Set
recordset = New ADODB.Recordset
recordset.Open Source, ActiveConnection, CursorType, LockType, Options


Contoh :
Deklarasi object Connection:
Dim cn As New ADODB.Connection
atau
Public cn As New ADODB.connection


Selanjutnya mengalokasikan memori untuk onject connection sbb:
Set cn = New ADODB.Connection


Kemudian isi property object connoction tersebut sbb:
Contoh berikut adalah property untuk MSSQL Server
cn.connectionString = “Perovider=SQLOLEDB;” & _
”Server=NamaServer; ” & _
“Initial catalog= inventory” & _
“user Id = zulidamel” _
“Password=terserah”
cn.Open


atau


koneksi = “Perovider=SQLOLEDB;” & _
”Server=NamaServer; ” & _
“Initial catalog= inventory” & _
“user Id = zulidamel” _
“Password=terserah”
cn.Open koneksi


Jika tidak ingin menggunak authentik pada proses koneksi gunakan cara berikut:

Bila koneksi sudah tidak diperlukan tutuplah koneksi dengan perintah
”Set cn = Nothing”


2. Object Command
Digunakan untuk perintah SQL ke sumber data atau server. Deklarasi object command sbb:
Dim CmdBarang As New ADODB.Command
atau
Public Cmd as New ADODB.Command


selanjutnya alokasikan memori untuk object Command sbb:
Set CmdBarang = New ADODB.Command


Kemudian Isi property object command
CmdBarang.ActiveConection = cn
CmdBarang.CommandText = “select * from barang”
CmdBarang.CommandType = adCmdText


3. Object Recordset
Digunakan sebagao penampung hasil eksekusi dalam bentuk tabel. Deklarasi object record sbb:
Dim rsBarang As New ADODB.Recordset
atau


selanjutnya alokasikan memori ynryk object recordset sbb:


lalu isikan property object record set
rsBarang.CusrsorTYpe = adOpenDynamic
rsBarang.LockType = adLockOptimistic
rsbarang.Open cmdBarang
atau


Data Control
Visual Basic terus berkembang dalam pengembangan general purpose Windows, tetapi kemampuan database tidaklah bagian murni dari desainnya. Data Control, Visual Basic menambahkan, membuat Visual Basic suatu alat yang jauh lebih tangguh untuk pembuatan pemrograman database.
Data Control memberikan kemudahkan untuk mendapat data ke dalam program anda. Anda dapat dengan mudah mengaitkan suatu file database, lalu mengaitkan data lainnya untuk memindahkan data dari database ke aplikasi anda dan sebaliknya. Data Control pada suatu database dan menetapkan tabel mana yang akan diambil, lalu mengaitkan ke pengendali Data Control, menetapkan field yang akan ditampilkan. Pada waktu itu aplikasi anda dapat mengambil, menampilkan, memperbaharui dan membuat database merekam

DATABASE SQL AND ACCESS

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:

Collapse
    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”:

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

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

Collapse
#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:

Collapse
#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:

Collapse
#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:

Collapse
#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
      • 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.

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

Collapse
#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.

Collapse
#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.

Collapse
    ''' <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.

Collapse
#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.