INFO: Roadmap for Visual Database Tools and Typed DataSets

This article provides a roadmap to learn and to master the Microsoft Visual Studio .NET Visual Database Tools and typed DataSet classes.

Roadmap articles provide links to useful information, including online documentation, Microsoft Knowledge Base articles, and Microsoft white papers, to help you learn about a Microsoft product or technology. For additional information about ADO.NET technology roadmap articles, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about ADO.NET DataAdapter objects, click the article number below to view the article in the Microsoft Knowledge Base:

Visual Database Tools

You can use the Visual Database Tools to do the following tasks:

• Connect to and explore any OLE DB-compliant database.
• Create and modify Microsoft SQL Server databases by using database diagrams.
• Design, execute, and save complex queries.
• Add, update, and delete data that is stored in database tables.
• Design objects, such as tables, triggers, and stored procedures, in Microsoft SQL Server and Oracle databases.
• Drag database objects onto a design surface, such as a Web Form, and then bind controls to those objects.

The Visual Database Tools are divided into two main categories:

• Server Explorer
• Remaining tools

You can use Server Explorer to create and to edit database schema and to update data interactively. You can use the remaining tools to generate code and to edit properties. These other tools affect your project directly, including generating typed DataSet class based on the server schema.

There is some overlap and interaction between these two categories. For example, Server Explorer uses some editors, and you can drag a table from Server Explorer to a design surface to invoke code generators.

The main components of Server Explorer are as follows:

• Database Designer
• Table Designer
• Query and View Designer
• SQL Editor

The other Visual Database Tools are as follows:

• Data Form Wizard
• XSD Editor
• XML Editor
• Connection, DataAdapter, DataSet, DataView Components
• Generate Dataset Dialog Box
• DataAdapter Configuration Wizard
• Parameter Collection Editor
• Table Mapping and Column Mapping Editors
• Data Link Properties Dialog Box

NOTE: The Visual Database Tools only support the SqlClient and the OleDb .NET Data Providers. Other providers such as the ODBC .NET Data Provider and the .NET Data Provider for Oracle, which released after Visual Studio .NET, are only supported in a very limited fashion. However, you can use the code that is generated for one data provider and modify it to work with another.

Server Explorer

With Server Explorer, you can view database schema, edit SQL Server database schema, retrieve data interactively, and modify data interactively in the Visual Studio .NET integrated development environment (IDE).

The connections are represented in a tree view. You can add connections to the Servers node or to the Data Connections node.

• If you add connections to the Servers node, one of the child nodes is SQL Servers. If you drag tables from under the SQL Servers node, the SqlConnection and the SqlDataAdapter components are added to your project.
• If you add an OLE DB connection through the Data Connections node, the Data Link Properties dialog box appears.
  
  NOTE: The OLE DB connection dialog box includes connections that ADO.NET does not support, such as the Microsoft OLE DB Provider for ODBC.
• If you drag a table from under the Data Connections node to your project, the OleDbConnection and the OleDbDataAdapter objects are created and configured. If you connect to Microsoft SQL Server version 7.0 or later, the SqlConnection and the SqlDataAdapter components are added instead.

For more information about how to configure these components, see the Other Visual Database Tools section.

Server Explorer includes several editors for manipulating SQL Server. In the tree view, you can right-click the Data Connections node to create a new SQL Server database. You can right-click the Tables, the Views, the Stored Procedures, and the Database Diagrams nodes to create or to modify a table, a view, a stored procedure, or a database diagram respectively. You can also generate SQL script that you can run against a different server to re-create the database. Most of these functions, such as creating and modifying tables and stored procedures, are also available for Oracle databases.

Database Designer

You can use the Database Designer to edit database diagrams and create relationships by dragging fields from one table to another. back to the list of tools

Table Designer

You can use the Table Designer to create or to modify tables in the database. You can specify or modify field names, data types, constraints, and indexes, among other items.

For more information about the Table Designer, visit the following Microsoft Web site: back to the list of tools

Query and View Designer

The Query and View Designer is shared by other data components, such as the Command and DataAdapter components. You can use the Query and View Designer to design queries visually and to view the results of the query. In Server Explorer, when you create or edit a view in the DataAdapter Configuration Wizard, or when you edit the CommandText property of a Command or a DataAdapter component, the Query and View Designer is invoked to generate the SQL statement for you. The SQL generating code only quotes identifiers for Microsoft SQL Server, Microsoft Jet, and Oracle. If you use a different back-end database, the identifiers will be unquoted. This may pose a problem if your database contains non-standard table or field names. For example, the names contain a space or other non-alphanumeric character. To correct this problem, you must manually make changes to the SQL in the generated code. However, if you regenerate the SQL, the changes will be lost.

