Visual Basic Team Releases January LINQ CTP

Microsoft released the January CTP of the Language Integrated Query (LINQ) project's Visual Basic preview for Visual Studio 2005 on January 17, 2006. The big news is that the January CTP finally adds DLinq support to Visual Basic 9.0. You can download the January bits here. The new CTP replaces the September (PDC 2005) Visual Basic CTP that, unlike the C# version, didn't support DLinq for relational data sources. [Updated 2/7/2006]. The January CTP requires the release version of Visual Studio 2005 or Visual Basic Express, and works with SQL Server 2005 or SQL Server Express. (It probably also works with SQL Server 2000 or MSDE 2000, but I haven't tested that capability.) Microsoft hasn't tested the CTP with languages other than English. Although not stated in the Readme page, it's a good idea to use Control Panel's Add/Remove Programs tool to remove the September CTP version before executing Linq VB Preview (Jan 2006).msi. Running the CTP's installer adds a VB LINQ Preview item to your Programs menu with Readme, Visual Basic 9.0 Overview, Visual Basic 9.0 Samples, Visual Basic LINQ Project Overview, and XLINQ Overview choices.

SQLMetal.exe Updated for Visual Basic 9.0

The January CTP's version of the SQLMetal.exe utility creates the DatabaseName.vb class file that's necessary for mapping an SQL Server resultset to a LINQ object. (The September CTP's SqlMetal.exe didn't work with the /language:vb switch.) Later VB 9.0 versions will add a designer to simplify generating the mapping class file.

To create a Northwind.vb class file with nwind as its namespace by attaching the Northwind.mdf file in the C:\Program Files\VB LINQ Preview\Samples\Sample Queries\SampleQueries folder to your default SQL Server 2005 instance, execute the following from the "\Program Files\VB LINQ Preview\Samples\Sample Queries\SampleQueries folder:

"\program files\vb linq preview\bin\sqlmetal" /namespace:nwind /language:vb /code:Northwind.vb northwind.mdf

If you've already added a Northwind database to your default SQL Server instance, you receive a "Failed to attach an auto-named database for file northwind.mdf. A database with the same name exists ..." or "A connection was successfully established with the server, but then an error occurred during the loginprocess. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" exception.

In this case, execute the following to create the class file from the server and database you specify in the following example, which assumes SQL Server Windows authentication:

"\program files\vb linq preview\bin\sqlmetal" /server:localhost /database:Northwind /namespace:nwind /language:vb /code:Northwind.vb

New January CTP Features

According to the January CTP's Readme page, the CTP adds the following new features to the September release:

LINQ Intellisense: Query statements now have statement completion, Intellisense, and Pretty Printing to improve overall discovery and readability of VB queries. DLinq Support: Previous tech previews of the LINQ CTP did not fully support leveraging the DLinq technology which is for querying against relational data stores, such as SQL Server. DLinq support in this release is limited in that only the working query syntax over objects will extend to DLinq. Editing support for XML Literals: The Visual Basic background compiler provides immediate feedback, with this release this support has extended to integrated XML. Specifically, XML literals are now automatically indented and XML literals plug into auto-correction. The XML literals are now completely colorized with customizability through the VS 2005 Options, Fonts and colors.

The following new features apply to XML late-binding:

Importing XML Namespaces: XML namespaces can be imported to the VB program using similar mechanism to importing CLR namespace. Accessing Elements with Namespaces: Once an XML namespace is imported, you can use qualified identifiers to access elements with namespaces. Indexer for IEnumerable(Of T): An indexer was added to IEnumerable of any type. This enables easy access to any element from a generic IEnumerable, specifically for accessing the first XElement from the XElements collection that is returned from XML late binding expressions.

Paul Vick Summarizes New January CTP Features

Paul's February 6, 2006 post on the availability of the January CTP was a bit late, but his informal summary of the new DLinq and XLinq features is quite useful:

• We now support some Intellisense for Select expressions. This is a step forward in our investigation of the Select/From vs. From/Select question, so we’re definitely interested in feedback here.
• We now support a lot more LINQ, specifically DLinq and variable capture (so you can now access local variables in queries). A huge chunk of my November/December went towards implementing lambda expressions and expression trees in Visual Basic and that, combined with some excellent work by another team member on variable capture, means a lot of stuff works now that didn’t before.
• The editing/display experience for XML literals has been greatly enhanced — just having colorization makes a huge difference.
• A bunch of extensions were made to the XLinq support to make working with namespaces and elements easier.

Here's a screen capture that demonstrates colorization of part of the code for generating XLinq XML literals with element values obtained with DLinq from the Northwind sample database: Online Demo and Newsgroup Paul Vick and Amanda Silver deliver "Visual Basic Futures: Latest Developments in the LINQ Project", a 10-minute video overview of the objectives of the LINQ project, plus a brief description of January CTP's new and improved DLinq and XLinq features. Amanda continues the presentation with a 20-minute demonstration of DLinq and XLinq in Visual Studio 2005 console applications that demonstrate basic DLinq and XLinq syntax in simple console applications, the LINQ VB Project Sample Query Explorer Windows form project, and a new (but apparently unfinished) visualizer that displays expression trees for LINQ queries, as shown here:

Unfortunately, the expression tree visualizer isn't included with the January CTP bits. Click here to read a trasncript of the overview and demo. You can post questions about the new VB 9.0 features and (hopefully) receive answers on the LINQ (General) newsgroup. The newsgroup's MVPs are mostly—or, more likely, entirely—C# coders. Sample Applications

The Programs, VB LINQ Preview, Visual Basic 9.0 Samples menu choice opens the [C:]\Program Files\VB LINQ Preview\Samples folder, which contains Hands On Labs, Sample Queries, Visual Basic 9.0 Survey, and XML DOM Survey subfolders. Following is a screen capture of the LINQ VB Project Sample Query Explorer form that opens when you double-click the [C:]\Program Files\VB LINQ Preview\Samples\Sample Queries\SampleQueries.sln solution file and run the 101 DLinq Samples JOIN SelectMany - 1 To Many - 1 sample (DLinq27()).

Note: The default connection string is: connString = "AttachDBFileName='C:\Program Files\VB LINQ Preview\Samples\Sample Queries\SampleQueries\Northwind.mdf'; Server='.\SQLEXPRESS';Integrated Security=SSPI;enlist=false". If you don't have SQL Server Express installed or you already have added the Northwind sample database, you must change the connection string in the DLinqSamples class's Declarations section to a value that suits your installation.

This project lets you execute the sample LINQ, DLinq, and XLinq queries and, for DLinq, display the T-SQL statement and, where applicable, the query resultset for the sample.

The other two sample projects are console applications that are similar to their corresponding September CTP counterparts.

Links to Earlier LINQ Articles and OakLeaf Blog Posts

"Comparing LINQ and Its Contemporaries" by Ted Neward from the .NET Framework Developer Center (December 2005)

"Late Binding in Visual Basic" by Paul Vick and Erik Meijer (December 1, 2005, 00:22:45 MSDN TV video)

"Streamline Mapping With Orcas and LINQ" article from the December 2005 issue of Visual Studio Magazine.

"Cure Data Type 'Impedance Mismatch' With LINQ," from Fawcette Technical Publishing's .NETInsight newsletters for PDC 2005 (September, 2005)

"The Language Integrated Query (LINQ) Project" (first LINQ post), September 14, 2005

"Erik Meijer on LINQ as a 6GL Language Feature," September 14, 2005

"LINQ Interview with Anders Hejlsberg and Paul Vick," September 19, 2005

"Jon Udell: Microsoft driving toward .Net unity," September 21, 2005

"XLinq Presentation at the XML 2005 Conference," September 23, 2005

"Comparing C# 3.0 and VB 9.0 LINQ Syntax," September 26, 2005

"More on Object-XML and Object-Relational Impedance Mismatch," September 29, 2005

"Jon Udell: LINQ 101," September 29, 2005

"Compose XML Content with XLinq Expressions and VB 9.0 XML Literals," October 1, 2005

"More on Visual Basic 9.0 and LINQ versus SQL," October 10, 2005

--rj Technorati: LINQ DLinq XLinq Visual Basic 9 VB 9 Orcas Visual Studio 2005 VS2005 Visual Studio Visual Basic Express

InfoWorld Gives Thumbs Up to SQL Server 2005

InfoWorld magazine's "Special Report" for January 16, 2006, "SQL Server's extreme makeover," begins with this summary:

"Microsoft's flagship database pumps up to enterprise class with high availability, slick disaster recovery, rebuilt integration services, stronger analytics, and other heavy-duty enhancements."

Contributing Editor Sean McCown, who wrote "SQL Server bulks up," the five-page review of SQL Server 2005 and its management tools, says:

"More than five years in the re-making, SQL Server 2005 not only addresses shortcomings in high-availability and disaster recovery, but brings sweeping mprovements in almost every area. Management tasks have been streamlined, and monitoring and diagnostics have been beefed up. A default locked-down configuration, data encryption, and other new features strengthen security. Analysis Services, Reporting Services, and data Mining are now fully integrated and mature. DTS (Data Transformation Services) has been rewritten from the ground up, emerging as the more powerful and flexible SQL SSIS (Server Integration Services.)"

He gives SQL Server a score of 9.1 (Excellent) with 9 out of 10 points for managability, performance, availability and scalability, plus 10 points for value. I would have awarded 10 points for managability, which has been SQL Server's forte since version 7.0 introduced the "Starfighter" management tools. Starfighter was the code-name for the first version of SQL Server Enterprise Manager implemented as a Microsoft Management Console (MMC) application.

McCown reviews new SQL Server Integeration Services (SSIS), which replace Data Transformation Services (DTS) and feature .NET Framework 2.0 integration. He points out some annoyances with SQL Server Management Studio (SSMS), but gave the new Data Management Views (DMVs) high marks for providing DBAs with management assistance, server statistics, and reports. Increased security with DDL triggers to prevent undesired schema changes, better password management, control over schema changes, and column/cell-level encryption also received a nod. (See my "Encrypt and Decrypt Data in Yukon" article from Visual Studio Magazine for a developer's eye view of SQL Server 2005's encryption/decryption processing.)

High-availability (HA) features and disaster recovery capabilities get high marks for mirroring, online restores, and page-level restores. Although McCown noted that mirroring was unsupported in the November 2005 release version of SQL Server 2005, Microsoft promises a supported version in 2006. Online indexing also contributes to HA.

A "Development Snapshot" graphic's Pros section outlines new SQL Server 2005 features for developers, such as CLR integration, new T-SQL constructs, and native XML data storage. The Cons include lack of IntelliSense for T-SQL queries, which Microsoft promised and then reneged, problems with an "unresponsive interface" for SSMS (which I haven't encountered), and more complex queries for DVMs than their "deprecated counterparts." A "Cool Tools for SQL Server Coders" graphic lists the most important T-SQL enhancements. See "Take Advantage of New T-SQL Features" and "Exploit Yukon's XML Data Type" for more details and downloadable Visual Basic 2005 source code examples for these new features. The article doesn't review new SQL Server Service Broker (SSSB) features or native XML Web services capabilities. (See my "What’s New With VS and Data" interview, forthcoming Visual Studio Magazine article on SSSB and August 2005's "Create Interoperable Native Web Services.")

