OakLeaf Systems

Note: This post is updated daily or more frequently, depending on the availability of new articles.

••• Updated 10/25/2008 3:30 PM PDT: Minor additions
•• Updated 10/23/2008 9:00 AM PDT: Amazon EC3 (big news), EF, LINQ to SQL, LINQ, Astoria, and SSDS/Cloud Computing additions
• Updated 10/22/2008 3:00 PM PDT: Several additions

••• Links to Streaming PDC 2008 Keynotes from Guy Burstein:

Monday: 10/27
8:30 a.m. - 10:30 a.m.
100 kbps | 300 kbps | 750 kbps

Tuesday: 10/28
8:30 a.m. - 10:30 a.m.
100 kbps | 300 kbps | 750 kbps

Entity Framework and Entity Data Model (EF/EDM)

••• Jarek Kowalski provides a detailed step-by-step guide for Using EF Oracle Sample Provider with EDM Designer on 10/24/2008. Jarek says:

Many people are asking if it is possible to use EFOracleProvider with EDM Designer in Visual Studio 2008 SP1. The answer is yes, but because the sample doesn't include a DDEX provider required for VS integration, there are certain steps that have to be run manually.

Jarek’s guide has 15 steps.

••• Steve Lasker offers a tutorial for delivering SQL Server Compact (SSCE) with EF client applications in his Privately Deploying SQL Server Compact with the ADO.NET Entity Provider post of 10/21/2008. The highly detailed process covers SSCE installations on locked-down computers whose users don’t have Administrator accounts.

•• Bill Vaughn’s Is Microsoft Listening or Open for Output Only? post of 10/22/2008 proposes that Microsoft add semi-mandatory WHERE clauses and remove SELECT * options from SQL-generating wizards.

He also states that devs using the Entity Framework must process schema changes manually. I noted in a comment that “the Entity Data Model Designer has an Update from Database context menu command that opens a tabbed dialog that regenerates the entity classes automatically.”

• Saaid Kahn, a Program Manager on the Visual Studio Pro Tools team, describes how to create an n-tier database application  using ADO.NET Data Services (Astoria) and an Entity Data Model, in this Channel 9 Interview: ADO.NET Data Services in Visual Studio 2008 SP1 of 10/21/2008 by Beth Massi. (Copied from Astoria.)

David Sceppa announced on 10/21/2008 that Sybase's SQL Anywhere ADO.NET Provider Supports the ADO.NET Entity Framework!

Julie Lerman’s Best approach for teaching first exposure to Entity Framework post of 10/20/2008 discusses her choice between “teaching EF from the top down (EDM, LINQ to Entities, Object Query then EntityClient) or bottom up (EDM, EntityClient, ObjectQuery then LINQ to Entities).”

LINQ to SQL

••• Anders Janmyr’s TDD and LINQ to SQL post of 10/25/2008 describes two interfaces, IUnitOfWork and ITable<T>, two classes LinqToSqlUnitOfWork and LinqToSqTable<T> and for testing purposes two additional classes InMemoryUnitOfWork and InMemoryTable<T>, which make it “easy to mock out the Persistence Layer when using LINQ to SQL.”

This is another interesting example of the third-party efforts devoted to improving the testability of LINQ to SQL.

••• Jon Skeet asks What other Enumerable extension methods would you like to see? on 10/23/2008. So far, comments have requested a number of candidates.

•• Michael Minutillo describes an abstraction of LINQ to SQL for his MvcSupportFacility for ADO.NET MVC that uses the Unity Inversion of Control (IoC) container in his detailed Linq Repositories, Lifetime Manaqement and Unity post of 23 October 2008.

• Scott Hanselman’s Informal .NET Subsystem Survey shows use of LINQ to SQL is within about 5% of ADO.NET DataSets (1,734 to 1,887) among the 4,899 responses that comprise his Survey RESULTS: What .NET Framework features do you use? post of 10/22/2008.

• Matt Hunter is close to releasing his base class that supports n-tier, disconnected operation of LINQ to SQL. The LINQ to SQL Entity Base Release Candidate 3 post includes links to LINQ to SQL Entity Base - Disconnected LINQ Version 1.0 Release Candidate 3 of 10/22/2008. The project’s CodePlex home page explains its features.

This release features bidirectional serialization with the DataContractSerializer and the IsReference attribute for many:one associations. It requires a licensed version of VS 2008 SP1, not the Express edition.

• Jim Wooley’s Updated source for ThinqLinq now available post of 19/21/2008 offers 18 months of accumulated updates to his LINQ-based blogging application.

 Cirilo Meggiolaro Tip of the day #3 - Getting details of a LINQ change conflict exception explains an “easy way to get a list of tables and columns that have thrown the exception. The [sample] code … catches the exception and loop through conflict objects to generate a full report.”

LINQ to Objects, LINQ to XML, et al.

Agnes Molnar (Aghy) reports on 10/25/2008 that Linq4SP - RC2 is available to download! from here. Her post includes a list of RC1 bugs fixed in RC2.

