Manage Data With VS 2005

VS 2005's new visual data tools and data-bound controls, together with ADO.NET 2.0 data sources, simplify creating scalable, data-intensive Smart Client and Web applications.

Technology Toolbox: Visual Basic; XML; Visual Studio 2005, Visual Basic Express, Visual C# Express, or Visual Web Developer; SQL Server 2005 Developer Edition or higher, or SQL Server 2005 Express Edition

The Microsoft Developer Tools Roadmap promised that Visual Studio 2005 would "radically enhance the experience of manipulating and retrieving data" by simplifying "data source design from within the development environment." Planned UI enhancements included the ability to create data sources from local and remote data, business objects, and Web services for generating "data-bound forms without code." Microsoft also encouraged writing VB 8.0 or C# 2.0 managed code to replace or supplement T-SQL stored procedures and user-defined functions, as well as to generate user-defined data types (see the sidebar, "When to Use the SQLCLR"). Now that Microsoft has finally released VS 2005, SQL Server 2005, and their Express editions, it's time to put the developer tools teams' feet to the fire with a real-world evaluation of their handiwork.

Microsoft invested thousands of developer hours to optimize VS 2005's techniques for constructing Smart Client and Web-based data display and editing forms. It has made significant progress in this area, but we're still waiting for the day when deploying totally codeless, data-intensive applications in production environments will be practical. That said, I believe VS 2005 is likely to cut overall development costs for most two-tier or three-tier database front ends by 50 percent or more compared with VS 6.0, 2002, or 2003. Download and run this article's almost-codeless sample projects, and see if you agree.

Microsoft includes significant new improvements for handling data in both Smart Client and Web projects. I'll show you how to use the new ADO.NET 2.0 features and data-bound controls with emphasis on rapid application development (RAD) techniques for Smart Client projects and ASP.NET 2.0 Web sites. The sample code includes a Visual Basic SmartClient1.sln project and DataWebSite1 file-system Web site as working models that demonstrate these new VS 2005 features.

It's ironic that the Visual Basic team owned the data design-time experience for Smart Client (née Windows Forms) applications during most of the Whidbey beta period. After all, the stated objective for the development environment is eliminating or—more accurately—minimizing the code needed to implement data-bound forms. On the other hand, VB is the most popular RAD language for custom business projects, so the VB team's ownership of the Data Sources window, DataSet designer/editor, most of Server Explorer, and local data features makes sense. The VS Data team is in charge of data at run time, as well as SQL Server projects, database projects, and Visual FoxPro. The ASP.NET 2.0 (née Venus) team owns the data design-time experience for Web Forms, which differs considerably from the Smart Client approach.

The first data-related change from VS 2003 that you'll notice when opening a new Windows project or Web site in VS 2005 is the upgraded Server Explorer window. The new Add Connection dialog defaults to the SqlClient native data provider for SQL Server instead of the VS 2003 Data Link Properties dialog's hard-wired OLE DB provider. The Add Connection dialog also simplifies auto-attaching a SQL Server MDF/LDF file pair, which is the preferred approach for connections to local SQL Server instances. Attached SQL Server 2005 Express databases automatically detach after all connections close, which enables XCOPY deployment for Smart Client apps or Web sites with local data requirements. You can use SQLCMD or OSQL to manually detach an MSDE 2000 database file, copy it, and then use a connection string for the new connection to attach the copy to a local SQL Server 2005 Express instance. The Add Connection dialog's Data Source Change button lets you select relational database management system (RDBMS) alternatives, such as the native data provider for Oracle 7.3, 8i, and 9i; SQL Server Mobile Edition; Access (Jet) databases; ODBC; or other OLE DB drivers. The Server Explorer and its Add Connection dialog are identical for Smart Client projects and Web sites.

The next step for data-dependent Smart Client projects—after you've added a connection to a database server or file—is to add a new data source to the Data Sources window. The Data Sources window, not Server Explorer, is the starting point for the drag-and-drop addition of data components and data-bound controls to Windows Forms. (Unlike VS 2003, you can't drag tables from VS 2005's Server Explorer to add SqlConnection and SqlDataAdapter objects to the form designer's tray.) Choosing Add New Data Source from the Data menu starts the Data Source Configuration Wizard, which offers Database, Web Service, and Object options. Database data sources let you save a connection string clone to the App.config file and generate a typed DataSet from the database's tables, views, stored procedures, or functions. For simplicity and familiarity, this article uses Northwind rather than AdventureWorks tables for all examples.

