Export (0) Print
Expand All
Debugging: Root Out Elusive Production Bugs with These Effective Techniques
Smart Tags: Simplify UI Development with Custom Designer Actions in Visual Studio
Ten Essential Tools: Visual Studio Add-Ins Every Developer Should Download Now
XML Comments: Document Your Code in No Time At All with Macros in Visual Studio
Expand Minimize

Design-Time Data Tools in Visual Studio .NET

Visual Studio .NET 2003
 

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.

Tool Description More information
Server Explorer View and manipulate data links, database connections, and system resources on any server to which you have network access. Connecting to Remote Resources with Server Explorer
Data Link Properties Dialog Box Configure connection strings. Adding New Data Connections in Server Explorer
Visual Database Tools Create and maintain databases; typically accessed from Server Explorer. Work You Can Do With the Visual Database Tools

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.

Tool Description More information
Data Adapter Configuration Wizard Set the properties of a new or existing data adapter. Creating Data Adapters Using a Wizard
Data Adapter Preview Dialog Box See how data will be filled into a dataset. ADO.NET Data Adapters
Table Mappings Dialog Box Specify how the adapter will translate columns in a data source to columns in a dataset table. Table Mapping in Data Adapters

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.

Tool Description More information
Generate Dataset Dialog Box Generate a dataset schema and a typed dataset class. The structure is inferred from specified data adapters. ADO.NET Datasets
Add Dataset Dialog Box Add an instance of a typed or untyped dataset class to an application. Introduction to Datasets
Dataset Properties Dialog Box Display the structure of a dataset in a read-only window. Visual Studio Tools for Creating Datasets
XML Designer Visually create and edit XML Schemas for datasets. XML Schemas and Data
Edit Key Dialog Box Create and edit primary key definitions for XML Schemas that define relational data structures Creating Primary and Unique Keys in XML Schemas
Edit Relation Dialog Box Create and edit XML elements that define relationships between data structures, such as foreign key relations. Introduction to DataRelation Objects

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.

Tool Description More information
Add Dataset Dialog Box Add an instance of a typed or untyped dataset class to an application. Introduction to Datasets
Tables Collection Editor Add and edit tables in untyped datasets. Adding Untyped Datasets to a Form or Component
Columns Collection Editor Add and edit columns in untyped datasets. Adding Untyped Datasets to a Form or Component
Constraints Collection Editor Add and edit constraints in untyped datasets. Adding Untyped Datasets to a Form or Component
Relations Collection Editor Add relations to untyped datasets. Introduction to DataRelation Objects
Relation Dialog Box Edit relations in untyped datasets. Introduction to DataRelation Objects

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.

Tool Description More information
Data Form Wizard Create Web Forms pages or Windows Forms with data-bound controls. Data Form Wizard Generated Code
Advanced Data Binding Dialog Box Bind any property that appears in the Properties window of a control to values from valid providers of data to Windows Forms. Data Binding and 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.

Tool Description More information
XML Designer Visually create and edit XML Schemas. XML Schemas and Data
Edit Key Dialog Box Create and edit primary key definitions for XML Schemas that define relational data structures. Creating Primary and Unique Keys in XML Schemas
Edit Relation Dialog Box Create and edit XML elements that define relationships between data structures, such as foreign key relations. Introduction to DataRelation Objects

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.

To . . . Action More information
Connect to a data source From the Tools menu or from Server Explorer, click Connect to Database. Adding New Data Connections in Server Explorer

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.

To . . . Action More information
Create a new SQL Server database Right-click the Data Connections node, then click Create New SQL Server Database on the shortcut menu. Creating a New SQL Server Database in Server Explorer
Modify a database connection Right-click the database node, then click Modify Connection on the shortcut menu. Data Link Properties Dialog Box

Database Diagrams

To . . . Action More information
Create a new database diagram Right-click the Database Diagrams node, then click New Diagram on the shortcut menu. Creating a New Database Diagram
Edit an existing database diagram Right-click the database diagram you want to edit, then select Design Database Diagram on the shortcut menu. Opening Database Diagrams
View an existing database diagram Double-click any existing database diagram to open it in the designer. Opening Database Diagrams

Tables

To . . . Action More information
Create a new table Right-click the Tables node, then click New Table on the shortcut menu. Adding Tables
Edit an existing table Right-click the table you want to edit, then click Design Table on the shortcut menu. Working with Tables
Display data in a table Right-click the table you want to view, then click Retrieve Data from Table on the shortcut menu (or double-click the table to return its data). Manipulating Data

Views

To . . . Action More information
Create a new view Right-click the Views node, then click New View on the shortcut menu. Creating Views
Edit an existing view Right-click the View you want to edit, then click Design View on the shortcut menu. Working With Views
Display data returned by a views Right-click the table you want to view, then click Retrieve Data from View on the shortcut menu (or double-click the view to return its data). Manipulating Data

Triggers

To . . . Action More information
Create a trigger Right-click the Table or View you want create the trigger for, then click New Trigger on the shortcut menu. Creating a Trigger
Edit a trigger Right-click the Trigger you want to edit, then click Edit Trigger on the shortcut menu. Opening a Trigger

Stored Procedures