•• LinqMaster’s How to Get a List of Installed Applications with LINQ post of 10/22/2008 shows you how to reduce the number of lines of C# 2.0 code in half with LINQ to Objects.

• Jay Sawyer conducts a series of performance tests against LINQ to Objects and observes some unexpected results in these two recent posts:

• Linq Performance Part II – Filtering (10/22/2008)
• Linq Performance – Part I (10/8/2008)

Part II includes comparisons with DataViews, filtered DataSets, LINQ to Objects, and LINQ to SQL.

Eric White’s Creating Data-Bound Content Controls using the Open XML SDK and LINQ to XML explains how to “take a document that has un-bound content controls, generate a custom XML part automatically (inferring the elements of the custom XML from the content controls), and then bind the content controls to the custom XML part.”

Bart De Smet “The C# Programming Language Third Edition” and thoughts on language evolution is a lengthy essay on the history of C# 2.0/3.0 features and features to be expected in C# 4.0.

In his LINQ to Anything – Channel 9 interview and a few more thoughts, Bart expands on Channel9’s announcement with links to related blog posts. He also “elaborate[s] a bit on fan-in and fan-out and [his] statement on the square infinity possibilities of LINQ, and take[s] a look at the following slides from a recent presentation [he] delivered on the topic” of LINQ to ∞ Squared “Proof Obligation” and “Function Composition.”

Jim Wooley’s LINQ is not an excuse for sloppy code post of 10/20/2008 warns developers to limit the number of elements returned to LINQ to SQL projects by LINQ queries by always including a Where predicate or Skip/Take function pairs.

ADO.NET Data Services (Astoria)

••• Pablo Castro mentioned but refused to describe an unlisted PDC 2008 stealth session in Channel 9’s Astoria Design Walkthrough: No design... just plugging our PDC talks! video of 10/24/2008. The session turns out to be ES07 Modeling Data for Efficient Access at Scale (Wed 10/29 | 1:15 PM-2:30 PM | 403AB):

Learn how to model data for cloud services. Topics include: mapping common data idioms to tables, tuning data models for common access patterns, and creating efficient queries.

The interview also includes uninspired descriptions of Pablo’s TL08 Offline-Enabled Data Services and Desktop Applications (Wed 10/29 | 3:00 PM-4:15 PM | 408) and Mike Flasko’s TL07 Developing Applications Using Data Services (Tue 10/28 | 1:45 PM-3:00 PM | 151) published presentations.

••• Phani Raju takes on one:many associations in his Working with associations in ADO.NET Data Services , Part 2 tutorial of 10/23/2008. Part 1 of the series, Working with Associations in ADO.NET Data Services, of July 2, 2008 covered one:one associations.

•• Pablo Castro describes several first-priority scenarios for “Astoria Online” in his Astoria futures: offline-enabled data services post of 10/22/2008:

• Outlook-style 1-tier applications that connect to a local data store (presumably SQL Server Compact v3.5 SP1) and synchronize with an online data store such as SQL Server Data Services (SSDS), SQL Server 2008 running under Windows Server 2008 on Amazon EC2 or from a Microsoft data center.
• Independent clients and servers in a synchronization relationship, such as a service that’s available for Web-based synchronization only.
• Local replicas of cloud-stored data, such as SSDS entities stored locally on SSCE.
• Data consolidation by synchronizing slices of data from multiple cloud-stored sources.

Pablo’s Astoria Design Walkthrough: Thinking of a future with sync & offline video is a preview of his Offline-Enabled Data Services and Desktop Applications session at PDC 2008 (Wed 10/29 | 3:00 PM-4:15 PM | 408A).

• Scott Hanselman’s Informal .NET Subsystem Survey shows use of ADO.NET Data Services is substantially greater than that of Entity Framework based on the 4,899 responses that comprise his Survey RESULTS: What .NET Framework features do you use? post of 10/22/2008.

Question: Did the respondents simultaneously select a LINQ to SQL or Entity Framework data source for Astoria? Doesn’t look like it to me from the data.

• Saaid Kahn, a Program Manager on the Visual Studio Pro Tools team, describes how to create an n-tier database application  using ADO.NET Data Services (Astoria) and an Entity Data Model, in this Channel 9 Interview: ADO.NET Data Services in Visual Studio 2008 SP1 of 10/21/2008 by Beth Massi. (Copied from EF.)

Phani Raju’s Accessing Cross-Domain ADO.NET Data Services from the Silverlight Client Library of 10/20/2008 discusses guidelines for cross-domain access using the Silverlight Client Library

Gil Fink describes Building an Ajax Client for ADO.NET Data Services to complement his previous Astoria posts:

• ADO.NET Data Services Introduction
• Building a .Net Client for ADO.NET Data Service

Roy T. Fielding’s REST APIs must be hypertext-driven post of 10/20/2008 takes the following position on the rules a REST API should follow:

