Executive Summary:

With the release of Visual Studio 2008 Service Pack 1 in August 2008 developers now have access to features that weren’t in the RTM release. Of interest to SQL developers are reporting and data caching features that you can use in your applications.

Visual Studio 2008 Service Pack 1 (SP1), released in August of 2008, finally enables many features that didn’t make the cut in the initial Visual Studio 2008 release to manufacturing (RTM). I’ll show you SP1 reporting and data caching features that developers and architects can leverage in their applications. For information on other features in Visual Studio 2008 SP1, see the web-exclusive sidebar “List of New and Enhanced Features in Visual Studio 2008 SP1” (www.sqlmag.com, InstantDoc ID 101174.

What’s New in Reporting?

Since working with Peter Blackburn on Hitchhiker’s Guide to SQL Server 2000 Reporting Services I’ve acquired a new affinity for reporting technologies. I really got hooked when Visual Studio 2005 introduced the ReportViewer control and gave SQL Server Reporting Services (SSRS) developers a refined Report Designer and deployment tool.

I was disappointed to find that Visual Studio 2008 RTM didn’t support any of my (or your) existing business intelligence (BI) projects, nor could I create new ones. While Visual Studio 2008 could open projects that included the ReportViewer control, none of my SSRS BI projects could be opened. I also discovered that I had difficulty creating data sources against the pre-release versions of SQL Server 2008. (I recommend that you never install pre-release software on a system that you can’t easily formahttps://secure.windowsitpro.com/filedirupload/zip/index.cfm?PubID=1t and rebuild.)

The Report Designer

When I had both the RTM and the SP1 versions of Visual Studio 2008 installed, I was finally able to open my Visual Studio 2005 BI projects. I also found the cool new SQL Server 2008 Report Designer, which re-enabled the projects and associated templates such as the Report Server Project Wizard and Report Server Project. If you’re familiar with the Visual Studio 2003 Report Designer (carried forward from the original Reporting Services add-in for Visual Studio), you’ll immediately see there is no Data tab in the new Visual Studio 2008 SP1 Report Designer.

The Data tab has been replaced with a new Report Data Window, as shown in Figure 1. Note that it’s a peer of the Report Item Toolbox, Server Explorer, and Data Sources window.

Built-in Fields are now exposed in the Report Data window. This makes it easy to incorporate common system-generated values into a report. For example, the Total Pages and User ID are all exposed here.

Parameters are now managed from the Report Data window—right-click the parameters node to add a new Parameter (query or report). This launches a dialog box you can use to configure the parameter’s name, prompt, data type, available values, and default values, as well as enable auto refresh when the parameter changes. You can also manage Images through the Report Data window.

More importantly, Data Sources and DataSets used to fetch report or parameter list data are exposed here. This is also where you can add a new Query Field, add a Calculated Field, manage the Query SQL, or set the Dataset properties as illustrated in Figure 2. Again, this is a change from the Data Set enumeration window in the old designer, but it’s not that different. This is where you choose fields that are dragged to report elements.

You can use the new Dataset Properties dialog box to configure the Dataset. This is where you define or edit the query, specify a stored procedure, set the query timeout, manage (report) parameters, manage query and calculated fields (including the name and Field source), set a number of options, including collation, and manage how subtotals are managed.

The Dataset Properties Filters option window is where you configure how the report processor filters inbound rows as I describe in my books.

While there isn’t room here to go into the details of the new server-side Reporting Services report processor which interprets the RDL report definition created by the Report Designer, suffice it to say that there have been a number of significant changes. However, these have only been partially enabled with Visual Studio 2008 SP1. These innovations include the “Tablix” controls which combine features and functionality of the old Table and Matrix controls. There’s also a new Gauge control and the ability to render rich text in the TextBox and other controls. Sadly, these new features are not available when using the Report Viewer control but I’ll get to that.

RDL and RDLC Files

The dialog boxes I described earlier are just a new way to do the same tasks you could do using the Visual Studio BI Tools Report Designer in earlier versions. All of these settings are persisted in the Report Definition Language (RDL) as XML so you can (at your own risk) edit this file directly when Visual Studio gets confused (as I have seen it do on a few occasions).

The new SQL Server 2008 Reporting Services server-side report processor interprets what I call second-generation RDL files as generated by the Visual Studio 2008 SP1 BI tools. But here’s the rub: Visual Studio 2008 projects still create first-generation client report definition files (RDLCs), even after SP1. This means that you’ll be able to import first generation RDL reports into the new Visual Studio 2008 SP1 Report Designer, but you won’t be able to use them with the “new” Visual Studio 2008 MicrosoftReportViewer control or in any older versions. The “real” MicrosoftReportViewer control is not expected until2010—in the next Visual Studio release.

Visual Studio 2008 SP1 and SQL Server 2008

Frankly, SQL Server 2008 brings a lot of useful functionality to your toolbox. Visual Studio 2008 SP1 (finally) adds the ability to manage SQL Server 2008 databases from the Server Explorer. This includes the ability to create SQL Server 2008 server-side executables. Using Visual Studio 2008 SP1, you’ll be able to use the Server Explorer to:

  • Access SQL Server 2008 and create Data Connections against SQL Server 2008 instance databases or create new SQL Server 2008 databases.
  • Manipulate SQL Server 2008 executables. To do so, start a Visual Basic, C# or C++ “Database” project, or simply navigate to and then manage stored procedures as you could in Visual Studio 2005. You’ll also discover that SQL Server Management Studio (SSMS) has also incorporated the ability to step through stored procedures in SQL Server 2008.
  • Create tables that leverage the new SQL Server 2008 data types including GEOMETRY, GEOGRAPHY, HIERARCHYID, DATE, TIME, DATETIMEOFFSET, and DATETIME2—but not the new FILESTREAM designation on Varbinary(max) columns.
  • Leverage the new ADO.NET 3.5 Synchronization Services and Visual Studio Local Data Cache technology in a whole new way.
  • Create the new “SQL Server 2008 Compact 3.5” databases. I’ve started to call this database engine “SQL Compact” to keep folks from confusing it with other SQL Server versions that share common binaries.
  • Permit developers to use ADO.NET 3.5 to pass tables as ad hoc or stored procedure parameters. This solves a couple of long-standing issues and opens up several new data management paradigms. For example, you can pass a set of rows in a (structured) DataTable to be merged into a database without having to send individual SQL DML queries. You can also pass a table of acceptable values directly to a WHERE clause IN expression (as shown in Listing 1).
  • Visual Studio 2008 SP1 and Caching

    With SP1, Visual Studio 2008 has refined the ability to create and manage replicated data sources with the Local Data Cache classes it can generate. This is one of the most important and far-reaching innovations introduced by the Visual Studio team in some time. Up until now, replication scenarios have been the sole purview of skilled DBAs (with considerable patience). Now that virtually any Visual Studio Data Source can be synchronized with its data source, developers no longer have to worry about:

  • SQL-based updates against local data. Changes can be saved to the data row Value property and the row(s) synchronized against a common database.
  • Synchronizing lookup tables with common database data values. Keeping UI metadata in sync in deployed applications. These values can also be synchronized against a common database.
  • Working with disconnected data sources for fear that the local data is not secure. Consider that the SQL Compact database used to host client-side data can be easily encrypted or protected using an ACL (or both). What’s cool about the RTM and SP1 versions of SQL Server 2008 is that the impact on the server-side database is virtually invisible. If you try to enable Local Data caching (as shown in Figure 3) with a pre 2008 SQL Server and you select the Incremental Synchronization Mode (see Figure 4), your DBA must permit you to make a number of significant changes to the database tables, as I describe below.
  • The script to generate these base table changes can be added to your project so you can easily send it to your DBA. I won’t bore you with the SQL (which you can easily generate yourself), but here’s what the script does within a transaction:

  • Adds LastEditDate and CreationDate columns to the specified table.
  • Adds a new table _Tombstone which contains the (required) PK and DeletionDate columns.
  • Adds a _UpdateTrigger, _InsertTrigger and _DeletionTrigger that manage the newly added Tombstone table.
  • The Wizard also creates an undo script to back out these changes. Both scripts are saved to your project upon demand. You can choose not to execute the script in case you don’t have rights or you fear the wrath of your DBA (as I do).

    However, if you’ve installed SQL Server 2008 you can check the Use SQL Server Change Tracking box at the bottom of the Data Source Configuration Wizard, as shown in Figure 4. This also creates a script—but it’s a bit simpler as Listing 2 shows.

    Features I’m Still Looking Forward To

    Developers still can’t use Visual Studio 2008 SP1 to view, create, or set properties for log-in accounts or set rights on any object in the database. You’ll have to use SSMS for these essential tasks or use SSMS to build some scripts to do so. Also, Visual Studio can’t create a new table that has a FILESTREAM designation. I’m hoping to see these enhancements in future versions.