Dabble DB: The New Look in Web Databases

In his widely circulated "Internet Services Disruption" memo, Microsoft Chief Technical Officer Ray Ozzie asked—but didn't answer—the following questions:

1. How can we utilize our extant products and our knowledge of the broad historical adoption of forms-based applications to jump-start an effort that could dramatically surpass offerings from Quickbase to Salesforce.com?
2. How could we build it to scale to hundreds of millions of users at an unimaginably low cost that would change the game?
3. How could we re-shape our client-side software offerings such as Access and Groove, and our server offerings such as SharePoint, to grow and thrive in the presence of such a service?
4. Could these rapid solutions encourage a new ISV ecosystem and business model?

Note: For more about Ray Ozzie's memo and its Access- and SharePoint-related content, see the "Ray Ozzie's 'Internet Service Disruption' Memo and Microsoft Access" post.

Update 4/19/2006: David Kirkpatrick's "Microsoft's new brain" article for Fortune (April 18, 2006) has this deck: "Brutal competition. A stock going nowhere. Microsoft is in crisis, so Bill Gates has unleashed his new hire, software genius Ray Ozzie, to remake the company - and conquer the Web." The article describes in depth a June 2005 meeting of the top 15 Microsoft executives run by recently hired Ray Ozzie. The article claims that "Put simply, Ozzie's assignment is to Webify everything: To intertwine Microsoft's entire product line - software for consumers, software for businesses, Xboxes, all of it - with the vast and ever-growing power of the Net. 'Everything we do should have a presence on the Web,' Ozzie says."

SmallThought Systems Inc.'s Dabble DB Web-based data management solution appears to answer most of Ray's questions. What's more, Dabble DB won the Under the Radar: Why Web 2.0 Matters conference's Best In Show award in the "I'd Pay for That" category, as well as the Panelists' Choice and People's Choice awards for the "Make It Easy" category. Under the Radar's venue is Microsoft's Mountain View (Calif.) Campus and Microsoft is one of the show's six Partners and Sponsors.

Dabble DB is intended to be the Web-based replacement for ad hoc spreadsheet applications (a.k.a. hacks) that should have used the database model from the git-go. Typical Dabble DB solutions are databases for sales contacts, students and grades, billable time, customer service issues, employee vacation and sick-leave time, event planning, and similar tracking applications. Access 2007 emphasizes user-created tracking application by including templates for many similar solutions. Dabble DB's developers consider Access, FileMaker and QuickBase to be their product's primary competitors.

CrunchNotes' Michael Arrington said on March 11, 2006: "Dabble DB is a service that allows non technical people to set up and use a database, and includes import and export functions to [E]xcel. This is a powerful application, and bridges the gap between Excel and Access or FileMaker."

Dabble DB has just emerged from the beta stage, but a seven-minute QuickTime screen capture—created for the Under the Radar conference—provides an insight into the product's capabilities without requiring registration. If you'd like more details, you can watch a 40-minute QuickTime presentation delivered to the Vancouver (BC) Lisp Users Group. (Dabble DB is written in SmallTalk and uses the Seaside Web applications framework.) Dabble DB's Avi Bryant, the author of Seaside and on the right in this Flickr photo from the conference, promises a two-way Atom API implementation after the public beta starts.

Here's a sample Dabble DB UI for a filtered view of the schedule grouped by the presentation's room for an early O'Reilly OSCON conference (click image for full-size capture):


Brian Benzinger gave Dabble DB a thumbs-up Solution Watch review in early December 2005, and TechCrunch's Nik Cubrilovic wrote a glowing "Dabble DB: Online App Building For Everyone" review on March 11, 2006. Zeke Sneaker, a Vermont-based creative director, looks forward to substituting Dabble DB for Access, Filemaker, and/or SharePoint.

Dabble DB's Web-Based Competitors [Updated 3/25/2006] Intuit, Inc.'s QuickBase was the first of the online collaborative database management applications and appears to have the largest user base. Over the years, QuickBase morphed from a consumer or small-office/home-office (SOHO) solution priced at US$14.95 per month for up to 15 databases to a department- or enterprise-level service that starts at a minimum of US$249 per month for up to 10 users, 5 MB storage for data, and 100 MB for attachements. Another indication that QuickBase has abandoned the consumer, SOHO, and small to medium-size business (SMB) market is the new emphasis on employing developers and consultants to create solutions with the QuickBase HTTP API. Intuit's Wikipedia entry calls QuickBase a "Web-based corporate workgroup productivity solution."

Serran Technologies, Inc.'s eUnify DB began life in early 2006 as a lower-cost QuickBase competitor targeted to the SMB market. eUnifyDB offers a 14-day free trial and starts at $69.95 per month for up to 5 users with 10 MB database and 1 GB file attachment storage. The Small Business Technology News site gave eUnify DB a brief but favorable review in January 2006.

Caspio, Inc. offers a basic forms-based database solution, Caspio Express DB, that captures data from existing online HTML, Flash, PDF, PDA, and WML forms. Express DB costs $16.95 per month for an unlimited number of forms, plus $0.02 per form submission. The Caspio Bridge Online feature lets you or consultants design and program custom "DataPages." Solution(s) with a total of one to five DataPages cost $39.95 per month, which includes 35 MB storage (in a Microsfoft SQL Server database) and 500 MB data transfer. Tiered pricing tops out at $799.95 per month for 100 DataPages, 600 MB storage and 10 GB data transfer. Caspio Bridge, which has been available since 2001, uses DHTML to create forms and wizards modeled on Microsoft Access. PC Magazine gave Caspio Bridge a favorable review (3 out of five stars) in October 2001.

