This article provides a roadmap to learn and to master the Microsoft Visual Studio .NET Visual Database Tools and typed DataSet
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:
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:
313483back to the top Visual Database Tools
INFO: Roadmap for ADO.NET DataAdapter Objects
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
- 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
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
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
- 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:
back to the top Walkthroughs, Visual Studio .NET Help Topics, and Microsoft Knowledge Base Articles
- 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 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
: 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 Topicsback to the top Microsoft Knowledge Base Articles
Walkthrough: Creating a Master-Detail Windows Formhttp://msdn.microsoft.com/en-us/library/aa984462.aspx
This walkthrough uses a DataAdapter component. The DataAdapter Configuration Wizard builds a typed DataSet. The walkthrough uses the XSD Editor to create a DataRelation and then binds controls through the Property window to the typed DataSet.
Adding Tables and Columns to the Windows Forms DataGrid Controlhttp://msdn.microsoft.com/en-us/library/aa984314.aspx
This article demonstrates how to customize the appearance of a bound Windows Form DataGrid control by adding DataGridTableStyle and DataGridColumnStyle objects through an editor that is opened from the Property window.
HOW TO: Create and Use a Typed DataSet by Using Visual Basic .NET
HOW TO: Create and Use a Typed DataSet by Using Visual C# .NET
HOW TO: Make a Typed DataSet Return a Default Value Instead of DBNull by Using Visual Basic .NET
318048back to the top Troubleshooting
HOW TO: Make a Typed DataSet Return a Default Value Instead of DBNull by Using Visual C# .NET
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.