McCown gives SQL Server 2005’s business intelligence (BI) tools—SQL Server Analysis Services (SSAS), SQL Server Data Mining (SSDM), and SQL Server Reporting Services (SSRS)—a test drive and finds them "finally designed to work together to answer your intelligence needs." SSRS's Report Builder gets praise for its Report Model, which lets administrators define the data available to user-defined reports.

The review concludes with a rehash of what's missing in SQL Server 2005—Intellisense for T-SQL queries, a centralized code repository, and lack of a separate extract, transform, and load (ETL) server—and advice about if and when to upgrade to SQL Server 2005. McCown's closing remarks, however, say it all:

SQL Server 2005 is an excellent release that will gain Microsoft some real respect in the high-end database market. SQL Server’s tools now lead Oracle and DB2 in pretty much every area—management, monitoring, and especially development—and of the three vendors, Microsoft seems to be the most concerned about improving them.

About the Reviewer:

Sean McCown is a Contributing Editor for InfoWorld magazine and a frequent contributor to SQLServerCentral.com as well as the SQL Server Worldwide Users Group. He also writes the occasional product review for Computerworld and article for SQL Server Standard. His April 2004 "Databases flex their XML" review for InfoWorld drew this detailed response from Michael Rys, Microsoft's point man for SQL Server 2005's XML data type.