1. A REST API should not be dependent on any single communication protocol, though its successful mapping to a given protocol may be dependent on the availability of metadata, choice of methods, etc. …
2. A REST API should not contain any changes to the communication protocols aside from filling-out or fixing the details of underspecified bits of standard protocols, such as HTTP’s PATCH method or Link header field. …
3. A REST API should spend almost all of its descriptive effort in defining the media type(s) used for representing resources and driving application state, or in defining extended relation names and/or hypertext-enabled mark-up for existing standard media types. …
4. A REST API must not define fixed resource names or hierarchies (an obvious coupling of client and server). Servers must have the freedom to control their own namespace. …
5. A REST API should never have “typed” resources that are significant to the client. Specification authors may use resource types for describing server implementation behind the interface, but those types must be irrelevant and invisible to the client. …
6. A REST API should be entered with no prior knowledge beyond the initial URI (bookmark) and set of standardized media types that are appropriate for the intended audience (i.e., expected to be understood by any client that might use the API). …

The way I read the full version of Fielding’s six rules, Astoria isn’t a RESTful API. Perhaps Pablo Castro would care to contest my conclusion or, for that matter, Fielding’s criteria.

ASP.NET Dynamic Data (DD)

••• Rob Conery’s ASP.NET MVC Storefront Part 23: WebForms and Dynamic Data of 10/24/2008 announces the start of a new Commerce Starter Kit v3.0 that’s uses “components and business logic based on the same components and business logic used for MVC Storefront but with a traditional WebForms UI” that will use LINQ, support full localization, and other new features described in J Sawyer‘s Commerce Starter Kit 3.0 – CSK Reprise post of the same date. The project will include an administrative site based on DD and LINQ to SQL.

You can watch this 15:00 video with Rob and J that describes the project. Support for CSK 3.0 will be part of J’s job description as a Microsoft developer evangelist.

••• Steve Naughton continues his DD posts with a question from the Dynamic Data forum in his Dynamic Data - Hiding Tables on the Default.aspx page post of 10/24/2008.

Steve Naughton’s Dynamic Data - Hiding Columns in selected PageTemplates of 10/20/2008 returns to IAutoFieldGenrators, attempts to specify the page template on which to hide columns.

SQL Server Data Services (SSDS) and Cloud Computing

••• David Robinson of the SSDS team says in his 2 Days to PDC2008!!!!!!!!!!!!! post of 10/24/2008:

We have some really super, pimped out, mack daddy, completely awesome stuff coming. Both in the teams presentations and in new features for Sprint 5. I just sent out the new features list to some internal softies for "Word Smithing" which you all should see in a few days, and the list is long and chock full o' goodies.

PS - I promise much more posting after Monday.

Promises, promises???

••• Ludwig Siegele analyzes the trend to mobile datacenters (Microsoft), virtualization (VMWare), and cloud computing (Amazon Web Services and Google App Engine) in his Where the cloud meets the ground: Data centres are quickly evolving into service factories special report for the 10/23/2008 issue of the Economist.

••• Roger Jennings reports another spurious attempt to register a trademark on “Cloud Something” in Arista Networks Claims “Cloud Networking” As Its Trademark of 10/24/2008.

••• Alan Williamson will conduct a one-day, hands-on Cloud Computing Bootcamp on 11/29/2008 in conjunction with Sys-Con’s Cloud Computing Expo at San Jose’s Fairmont Hotel. The (pricey) registration fee rises from 1,695ドル to $1,795 tomorrow. (List price is $1,995.)

•• Jeff Barr reports the following about Amazon EC2, Windows 2003 Server, and SQL Server 2005 in his Big Day for Amazon EC2: Production, SLA, Windows, and 4 New Capabilities post of 10/23/2008:

• Amazon EC2 is now in full production. The beta label is gone.
• There's now an SLA (Service Level Agreement) for EC2.
• Microsoft Windows Server 2003 is now available in beta form on EC2.
• Microsoft SQL Server 2005 is now available in beta form on EC2.
• We plan to release an interactive AWS management console.
• We plan to release new load balancing, automatic scaling, and cloud monitoring services.

It will be interesting to see whether Microsoft’s corresponding offering (or SSDS) will be price-competitive and when it will go online with an SLA.

It’s curious that Amazon is hosting non-current versions of Windows Server and SQL Server, which isn’t well-advertised on the AWS site. Perhaps Microsoft’s licensing terms were better than those for the 2008 versions.

Mary Jo Foley chimes in with Amazon battens down the hatches before Microsoft’s cloud launch next week and Werner Vogels posts Using the Cloud to build highly-efficient systems.

It wasn’t such a big day for Amazon stock: Down as much as 13% today (Thursday).

For a synopsis of pricing, see my Amazon Web Services Announces SLA Plus Windows Server and SQL Server Betas for EC2 post. That post hit Techmeme at 3:05 PM EDT