AMULET Development Corp.'s eCriteria is a low-cost, no-frills Web database publishing system. eCriteria's Standard service (2.5 MB data storage) costs $4.95 per month. The Premium (5 MB, SSL and no banner ads) service is $19.95 per month; Enterprise (10 MB, enhanced security) accounts cost $39.95 per month. eCriteria commenced operation in 2000. eWeek magazine's Michael Caton gave eCriteria a favorable review in February 2004.

Oracle Corp.'s HTML DB site offers Oracle Application Express Studio (formerly HTML DB Studio) for creating forms-based database applications. Oracle offers free HTML DB accounts and a variety of sample solution templates, but creating HTML DB solutions isn't for the faint of heart (i.e., ordinary database users). Applications created on the HTML DB site are for "demonstration purposes only." Developer.com offers a two-part tutorial called "Building Web Applications with Oracle HTML DB."

Google's GoogleBase arrived with much fanfare in mid-November 2005, but the hue and cry quickly subsided when beta participants began testing this free flat-file database. See "Google Base and Atom 0.3 Bulk Uploads," "Google Base and Bulk Uploads with Microsoft Access," "Problems Uploading a Google Base Custom Item Type from a TSV File," and "Google Base and Blogger Items Missing from Google Search" OakLeaf blog entries as examples of issues with the early beta version.