You can read Sean's book reviews on database programming, database and Windows security, and general database topics at his ITBookworm site, and read his advice for DBAs and DBA "war stories" on his DBA Rants blog. Sean is the Senior DBA for SourceCorp of Dallas, Tx, where he lives with his wife and two kids. He's practiced and taught Kenpo for the past 20 years. [Kenpo competency is very useful for DBAs, especially Senior (supervisory) DBAs.]

Sidebar: Taking the Database Beyond SQL

Tom Yeager, InfoWorld's Test Center chief technologist and new-technology blogger begins his analysis with this paean to integration of the .NET Framework 2.0 with SQL Server 2005:

Through integration with .Net, SQL Server 2005 takes its rightful place as an active peer, not a detached agent, in large-scale distributed applications. ... SQL Server 2005’s .Net integration creates a dream scenario in which SQL developers can incorporate the advanced functionality of .Net in their stored procedures, while C#, Visual Basic, and even JavaScript developers can work with databases as though they were language extensions.

But .NET integration isn't limited to creating stored procedures with native code; you can also write user-defined functions (including table-valued functions), user-defined aggregates, triggers, and—if you have the courage—user-defined data types with Visual Basic 2005 and C# 3.0. (JavaScript isn't a supported language for creating SQL Server objects.)

.NET might be the best thing since sliced bread (or Java integrated with competitors' RDBMSs), but native code running in the SQL Server 2005 process has the potential to be the most misused feature in this release. (See the "When to Use SQLCLR" sidebar to my November 2005 "Manage Data With VS 2005" Visual Studio Magazine article.

Ahead of the Curve: SQL Server could make 'better together' ring true

Tom Yeager says, "Windows [2003] Server [R2], Visual Studio [2005], and SQL Server [2005] may impress even hardened skeptics," and goes on to explain why "reality-based" is a better marketing slogan than "better together" for Microsoft's newly upgraded server systems and developer tools. Yeager contends that testing by Microsoft's IT department and tens or hundreds of thousands of independent beta testers creates a truly "reality-based" server suite.

Database Underground: An Answer to Open Source

Sean McCown's December 11, 2005 blog post argues that SQL Server and Oracle "freeware" (Express Edition) versions of their proprietary relational database management systems (RDBMSs) are more suited to mission-critical applications than "open-source" databases like MySQL and PostgresSQL. (See "Oracle Releases Free 10g Express Edition Beta," October 2005). McCown sees advantages to Express Editions' maturity, performance monitoring, and well-travelled upgrade path.

McCown didn't make an important argument in favor of Express Editions: There is little or no relief from vendor lock-in with "open-source" databases. Migrating databases from MySQL to PostgreSQL or—heaven forbid—HSQLDB is likely to be far more difficult than moving to and from Oracle, SQL Server, DB2, or Sybase. The commercial RDBMS vendors provide tools to accomplish the transation; with an "open-source" database, you're mostly on your own.

McCown wants Microsoft and Oracle to remove the 4-GB size limits from their Express Editions in the hope that doing this will induce Web site owners to migrate from "open-source" to "proprietary" databases. It seems to me that Microsoft and Oracle are delivering much a more useful and stable product than MySQL or PostfresSQL—the two major players in the open-source database market—and neither vendor charges for support. Microsoft provides an Express version of SSMS to handle simple database chores in a Windows UI. Bear in mind that the size limit is per database, not per server, and you can run many named instances of SQL Server Express on a single computer running Windows XP or 2003 Server. (See my May 2005 "Migrate to SQL Server 2005 Express Edition" Visual Studio Magazine article.)

Editor's Letter: SQL Server 2005 leaves open source databases in the dust InfoWorld editor Steve Fox supplies a pointer to Sean McCown's December 28, 2005 review of MySQL 5.0, "Long-awaited MySQL 5.0 makes its debut," and quotes McCown as referring to DBAs that use "open source" databases as “'tree huggers' who don’t want to pay for commercial software." The woods are full of folks who subscribe to the "software should (or must) be free" theory, so it's unlikely that this opinion piece will change many "open-source" proponents' minds. However, McCown's An Answer to Open Source might convince rational DBSs to at least consider an Express Editions of a commercial RDBMS. Note: eWeek's Lisa Vass wrote a similar column titled "MySQL vs. the Lite Databases: A Fair Comparison?" that appeared December 29, 2005. She published a related column, "Readers Respond to MySQL, Lite Databases Comparison," the following week. This second column described how readers took her to task for omitting PostgreSQL and Firebird from the initial comparisons. Technorati: Databases Database SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Express SQL Server Express SQL Express Oracle Express Oracle 10g Express MySQL PostgreSQL P.S. In honor of Martin Luther King, Jr.'s birthday, celebrated Monday, January 16, 2006, I've finished a major update to my Squidoo lens (Web page) about The Black Scholar: Journal of Black Studies and Research, its history, editors and contributors.