•• Jake Sorofman’s The Cloud Computing Adoption Model post of 10/23/2008 is a “context for thinking strategically about cloud computing” that’s “[l]oosely modeled after the Capability Maturity Model (CMM) from the Software Engineering Institute (SEI) at Carnegie Mellon University.” Note that CMM has been superceded by CMM-Integration (CMMI).

• Frank Gens predicts the Cloud Computing market will grow from 16ドル billion in 2008 to 42 billion in 2012, an annual compounded growth rate of 27% in IDC’s IT Cloud Services Forecast - 2008, 2012: A Key Driver of New Growth of 10/8/2008. IDC includes business applications, application development/deployment, system infrastructure software, storage, and servers expenditures in the estimates.

You can watch a three-minute video summary of the prediction and download slides at Cloud Computing |Preparing for the Next 20 Years of IT.

SSDS Team: Update Announcement Coming at PDC 2008 (10/20/2008)

The following message hit my inbox on 10/20/2008:

On October 27, 2008 we will be announcing, the next upgrade of SSDS at the PDC2008 conference in Los Angeles. In order to support this announcement, at that time we will update the documentation on the SSDS DevCenter site to include the new features in this upgrade. We will follow up on our PDC announcements, on the first day of the conference. …

The SSDS upgrade to be announced at PDC will be made available to you in early November.

Thank You,

The SSDS Team

Frank Gens’ IDC on “the Cloud”: Table of Contents post of 9/23/2008 offers links to recent IDC research on Cloud Computing:

• Defining “Cloud Services” and “Cloud Computing”
• What’s Driving the Cloud Computing Era? An IT Market View
• Framing the Cloud Opportunity for IT Suppliers
• The Transformative Interplay Between Cloud and Traditional IT Offerings
• IT Cloud Services User Survey, pt. 1: Crossing the Chasm
• IT Cloud Services User Survey, pt.2: Top Benefits & Challenges
• IT Cloud Services User Survey, pt.3: What Users Want From Cloud Services Providers
• IT Cloud Services Forecast - 2008, 2012: A Key Driver of New Growth

SQL Server Compact (SSCE) 3.5 and Sync Services

••• Steve Lasker offers a tutorial for delivering SQL Server Compact (SSCE) with EF client applications in his Privately Deploying SQL Server Compact with the ADO.NET Entity Provider post of 10/21/2008. The highly detailed process covers SSCE installations on locked-down computers whose users don’t have Administrator accounts. (Copied from EF.)

Liam Cavanagh reports on 10/20/2008 that the updated RTM1 version of the Sync Framework download reported in last week’s LINQ and Entity Framework Posts for 10/13/2008+ item is Not an Updated Version of Microsoft Sync Framework.

Anthony Carrabino’s SQL Server 2008 Feature Pack Released post of 10/18/2008, which first appeared 10/21/2008 in the Data Platform Insider blog, notes that the Feature Pack includes SQL Server Compact SP1 and the Microsoft Sync Framework.

And, for what it’s worth, Euan Garden reports that SQL Server 2008, Reportbuilder RTMs on 10/20/2008 (but it’s not in the Feature Pack).

Miscellaneous (WPF, WCF, MVC, Silverlight, etc.)

••• Aaron Skonnard delivers on 10/26/2008 two new episodes of his endpoints.tv WCF Screencast series on Channel9:

• Configuring WCF Service References (10/15/2008)
• Creating Your First WCF Client (10/8/2008)

••• Mike Taulty posted on 10/24/2008 detailed code samples for the first two of the three following questions that arose at his MSDN “Roadshow Rerun” in London

1. Can ObservableCollection<T> cope with a particular element being replaced? Not Added. Not Deleted. Replaced.
2. How to work with lookup fields in a database and data-bind to them?
3. How to take action when an ItemsControl creates an item and get hold of the item's UI elements?

••• John Papa’s Day of Silverlight 2 Recap post of 10/24/2008 has a link to the slide deck for his Day of Silverlight 2.0 presentation in Tampa, FL. Not surprisingly, his

3.5-hour presentation [focused] on Silverlight 2 data binding, development techniques, using it with SOAP, ASMX, WCF, REST, 3rd party services, cross domain policies, debugging tips, and ADO.NET Data Services.

The slides are quite informative but sample code would be even more so. So I’ve requested in a comment that John post the demo source.

•• Microsoft has assembled a new SOA & Business Process site that, at least so far, consists primarily of agitprop for and links to existing Microsoft products and services. Hopefully, the site will gain some developer-oriented content after PDC 2008.

Rob Conery, Scott Hanselman and Phil Haack are writing Professional ASP.NET MVC for WROX/Wiley, scheduled to hit the shelves in February 2009, according to Rob’s ASP.NET MVC DropDownList and HTML Attributes post of 10/21/2008.

Mike Ormond’s ASP.NET MVC and Scottish Developers (Demo) post of 10/20/2008 includes a link to his demo code for the recent Scottish Developers User Group meeting.

Posted by Roger Jennings (--rj) at 3:45 PM 0 comments  

