Manipulating a DataSet Schema
Create and Manipulate DataSets: Manipulate a DataSet Schema.
A DataSet schema is an XML file that describes the structure of a DataSet object. You can work with such files as raw XML, but if you have Visual Studio .NET, there's a better way. Visual Studio .NET includes a specialized XML designer that provides a visual representation of DataSet schema files. In this section, you'll learn how to use this designer. You'll see that you can create a DataSet schema and edit it easily with the same sort of drag-and-drop operations that are used to create Windows forms or Web forms.
The XML Designer is also integrated with the database support in Server Explorer. You can add items to a DataSet schema file by dragging and dropping from Server Explorer.
To use the XML designer in Visual Studio .NET with a DataSet schema file, you need to create a new Visual Studio .NET project to host the file. After you've done that, you can perform many design tasks. In this section, you learn how to perform the following tasks on a DataSet schema:
Create a new schema
Add items such as elements, attributes, types, and facets to the schema
Use items from Server Explorer in a schema
Create element groups and attribute groups
Creating a DataSet Schema
Visual Studio .NET allows you to create a DataSet schema from scratch, without reference to an existing DataSet or to a table stored on a database server. Step-by-Step 1.1 demonstrates how you can do this.
1.1 Creating a New DataSet Schema
Create a new Visual C# .NET Windows application. Name the application 320C01.
Right-click on the project node in Solution Explorer and select Add, Add New Item.
Select the Local Project Items node in the Categories tree view. Select the DataSet template. Name the new DataSet dsCustomers.xsd, as shown in Figure, and click OK.
Figure. The Add New Item dialog box enables you to create a new DataSet schema file.
The new DataSet schema file is opened in the XML Designer, as shown in Figure. The Toolbox displays tools appropriate for creating new content in the schema file.
Figure. You can place objects from Server Explorer or the Toolbox to the create a DataSet schema file in the XML Designer.
An empty DataSet schema is not very interesting or useful. In the next section of the chapter, you learn how to add information to the schema to help dictate the layout of the DataSet object that it defines.
Elements and Attributes
Like other XML files, DataSet schema files are made up of XML elements. You can think of an element as a unit of information. For example, if you're designing a DataSet to hold data from a database table named Customers with columns named CustomerID and CompanyName, then Customers, CustomerID, and CompanyName would all be represented by XML elements. Step-by-Step 1.2 shows you how to add elements to a DataSet schema file.
1.2 Adding Elements to a DataSet Schema
Ensure that the DataSet schema file that you created in Step-by-Step 1.1 is open in the XML Designer.
Select the Element tool in the Toolbox. Drag the element from the Toolbox and drop it on the design surface. This creates an element named element1. Rename the element Customers by typing over the name, as shown in Figure.
Figure. The top-level element—Customers—represents a table in a DataSet schema file.
Drag another element and drop it on top of the first element. Name the new element CustomerID. This creates a nested element, as shown in Figure.
4. You can create a nested element by dropping a child element on top of the parent element.
The XML Designer assumes that the new element will be a complex element—that is, one that contains other elements. To change the CustomerID element to a simple element, select a data type from the drop-down list to the right of the element. Figure shows the result of defining the CustomerID element to be a string.
5. You can create a simple element by selecting an elementary data type.
Create a CompanyName element by dragging another element from the Toolbox, renaming it, and changing its data type to string.
Switch to the XML view of the schema file (using the tab at the bottom of the XML Designer) and inspect the generated XML.
You can also use the XML designer to add attributes to a DataSet schema. Attributes provide an alternative way to represent columns within a DataSet object. Step-by-Step 1.3 shows how to add attributes to a DataSet schema.
1.3 Adding Attributes to a DataSet Schema
Ensure that the DataSet schema file that you edited in Step-by-Step 1.2 is open in the XML Designer.
Select the Attribute tool in the Toolbox. Drag the attribute from the Toolbox and drop it on top of the complex element named Customers. This creates an attribute named attribute1. Rename the attribute ContactName by typing over the name. You'll see that the attribute is assigned the string data type by default. Attributes cannot be used for complex data types.
Drag a second attribute to the Customers element. Rename the attribute ContactTitle. Figure shows the state of the DataSet schema after this change.
Figure. You can represent columns by either elements or attributes within a DataSet schema file.
Given that you can represent columns by either elements or attributes within a DataSet schema file, how do you decide which representation to use? Here are some points to consider:
If you need to exchange the schema file with other applications, you must be sure that it is compatible with those applications. Microsoft Access, for example, can import a schema defined with elements but not one defined with attributes.
If your DataSet requires that a custom data type be used to define columns, you must use elements for those columns. Attributes are restricted to the built-in data types defined by the World Wide Web Consortium (W3C).
To represent a child table in a hierarchy, you must use an element.
Visual Studio .NET Default
When you use Visual Studio .NET's built-in tools to create a DataSet schema from a database, Visual Studio .NET always uses elements to represent the database columns.
Using Simple Types
When you define a column with an element or an attribute, there are no restrictions on that column beyond those imposed by the data type that you choose for the element or attribute. For example, you may want to require that the CustomerID column be a string consisting of somewhere between five and ten characters. To do this, you can define a simple type in the XML Designer, using the technique from Step-by-Step 1.4.
1.4 Adding a Simple Type to a DataSet Schema
Ensure that the DataSet schema file that you edited in Step-by-Step 1.3 is open in the Designer.
Select the SimpleType tool in the Toolbox. Drag the simple type from the Toolbox and drop it in a blank area of the XML designer. This creates a simple type named simpleType1. Rename the simple type CustomerIDType by typing over the name. You'll see that the simple type is assigned the string data type by default.
Click in the second row of the simple type, directly under the ST icon. This produces a drop-down arrow. Click the arrow and select Facet from the list (it is the only item in the list). In the next column select minLength. In the last column type the value 5.
Add another facet to the simple type. Select the maxLength facet and set the maximum length to 10. Figure shows the simple type at this point.
7. You can create a custom simple type in the XML Schema designer as per the requirements.
Click the data type drop-down for the CustomerID element. You'll find that this list now contains the CustomerIDType data type. Set the element to use this data type, as shown in Figure.
8. You can associate a custom-created simple type to an XML element in the XML Schema designer.
In this Step-by-Step, minLength and maxLength are data type facets. Facets help define the acceptable range of data for an element. Figure shows the data type facets that are available in the DataSet schema designer. This table gives you a sense of what restrictions you can place on DataSet elements.
Facets in the XML Designer
At any time, the XML designer shows you only the facets that are applicable to the current data type. For example, if you're basing a simple data type on the string data type, you can set only the enumeration, length, minLength, maxLength, pattern, and whiteSpace facets.
Data Type Facets
Set of allowable values.
Maximum number of digits in the fractional part of a number.
Fixed length of the data.
Maximum allowed value must be less than this number.
Maximum allowed value must be less than or equal to this number.
Maximum length of a variable length field.
Minimum allowed value must be greater than this number.
Minimum allowed value must be greater than or equal to this number.
Minimum length of a variable length field.
Regular expression that specifies a pattern to which the data must conform.
Maximum number of total decimal digits in a number.
Can be set to preserve (to leave whitespace unchanged), replace (to replace tabs, line feeds, and carriage returns with spaces) or collapse (to replace all contiguous whitespace characters with a single space character).
Using Server Explorer with the XML Designer
You can also create DataSet schemas quickly by using the Server Explorer, which enables you to interact directly with SQL Server or other databases. You may not have used Server Explorer in the past, so before I show you this particular use I'll give you a quick overview.
By default, the Server Explorer window in Visual Studio .NET is displayed as a small vertical tab to the left of the Toolbox. When you hover the mouse over this tab, the Server Explorer slides out to cover the Toolbox. Figure shows the two states of the Server Explorer window.
9. The Server Explorer is normally displayed as a small vertical tab; when you hover the mouse over this tab, the window slides out to cover the Toolbox.
Although we're going to use the Server Explorer to work with databases, it's really a general-purpose tool for managing server resources of many types. Figure lists the resources that you can manage with Server Explorer.
Resources That You Can Manage with Server Explorer
A connection to a particular database
Options for Crystal Reports
Windows event logs
Windows message queues
Windows performance counters
Microsoft SQL Servers
To work with DataSet schema files, you'll use the Data Connection node in Server Explorer and its children. To start working with those objects, you need to add a Data Connection to the Server Explorer tree. Step-by-Step 1.5 shows you how to do this.
1.5 Adding a Data Connection from Server Explorer
Open Server Explorer.
Right-click the Data Connections node and then select Add Connection. This opens the Data Link Properties dialog box.
Fill in the connection information for your data source. The dialog box defaults to using the Microsoft OLE DB Provider for SQL Server, but you can change that on the Provider tab if you like.
Click OK to create the Data Connection. For ease in completing the other Step-by-Steps in this chapter, you should create at least one Data Connection based on an instance of the SQL Server Northwind sample database.
Supported Connection Types
You've probably noticed that the Data Link Properties dialog box gives you a great many choices on the Provider tab. In addition to SQL Server provider, the .NET Framework also supports connections that use the Oracle or Jet providers. Other providers may work, but there's no guarantee, so you should test your application carefully if you decide to use another provider.
Visual Studio .NET remembers your Data Connections across sessions and projects. Any Data Connection that you've created appears in Server Explorer in all your projects, unless you right-click the Data Connection and choose Delete.
After you've created a Data Connection, you can use objects from that Data Connection in DataSet schema design. Step-by-Step 1.6 demonstrates this process.
1.6 Using a Server Explorer Table in the DataSet Schema Designer
With the DataSet schema from Step-by-Step 1.4 open in the Designer, open Server Explorer.
Expand the Server Explorer tree view to show a Data Connection to the Northwind sample database. Drill into the Tables folder within this database.
Drag the Orders table from Server Explorer and drop it on the DataSet schema designer. This creates a new element with all the necessary included elements to represent the Orders table.
Repeat the process to bring the Order Details table into the DataSet schema designer. Figure shows the DataSet schema designer with three tables and one simple type on the design surface.
10. You can create an element by dragging and dropping a table from the Server Explorer to the DataSet schema designer.
If you're an experienced database developer, you may have noticed that there's no sign of any relationship between the three tables in the DataSet schema. I'll show you how to create such a relationship in the next section of the chapter.
You can also create schema elements by dragging a view, a stored procedure, a function, or a set of columns from Server Explorer to the DataSet schema designer.
DataSet schema files represent the metadata that describes a DataSet object's allowable content.
You can create a DataSet schema file from scratch by dragging and dropping elements and attributes within the DataSet schema designer.
Simple types enable you to apply constraints to the data that will be allowed in a DataSet object.
You can quickly create a DataSet schema to represent an existing table by dragging and dropping the table from Server Explorer to the DataSet schema designer.