Typed DataSets Create Heavyweight Projects
Creating a three-table (Customers, Orders, Order Details) typed DataSet autogenerates large chunks of code. The NorthwindDataSet.xsd file contains a 698-line annotated XML schema and the NorthwindDataSet.Designer.vb file weighs in at 3,391 instructions. (The VS 2003 versions created by choosing Generate DataSet from the Data menu are 70 and 1,571 lines, respectively.) The DataSet designer file defines partial classes, which let you customize DataSet behavior with partial classes that you add to a NameDataSet.vb/cs source file. Partial classes enable retaining your DataSet modifications when you refresh the DataSet's schema, which overwrites the NameDataSet.Designer.vb/cs file. Cached DataSets provide the local data source that Smart Clients access when disconnected from the database or network.

Dragging a table—Customers, in this example—as a Details view from the Data Sources window to a form adds labeled and bound TextBox, CheckBox, or DatePicker controls for each table field you select in the Data Sources window. (A drop-down list lets you select between dropping a Details view or DataGridView.) You also can choose to bind a NumericUpDown, ComboBox, Label, LinkLabel, or ListBox to a table field. Related child (detail) tables appear as fields added to their parent (master) table, which simplifies creating master-detail forms. The first table you drop on a form adds a TableNameDataNavigator ToolStrip control that has predefined VCR-style record navigation and Insert, Delete, and Save Data buttons. Then DatabaseNameDataSet, TableNameBindingSource, and TableNameTableAdapter data component icons materialize in the form designer's tray (see Figure 1). The new DataSet designer gains a field list with editable TableAdapter items (see Figure 2).

Dropping the table icon on the form adds this command to the Form1_Load event handler to fill the first TableAdapter:


The first table also adds a bindingNavigatorSaveItem_Click event handler to update the base table(s) with edits made to the DataSet by the user.

Dragging related tables to generate and populate new DetailsView or DataGridView controls adds TableNameBindingSource and TableNameTableAdapter data components to the tray, table field lists to the DataSet designer, and TableNameTableAdapter.Fill instructions. The default Form1.Designer.vb file for a basic Customer-Orders-Order Details editing form contains about 670 instructions for a total of more than 4,000 code lines for the project. Clearly, VS 2005 doesn't generate "data-bound forms without code," but, unlike its predecessors, you don't need to write code or edit property values to demonstrate a superficial—but non-trivial—database front end in five minutes or less. Reaching this point in VS 2003 requires a substantial amount of DataSet and DataAdapter customization and code to handle PropertyManager and CurrencyManager objects. Partial classes let you customize strongly typed DataSet code without losing your modifications when regenerating the DatabaseDataSet.designer.vb file.

The new TableAdapter class is a strongly typed, RDBMS-independent wrapper over a SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, or OdbcDataAdapter. TableAdapters and their associated DataTables support default and parameterized Fill and GetData SELECT queries, as well as default and customized UPDATE, INSERT, and DELETE commands. The completely redesigned DataSet designer/editor provides a graphic environment for customizing the default TableAdapters that you add when generating the DataSet with the Wizard. You can edit the Fill method's SELECT query to add a parameter or TOP n modifier to reduce the DataSet's size. This SELECT statement is the main query for auto-generating the UPDATE, INSERT, and DELETE commands. Alternatively, you can add named queries to serve the same purpose. For example, you might want to return only the last 100 Orders records and their related Customers and Order Details records. In this case, right-click on the OrdersTableAdapter header and choose Add Query to start the Table Adapter Query Configuration Wizard, which gives you the option of using SQL SELECT statements, creating a new stored procedure, or using an existing stored procedure. The FillLast100Orders and GetLast100Orders SELECT statements are:

SELECT TOP 100 OrderID, CustomerID, 
EmployeeID, OrderDate, RequiredDate, 
ShippedDate, ShipVia, Freight, ShipName, 
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, 

At this point, you fill the CustomersTableAdapter and Order_DetailsTableAdapter with records related to those returned by the preceding query:

SELECT CustomerID, CompanyName, ContactName, 
ContactTitle, Address, City, Region, PostalCode, 
Country, Phone, Fax 
FROM dbo.Customers WHERE CustomerID IN 
(SELECT TOP 100 CustomerID 
FROM dbo.Orders ORDER BY OrderID DESC)


SELECT OrderID, ProductID, UnitPrice, Quantity, 
FROM dbo.[Order Details] WHERE OrderID IN 
(SELECT TOP 100 OrderID 
FROM dbo.Orders ORDER BY OrderID DESC)