Labels: ADO.NET Data Services, Amazon EC2, Astoria, Cloud Computing, EDM, Entity Data Model, Entity Framework, LINQ, LINQ to REST, LINQ to SQL, LINQ4SP, PDC 2008, Silverlight 2, SQL Server 2005 Express SP2, SQL Server 2005 SP2, SQL Server CE, SQL Server Compact Edition, SSCE, Sync Framework, Sync Services

Jeff Barr reports the following about Amazon EC2, Windows 2003 Server, and SQL Server 2005 in his Big Day for Amazon EC2: Production, SLA, Windows, and 4 New Capabilities post of 10/23/2008:

• Amazon EC2 is now in full production. The beta label is gone.
• There's now an SLA (Service Level Agreement) for EC2.
• Microsoft Windows Server 2003 is now available in beta form on EC2.
• Microsoft SQL Server 2005 [Express] is now available in beta form on EC2.
• We plan to release an interactive AWS management console.
• We plan to release new load balancing, automatic scaling, and cloud monitoring services.

Additional details:

• [Amazon] provides a C# library that wraps the [Web service] APIs, so that you can build and deploy your applications within .NET.
• [If] you require more than five Windows accounts or need to leverage applications that require LDAP, Kerberos, RADIUS, or other credential services, you must use Windows with Authentication Services.
• Beta level support for Microsoft Windows is now available on EC2, in the form of 32 and 64 bit AMIs.
• Microsoft SQL Server is also available in 64 bit form.
• [Amazon will] be adding support for DevPay in the near future.

Release Notes: Release: Amazon EC2 on 2008年08月08日

Articles and Tutorials: Feature Guide: Amazon EC2 Running Windows

Note: It’s curious that Amazon is hosting non-current versions of Windows Server and SQL Server, which isn’t well-advertised on the AWS site. Others are reporting (apparently incorrectly) that the 2008 versions are on line.

Mary Jo Foley chimes in with Amazon battens down the hatches before Microsoft’s cloud launch next week and Werner Vogels posts Using the Cloud to build highly-efficient systems, both of 10/23/2008.

The following is from the AWS Web site’s Amazon Elastic Compute Cloud (EC2) Running Microsoft Windows Server and SQL Server (Beta) page.

Pricing for Instances Running Windows Server 2003 with SQL 2005 Server Express

SQL Server Express, Microsoft IIS and ASP.NET can be used on any Amazon EC2 instance running Windows Server for no additional cost.

Pricing for Instances Running Windows Server 2003 with SQL Server 2005 Standard

Pay only for what you use. There is no minimum fee. Pricing is per instance-hour of Amazon EC2 consumed for each instance type. Partial instance-hours consumed are billed as full hours.

Posted by Roger Jennings (--rj) at 11:40 AM 0 comments  

Labels: Amazon, Amazon EC2, Cloud Computing, SQL Server 2005 Express SP2, SQL Server 2005 SP2, Windows Server 2003

My "compulsive obsession" to bring SQL Server 2005 stored procedures to the user-experience equivalent of dynamic T-SQL queries with a GridView bound to a LinqDataSource—regardless of the development effort required—continues.

When hydrating LINQ to SQL entities, I finally got server-side (incremental) paging to work properly with a LinqDataSource, bound DataGrid view and a stored procedure derived from a sample query. (See the introduction and "Attempts to Fix the Server-Side Paging Problems" sections of Problems Using Stored Procedures for LINQ to SQL Data Retrieval from 9/8/2007, updated 9/17/2007.) Here's a screen capture of the Orders test harness displaying 10 of the 122 Northwind Orders from customers in the USA:

Click capture to display full-size image in another window.

It was obvious that ad-hoc sorting wasn't behaving as expected for properties other than OrderID. For example, the preceding screen capture shows a descending sort on the Customer.CompanyName property. The sort's scope is the 10 rows displayed; the GridView sorts the rows. The same is true for ad-hoc sorts on all other properties except OrderID.

A quick review of a typical dynamic T-SQL query for page 2 with the simplest case, an ascending sort on the Freight property, showed the syntax required to increase the scope of the sort to all entities in the set:

exec sp_executesql N'SELECT TOP 10 [t2].[OrderID], [t2].[CustomerID],
[t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate],
[t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName],
[t2].[ShipAddress], [t2].[ShipCity], [t2].[ShipRegion],
[t2].[ShipPostalCode], [t2].[ShipCountry]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Freight], [t0].[OrderID]
DESC) AS [ROW_NUMBER], [t0].[OrderID], [t0].[CustomerID],
[t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1]
ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t1].[Country] = @p0
) AS [t2]
WHERE [t2].[ROW_NUMBER] > @p1
ORDER BY [t2].[Freight], [t2].[OrderID] DESC',N'@p0 nvarchar(3),
@p1 int',@p0=N'USA',@p1=10