To . . . Action More information
Create a new stored procedure Right-click the Stored Procedures node, then click New Stored Procedure on the shortcut menu. Creating Stored Procedures and User-Defined Functions
Edit an existing stored procedure Right-click the stored procedure you want to edit, then click Edit Stored Procedure on the shortcut menu (or double-click the stored procedure you want to edit). Opening Stored Procedures and User-Defined Functions
Run a stored procedure Right-click the stored procedure you want to run, then click Run Stored Procedure on the shortcut menu. Running Stored Procedures and User-Defined Functions
Debug step a stored procedure Right-click the stored procedure you want to step into, then click Step Into Stored Procedure on the shortcut menu. Opening Stored Procedures and User-Defined Functions

Functions

To . . . Action More information
Create a new function Right-click the Functions node, then click New Inline Function, New Table-valued Function, or New Scalar-valued Function on the shortcut menu, depending on the type of function you want to create. Creating Stored Procedures and User-Defined Functions
Edit an existing function Right-click the function you want to edit, then click Edit Inline Function, Edit Table-valued Function, or Edit Scalar-valued Function on the shortcut menu, depending on the type of function you are working with (or double-click the function you want to edit). Opening Stored Procedures and User-Defined Functions
Run an existing function Right-click the function you want to run, then click Run Inline Function, Run Table-valued Function, or Run Scalar-valued Function on the shortcut menu, depending on the type of function you are working with. Running Stored Procedures and User-Defined Functions
Debug a function Right-click the Function you want to step into, then click Step Into Inline Function, Step Into Table-valued Function, or Step Into Scalar-valued Function depending on the type of function you are working with. Opening Stored Procedures and User-Defined 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.

Drag the following onto your form Outcome
Database Connection object.
Entire Tables node Connection object and a data adapter for each table.
Individual table Connection object and a data adapter configured to read and update the selected database table.
One or more tables or columns Connection object and a data adapter for each table the columns are selected from.
Entire Views node Connection object and a data adapter for each view.
Individual view Connection object and a data adapter for the view selected.
Entire Stored Procedures node Connection object and a data command object containing a reference to the stored procedure.
Individual stored procedure Connection object and a data command object containing a reference for the stored procedure.

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.

Drag the following onto your form Outcome
SqlConnection, OleDbConnection, OdbcConnection or OracleConnection An unconfigured connection object is added to the component. To configure the connection, set its ConnectionString property. Selecting None will open the Data Link Properties dialog box.
SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter or OracleDataAdapter The Data Adapter Configuration Wizard starts, which allows you to select or create a connection. Select New Connection during the wizard to open the Data Link Properties dialog box and create a new connection 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.

Drag the following onto your form Outcome
SqlDataAdapter The Data Adapter Configuration Wizard starts. Upon completion a SqlDataAdapter object and a SqlConnection object appear in the component tray configured based on the choices made in the wizard.
OleDbDataAdapter The Data Adapter Configuration Wizard starts. Upon completion an OleDbDataAdapter object and an OleDbConnection object appear in the component tray configured based on the choices made in the wizard.
OdbcDataAdapter The DataAdapter Configuration Wizard starts. Upon completion an OdbcDataAdapter object and an OdbcConnection object appear in the component tray configured based on the choices made in the wizard.
OracleDataAdapter The DataAdapter Configuration Wizard starts. Upon completion an OracleDataAdapter object and an OracleConnection object appear in the component tray configured based on the choices made in the wizard.

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.

Drag the following onto your form Outcome
Entire Tables node A connection object and a data adapter for each table.
Individual table A connection object and a data adapter for the table selected.
Columns - any number or combination from one or more tables A connection object and a data adapter for each table the columns are selected from.
Entire Views node A connection object and a data adapter for each view.
Individual view A connection object and a data adapter for the view selected.

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.

Tool Description
Generate Dataset Dialog Box (for typed datasets) Infer a dataset schema from one or more data adapters and generates a typed dataset class.
XML Designer (for typed Datasets) Visually create and edit 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.

Tool Description
Generate Dataset Dialog Box (for typed datasets) Add a .xsd file to Solution Explorer (with a hidden .vb or .cs file defining the dataset class). An instance of the dataset will also be added to your application if the Add this dataset to the designer option is selected.
Add Dataset Dialog Box (for typed and untyped datasets) Add an instance of a typed dataset class that exists in your project.

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.

To . . . Action
Create an untyped dataset Drag a DataSet object from the Data Toolbox onto a form, then click Untyped dataset in the Add Dataset dialog box.
Create a typed dataset Drag a DataSet object from the Data Toolbox onto a form, then click Typed dataset in the Add Dataset dialog box. Choose the Name of an existing dataset in the project, or the name of a referenced dataset.

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

To . . . Tool
Work with tables in an untyped dataset Tables Collection Editor
Work with columns in an untyped dataset Columns Collection Editor
Work with constraints in an untyped dataset Constraints Collection Editor
Work with relations in an untyped dataset Relations Collection Editor and Relation Dialog Box

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.

Drag the following onto your form Outcome
SqlCommand An unconfigured SqlCommand object appears in the component tray.
OleDbCommand An unconfigured OleDbCommand object appears in the component tray.
OdbcCommand An unconfigured OdbcCommand object appears in the component tray.
OracleCommand An unconfigured OracleCommand object appears in the component tray.

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.

Drag the following onto your form Outcome
Entire Stored Procedures node A separate command object is created for each stored procedure.
Individual stored procedure A command object is created for the stored procedure.

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.

Show:
© 2014 Microsoft