SUMMARY
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:
313590 INFO: Roadmap for ADO.NET
For additional information about ADO.NET
DataAdapter objects, click the article number below to view the article in
the Microsoft Knowledge Base:
313483 INFO: Roadmap for ADO.NET DataAdapter Objects
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:
The other Visual Database Tools are as follows:
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.
back to the top
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
back to the top
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
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
Visual Studio .NET Help Topics
back to the top
Microsoft Knowledge Base Articles
315678 HOW TO: Create and Use a Typed DataSet by Using Visual Basic .NET
320714 HOW TO: Create and Use a Typed DataSet by Using Visual C# .NET
318039 HOW TO: Make a Typed DataSet Return a Default Value Instead of DBNull by Using Visual Basic .NET
318048 HOW TO: Make a Typed DataSet Return a Default Value Instead of DBNull by Using Visual C# .NET
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.
back to the top
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.