The red ORDER BY expression for the ROW_NUMBER() function shows the syntax for creating the rowset from which page 2 (or greater) is obtained. Notice that both Freight and OrderID fields are included in the sort expression. The green expression is a holdover from the page 1 code and isn't required by the preceding example.

It's a better approach to use an explicit query than run sp_executesql to conditionally execute string-based queries from within the stored proc because of possible problems with caching the query execution plan and the brittleness of unchecked query syntax.

Garth Wells' demonstrated how to write CAST expressions to customize ORDER BY expressions in his 2001 Dynamic ORDER BY article; a fix by "Wildthing" in the comments thread accommodates different datatypes. Here's the stored proc code to handle various single-parameter sorts:

CREATE PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSorted(
@Country nvarchar(15) = 'USA',
@MaximumRows int = 20,
@StartRowIndex int = 0,
@SortCol nvarchar(128) = 'OrderID',
@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
t2.OrderDate, t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
t2.ShipPostalCode, t2.ShipCountry
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE -- Numeric types ascending
WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
THEN t0.Freight
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
THEN t0.EmployeeID
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
THEN t0.ShipVia
END ASC,
CASE -- Numeric types descending
WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
THEN t0.Freight
END DESC,
CASE -- Character types ascending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
THEN t0.CustomerID
END ASC,
CASE -- Character types descending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
THEN t0.CustomerID
END DESC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
THEN t0.ShippedDate
END ASC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
THEN t0.ShippedDate
END DESC
) AS ROW_NUMBER, t0.OrderID,
t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
FROM dbo.Orders AS t0
LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
WHERE Country = @Country
) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's a screen capture of a later version of the test harness that sorts all columns with the preceding stored procedure. The ORDER BY clause is CustomerID DESC, OrderID DESC with many instances of ASC, ASC, ASC, ... DESC, DESC, DESC, which the query parser appears to ignore.

Click capture to display full-size image in another window.

The basic CAST approach gives the same sort sequence for CustomerID vs. Customer.CompanyName, but different sequences for EmployeeID vs. Employee.LastName and ShipVia vs. Shipper.CompanyName. Here's the modified stored proc code (red) to sort the entire data set on EntityRef values:

ALTER PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSortedExt(
@Country nvarchar(15) = 'USA',
@MaximumRows int = 20,
@StartRowIndex int = 0,
@SortCol nvarchar(128) = 'OrderID',
@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
t2.OrderDate, t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
t2.ShipPostalCode, t2.ShipCountry
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE -- Numeric types ascending
WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
THEN t0.Freight
END ASC,
CASE -- Numeric types descending
WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
THEN t0.Freight
END DESC,
CASE -- Character types ascending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
THEN Customer.CompanyName
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
THEN Employee.LastName
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
THEN Shipper.CompanyName
END ASC,
CASE -- Character types descending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
THEN Customer.CompanyName
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'DESC'
THEN Employee.LastName
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'DESC'
THEN Shipper.CompanyName
END DESC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
THEN t0.ShippedDate
END ASC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
THEN t0.ShippedDate
END DESC
) AS ROW_NUMBER, t0.OrderID,
t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
FROM dbo.Orders AS t0
LEFT OUTER JOIN dbo.Customers AS Customer
ON Customer.CustomerID = t0.CustomerID
LEFT OUTER JOIN dbo.Employees AS Employee
ON Employee.EmployeeID = t0.EmployeeID
LEFT OUTER JOIN dbo.Shippers AS Shipper
ON Shipper.ShipperID = t0.ShipVia
WHERE Customer.Country = @Country
) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's the test harness with the Employee column sorted ascending and an Ext. check box to select the extended sort capability:

Click capture to display full-size image in another window.

The preceding code doesn't attempt to sort multiple columns. It might be tempting to write a stored proc for two or three successive sort sequences with EXEC or sp_executesql and dynamic T-SQL. However, LINQ to SQL won't codegen function calls for stored procs that execute dynamic SQL. Also considering the security and query plan issues, it probably makes more sense to pass two or three pairs of @SortCol/@SortDir values and stack a set of CASE statements for each.

Update 9/19/2007: Minor edits and typos corrected .

Posted by Roger Jennings (--rj) at 10:58 AM 5 comments  

Labels: LINQ, LINQ to SQL, LinqDataSource, SQL Server 2005, SQL Server 2005 Express, SQL Server 2005 SP1, SQL Server 2005 SP2, TOP (100) PERCENT, VB 9.0

My September 11, 2006 SQL Server 2005 Ordered View and Inline Function Problems post described a change of behavior between SQL Server 2000 and 2005 when displaying views created with a SELECT TOP 100 PERCENT ... ORDER BY Whatever query: SQL Server 2000 sorts the resultset and SQL Server 2005 [Express] doesn't.

The post received many comments, including several that objected to my claiming this behavior was an issue for SQL Server users because the behavior was by design. Unfortunately, comments posted before the change of the OakLeaf blog format were lost in the transition to the wider page.