Google says "GoogleBase provides data structure and distribution for a wide range of content and information[. A] subset of items are for sale." GoogleBase's attribute-centric database is better suited for describing items than tracking solutions. GoogleBase items are accessible only through proprietary GoogleBase forms, and you can't design custom forms to display your records. (There's no public API for GoogleBase.) Many types of GoogleBase records—such as items for sale—expire (disappear) in 30 days. Here's a typical example of a GoogleBase form displaying a permanent record with an image (click image for full-size capture):


Click here to open a GoogleBase search results page of OakLeaf blogs and entries.

Advent.Net's Zoho Creator is yet another Web-based tracking application generator that emerged from private to public beta status in early March 2006. Zoho Creator is one of seven Zoho Web applications, which include Zoho Writer, Zoho Virtual Office, Zoho CRM, Zoho Sheet (private beta), and Zoho Planner. These applications are free for a limilted number of users or during their beta cycle. You can listen to or read a transcript of a "Robin Good" interview of Zoho.com's CEO, Sridhar Vembu, to learn more about the Zoho suite. In the meantime, you can find brief Zoho Creator reviews at Shadows in Motion and ITRedux. Zoho Creator has a minimalist feature set; for example, you can't import existing data. A Zoho representative demonstrated the suite at Under the Radar's "Make It Easy 2.0" session. Search Adam Kalsey's and Ajaxian's session notes for "Zoho." Unlike Dabble DB, the notes indicate that Michael Arrington was unimpressed with Zoho and its products.

Toby Segaran's Lazybase is a simple flat-file web-based list creation and management system. You can create a new database, add tables to it, and then add or edit rows. An interesting feature is a JavaScript generator that lets you embed a read-only view of the data on your own Web page. [Updated April 25, 2006].

Note: Xlinesoft's ASPRunnerProfessional generates ASP (not ASP.NET) pages that run from your Web site and database. While this application doesn't qualify as a Web-based data management system, its presentation techniques—especially for lookup fields and parent-child records—are worth reviewing.

Preliminary Conclusion [Updated 2/25/2006] Microsoft's Dare Obasanjo, a program manager on the MSN Contacts & Storage team and XML guru extraordinaire, observed in a March 17, 2006 Carnage4Life blog post, "[T]he writing is on the wall that the era of desktop software is coming to an end. I suspect that the folks driving our technical direction like Ray Ozzie and Bill Gates have already accepted this, which is why almost everytime [you] see them giving speeches on our future direction, you hear the magic buzzword services."

As if to prove Dare's point, Bill Gates said in his March 20, 2006 keynote address to the Mix06 conference: "So it's a world of software running on devices, but connecting up to software running on servers, what we often talk about as services. ... It's a variety of business models, new exploration, it's great to see the experimentation, as obviously moving in new levels, transaction fees, subscriptions, one-time payments, all of those make sense, and the platform will facilitate that, give you things to connect up to no matter what your model is."

Here's a corroborating excerpt from Michael Arrington's CrunchNotes from his lunch with Bill Gates on Mix06's first day: "I asked a few questions, specifically about what Microsoft’s plans are around an online version of Office. Bill responded at length without really giving an answer. He did say that he thought people were too infatuated with the thought of an online version of Office, but that they were really focused on the idea of cloud storage for [O]ffice files. This fits right in with their strategies around Office Live, of course." And, obviously, with Google's internal development and acquisition strategies.

Finally (for now), Bill Gates told ZDNet interviewer Martin Lamonica at Mix06: "There was a major demarcation when Ray (Ozzie) put out his memo last year, really saying the primary applications model will have everyone delivering through the Web, monitored through the Web, updating through the Web. And many of these services like storage or authentication that you think of as Active Directory or SharePoint on premise, we've got to get those out (so people can) simply connect up to them. We're making great progress on that." [Check out what more Bill Gates had to say about Ray Ozzie and online applications on page 2 of the interview.]

The arrival of an adequate online, AJAXified, Office-compatible productvity suite is inevitable. It's only a matter of time before someone will assemble the right mix of features, reliability, performance, price, and security to deliver online word processor, spreadsheet, presentation, and database applications that will satisfy 50% or more of today's Microsoft Office Professional users. I bet that Dabble DB could easily handle more than half the current crop of Access 97 to 2003 applications created for individual and business use. Self-service Web-based data management solutions is a crowded application category.

QuickBase is well-established, but its minimum price of admission is substantially higher than that of current and potential competition. Dabble DB appears to lead the pack in ease of use for simple projects. Dabble DB provides a feature that Access developers have requested since version 2.0—an Outlook-style monthly calendar form—as does Zoho Creator. Here's the Dabble DB version (click image for full-size capture):


The SmallThought folks must keep Dabble DB's entry barrier low enough to whet the interest of casual users (possibly $0.00). On the other hand, volume pricing must be sufficiently high to deliver a viable business plan that satisfies the stability concerns of enterprise-grade customers and the greed of venture capitalists. Whether "I'd pay for that" isn't the issue; the real question is: "You want me to pay how much for that?"

[Update 3/24/2006]
Dabble DB went live with the production version on March 24, 2006, and SmallThought, Inc. announced the following tiered pricing for the service:


SmallThought offers all comers a free 30-day trial of the Basic plan.
An application, which corresponds to a database or workbook, may have any number of categories, which are the equivalent of a table or worksheet. Entries equate to table rows, so you'd expect the Company pricing tier to provide 20,000—not 10,000—entries per category. Preliminary tests with categories having 100 to 2,000 entries indicate that performance might be an issue for categories with several thousand entires.

Stay tuned for a future Dabble DB review with categories created from tables of Microsoft Access's Northwind sample database.

Technorati: Databases DabbleDB Microsoft Access QuickBase eUnifyDB Caspio Express DB Oracle HTML DB Google Base

SQL Server 2005 SP1 CTP Available for Download

Microsoft posted on 3/16/2006 download instructions for the first public Community Technical Preview (CTP) of Service Pack 1 (SP1) for SQL Server 2005 and SQL Server Express. The most important updates included in SP1 March CTP are promotion of database mirroring to production status for SQL Server 2005, and the addition of SQL Server Reporting Services (SSRS) plus full-text search features to SQL Express. These new capabilities will make SQL Server Express more attractive to potential users than its primary "free" and "open source" competitors—Oracle 10g Express Edition, IBM DB2 Express-C, MySQL 5.0, and PostgreSQL 8.1.

SP1 fixes the 19 bugs listed in Microsoft Knowledge Base article KB 913090, "A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP) March 2006." This article links to the individual KB articles that describe each bug. These fixes apply to all SQL Server 2005 editions, including SQL Express.

Brian Weckler's 2/16/2006 Direct Reports blog post notes that Report Builder gains the capability to add static images (for logos, backgrounds, et al.) and has a new Data Extension and Query Designer for SAP NetWeaver BI's InfoCubes and queries. (The .NET Framework 2.0's ReportViewer controls for Windows and Web forms have always supported static images.)

This post, updated 3/21/2006, summarizes the results of my initial SP1 March CTP test-drive with both SQL Server 2005 Standard Edition and SQL Server Express on separate partitions of a Dell PowerEdge 400SC server running Windows Server 2003 SP1 and Windows XP SP2, respectively (not virtual machines). The Microsoft SQL Server 2005 on Microsoft Windows Vista and 'Microsoft Windows Server Longhorn' page (posted March 6, 2006) states: "All editions of SQL Server 2005 Service Pack 1 (SP1) are supported on the February 2006 CTP release of Windows Vista and Windows Server 'Longhorn.' Note: The "Microsoft SQL Server 2005 Service Pack 1 Community Technology Preview" page is dated March 14, 2006, but wasn't accessible until 3/16/2006. Knowledge Base article 913090 was missing from the Microsoft site until 3/20/2006. Paul Flessner, Microsoft senior vice president of Server Applications, championed substitution of frequent, public CTPs to supplement periodic, restricted SQL Server beta 2005 releases. According to Microsoft Watch's Mary Jo Foley, "If Paul Flessner and his SQL Server team have their way, traditional beta releases may soon be a thing of the past." SP1 March CTP for SQL Server 2005 Individual self-extracting executable files are available from the Download Details page for SQL Server 2005's three platform versions: 32-bit X86 (251 MB), 64-bit X64 (302 MB), and 64-bit IA64 (351 MB). You can use each SQLServer2005SP1-KB913090-{x32/x64/ia64}-ENU.exe file to update Developer, Workgroup, Standard, and Enterprise editions. You can specify the components to which you apply the SP-1 March CTP in this installation dialog:

A few features must be updated, and Database, Analysis, and Reporting Services must be upgraded as a group. You can't roll back the SP1 installation.

Applying the full SP1 March CTP takes about 30 minutes on a moderately fast server. You'll probably need to reboot the server to complete the update. Another dialog displays SP1 installation progress:

You can read a semi-detailed installation log at the end of the update process.

SP1 March CTP for SQL Server Books Online

SP1 March CTP includes a prerelease version of an "April 2006 update" to SQL Server Books Online (BOL, 120 MB), which isn't guaranteed to include information about the modifications and additions made by SP1. Installation of the update takes about five minutes and "Removing backup files" consumes about 10 minutes.

Note: If you're using SQL Express and haven't installed the full version of SQL Server Books Online, download the December 2005 BOL update before updating BOL with the March CTP.

I wasn't able to find a topic that describes SP1 modifications and additions to BOL, but a search with "SQL Server 2005 Service Pack 1" returns 11 topics, most of which apply to SQL Server Reporting Services (SSRS).

Status of Database Mirroring

A Microsoft representative confirmed by e-mail that the SP1 release version will elevate database mirroring to production-grade, supported status. Thus, you can disregard the following note that each Books Online subtopic of the 'database mirroring [SQL Server]' index topic repeats and which the SP1 March CTP doesn't remove or update:

Important: Microsoft supportpolicies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

Ilya Bukshteyn, Microsoft’s director of product management for SQL Server, is quoted by several source as saying, "SP1 includes full database mirroring to offer real-time data backups." If that's the case, why wasn't BOL's 'database mirroring [SQL Server], about database mirroring' topic updated to reflect the change of status?

SP1 March CTP for SQL Server Express The Download Details: SQL Server 2005 SP1 Express Edition and Tools CTP page provides brief descriptions and links for downloading SP1 March CTP for SQL Server Express. Unlike SP1 for SQL Server 2005 Developer through Enterprise editions, to which you apply the service pack, you must replace your SQL Server Express instance with one of the following two new versions:

• Microsoft SQL Server 2005 Express Edition Service Pack 1 CTP March 2006 (SQLEXPR.EXE, 54 MB). This version replaces your existing SQL Server Express instance (usually SQLEXPRESS).

• Microsoft SQL Server 2005 Express Edition with Advanced Services CTP March 2006 (SQLEXPR_ADV.EXE, 235 MB). This version your existing SQL Server Express instance and adds support for SQL Server full-text catalogs (full-text search) and Reporting Services with Report Server. You can view reports created from data on a local SQL Server Express instance only. (You must install Business Intelligence Development Studio [BIDS] from the SQL Server Express Toolkit download to design reports.) SP1 March CTP also installs an updated version of SQL Server Management Studio Express (SSMSE).

Both preceding downloads install SQL Server Express March CTP with SP1 if you didn't install the RTM version.

Running the SQL Server Express with Advanced Services March CTP Setup Program

Executing SQLEXPR_ADV.EXE launches the Express version of the SQL Server 2005 setup program. In most cases, you'll want to install all Advanced Services, as shown here:

The SQL Express instance I attempted to update was installed on a Windows XP partition on which SQL Server 2005 Standard Edition and SQL Express were installed. Standard Edition was removed before starting the upgrade. I encountered an undocumented error during Database Engine installation that opened the following message box:

It was impossible to recover from this error, so I deleted the SQL Server Express instance and re-started the installation with mixed-mode authentication, which ran to completion, as shown here:

I then used SSMSE to reattach the original SQLEXPRESS instance's .mdf and .ldf files to the new SQLEXPRESS instance:

The SQL Server Configuration untility gains FullText Search and Reporting Services nodes:

The following downloads are available for SQL Server Express with Advanced Services and "vanilla" SQL Server Express:

• Microsoft SQL Server 2005 Management Studio Express - CTP March 2006 (SQLServer2005_SSMSEE.msi, 39 MB). This download installs an updated version of SSMSE and is intended for users of SQL Server Express that's been updated to SP1 (without Advanced Services).

• Microsoft SQL Server 2005 Express Edition Toolkit CTP March 2006 (SQLEXPR_TOOLKIT.EXE, 213 MB). The toolkit installs SSMSE, BIDS, client connectivity components, and SDKs. You need to install only BIDS from the Toolkit if you installed or upgraded SQL Server Express with Advanced Services.

Installing Business Intelligence Development Studio

Executing SQLEXPR_TOOLKIT.EXE launches the same setup app with a different list of installation options:

Features you select for installation that duplicate previously installed items, such as the Software Development Kit, are skipped during the installation process.

Expected SP1 RTM Date

According to Microsoft's Ilya Bukshteyn, RTM for SQL Server 2005 SP1 is scheduled for April 2006, which doesn't leave much time for the community to test the preview.

Technorati: Databases SQL Server 2005 SQL Server SQLServer MSSQL SQL Server 2005 Service Pack 1 SQL Server 2005 SP1 SQL Server Express SQL Express SQL Server Express Service Pack 1 SQL Express Service Pack 1 SQL Server Express SP1 SQL Express SP1

Add .NET Data Reporting to Windows and Web Forms

The second article on data reporting for Visual Studio Magazine completes the "Visual Studio ReportViewer Duet." Here are brief descriptions and links to both pieces and their downloadable VB 2005 sample code:

• Add Reports and Charts to Web Pages (March 2006 issue)

Deck: The ASP.NET 2.0 version of Visual Studio 2005's ReportViewer control and its Report Designer let Web page developers quickly embed, format, and export interactive reports and charts without running a SQL Server Report Server.

• Build Client-Side Reports Easily (November 2005 issue)

Deck: VS 2005's new ReportViewer controls and its built-in Report Designer enable smart-client and Web page designers to lay out, format, embed, export, and print interactive reports without running a SQL Server Report Server.

Unlike SQL Server Reporting Services, you can use any data source for which you have a native data provider or an OLE DB/ODBC-compliant Windows driver. This means you can bind ReportViewer reports to SQL Server Express, Oracle, IBM DB2, Microsoft Access, dBASE, MySQL, PostgreSQL and relational databases. Tip: The Other Resources link in the Article Tools box on each first page has download details for required components, additional documentation, and developer assistance for ReportViewer controls. Download the sample code from the box's Get the Code link. Technorati: Visual Studio 2005 VS 2005 VS2005 ReportViewer SQL Server 2005 SQL Server SQL Server Express SQL Express Databases

Anomalies and Issues with VB 9 DLinq Code

The January 2006 VB DLinq Technical Preview finally lets VB coders create Visual Studio test projects that explore most LINQ capabilities. I find that I seldom discover bugs, coding pitfalls, performance hits, and other problems when executing simple test examples, such as those of the preview's SampleQueries.sln project. So my "Test Drive VB9 and DLinq" article for Visual Studio Magazine's April 2006 issue and the .NETInsight newsletter uses moderate- to large-size object collections in the accompanying VbLinqDemo.sln sample project. VbLinqDemo.sln illustrates how to take advantage of most new DLinq and XLinq features. The VB team didn't update the "DLinq Overview" documentation for the preview. The white paper states: "Although the samples in this document are shown in C#, DLinq can be used with the LINQ-enabled version of the VB.NET compiler as well." Current LINQ, DLinq, and XLinq documentation contains few—if any—VB code examples, so you're on your own when it comes to VB query syntax beyond simple Select ... From Alias In DataContext Where ... Order By ... operations.

Following are a few of the problems I encountered when writing the article and the accompanying VB 9 sample project, which has about 2,000 lines of original source code, plus an 850-line DataContext class generated by SqlMetal.exe. Clearly, the problems could be the result of cockpit error on my part. I'll post solutions to the problems, if and when they're solved. I'll also post links to the article and its VB 9 source code when they're available from FTPOnline.com.

DataContext.GetQueryText(objQuery) Doesn't Return the SQL String Amanda Silver, a VB Team program manager, provided this reply to a question posed during the February 2006 DLinq chat about the T-SQL generated for DLinq queries:

"A: The magic API is GetQueryText on the DataContext object. And you pass in an IQuery object."

Amanda didn't provide a code example for the "magic API." Windows Live Web Search returned only three distinct hits for DLinq GetQueryText and Google returned only two. One hit of both searches unearthed the following:

Keith Farmer, a Microsoft developer on the DLinq team (and an Expert for the DLinq chat), suggested the following code to return the generated T-SQL statement from a C# 3.0 DLinq query in response to question posted in Channel 9's "Anders Heljsberg - LINQ" video forum:

var customers = from c in db.Customers
select c; // base query
IQuery query; // final query
if (filterByCity)
{
   string cityName = "London";
   // modify the base query
   query = from c in customers
   where c.City == cityName select c;
else
   // use the base query directly
   query = customers;
// base query
Console.WriteLine(db.GetQueryText(customers));
// correctly modified query
Console.WriteLine(db.GetQueryText(query));

The preceding code executes as expected with the original PDC technical preview upgraded to the RTM version of VS 2005. Here's the corresponding—but simplified—VB 9 translation:

Dim qryCustomers As IQuery
Dim strSQL As String
Dim objCustomers = Select c _
   From c In dbNwind.Customers _
   Where c.City = "London"
qryCustomers = objCustomers
strSQL = dbNwind.GetQueryText(qryCustomers)

The two examples' db and dbNwind DataContexts bind the Northwind sample database running on SQL Server 2005 or SQL Express. The VB 9 code fails when attempting to execute the qryCustomers = objCustomers instruction. The error message is Unable to cast object of type '<select>d__b`2[VbLinqDemo.Nwind.Customers, VbLinqDemo.Nwind.Customers]' to type 'System.Data.DLinq.IQuery'. There are even fewer search hits from linq IQuery. The obvious question is "Where or how do you get the IQuery object for GetQueryText's argument?" Logging T-SQL Sent to the Server A partial workaround for the GetQueryText problem is to capture the T-SQL statements sent to the server with a TextWriter object assigned to the DataContext.Log property. The most common log destination is the Console.Out object, but you can use add following code immediately after instantiating the DataContext object to assign a StringBuilder for logging:

dbNwind = _
  New Nwind.Northwind(My.Settings.NwindConn)
Dim sbLog As New StringBuilder
Dim twLog As TextWriter
twLog = New StringWriter(sbLog)
dbNwind.Log = twLog

sbLog contains T-SQL for parameterized SELECT queries and INSERT operations. But UPDATE and DELETE statements are missing (or I'm not privy to the legerdemain needed to capture them.) Alternatively, use SQL Server Profiler to view T-SQL statements and their parameter values. Issues with Bound Object Inserts in DataGridViews Databinding is critical to the success of LINQ and DLinq in mainstream Windows and Web client development. Smart client developers must be able to bind DLinq DataContext objects to design-time components—such as BindingSources—and DataGridViews, TextBoxes, and DateTimePickers. Web page designers need similar capabilities for ASP.NET server controls. To achieve ubiquity, DataContext objects must offer binding flexibility and performance equal to—if not better than—strongly typed DataSets.

One of the two Windows forms of the VbLinqDemo.sln sample project contains a three-level, hierarchical set of DataGridView controls bound to the Customers, Orders, and OrderDetails Table members of a DataContext object generated by SqlMetal.exe from the Northwind sample database.

I used the Data Source Configuration Wizard to generate an Object Data Source from the Nwind.Northwind DataContext class, and dragged the Customers node from the Data Sources window to the form to generate CustomersBindingSource, CustomersDataNavigator, and CustomersDataGridView controls. Dragging the Orders node (under the Customers node), which represents the Orders EntitySet of the Customers member, to the form added the OrdersBindingSource and OrdersDataGridView. Finally, dragging the OrderDetails node (under the preceding Orders node), which represents the OrderDetails EntitySet of the Orders member, to the form added the OrderDetailsBindingSource and OrderDetilsDataGridView.

An empty AddNew row appeared in the Orders and Order Details DataGridViews, but not in the CustomersDataGridView. Setting CustomersBindindingSource.AllowNew = True generates an AddNew row with the CustomerID column set read-only. You can type values in the remaining columns, but you can't persist a Customers member instance for the new row. Populating the cells with a DefaultValuesNeeded event handler doesn't solve the persistance problem. Clicking anywhere in the empty AddNew row of the Orders or OrderDetails DataGridView throws an untrappable "Value does not fall within the acceptable range" exception. Populating the rows with a DefaultValuesNeeded event handler doesn't work and thus doesn't solve the problem.

The incomplete workaround is to write code to populate the new objects with code similar to the following:

Private Sub AddNewCustomer()
   'Create some new OrderDetails instances
   Dim objLineItem1 As New Nwind.OrderDetails
   With objLineItem1
      .ProductID = 1
      .Quantity = 12
      .UnitPrice = 22.25
      .Discount = 0.05
   End With
   ...

   'Create a new Order instance
   'This requires changing Orders.EmployeeID and
   'Orders.ShipVia field data types from
   'Nullable(Of Integer) to Integer
   'Otherwise these field values remain Null,
   'regardless of the property values set
   Dim objOrder As New Nwind.Orders
   With objOrder
      .CustomerID = "ABOGC"
      .EmployeeID = 1
      .OrderDate = Now
      .RequiredDate = Now.AddDays(14)
      .ShippedDate = Nothing
      .ShipVia = 2
      .Freight = 15.15
      .ShipName = "A Bogus Customer, Inc."
      .ShipAddress = "1000 Broadway"
      .ShipCity = "Oakland"
      .ShipRegion = "CA"
      .ShipPostalCode = "94608"
      .ShipCountry = "USA"
      .OrderDetails.Add(objLineItem1)
      ...
   End With

   'Add the new Customer instance
   Dim objCustomer As New Nwind.Customers
   With objCustomer
      .CustomerID = "ABOGC"
      .CompanyName = "A Bogus Customer, Inc."
      .ContactName = "Joe Bogus"
      .ContactTitle = "President and CEO"
      .Address = "1000 Broadway"
      .City = "Oakland"
      .Region = "CA"
      .PostalCode = "94608"
      .Country = "USA"
      .Phone = "(510) 555-1212"
      .Fax = "(510) 555-1213"
      .Orders.Add(objOrder)
   End With
   dbNwind.Customers.Add(objCustomer)
   dbNwind.SubmitChanges()
End Sub

The most important issue with creating new Orders object instances is the need to modify the data type of the class's EmployeeID and ShipVia fields from Nullable(Of Integer) to Integer. Foreign key constraints with the Employees and Shippers tables preclude NULL values.

The need to add a new Orders object for an existing customer to the Customers.Orders EntitySet, rather than to the Table(Of Orders) object directly is an anomaly. Doing the latter operation adds an orphan Orders record with a NULL CustomerID value. The same is likely to be true for adding a new Order Details record, but hasn't been tested.

Adding new Customers or Orders records in a grid isn't a common practice in the real world. Thus, the sample project's literal values would be replaced by values supplied from TextBoxes, ComboBoxes, DateAndTimePickers, or the like on another form. The preceding code assumes a business rule that requires a new customer to process an initial order with at least one line item.

Deletion of Rows in Bound DataGridViews

You can delete Orders and OrderDetails instances and their corresponding table rows by selecting the row in the DataGridView and pressing Delete. The DataGridView.UserDeletingRow event handler displays a message box that lets the user cancel the pending deletion. The DataGridView.UserDeletedRow event handler contains or calls the code to delete the entity and related entities, if any.

Deleting a parent instance deletes it's child (and grandchild) entities, if present. However, the deletion process differs depending on the position of the deleted instance in the DataContext hierarchy. As an example, deleting a Customers entity and its related Orders and OrderDetails entities requires code similar to the following:

Dim objNewCust = (Select c _
   From c In dbNwind.Customers _
   Where c.CustomerID = "ABOGC").First()
dbNwind.Customers.Remove(objNewCust)
dbNwind.SubmitChanges()

Deleting one of two or more OrderDetails entities from an order requires the following code:

Dim objDelDetail = (Select d _
   From d In dbNwind.OrderDetails _
   Where d.OrderID = delOrderID AndAlso _
      d.ProductID = delProductID).First()
dbNwind.OrderDetails.Remove(objDelDetail)
dbNwind.SubmitChanges()

where the OrderDetailsDataGridView.UserDeletingRow event handler returns the delOrderID and delProductID values.

However, deleting one of two or more Orders entities requires invoking the dbNwind.SubmitChanges() method only. These apparent inconsistencies seem to be anomalies.

Persisting edits of existing entities appears to require invoking the dbNwind.SubmitChanges() method only.

Design-Time Errors Occur with an OrderBy Clause

The sample project's other form (VBLinqDemo.vb) demonstrates creating singleton and multi-row joins between a DLinq entity (Orders) and XLinq in-memory XML documents (Customers, LineItems, Products, Suppliers, Employees, and Shippers). The joined entities use VB 9 XML literal code to generate a complex, hierarchical XML SalesOrders document.

Adding the Order By o.OrderID Descending _ clause to the following code

Dim docSalesOrders As XDocument = _
<?xml version="1.0" standalone="yes"?>
<SalesOrders>
  <%= Select _
    <SalesOrder _
      EmployeeID=<%= o.EmployeeID %>
      ShipVia=<%= o.ShipVia %>>
      ...
    </SalesOrder> _
    From o In dbNwind.Orders _
    Where o.ShipCountry = strCountry _
    Order By o.OrderID Descending _
  %>
</SalesOrders>

displays an "Order By clause in query expression is invalid" error and the project won't compile.

Large Amounts of XLinq Code Confound the Debugger

Setting a breakpoint on a valid instruction in the code for the CreateXmlFromJoins procedure behind the VBLinqDemo.vb form and pressing F5 throws an exception with the following (typical) message: "The following breakpoint cannot be set: At VbLinqDemo.vb, line 917 character 13, ('Create from Joins' line 16). The Common Language Runtime was unable to set the breakpoint." (Windows Live Search returns a link to the Visual Studio Debugger Breakpoint Helper.)

Clicking OK to dismiss the message opens another message box that states: "Execution has stopped at the beginning of the program because one or more breakpoints could not be set." (No search returns.)

The lack of search hits indicates that this problem occurs infrequently.

Conclusion

LINQ and its DLinq and XLinq expressions are in the pre-beta stage, so it's not surprising to find that these VB 9 extensions have anomalies and problems. However, it would be useful if documentation covering the technical previews was more forthcoming about limitations and issues. Updated overview whitepapers with non-trivial VB 9 sample code also would be useful.

Leave a comment or e-mail me at Roger_Jennings@compuserve.com if you have questions, suggestions, or solutions.

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

Changes on the Way for VB 9.0 and XLinq

The January 2006 technical preview of Visual Basic 9 and Language Integrated Query (LINQ) is about to reach its second month of existence, but the XLinq team has already announced a syntax change for VB 9 XML literals. According to Avner Aharoni, a program manager for WebData XML team, there will be "a new syntax for these late-bound [XML literal] properties. Angle brackets now wrap the Xml member name so it is clear that the name refers to the elements with that name. For example Person.<Name> returns the elements called Name in the above document. ... In addition to this change in the axis members’ syntax, we are planning to change the binding of the Xml attribute member @ to XLinq’s Attributes method as opposed to the Attribute method as the compiler did in the January CTP." From Avner's response to a reader's comment [emphasis added]:

Our design goal is to make the Xml axis members experience as close as possible to regular CLR members, so users have a very easy learning curve. Our preference was to avoid the special syntax for Xml members, however because of the ambiguity and double colon problems we had to choose special syntax. We want users to continue using the most intuitive way to access members, which is the "Dot". Our plan is to add the known Xml members to the intellisense drop down so that the Xml members and the new syntax are easily discoverable. We hope that the new syntax will contribute the intellisense experience since the Xml members will show up in the same area due to the sorting of the intellisense members.

You can read more about the forthcoming changes to XLinq and the VB 9.0 compiler in Avner's "VB 9.0 Xml Features - latest update" post to the XML Team's blog.

---

Mike Champion took over Dave Remy's duties as XLinq program manager in mid-January 2006. (Dave is now the Group Program Manager for the XML Team.) According to his "New LINQ / XLinq Links" post, Mike continues to own the XML "DOM and the business case for XQuery in the mid-tier." Only time will tell if there is a "business case for XQuery" in the .NET Framework 3.0 or later; XLinq has the potential to handle the most common use cases for XQuery 1.0. In the meantime, Mike will be updating the "XLinq Overview" whitepaper that was included with the original (PDC) LINQ technical preview.

---

Visual Basic 9.0 might lose the expected lambda functions feature if the VB syntax problems for single- and multi-statement lambdas that are outlined in Paul Vick's pessmistic February 13, 2006 post can't be worked out. As Paul observes, the "Just because it comes in your size, doesn't mean you should wear it" admonition applies to programming languages, as well as to clothing. From Paul's response to a reader's comment [emphasis added]:

We're definitely started with nested functions and working our way forward, since nested functions give us at least the variable capture semantics that we need to properly do stuff like LINQ. So I would expect (but won't promise) that nested functions will make an appearance in VB. The question really is about the next step -- moving the nested functions inline. True lambda functions might not be so bad, and we're looking at some options. What looks really difficult is multi-statement lambdas. It's not so much an issue with line continuations (although we're looking at that issue too, given the length of LINQ query statements), but more with block delimiters. The fact that VB uses words for block delimiters makes inline multi-statement lambdas look pretty wordy. I'm not saying the issues are insurmountable -- we're still designing it -- just that it's not an entirely obvious piece of work...

Technorati: LINQ XLinq VB 9 Visual Basic 9 Orcas

Erik Meijer to Deliver Two LINQ Tutorials at ECOOP 2006

Erik Meijer, who describes himself as a "architect/language pimper in the SQL Server division," will conduct two LINQ tutorials at the 20th annual European Conference on Object-Oriented Programming (ECOOP) gathering in Nantes (France) on July 3 - 7, 2006. Following are excerpts from the two tutorials' abstracts:

• An Introduction to .NET using Visual Basic 9 and C# 3.0: "[T]he next versions of C# and Visual Basic will bring many features such as local type inference, anonymous types, nested functions and lambda expressions, extension members, meta-programming, and monad comprehensions, that we know and love from programming language research into the mainstream. Visual Basic adds further functionality such as deep XML support, relaxed delegates, strong duck typing, and dynamic identifiers that leverage its unique trait of having static typing where possible and allowing dynamic typing where necessary. • A Language Geek Perspective of LINQ, XLINQ, DLINQ: "This tutorial explains the programming language theory roots (monads and monad comprehensions, lazy/co-inductive functional programming, meta-programming) behind language integrated queries (LINQ) and briefly discusses the language enhancements to support them. We will give an in-depth treatment of the three domain specific APIs that constitute the LINQ framework namely the standard query operators for objects, the new XLinq API for manipulating XML, and the new DLinq and ADO.Net object-persistence infrastructures."

Earlier this year, Erik Meijer and Brian Beckman presented an XLinq demonstration titled "XML Support in Visual Basic 9.0" at the Programming Language Technologies for XML (PLAN-X) Workshop on January 14, 2006. PLAN-X was colocated with the 33rd Annual ACM SIGPLAN - SIGACT Symposium on Principles of Programming Languages (POPL 2006) held in Charleston, SC on January 11 - 13, 2006. Erik and Brian recently posted to the Microsoft Research site what appears to be a preview of a future, full-length technical paper, "XML Support in Visual Basic 9," that's based on—or forms the basis for—the PLAN-X demo. The paper's sample XLinq code is based on the January 2006 LINQ technical preview. My "Compose XML Content with XLinq Expressions and VB 9.0 XML Literals" post uses similar syntax from the earlier LINQ technical preview for the release version of Visual Studio 2005. Click here for a Google Blog search to display links to recent posts about—or references to—Erik Meijer from the OakLeaf blog. Technorati: LINQ DLinq XLinq C# 3.0 VB 9.0 Visual Basic 9.0 Orcas Erik Meijer

Anders Hejlsberg and LINQ: A Belated Interview with the C# Guru

Anders Hejlsberg said in this pre-PDC 2005 "Behind The Code" interview (02:20) that Bill Gates called the Clarity project "The most exciting thing he saw during his demo days." Clarity was the earlier code name for Language Integrated Query (LINQ) and C# 3.0. The "Life and Times of Anders Hejlsberg" traces the career of C#'s chief architect from his early days in Denmark to Scotts Valley (Calif.) as Borland International's Principal Engineer, and finally to Redmond and Microsoft, where he's now a Distinguished Engineer and Technical Fellow. The interview is laced with mentions of LINQ and related C# 3.0 technologies, despite interviewer Barbara Fox's attempts to minimize the episode's technical content. Anders' remarks on C# 1.0 and 2.0 start at 31:15. A brief discussion of "What's Next for C#" begins at 43:55. Type "LINQ" in the Search This Blog input box and click the link or click here to perform a Google Blog Search for the close-to-30 posts on the OakLeaf blog that relate to LINQ, DLinq, XLinq, or all three. Somewhat off-topic: The "Behind the Code" series appears to be an enormous resource consumer, at least compared with the traditional hand-held sessions conducted by Robert Scoble and his colleagues. This one-hour episode required(?) a 27-person staff: Producer, co-producer, director, line producer, floor manager, technical director, lighting director, gaffer, grip, audio mixer, two audio assistants, four cameramen, prop master, video (switcher?), engineer (for ?), tape operator, makeup person, script supervisor, media processor, editor, title designer, audio post (producer), and field unit camerman. The production overhead might explain why an interview taped "five weeks before PDC" wasn't posted to the Channel 9 lineup until February 1, 2006. I didn't discover the existence of the interview until I wrote the post for the "Behind the Code" Jim Gray interview. Technorati: LINQ DLinq XLinq C# 3.0 TurboPascal Delphi Anders Hejlsberg

Jim Gray: An Interview with the "Database Legend"

MSDN's Channel 9 Forums released on March 3, 2006 a one-hour interview with Jim Gray, Technical Fellow of the Scaleable Servers Research Group and the manager of Microsoft Research's eSciences group at the Bay Area Research Center (BARC) in San Francisco. Jim probably is best known to the Web community for the TerraService and SkyServer databases, but his credentials as a database legend stem from his seminal work in transaction processing, concurrency, and scaleability theory and practice. Jim is editor of the Performance Handbook for Database and Transaction Processing Systems, co-author with Andreas Reuter of Transaction Processing Concepts and Techniques, and Editor of the Morgan Kaufmann series on Data Management. He won the Association for Computing Machinery's prestigious Turing Award in 1998 and the US Geological Survey's John Wesley Powell Award (with Tom Barclay) for TerraServer in 2000. (Jim and Tom discuss the TerraServer project starting at 46:50.) He also gained the title of Microsoft "Distinguished Engineer" in 2000. This episode is the second in Microsoft's fledgling "Behind the Code" interview series, which Channel9 describes as follows:

Occasionally the office of the CTO, David Vaskevitch, sits down with some of Microsoft’s most influential technical employees to capture their stories. Instead of examining specific technologies, BTC takes a closer look at the person, the career and what it takes to produce world-class software.

There's no explanation of how an "office ... sits down with .. technical employees," but the interviews differ greatly from Channel9's technically focused fare gathered with handheld camcorders. Barbara Fox, formerly Microsoft's senior security architect of cryptography and digital rights management, hosts the interviews, which are professionally produced and edited. (The credits for the Microsoft Studios crew were about as lengthy as that for the first episode—27 people.) The interviews eschew detailed technical coverage for insight into the subject's career, colleagues, and scientific or technical community before and during his or her employment by Microsoft. Other OakLeaf posts that refer to Jim Gray: Jim Gray Podcast About SQL Server 2005 (and Later) and Microsoft Web Services DevChannel Opens at FTPOnline (.NET Web services client for TerraServer.) From FTPOnline: Build Petabyte DBs With SQL Server (VSLive! 2002 keynote, slides and audio track), A Talk With Database Guru Jim Gray (February 2002 interview by Lee The), and SQL Server 2005 Amid DBMS Market Dynamics by Peter O'Kelly (November 2004).You can read more about the TerraService Web services client and download its Visual Basic .NET source code from my "Build Real-Time Web Images" article from the August 2004 issue of Visual Studio Magazine. Technorati: Databases Database SQL Server 2005 SQL Server SQLServer MSSQL Jim Gray TerraServer TerraService SkyServer Behind The Code