Design-Time Data Tools in Visual Studio .NET

Steve Stein
Visual Studio Team
Microsoft Corporation

January 2002

Summary: Visual Studio® .NET provides many tools for working with data. Some tools are available for working directly with data at its source, whereas other tools are for working with data that is integrated into your Visual Basic® or Visual C#™ application. This paper will assist you in discovering what tools are available, as well as where to find specific information about these tools in the Visual Studio documentation. (16 printed pages)

Contents

Introduction
Compendium of Design-Time Data Tools
Tools for Designing and Managing Database Structures
Tools for Adding ADO.NET Data Access to Your Application
   Adding Data Connections
   Adding or Editing Data Adapters
   Adding or Modifying Datasets
   Adding or Editing Command Objects
   Data Binding
Conclusion

Introduction

The information in this paper summarizes what each design-time data tool helps you accomplish and how to use it.

Note   Not all design-time tools are available with all editions of Visual Studio .NET. For more information, see Visual Studio .NET Editions.

Compendium of Design-Time Data Tools

The following tables provide a comprehensive list of the tools available for working with data at design time.

Database Design

The following tools allow you to create and edit database structures from within Visual Studio.

Data Adapters

Data adapters are ADO.NET objects containing references to SQL commands or stored procedures. They are used to fill data into datasets and to write changes from a dataset back to a data store.

Datasets

Datasets store data in a disconnected cache. The following tools allow you to create generate, edit, and use ADO.NET datasets in your applications.

Typed Datasets

Typed datasets are defined by the following:

• an XML Schema that describes the tables, columns, keys, and relationships in the dataset and
• a class file that inherits from System.Data.DataSet and implements members that provide access to a dataset with the specified schema

Therefore, some of the tools for typed datasets involve those used to edit the XML Schema, because the schema is used to define a dataset structure.

Untyped Datasets

Untyped datasets are instances of the System.Data.DataSet class. Their structure is not defined by means of a schema; instead, the dataset's structures are implemented programmatically at run time, or through the collection editors (accessed through the Properties window), which are described in the following table.

Data Binding

Data binding defines how data is displayed in controls on a form. For Windows Forms, it also defines how data is formatted and written back to the data source.

The following tools are available for data binding in Windows Forms.

XML Schemas and Files

The following tools allow you to create XML Schemas and to edit XML files directly. XML Schemas can be used to define the structure of typed datasets, and are also used to define the structure of any XML file.

Tools for Designing and Managing Database Structures

While working in Visual Studio, you can use various tools to view data in a database, as well as to add and edit database structures (such as tables, columns, database diagrams, stored procedures, functions, and triggers). In effect, there are Visual Studio tools that allow you to perform most of the tasks with your database that you can do using the database's own toolset. At design time, you access most data tools from Server Explorer.

Creating Connections

Before you can manage a database, you have to establish a connection to it. The design tool for creating connections is the Data Link Properties Dialog Box.

Adding and Modifying Database Elements

The design tools for working with data in Server Explorer are the Visual Database Tools. To access the visual database tools, right-click the appropriate area in Server Explorer. For example, to create a new table, expand the data connection until you see the Tables node. Right-click the Tables node, then click new table on the shortcut menu.

Note   The functionality of Visual Studio data tools depends on the individual data source you are connected to. For example, Visual Studio has more tools for working with SQL Server databases than for other databases.

The following design-time tools are available for working with database elements.

Database Diagrams

Tables

Views

Triggers

Stored Procedures

Functions

Tools for Adding ADO.NET Data Access to Your Application

An important part of your application design is adding data access to forms, Web pages, XML Web services, components, and other application elements. In most cases, this consists of adding objects to your application that will then be instantiated and executed when the application runs.

Adding Data Connections

To connect to a data source, your application uses ADO.NET data connection objects. For more information, see Creating ADO.NET Connection Objects. Connection objects contain a connection string that is used at run time, when the connection object is instantiated, to establish a connection to the database.

It is important to understand the distinction between a connection you use at design time to view and manage database elements, and a connection used at run time to read and write data. A design-time connection is a live connection between the Visual Studio integrated development environment (IDE) and your database. The connection objects you add to your forms, Web pages, and components are not live connections. They are simply objects whose properties include a connection string that will be activated when the application runs.