Yesterday Microsoft issued a hotfix, FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2005, the result is still returned in random order, which is only available from Microsoft Support, describes the following Symptoms:

You have a view in a database in SQL Server 2005. In the definition of the view, the SELECT statement meets the following requirements:

• The SELECT statement uses the TOP (100) PERCENT expression.
• The SELECT statement uses the ORDER BY clause.

When you query through the view, the result is returned in random order.
However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.

The hotfix involves modifications to 11 files, including Sqlservr.exe and the workaround described in my post is simple, so Microsoft must have received many complaints about the problem.

Thanks to IDisposable (Marc Brooks) for the heads-up on the hotfix.

Technorati tags: Databases, SQL Server 2005, TOP (100) PERCENT, ORDER BY, SQL Server, SQLServer, MSSQL, SQL Server 2005 Express, SQL Server Express, SQL Express, da Vinci Toolset, Visual Data Tools, SQL Server Management Studio, Access 2007, Microsoft Access, Access Data Projects

Posted by Roger Jennings (--rj) at 11:04 AM 3 comments  

Labels: Access 2007, Access Data Projects, ADO.NET, ADO.NET 3.0, da Vinci Toolset, Databases, SQL Server, SQL Server 2005, SQL Server 2005 Express, SQL Server 2005 Express SP2, SQL Server 2005 SP1, SQL Server 2005 SP2, Visual Data Tools

Microsoft has issued a critical update for SQL Server 2005 SP2 Workgroup edition and higher. The update does not apply to SQL Server 2005 Express Edition (SSX).

According to Knowledge Base article 933508, Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended:

If you apply the initial release version of Microsoft SQL Server 2005 Service Pack 2 (SP2), existing SQL Server 2005 maintenance plans and Integration Services packages that contain cleanup tasks run those tasks at shorter intervals. The issue occurs only if all the following conditions are true:

• You downloaded SQL Server 2005 SP2 before March 05, 2007, and installed that download package.
• You use SQL Server 2005 maintenance plans or Integration Services packages.
• Those plans or packages include History Cleanup tasks or Maintenance Cleanup tasks.

The resolution for this issue is described in the "Resolution" section.

Users of SQL Server 2000 legacy maintenance plans and users who downloaded SP2 after March 05, 2007 are not affected. On this date, SQL Server 2005 SP2 was refreshed to include an update that avoids this issue.

Note This problem only affects the main SQL Server 2005 SP2 packages. These packages use the following naming convention:

SQLServer2005SP2-KB921896-ProccessorType-Language.exe

This problem does not affect SQL Server 2005 Express Edition or SQL Server 2005 Feature Pack packages.

Posted by Roger Jennings (--rj) at 8:33 AM 0 comments  

Labels: SQL Server 2005, SQL Server 2005 SP2

Microsoft announced on February 19, 2007 the availability of the Release-to-Web (RTW) versions of SQL Server 2005 Service Pack 2 for Enterprise, Standard, Workgroup, Developer, and Express editions. The What's New in Microsoft SQL Server 2005 SP2 page offers a detailed list of additions and improvements to the Database Engine, Analysis Services, Integration Services, Replication, Reporting Services, and shared tools. Links to SP2 upgrades for all SQL Server 2005 editions and replacements for earlier versions of SQL Server Express (SSX) with Advanced Services, (SSXAS), SQL Server Management Studio Express (SSMSX), and Microsoft SQL Server 2005 Express Edition Toolkit (SSXTK) are on the SQL Server 2005 Service Pack 2 page. There's also a link on the page to download the updated SQL Server Books Online SP2 version. Note: As of 11:00 am PST on 2/19/2007, the download links on the SSX Download Now! page that's linked from the above SQL Server 2005 Service Pack 2 page all refer to SP1. As of about 9:50 a.m. PST on 2/20/2007, the SP1 references changed to SP2. If you want to ensure that you're downloading the SP2 RTW build 9.00.3042.00 go to:

• Microsoft SQL Server 2005 Express Edition Service Pack 2 (SSX SP2, file date 2/18/2007, 55.4 MB for 32/64-bit version or 36.5 MB for 32-bit only version)
• Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2 (SSXAS SP2, file date 2/18/2007, 256.8 MB)

The primary improvements to SSX are support for operation with normal user credentials under Windows Vista's User Access Control, SSMSX compatibility with Vista, and improvements to backup, restore, and Reporting Services.

Following is the Configuration Options dialog that enables normal Windows users to run SSX instances (enabled by default) and optionally adds the installing Vista user as a member of the instance's SysAdmin (sa) role that's not enabled by default:

SSMSX has been enhanced with support for SQL Server 2005 Compact Edition (SSCE) databases, Vista compatibility, and new options for generating scripts.