For more information about the Query and View Designer, visit the following Microsoft Web site: back to the list of tools

SQL Editor

The SQL Editor is invoked when you create a stored procedure. The SQL Editor provides color-coding to your T-SQL and PL/SQL statement and bounds individual statements with a rectangle for clarity. Additionally, when you edit multistatement SQL script, you can right-click individual statements and use the Query and View Designer to visually compose or edit these statements.

For more information about the SQL Editor, visit the following Microsoft Web site: back to the list of tools

Other Visual Database Tools

The tools that are listed in this section write code into your project. You can modify this code or write equivalent code. Some tools, such as the Data Form Wizard, generate a Windows Form class that includes DataAdapter components, a typed DataSet, and bound controls. Other tools, such as the Parameter Collection Editor, perform more limited tasks, such as editing the Parameters collection of a single Command.

Data Form Wizard

The Data Form Wizard is similar to the Form Wizard in Microsoft Access. With the Data Form Wizard, you can specify one or two tables on the server and then choose from a number of styles. The wizard then configures how the elements appear on the Windows Form.

The Data Form Wizard does the following tasks:

• Creates a typed DataSet for the tables that you select.
• Creates Connection, Command, and DataAdapter objects to retrieve and to update data.
• Creates controls on the form and binds these controls to the typed DataSet.

For more information, visit the following Microsoft Web sites: back to the list of tools

XSD Editor

You can use the XSD Editor to create and to edit an XML Schema Definition Language (XSD) schema file in your project. You can edit the XSD file as straight Extensible Markup Language (XML) or in a graphical editor that is similar to the Table Designer and Database Designer of Server Explorer.

You can generate a typed DataSet from an XSD file by setting the Custom Tool property to MSDataSetGenerator. If you leave this property blank, the DataSet class file is no longer a part of the project and will be deleted from the disk.

back to the list of tools

XML Editor

You can use the XML Editor to edit an XML file in your project. You can edit the XML through a text interface that supports color coding and tag completion or through a hierarchical, tabular editor.

back to the list of tools

Connection, Command, DataAdapter, DataSet, and DataView Components

You can add these components from the Data tab of the toolbox, or you can use Server Explorer or the Data Form Wizard to add and to configure these components for you.

You can use the typed DataSet component for design-time data binding. Occasionally, data binding can be a confusing. For example, in a Windows Form, the designer only allows you to bind simple controls one way to a data source, but you can bind a DataGrid control or the list of a ListBox control or a ComboBox control two different ways. One of these ways is incompatible with the way that simple controls are bound. By using a DataView component, you can make sure that all of your controls are consistently bound.

You can open a number of builders from the properties of the various components. For example:

• Open the Data Link Properties dialog box from the Connection.ConnectString component.
• Open the Query Builder, which is similar to the Query and View Designer, from the Command.CommandText component.
• Open the Parameter Collection Editor from the Command.Parameters component.
• Open the Table Mapping Editor and the Column Mapping Editor from the DataAdapter.TableMappings component.

You can test a DataAdapter configuration by clicking Preview Data in the Property window. This displays the data in a grid.

back to the list of tools

Generate Dataset Dialog Box

When you select the graphical design surface or a DataAdapter component, a Generate DataSet hyperlink appears at the bottom of the Property window. In the Generate Dataset dialog box, you can select one or more DataAdapter components on the design surface to query for schema information. The wizard then builds the XSD schema file and typed DataSet class for you. The wizard also gives you the option to add an instance of the DataSet class to the design surface as a component.

For more information about the Generate Dataset dialog box, visit the following Microsoft Web site: back to the list of tools

DataAdapter Configuration Wizard

With the DataAdapter Configuration Wizard, you can configure a DataAdapter that is added through Server Explorer, through the Data Form Wizard, or manually from the toolbox. This wizard includes the following options:

• Generate SQL statements with or without optimistic concurrency checking.
• Use existing stored procedures.
• Generate new stored procedures.

You can use the Query and View Designer to edit the queries. The generated SQL statement only quotes identifiers for Microsoft SQL Server, Microsoft Jet, and Oracle. If you are using a different database back-end, the identifiers will be unquoted. This could pose a problem if your database contains non-standard table or field names. e.g. the names contain a space or other non-alphanumeric character. To correct this problem, you will have to manually make changes to the SQL in the generated code. However, if you regenerate the SQL, the changes will be lost.
If you select optimistic concurrency, the original value of all fields is checked against the values in the database. If you want to use a TimeStamp or similar version number field to simplify the SQL statement and reduce the data payload, you must build the queries yourself in code or through the Query and View Designer.