You can create connections explicitly by dragging them from the Data Tab of the Toolbox onto your form, dragging database elements from Server Explorer onto your form, or by setting specific connection related properties of other data objects.

Configuring Connections

To configure a connection, you use the Data Link Properties Dialog Box, which allows you to specify information that is saved as the connection object's connection string. Doing any of the following displays the Data Link Properties dialog box:

• Dragging connection objects or data adapters onto a form from the Data Toolbox.
• Dragging most database elements from Server Explorer onto a form. See "Creating Connections Using Server Explorer" below.
• Setting connection or connection-string properties of certain data objects. See "Creating Connections by Setting Properties" below.
• Selecting New Connection from within the Data Adapter Configuration Wizard.

Creating Connections Using Server Explorer

Your application needs only one connection object for each data connection. Connection objects will be created only if there are no other connections in the application that connect to the same database. For example, drag a table onto a form and a connection is created along with a data adapter. Now drag a second table onto the form (from the same database) and only a data adapter is created. If you examine the properties of the data adapter, you will see that the individual Connection properties of each command are set to the existing connection object.

The following table shows what objects to drag from Server Explorer when you want to create a connection object.

Creating Connections from the Data Tab of the Toolbox

Any element that you drag from the Data tab of the Toolbox onto a component requires an associated connection.

The following table details what objects you can drag, what happens, and how you can configure the connection needed by the object.

Creating Connections by Setting Properties

Connection objects can be edited or created by setting properties of certain objects. These objects may be members of other objects. For example, command objects are members of data adapter objects, so navigating to the command properties of data adapters will expose that command's connection property.

The following table shows the objects that have properties that, when set to New, will invoke the Data Link Properties dialog box.

Creating Connections Using the Data Adapter Configuration Wizard

The Data Adapter Configuration Wizard helps you set the properties of a new or existing data adapter.

Running the Data Adapter Configuration Wizard and selecting New Connection on the Choose Your Data Connection page will invoke the Data Link Properties dialog box and create a new connection object. For details about running the Data Adapter Configuration Wizard, see "Adding or Editing Data Adapters" later in this article.

Editing Existing Connection Objects

Select the connection object you want to edit. (When your form is in design view, the connection object is in the component tray.) In the Properties window select the ConnectionString property and select a connection from the drop-down list. If the desired connection is not available, select New Connection to display the Data Link Properties dialog box and create a new connection.

Tip   Other data objects may have properties that point to an existing data connection. If the existing data connection object is deleted, data objects that rely on it may no longer function until their appropriate properties are reset to point to the new connection object. If data connection requirements change after the connection object has already been created, you should edit the existing connection (do not delete it and create a new one).

Adding or Editing Data Adapters

A data adapter is an ADO.NET object that handles communication between a database and a dataset. The adapter incorporates four data command objects: Select, Update, Insert, and Delete command objects. Each object in turn includes an appropriate SQL statement or a reference to a stored procedure. For more information, see Introduction to Data Adapters.

Note   You can preview the data that will be returned by the Select statement of a data adapter by right-clicking an existing adapter, then clicking Preview Data on the shortcut menu. For more information, see Data Adapter Preview Dialog Box.

In Visual Studio .NET, you create data adapters with the Data Adapter Configuration Wizard. The wizard starts when you perform any of the following:

• Drag a data adapter object from the Data tab of the Toolbox onto a form.
• Right-click an existing data adapter object, then click Configure Data Adapter on the shortcut menu.
• Select an existing data adapter object, then click Configure Data Adapter in the Properties window.
• Select an existing data adapter object, then click Configure Data Adapter in the Data menu.

Creating New Data Adapters from the Data Tab of the Toolbox

The following table shows what objects to drag from the Data tab of the Toolbox when you want to create a data adapter.

Creating New Data Adapters from Server Explorer

The following table shows what objects to drag from Server Explorer when you want to create a data adapter.

Adding or Modifying Datasets

A dataset works as an offline cache of data for your application. Datasets contain one or more data tables that you can work with in much the same way you might work directly with a table in a database. For more information, see Introduction to Datasets.

There are two types of datasets:

• Typed datasets are datasets whose structure is defined in an XML Schema, which dictates the dataset's table names, column definitions, constraints, and relations. When you work with a typed dataset, you are actually working with a class that includes members for all the elements defined in the schema.
• Untyped datasets are not defined by a schema; instead, you add tables, columns, and other elements to it yourself, either by setting properties at design time or by adding them at run time.

To use a dataset, you add an instance of it to your component. You must also then provide a way for the dataset to be filled (usually a data adapter).

It is important to understand that the dataset itself has no knowledge of the data source that provides the data to fill it. If you are going to fill a dataset from a data adapter, you must be sure that the SQL commands or stored procedures represented in the data adapter are synchronized with the structure of the dataset. The easiest way to maintain this synchronization is to work with the data adapter, and use the data adapter to regenerate the dataset when modifications have been made.

Typed Datasets

Generating a Typed Dataset

To generate a typed dataset means to create a schema for it, and then to create a class file that derives from System.Data.DataSet and includes members for the dataset's elements. You can generate a typed dataset two ways:

• Use the XML Designer to create a schema. The designer can generate the appropriate class file automatically.
• Add a data adapter to your application component and then, using the adapter, have Visual Studio infer the schema and generate the typed dataset class.

The following tools are available for generating and creating typed datasets.

Adding Typed Datasets to Components

You can add typed datasets to applications in three ways:

• By dragging a Dataset object from the Data tab of the Toolbox.
• Generated based on existing data adapters.
• From scratch using the XML Designer.

The following tools are for adding typed datasets to your components.

Previewing the Contents of a Typed Dataset

You can preview the contents of a typed dataset using the Data Adapter Preview dialog box. From the Data menu select Preview Data. For more information, see Data Adapter Preview Dialog Box.

Viewing Dataset Properties

Right-click a typed dataset, then click Dataset Properties on the shortcut menu for a read-only view of the structure of a typed dataset. For more information, see Dataset Properties Dialog Box.

Untyped Datasets

Untyped datasets are added to applications from the Data tab of the Toolbox.

Creating New Datasets from the Data Tab of the Toolbox

Datasets created from the Data of the Toolbox can be typed or untyped, depending on the options selected in the Add Dataset dialog box. For more information, see Add Dataset Dialog Box.

Drag a DataSet object from the Data Toolbox to start the Add Dataset dialog box.

The following design-time tools are available for specifically working with untyped datasets.

Adding or Editing Command Objects

Command objects allow you to execute database statements or stored procedures directly from your application. For more information, see Working with Data Commands: High-Level Process.

Note   Data adapters incorporate data commands, but you typically do not work individually with those commands. The data commands in a data adapter do not appear in the component tray as separate objects. If you do need to manage a data adapter's command objects, you can do so by working with the individual command properties of the adapter.

Creating New Data Commands from the Data Tab of the Toolbox

The following table shows what objects to drag from the Data tab of the Toolbox when you want to create a data command.

To configure a data command, set the CommandText property, which starts the Query Builder.

Creating New Data Commands from Server Explorer

The following table shows what objects to drag from Server Explorer when you want to create a data adapter.

Working with Existing Data Commands

Editing existing command objects is done at design time with the Query Builder. Start the Query Builder by clicking the ellipsis next to the CommandText property of any command object.

Note   If the command you want to edit is not in the component tray, you can access it by expanding the specific command (SelectCommand, InsertCommand, and so forth) in the appropriate data adapter. For example, if you created a data adapter by dragging a table onto a form, the individual commands are all accessible as properties of that data adapter.

Data Binding

An in-depth discussion regarding data binding is beyond the scope of this paper. For more information, see Data Binding and Windows Forms or Web Forms Data Binding.

Note   The Advanced Data Binding Dialog Box allows you to bind any property that appears in the Properties window of a control to values from valid providers of data to Windows Forms. For more information, see Providers of Data to Windows Forms.

Data Form Wizard

The Data Form Wizard runs you through the entire process of creating a data-bound Windows Form or Web Form. For more information, see Data Form Wizard.

Note   The form created by the Data Form Wizard is like any other form added to a project, which means it does not automatically become the start-up form for the project.

Conclusion

Visual Studio .NET provides many design tools for working with data. This article has provided a single point of reference to aid in the discoverability of these tools, as well as providing links into the Visual Studio .NET documentation for more information.