Tip for SSX SP2 CTP and SSXAS SP2 CTP Only: If you've installed the SSX SP2 Community Technical Preview (CTP), upgrading SSX to SP2 RTW version will be blocked. In this case, you must stop and remove the SSX SP2 CTP instance and, if you installed SQL Server Reporting Services, remove the ReportServer$...mdf and ...ldf files from or rename the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Don't forget to run the SQL Server Surface Area Configuration tool and SQL Server Configuration Manager to reconfigure the default instance to that of the instance it replaced. (You can upgrade SQL Server 2005 Developer Edition or higher CTP and SSMSX SP2 CTP with no problem.)

SQL Server 2005 User Provisioning Tool for Vista

It's unlikely that many fully-licensed SQL Server 2005 Standard Edition or higher instances will run under Windows Vista, but you have the option of launching the SQL Server 2005 User Provisioning Tool for Vista after SQL Server installation completes:

Updating the SQL Server Client Tools doesn't install the February 2007 downloadable update to SQL Server Books On Line. (Only the English edition has been updated.)

Posted by Roger Jennings (--rj) at 12:12 PM 0 comments  

Labels: Management Studio Express SP2, SQL Server 2005 Express SP2, SQL Server 2005 SP1, SQL Server 2005 SP2

The C# team's Charlie Calvert posted "Connecting to Northwind" on February 16, 2007. The post contains instructions for enabling programmers new to Visual Studio 2005 Express editions to connect to the Northwind sample database. Note: Charlie's post is intended to assist readers of his "LINQ Farm: Connecting to a Database with LINQ to SQL" to create a local Northwind database SQL Server Express instance for demonstrating LINQ to SQL queries. The post is for beginners, so here are a few additions and clarifications that might aid new users:

1. Viewing Northwind in SQL Server Management Studio. "SQL Server Management Studio ships as part of SQL Server and SQL Server Express." Technically, SQL Server Management Studio Express (SSMSX) ships as a part of SQL Server 2005 Express Edition with Advanced Services SP1 . Alternatively, you can download SSMSX separately or as a component of Microsoft SQL Server 2005 Express Edition Toolkit SP1 .

2. Vista Problems. SQL Server Express (SSX) SP1 creates a login for members of the BUILTIN\Administrators and BUILTIN\Users groups. Administrators have SysAdmin (sa, or system administrator) permissions; Users have no permissions. Under Vista's User Account Control (UAC) feature, ordinary users can log in to SSX, but can't do anything else. The SQL Server Express Weblog's "Getting things working on Vista " post explains how to add a new login with SysAdmin privileges.

SSX SP2 will correct this problem; you can download the December 2006 CTP here . According to the post, Microsoft Update will automatically install SSX SP2 at RTM.

Tip: If you're running Vista, download SQL Server Management Studio Express Service Pack 2 - Community Technology Preview (CTP) December 2006 .

3. Under Figure 3. Visual Studio Express editions are restricted to User Instances by default. A User Instance attaches an SQL Server database file (Northwind.mdf) as a database (Northwind) when you open a project that uses the database and detaches it when you close the project. This feature enables XCopy distribution of your project by releasing SSX's lock on the database file when it's not open in your project.

When you first connect to a database file, you are offered the option to create a copy of the file in the folder with your project's .exe file. Unless you have a good reason not to do this, create the copy in the default file location.

4. Under Figure 5. Another option is "If you are using a remote instance of SQL Server Express, type the name of the server followed by \SQLEXPRESS: MyServer\SQLEXPRESS. If the remote server is a named instance of SQL Server, substitute the instance name for SQLEXPRESS."

5. Connecting to a pre-attached database. If you want to connect a VS Express Edition project to the database you've attached to SSX with SSMSX instead of attaching and detaching the file as a User Instance, you must change the default connection string or write your own. Creating a starter User Instance connection string and then modifying it is the easiest approach.

Here's a typical User Instance connection string in the project's app.config file:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=C:\Path\Northwind.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

Open the project's app.config file from Solution Explorer in the VS XML editor and change the connection string to the following:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

Note: You can't open the database of the preceding instance in VS Express editions' Database Explorer. Use SSMSX SP1 or SP2 CTP instead.

6. Connecting to a remote SQL Server or SSX instance. By default VS Express editions can't connect to databases of remote SQL Server or SSX instances. However, you can modify the project's connection string to connect to the remote instance:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=SERVERNAME\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

You must use SSMSX SP1 or SP2 CTP instead of Server Explorer in this case, too.

Update 2/19/2007: Mary Jo was right (as usual). See SQL Server 2005 SP2 Released to Web.

Posted by Roger Jennings (--rj) at 9:55 AM 2 comments  

Labels: C# Express, SQL Server 2005 Express, SQL Server 2005 SP2, VB Express, VS Express, Web Developer Express

The CNNMoney.com site published Business 2.0 magazine writer Owen Thomas's December 15, 2006 "Vista flaw could haunt Microsoft" article with this deck:

Posted by Roger Jennings (--rj) at 3:22 PM 0 comments  

Labels: DB2, DB2 9 Express-C, Oracle, Oracle Database 10g XE, SQL Server 2005 Express, SQL Server 2005 SP2, SSX