Video How to: Simple Object Model and Query (Visual Basic)
(LINQ to SQL)
Transcript
I'm Harry Miller, and I'm going to show you a basic LINQ to SQL example. I'll create an
entity class that models the Customers table in the sample Northwind database, and then
create a simple query to list customers who are located in London.
LINQ to SQL provides a run-time infrastructure for managing relational data as objects.
The data model of a relational database is mapped to an object model that is expressed in
Visual Basic. At run time, LINQ to SQL translates the Visual Basic into database queries,
and then translates the returned results into objects.
Creating the Project
Although typically you might use the Object Relational Designer to create entity classes
and relationships visually, for this example I'll create these classes manually, to help show
the LINQ to SQL concepts. I'll name it LinqConsoleApp.
I need to add two references, so in the Add Reference dialog box, on the .NET tab, I'll
select System.Data.Linq and click OK.
I also need to add System.Windows.Forms from the .NET tab because there's a
message box later in the example.
Adding the Code
In Module1, I'll put three Imports statements for LINQ and Windows Forms:
Imports System.Data.Linq
Imports System.Data.Linq.Mapping
Imports System.Windows.Forms
Now I'll create an entity class in the module. An entity class is a class that's mapped to a
database table. To do this mapping, all I need to do is apply the Table attribute. The
Name property specifies the name of the table in the database.
<Table(Name:="Customers")> _
Public Class Customer
End Class
The next step is to designate properties to represent database columns. There are two
properties here: CustomerID and City. They both get the Column attribute, which
designates them as representing columns in the database table. The CustomerID
property is a primary key column in the database. Both are designated for private storage,
which means that LINQ to SQL can store and retrieve values directly, instead of using
public accessors that might include business logic.
Private _CustomerID As String
<Column(IsPrimaryKey:=True, Storage:="_CustomerID")> _
Public Property CustomerID() As String
Get
Return Me._CustomerID
End Get
Set(ByVal value As String)
Me._CustomerID = value
End Set
End Property
Private _City As String
<Column(Storage:="_City")> _
Public Property City() As String
Get
Return Me._City
End Get
Set(ByVal value As String)
Me._City = value
End Set
End Property
I need to set up a connection to the Northwind database that I'm using, so I'll create a
DataContext object. The DataContext is the main channel for retrieving objects from the
database and for submitting changes.
Dim db As New DataContext _
("c:\linqtest\northwnd.mdf")
I'll also declare a Table(Of Customer) to act as the logical, typed table for the queries
I'll create against the Customers table.
Dim Customers As Table(Of Customer) = _
db.GetTable(Of Customer)()
Now to create a query to find out which customers in the Customers table are located in
London. This code just describes the query—to actually run it, I'll need to create a
For Each loop.
Dim custQuery = _
From cust In Customers _
Where cust.City = "London" _
Select cust
I'll also add a line of code here to show the SQL commands that LINQ to SQL generates, in
case I need to do some debugging. This shows the commands that are sent to the
database.
db.Log = Console.Out
Now I'll put in the For Each loop to run the query, and format a message box to show
the results. For each object returned by the query, add it to the message.
For Each custObj In custQuery
msg &= String.Format(custObj.CustomerID & vbCrLf)
Next
For the message, this code sets variables with formatting information such as the title and
the basic appearance.
Dim msg As String = "", title As String = "London customers:", _
response As MsgBoxResult, style As MsgBoxStyle = _
MsgBoxStyle.Information
This code shows the message using the variables.
response = MsgBox(msg, style, title)
Running the Program
Now I'll run it, and I get my list of customers in London. Also, the console window shows
the generated SQL code.
For More Information
You can get more information about developing Visual Basic applications in the Visual
Basic Help. You can find other resources such as technical articles, samples, blogs, and
videos at the Visual Basic Developer Center. Just go to msdn.microsoft.com/vbasic.
