DataSets and XML in Visual Studio
By David M. Woods
Published October 2, 2006, 12:18 pm in Guide.
VS.Net contains many tools for reading from and writing to XML documents. One of the most powerful tools is the DataSet. We tend to think of DataSets as strictly a database tool, but in behind the scenes, a DataSet is a representation of an XML document, and as such, XML functionality was designed in from the start.
We will begin our discussion of DataSets by outlining the differences between a DataSet class and a DataSet object.
DataSet classes
A DataSet class includes an XML schema, but no data. Schemas are XML files that conform to the XSD specification, and have a .XSD filename extension.
There are several ways to create a DataSet classes in a .Net project. The simplest DataSet class of all is System.Data.Dataset, part of the .Net framework, which has a blank schema.
If you need a specified schema, especially one that includes hierarchical data, .Net gives you the power to create it. If you already have an XSD file, you can create a DataSet class from it. But XSD is a complex and confusing language, and so VS.Net provides a drag-and-drop visual tool which can be used to create a schema and its corresponding DataSet class. See "Dataset Designer" below for more information.
DataSet objects
A DataSet object actually contains (disconnected) data, in addition to a "behind-the-scenes" schema. There are two kinds: Typed, and Un-typed.
An Un-typed DataSet object is created from the class System.Data.DataSet. It has no pre-defined schema. (A schema will be created dynamically, however, from incoming data.) You can add one visually by dragging a DataSet tool from the toolbox on the "Data" tab, and on the subsequent dialog box, select "Un-typed." Its icon will still be a small white square with a couple of linked tables on it. Or, you can create one in code:
Dim MyDataSetObj as new System.Data.DataSet()A Typed DataSet is created from a DataSet class that you create. It has a pre-defined schema (although it can change based on incoming data). To create one visually, drag the DataSet tool from the toolbox, and on the subsequent dialog box, select "Typed", and select a DataSet class from the dropdown box (assuming that you have created at least one). If you create it in this way, the icon is represented by a gear, and VS.Net will assign a default object name as follows: the DataSet class name with a "1" appended.
If you create a Typed DataSet visually and right-click it, the context menu will include the "View Schema" feature. Select that, and you will be taken directly to the DataSet Designer, described in the topic below.
Another method to create a DataSet object visually is via a DataAdapter. See "DataAdapters" below for more details. As always, you can also create one in code:
dim MyDataSetObj as new MyDataSetClass()So now that we've created a DataSet, let's put some data in it. There are two primary ways: First, you can use one or more DataAdapters and execute its "Fill( )" method. Alternately, you can get data directly from an XML file by using the DataSet's "ReadXML( )" method.
Note that, regardless of whether the DataSet is typed or un-typed, all DataSet objects will pretty much accept any data you throw at them. If a table, column, or relationship does not exist, VS.net will adjust the schema accordingly.
DataSet Designer
To create your own DataSet class using the Designer, first add the DataSet component to your project by clicking "Add -> Add new item" and selecting the "Dataset" template. (Do not use the "XML Schema" template; even though it includes an XML schema, it is not a class.)
At the bottom of the designer window, you can select "DataSet" view or "XML" view. If your XSD schema already exists, simply cut-and-paste it into the XML view.
The "DataSet" view is the visual drag-and-drop designer. Let's do a walk-through on how to create a 2-table relational schema. You should have the Toolbox open, and the "XML schema" tab selected.
Start by selecting the "E" (Element) tool and drawing a class rectangle for the parent table. (Note that, in XSD, the word "element" can describe either a table OR a column.) The visual designer assigns the diamond icon to represent the table class. Give this table class a name in the input box just to the right of the diamond. In our example, we named it "Invoice".
Now add column names in the input boxes below the table name. For each one, in the left-side box, select "E" for element or "A" for attribute. Then in the right-side box, select a data type from the dropdown, such as "string" or "int" or "date". Make sure the parent contains a column which will relate to the as-yet-uncreated child table.
To create the next lower level in the hierarchy, first add another row to the parent, name it (We used "details" in our example), and set it's type to "UnNamed Complex Type" (it's near the top of the types list). The visual designer will create a child table element and initialize the relationship. Note that the child table element is named automatically.
At a minimum, the child table needs to include a column to relate back to the parent. Create other child columns as necessary.
The last step is to create a relation by dragging a line from the parent key to the child key. A dialog box will appear. The "Key field" is the parent key field and the "Foreign key field" is the child key field. Click OK. The visual designer will create a relation, represented by a diamond icon. Finally, click the diamond icon, and change the "IsNested" property value to True.
Save the schema. A file with a .VB extension will be created. Make sure the "View All Files" option is selected on the Project Explorer. If the .VB file did not generate, go to the "Schema" menu and verify that the "Generate Dataset" option has been checked. If not checked, check it, then save the schema again.
Data Adapters
The primary purpose of the DataAdapter is to fill a DataSet object with data from a connected database. It matters not if the DataSet is Typed or Un-typed.
The "Fill( )" method of the DataAdapter does all the work. The "Select" property contains a SQL command that the Fill( ) method will use. (It can be a SQL string or a stored procedure.) Note that if the DataSet is Typed, and the SQL command contains fields not in the schema, that VS.Net will add these fields dynamically. However, the reverse is not true: if a field exists in the schema, then the SQL command must include it, else a runtime error will be raised.
Multiple DataAdapters can be used to fill a DataSet.
Like the DataSet, the DataAdapter can be created either visually, or in code. To create it in code (assuming an Sql Server database):
dim MyDataAdapter as new SqlClient.SqlDataAdapter()You can create one visually by dragging its icon from the tool box. A visual DataAdapter object has some additional useful functions, available on the context menu when you right-click it. One of these features is "Configure", which invokes a wizard that you can use to attach SQL commands to select or update data.
Another powerful feature is "Generate DataSet", which will automatically create a Typed DataSet object and class. On the subsequent dialog box, select "New DataSet", give it a unique name, and select which DataAdapter to utilize (if multiple ones exist). VS.net will create a visual Typed DataSet object, which you can then click on to view its properties or schema.
The final useful feature on the context menu is "Preview", which can actually show the data on a design-time data grid.
Creating XML files
You can create an XML file directly from a DataSet object by calling its "WriteXml( )" method. If the DataSet was typed, then the XML file will be formatted as per the schema, nested hierarchy and all.
For an un-typed DataSet, however, the resulting XML file will not contain any kind of nested hierarchical structure. If using a typed DataSet is not practical, but you still need a structured XML file, there are other alternatives to accomplish this entirely in code using members of the DataSet class such as "Relation". (Beyond the scope of this document.) Note, however, that there is no way to directly create a DataSet class from such a DataSet object, although it could be done indirectly by writing the XSD schema to a file, then using that file to create a DataSet class.
Comments & Trackbacks
No Comments/Trackbacks for this post yet...
This post has 70 feedbacks awaiting moderation...
Leave a comment