Replacing the Fill method with the FillLast100Orders or FillByLast100Orders method in the Form_Load event handler creates a much more lightweight DataSet and doesn't affect autogenerated UPDATE, INSERT, and DELETE commands.

The BindingSource class, as its name implies, links simple and complex data-bound controls to DataTables and manages record navigation and editing. A TableNameBindingSource has DataSource and DataMember properties. For the sample form's Customers parent table, the DataSource is NorthwindDataSet and DataMember is the Customers DataTable. Autogenerated code in the Form1.Designer.vb file binds each textbox's Text property to the appropriate field of the CustomersBindingSource. The OrdersBindingSource uses the CustomersBindingSource as its DataSource and FK_Orders_Customers (the CustomerID foreign key constraint) as its DataMember to display in the DataGridView only Orders records related to the current Customer record. Similarly, the Order_DetailsBindingSource has OrdersBindingSource as its DataSource and FK_Order_Details_Orders as its Data Member. TableNameBindingSource also has Filter and Sort properties; Filter takes a string that corresponds to a SQL WHERE clause (without the WHERE keyword), and Sort takes an ORDER BY string (without ORDER BY).

BindingSources handle disconnected record navigation with MoveFirst, MoveNext, MovePrevious, MoveLast, AddNew, and Find methods. Use this VB code to set the CustomersBindingSource.Current item to the record for Rattlesnake Canyon Grocery:

CustomersBindingSource.Position = 

Add this code to filter Customers records for U.S. firms only, and then remove the filter:

CustomersBindingSource.Filter = 
	"Country = 'USA'"

This code shows you how to reverse the default sort order of the CustomersBindingSource:

CustomersBindingSource.Sort = 
	"CustomerID DESC"

Invoke the BindingSource.RemoveAt(intIndex) method to delete the record specified by intIndex; to delete the current record from the DataSet, apply the BindingSource.Remove method.

The new complex-bound DataGridView replaces VS 2002/3's less-than-stellar bound DataGrid control for Smart Client projects. Dragging the default table node from the Data Source window to a form generates a TableNameDataGridView with columns and rows that have DataGridViewTextBoxColumns bound to the corresponding TableNameBindingSource's data members. A Smart Task tag lets you select an existing data source or create a new one; edit, add, or reorder columns; enable adding, editing, or deleting rows; or dock the DataGridView below the DataNaviagator. To accommodate non-text data types, you can substitute other ColumnTypes—DataGridViewCheckBoxColumns for Boolean values, DataGridViewImageColumns for graphics, and DataGridViewLinkColumns for URLs. DataGridViewButtonColumns fire events and DataGridViewComboBoxes act as bound pick lists that you populate with read-only data sources from foreign keys (see Figure 3). Clicking on the Columns property's Builder button opens the Edit Columns dialog, which lets you select the ColumnType for existing or added bound columns.

DataSet updates dictate that most DataGridViews be data-bound to relational tables, but you also can write code to add and populate rows of unbound DataGridViews with SqlCommands that execute SqlDataReaders. Few developers will miss the DataGridView's lack of support for the obsolete DataGrid's cumbersome hierarchical data display. DataGridViews sport an almost absurdly granular event repertoire; the controls' 149 events threaten to induce an event choice crisis. A combination of bound textboxes, other simple-bound controls, and DataGridViews probably will handle 90 percent or more of day-to-day data display and editing requirements. But the DataGridView certainly won't destroy the market for custom third-party grids and other data-bound controls with more extensive feature sets.

Many familiar Windows Forms controls support ex post facto databinding for an existing UI design. For example, you can drag a table column icon from the Data Sources window and drop it on an existing TextBox, CheckBox, RadioButton, NumericUpDown button, DateTimePicker, or MonthCalendar to bind the control's value. (DateTimePicker and MonthCalendar controls still don't handle null values gracefully.) DropDownList and ListBox controls have a Smart Task tag with a checkbox to enable databinding; marking the checkbox lets you select a Data Source, Display Member, Value Member, and Selected Value. The Windows Forms ReportViewer renders SQL Server Reporting Services report (RDLC) files you create with the ReportDesigner. Microsoft added the ReportViewer and ReportDesigner to VS 2005, VB and C# Express, and Visual Web Developer editions at the beta 2 stage.

