In-Depth
Test Drive VB9 and DLinq
The January 2006 Language Integrated Query (LINQ) preview for the next ("Orcas") version of Visual Basic enables automating SQL Server object-relational mapping for DLinq and enhances XLinq syntax for literal XML and late binding.
Technology Toolbox: VB.NET, SQL Server 2005, XML, Visual Studio 2005, or Visual Basic or Visual Web Developer Express editions, Visual Basic 9.0 LINQ Technology Preview (January 2006), SQL Server 2005 Express Edition or higher, Northwind sample database
The forthcoming Visual Studio "Orcas" release promises major upgrades to data-management programming with Visual Basic 9.0 and C# 3.0.
Language Integrated Query (LINQ) and its data (DLinq) and XML (XLinq) libraries transform relational data and XML documents into first-class, interoperable CLR objects. My "Streamline Mapping With Orcas and LINQ" article in the VSM December 2005 issue described the "impedance mismatch" caused by mixing literal SQL query strings and XML InfoSet text with .NET's statically-typed data objects. That article covered the initial LINQ technical preview, which Microsoft released in September 2005 at the Professional Developers Conference (PDC). The PDC release represented a major step toward CLR support for a common query language across application objects, relational data, and XML documents, but offered only XLinq support for VB. DLinq for object-relational mapping (ORM) worked in C# projects, but wasn't accessible to VB programmers.
The VB team overcame this lack of VB 9.0 parity with C# 3.0 by delivering an upgraded VB-only technical preview in January 2006. I'll explain the newly added DLinq and XLink features and why they're important to VB developers. I'll also show you how to generate VB classes from SQL Server databases, and describe the article's sample application that shows LINQ, DLinq, and XLinq coding techniques for querying, joining, and updating data objects and XML documents (see the Go Online box for details). The sample application includes a Windows Form that demonstrates DLinq databinding with three auto-generated DataGridView controls in a parent-child-grandchild relationship.
Paul Vick, Microsoft's technical lead for the VB language and compiler, says that the VB team has been "working on improving data access in the Visual Basic language" with the focus on two primary areas. First, the team wants to design a unified syntax—based on SQL's SELECT... FROM... WHERE... ORDER BY syntax—for querying application objects, relational data, and XML documents. Achieving this goal should more than compensate for loss of the ObjectSpaces ORM add-in and XQuery 1.0 namespaces that Microsoft dropped from VS 2005 and .NET 2.0. Second, the team intends to simplify production of XML InfoSet documents by embedding XML literals into VB 9.0 code. This feature makes it easy for database developers to generate XML documents in the middle or client tier, without incurring the server resource consumption and network traffic overhead of FOR XML AUTO queries. XLinq gains support for XML namespaces with the Imports statement, whitespace preservation for cut-and-paste insertion of XML document structures into the VB code editor, auto-indentation, auto-correction of closing tags, and code colorizing.
Get started with January's VB-only LINQ upgrade by downloading the LINQ VB Preview (Jan 2006) .msi file from the Future Versions page of MSDN's VB Developer Center. Running the installer adds a VB LINQ Preview node to your Programs menu, creates and populates a \Program Files\VB LINQ Preview folder with a Readme.htm file and Bin, Docs, and Samples subfolders. It also replaces the RTM version (8.0.50727.42) of the VB compiler with the LINQ-enabled release (8.0.50926.00) and adds LINQ Windows Application, LINQ Console Application, and LINQ Class Library templates to the New Project dialog's Visual Basic Templates pane. Updated System.Query.dll, System.Data.Dlinq.dll, and System.Xml.Xlinq.dll assemblies provide the namespaces for required LINQ, DLinq, and XLinq references. The VB LINQ upgrade runs side-by-side with, and doesn't affect, the November 2005 C# LINQ Preview and sample applications for the VS 2005 RTM version.
Create a new VB LINQ project in VS 2005 or VB Express by choosing the LINQ Windows Application template, which adds to the standard System, System.Data, System.Deployment, System.Drawing, and System.Windows.Forms namespace references to System.Query, System.Data.Dlinq, and System.Xml.Xlinq. Surprisingly, the expected System.Xml namespace reference is missing from the templated project.
Put the Pedal to SqlMetal
Writing and executing DLinq queries requires a class that implements the DataContext interface to map an SQL Server database's relational tables to LINQ objects, and vice-versa. The DataContext class also translates LINQ query code and object modifications into SQL statements for retrieving and updating data. The VB LINQ preview includes an upgraded version of the SqlMetal.exe utility to auto-generate a strongly-typed VB DataContext class file from an SQL Server 2000 or 2005 database's metadata with this execution syntax:
usage: \path\sqlmetal [options] [<input file[>]
options:
/server:<name> database server name
/database:<name> database catalog on server
/user:<name> login user id
/password:<name> login password
/views extract database views
/xml[:file] output as xml to file
/code[:file] output as code to file
/language:xxx for code (vb or csharp)
/namespace:<name> code namespace
/pluralize auto-pluralize table names
This instruction (the sample application's NwindCls.bat file) generates a Northwind.vb DataContext class file in the current folder from the Northwind sample database of a local SQL Server 200x instance with Windows authentication:
"\program files\vb linq preview\bin\sqlmetal.exe"
/server:localhost /database:Northwind
/namespace:NWind /language:vb /code:Northwind.vb
If you want to create an identical DataContext class file from a Northwind.mdf file that you attach to SQL Server 2005 [Express], drop the /server:localhost and /database:Northwind options and add the database file name as the <input file> argument:
"\program files\vb linq preview\bin\sqlmetal.exe "
/namespace:NWind /language:vb /code:Northwind.vb
Northwind.mdf
You also can generate an XML schema document (NwindCls.xml) from SQL metadata extracted from the Northwind database with this command (NwindXml.bat):
"\program files\vb linq preview\bin\sqlmetal.exe"
/server:localhost /database:Northwind
/xml:NwindCls.xml
Current LINQ previews don't use the schema document, which is not an XML schema (XSD) file. The final LINQ implementation probably will include a designer to simplify SqlMetal.exe operations.
The DataContext (database) class that SqlMetal.exe generates contains a strongly typed generic Table member for each of the database's table collections. Here's the abbreviated code for the sample project's Northwind DataContext class:
Partial Public Class Northwind
Inherits DataContext
Public Customers As Table(Of Customers)
Public Orders As Table(Of Orders)
Public OrderDetails As Table(Of OrderDetails)
...
Public Employees As Table(Of Employees)
Public Products As Table(Of Products)
..
Public Sub New(ByVal connection As String)
MyBase.New(connection)
End Sub
End Class
Creating a new DataContext requires passing an ADO.NET connection string for SQL Server 200x or SQL Express to the constructor. The LINQ team's current plan is to support only SQL Server 200x versions directly, while enabling other database vendors to substitute their native .NET data providers for SqlClient.
An entity class decorated with a <Table(Name:="Table Name")> attribute defines each table. Private members specify strongly-typed table columns and EntryRef(Of RelatedTable) types designate one-to-many foreign-key/primary-key relationships, such as that between the Order Details and Products tables:
<Table(Name:="Order Details")> _
Partial Public Class OrderDetails
Implements System.Data.DLinq.IChangeNotifier
Private _OrderID As Integer
Private _ProductID As Integer
Private _UnitPrice As Decimal
Private _Quantity As Short
Private _Discount As Single
Private _Orders As EntityRef(Of Orders)
Private _Products As EntityRef(Of Products)
...
End Class
An EntitySet<Of RelatedTable> defines one-to-many primary key/foreign-key relationships, such as Customers to Orders and Orders to OrderDetails (see Figure 1).
Column attributes designate the column storage member, SQL Server data type and, if applicable, Id=True specifies that the column is the primary key or part of a composite primary key, as in this partial entity class for the ProductID column:
<Column(Storage:="_ProductID", _
DbType:="Int NOT NULL", Id:=True)> _
Public Property ProductID() As Integer
Get
Return Me._ProductID
End Get
Set(ByVal value As Integer)
...
Me._ProductID = value
...
End Set
End Property
Map to Column References
The <Association> attribute maps related entities to column references so that queries can access column values from matching row(s) of related tables. The EntityRef's ThisKey attribute changes to OtherKey for EntitySet attributes. This abbreviated property procedure returns the Products row for the ProductID value of the current OrderDetails row:
<Association(Name:="FK_Order_Details_Products",
Storage:="_Products", ThisKey:="ProductID")> _
Public Property Products() As Products
Get
Return Me._Products.Entity
End Get
Set(ByVal value As Products)
...
Me._Products.Entity = value
...
End Set
End Property
Entity classes also contain event delegates and methods for raising ObjectChanged events when you update, insert, or delete an instance.
Competing with ADO.NET 2.0 DataSets requires the capability to bind DLinq DataContext instances to DataGridView, TextBox, DatePicker and other data-aware controls. The technical preview download doesn't include any updated DLinq documentation, but the SampleQueries.sln project has 101 simple DLinq query examples that you can execute against the Northwind sample database (see Figure 2). Unfortunately, the sample queries don't provide much help in testing DLinq databinding.
VS 2005's Data Source Configuration Wizard recognizes DataContext classes as business objects, so you can add hierarchical DLinq data sources to your project (see Figure 3). Data sources generated from DataContext classes are similar to those from DataSets; you can drag the top-level entity—Northwind's Customers Table for this example—node to a form to add CustomersBindingNavigator, CustomersBindingSource, and CustomersDataGridView, TextBox, or DatePicker controls. DLinq EntitySets correspond to members of the DataSet.Relations property's collection of DataRelation objects. Thus, you can drag DLinq EntitySets to the form Orders and OrderDetails EntitySet nodes of the Customers entity that add OrdersBindingSource and OrderDetailsBindingSource controls to the tray, and bound OrdersDataGridView and OrderDetailsDataGridView to the form (see Figure 4).
You can't add new rows to the DataGridView that's bound to the topmost entity; adding new rows to DataGridViews bound to EntityRefs causes the BindingSource to throw an "ArgumentException was unhandled; value does not fall within the expected range" exception. For the technical preview, it appears that you must write code to add new entity instances and rows to the database tables. Fortunately, you can add new parent, child, and grandchild rows to tables with a single DataContext.SubmitChanges method invocation (see Listing 1). Adding new rows with DataGridViews is an uncommon practice, so the code requirement isn't a serious DLinq drawback.
Attempting to delete rows from tables by deleting a DataGridView row also exhibits mysterious behavior; only the OrdersDataGridView responds as expected when you add the DataContext.SubmitChanges instruction to the UserDeletedRows event handler. Deleting Customers or OrderDetails rows from the database requires a query to return a specific instance and passing the instance as the argument of the DataContext.Entity.Remove(Object) method, as in this example:
Private Sub OrderDGV_UserDeletedRow(ByVal _
sender As Object, ByVal e As
System.Windows.Forms. _
DataGridViewRowEventArgs) Handles _
OrderDetailsDataGridView.UserDeletedRow
'Return the OrderDetails instance to delete
Dim objDelDetail = (Select d _
From d In dbNwind.OrderDetails _
Where d.OrderID = delOrderID AndAlso
d.ProductID = delProductID).First()
dbNwind.OrderDetails.Remove(objDelDetail)
dbNwind.SubmitChanges()
End Sub
Code in the UserDeletingRow event handler sets the values of the delOrderID and delProductID constraints to specify the OrderDetails instance to delete. Applying the SubmitChanges method deletes the row from the database table.
DLinq Tracks Changes in Memory
Edits to DataGridView cell values behave as expected, because DLinq tracks the changes in memory. Applying the DataContext.SubmitChanges method updates the database tables. The DataContext's SQL DELETE and UPDATE queries handle concurrency conflicts with syntax similar to that generated by the ADO.NET's CommandBuilder or strongly typed Data Sets. DataContext updates initiate a transaction; in the event of a conflict, DLinq rolls back the changes and throws an exception. The DLinq tracker records all changes, so you can add code to rectify the conflict and repeat the DataContext.SubmitChanges( ) instruction.
The preceding event-handling code includes a typical DLinq query to return an instance that represents a single row of an entity. Consider this statement:
Select d From d In DataContext.Table
Where Expression
It returns a Query(Of Table) object, which requires an iterator—such as a For Each [Dim] Object In Collection... Next construct—to execute the Query object and extract its value(s). Applying the First( ) method to the Select code executes the Query and eliminates the need for an iterator. Similarly, invoking the Count( ), Sum( ), Avg( ), Min( ) or Max( ) methods returns aggregate values. The VB 9.0 compiler infers data types and verifies the syntax, which minimizes the probability of runtime exceptions. The VB Team's goal is to provide full IntelliSense statement completion when writing the code for LINQ Query objects, without resorting to C#'s approach: moving the From clause before the Select list. The technical preview implements IntelliSense for Select lists in the For Each ? Next iterator only.
The XLinq library's literal syntax simplifies generating XML documents from DLinq or other business objects, as mentioned earlier. The sample project's VbLinqDemo form's LiteralXmlFromDlinq procedure generates a complex, hierarchical XDocument object from the dbNwind DataContext's Orders, Customers, OrderDetails, Products, Suppliers, and Shippers entities. XLinq XDocuments are a lighter-weight, faster version of the .NET Framework's full-bodied XmlDocument class; XDocuments are much less of a memory hog than XmlDocuments.
The CreateXmlFromJoins procedure illustrates using joins to combine data from DataContext entities and related XML documents containing related lookup elements into an almost-identical XML document (see Figure 5). The XLinq code joins the Northwind Orders entity with XDocument objects that contain Customers, Employees, Shippers, LineItems, Products, and Suppliers elements. (See Listing 2). Adding the six joins to the XDocument creation process increases the execution time by 20 percent or less. Although this example might appear contrived, merging XML content from Web services with client-side DLinq entities, or business object instances, is much easier for most VB or C# programmers than writing, testing, and debugging the corresponding XML transform.
My tests with much larger DLinq entities and XLinq documents indicate that LINQ's developers have made significant progress since the first technical preview at PDC 2005. VB 9.0 promises to meet or exceed the LINQ-related capabilities of C# 3.0 in the "Orcas" release. As Microsoft's Samuel Drucker noted in his DAT 312 presentation, "'WinFS' And ADO.NET: Future Directions for Data Access Scenarios" at PDC 2005, LINQ will be the primary API for querying and manipulating WinFS objects. It's a reasonably good bet that WinFS will RTM in about the same timeframe as Orcas's .NET Framework 3.0, C# 3.0, and VB 9.0.
It's not too early for VB programmers to climb on the LINQ bandwagon and become familiar with the next generation of Windows' data objects. C# aficionados can look forward to a Spring 2006 LINQ technical preview update, which probably will include minor improvements to VB XLinq literal syntax. Download and experiment with this article's VbLinqDemo sample and the SampleQueries projects.