For more information about the Data Adapter Configuration Wizard, visit the following Microsoft Web site: back to the list of tools

Parameter Collection Editor

You can use the Parameter Collection Editor to map command parameters to columns in the associated DataTable. You can also map the parameter to a particular row version. For example, when you update a column, the new value is obtained from the current version of the row. However, parameters in the WHERE clause that are used for concurrency checking obtain their value from the original version of the row.

For more information about the Parameters Collection Editor, visit the following Microsoft Web site: back to the list of tools

Table Mapping and Column Mapping Editors

With the DataAdapter, you can map table and field names so that the names that are used in a DataSet do not have to match those that are used in the database. You can use the Table Mapping and the Column Mapping Editors to map table and field names that are used in the database to different table and field names in a DataSet.

For more information about the Table Mappings dialog box, visit the following Microsoft Web site: back to the list of tools

Data Link Properties Dialog Box

When you select the ConnectString property in a Connection component, you can click Data Link Properties to connect to a new data source. If you are using a SqlConnection component, the data source must be a database in SQL Server 7.0 or later or you will receive an error. This is the same dialog box that is used to add a new connection to Server Explorer.

back to the list of tools

Typed DataSet

One of the end products of the Visual Database Tools is the strongly typed DataSet class. To generate the DataSet class, you can use one of the following methods:

• Click Generate Dataset in the Property window of a DataAdapter component (or on the design surface).
• Click Add New Item on the File menu to add a DataSet item to the project.

Both methods place an XSD schema file and an associated hidden Microsoft Visual Basic .NET or Microsoft Visual C# .NET class in your project. If you generate the DataSet from a DataAdapter object, the schema file is completed for you, and the DataSet class that is built reflects the XSD schema. If you add the file by clicking Add New Item on the File menu, the XSD file is blank, and you must create the schema yourself.

Whenever you edit the schema file, the DataSet class file is automatically rewritten. If you want to customize the DataSet class, use one of the following methods:

• You can subclass the DataSet class and modify the subclass. When the DataSet class file is regenerated, your changes are not lost.
• You can remove the XSD file from the project and then manually add the corresponding class file back into your project. This removes the link between the designers and the class. The DataSet class file becomes static so that any changes that you make are not overwritten later. If there are any schema changes, the class file is not updated to reflect these changes. Therefore, there is a risk to this method.

The typed DataSet has a number of advantages over a standard DataSet object:

• The IntelliSense feature is available for table and field names in the code editor. This helps to prevent coding errors.
• Because the schema is compiled into the class definition, the compiler can catch schema errors. With the standard DataSet class, schema errors are caught only at run time.
• Because the schema is compiled into the class definition, you do not have to download the schema at run time. This offers a performance benefit.
• The typed DataSet accesses table and field names as properties instead of as collection elements. This also offers performance benefits.
• You can bind controls at design time instead of at run time.

Because the schema is compiled into the class definition, the typed DataSet has a number of limitations:

• If the server schema changes, you must rebuild your project. For most applications, this is not a major burden because most applications must be modified if the schema changes. In enterprise-level applications, server schema is typically already well-defined and reasonably static.
• When you try to access fields that may contain a NULL value, you receive an error message if you read the field directly. You must use the IsxxxNull property first. When you set the field value, you must use the SetxxxNull method to store a NULL value in the table.
  
  NOTE: Bound controls handle this automatically.

Walkthroughs, Visual Studio .NET Help Topics, and Microsoft Knowledge Base Articles

Walkthroughs provide mini-tutorials that walk you through typical application development scenarios. Microsoft Knowledge Base "How To" articles provide step-by-step instructions about how to do specific tasks.

The Visual Studio .NET Help topics, walkthroughs, and Microsoft Knowledge Base articles in the sections to follow describe how to use Visual Database Tools and the typed DataSet.

NOTE: The walkthroughs in the section to follow demonstrate how to use the Visual Database Tools to do a particular task; these walkthroughs are not a general tutorial about the tools.

Walkthroughs

In Visual Studio .NET, click Index on the Help menu. In the Look For box, type Walkthroughs, data. The Index Results pane displays a list of data access walkthroughs.

Visual Studio .NET Help Topics

Microsoft Knowledge Base Articles

Troubleshooting

If you experience problems or if you have questions, you can refer to the MSDN newsgroups where you can share your experiences with your peers. You can also use the Microsoft Knowledge Base where you can search for articles about specific issues. The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.