Projectless ASP.NET 2.0 Web sites receive a complete data-bound control rework. Web sites don't have a Data Sources window, but—unlike Smart Client projects—you can drag a table node from a Server Explorer database connection to a page in Design view. Dropping a table's node on the page adds an SqlDataSource1 placeholder and a default read-only GridView control bound to the table. ASP.NET 2.0's version of the Configure Data Source Wizard is almost identical to that for Windows Forms. SQL Server is the default data source, as you'd expect, but you can specify Oracle, Access (Jet), or other OLE DB or ODBC data sources, each of which implements two-way binding for controls that derive from the DataBoundControl class. Alternatively, you can add a SqlDataSource, AccessDataSource, ObjectDataSource, XmlDataSource, or SiteMapDataSource from the ToolBox's Data section, and then configure it with the Configure Data Source Wizard.

The new GridView control, which replaces ASP.NET 1.x's DataGrid, offers declarative formatting, sorting, editing, deleting, and paging capabilities. The Auto Format Smart Task lets you apply one of 17 preset color schemes. Enable the behaviors you want by marking Smart Task checkboxes. Notice that GridView controls don't support inserting new rows (see Figure 4). The Delete command button isn't enabled for the sample Customers GridView because the Orders table's foreign-key constraint would throw an unhandled exception when attempting to delete a Customers record. In contrast to working with the DataGrid, you don't need to write any code to create an easy-to-use data-retrieval and editing page. However, the inability to execute INSERT commands from GridView controls is a serious limitation, and workarounds that use templated textboxes in footer columns are cumbersome at best. Use the new DetailsView or FormView control to add rows to the underlying table.

The Web site's UI lacks a Data Sources window with its hierarchical view of related tables to create codeless master-details forms. This omission means that you must add parameterized WHERE clause constraints to SqlDataSources so data-bound server controls display related record(s). When you add a WHERE ColumnName = @ColumnName clause to the SqlDataSource's SELECT statement, the Configure Data Source Wizard opens a Define Parameters dialog that lets you select the source for one or more parameter values. Parameter value sources include cookies, control values, form fields, profile properties, query strings, and session fields.

If you select QueryString as the parameter's data source, you can replace a GridView's Select command button with a HyperLinkField that has its DataNavigateUrlFields property value set to ColumnName and DataNavigateUrlFormatString set to WebForm.aspx?columnname={0}. The selected ColumnName value replaces the {0} placeholder. For example, clicking on the sample DataWebSite1 Web site's Select link for Rattlesnake Canyon Grocery on the Default.aspx page posts back with http://localhost:15739/DataWebSite1/DetailsView.aspx?customerid=RATTC as the URL. The customerid=RATTC query string sets the DetailsView page's CustomerByCustomerIDParam SqlDataSource to the appropriate row for editing in the CustomersDetailsView control. Similarly, a DataNavigateUrlFields property value of CustomerID, OrderID, and DataNavigateUrlFormatString value of DetailsView.aspx?orderid={0}&customerid={1} return http://localhost:15739/DataWebSite1/DetailsView.aspx?orderid=11077&customerid=RATTC as the post-back URL to populate OrderDetailsByOrderIDParam data source and its bound OrderDetailsGridView control (see Figure 5). Populating the DataWebSite1 application's four data-bound controls and editing the three tables don't require a single line of code.

GridView and DetailsView controls have optional Footer, Empty Data, and Pager templates; DetailsView adds an optional Header template. The Fields dialog has a link that lets you convert individual bound fields to template fields. FormView controls provide greater formatting flexibility by providing default Item, EditItem, and InsertItem templates for each field. The EditItem and InsertItem templates let you substitute other data-bound Web controls—such as DropDownLists (see Figure 6) or CheckBoxes—for textboxes. ASP.NET 2.0 DataLists and Repeater controls are quite similar to their 1.x counterparts. The ASP.NET ReportViewer is a sample server control that's intended for URL-based navigation and rendering of reports in Internet Explorer instead of a Smart Client form.

Test-drive the SmartClient1 project and then review the code behind the Northwind.vb form. A simple database front end that doesn't need to update back-end tables doesn't require writing a single line of code. Autogenerated code for the Northwind_Load event handler fills the three primary and two lookup tables. Updating more than one table requires adding only two instructions per table, as illustrated by the bindingNavigatorSaveItem_Click event handler's code. The DataWebSite1 project's two forms don't require any added code. Although real-world projects certainly will require .NET 2.0 developers to write some VB or C# code, I believe you'll agree that VS 2005 is as close as you're likely to come to the nirvana of a truly codeless presentation layer for data-intensive .NET applications.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.