Chapter 1. Creating And Manipulating Datasets
OBJECTIVES
This chapter covers the following Microsoft-specified objective for the "Consuming and Manipulating Data" section of the "Developing XML Web Services and Server Components with Microsoft Visual C# .NET and the Microsoft .NET Framework" exam:
Create and Manipulate DataSets.
· Manipulate a DataSet Schema.
· Manipulate DataSet Relationships.
· Create a strongly typed DataSet.
· Databases are at the core of many applications. Visual C# .NET allows you to work with databases in many different ways. Many Web services and other applications manipulate DataSet objects rather than work directly with a database. The DataSet object provides you with an in-memory relational store that abstracts away many of the differences between database implementations. To pass the Web services and server components exam, you need a strong background in working with DataSet objects. In particular, you should know how to manipulate the schema of a DataSet. The schema of a DataSet describes the types of data that the DataSet contains; it is the metadata for the DataSet. You can use Visual Studio .NET to manage DataSet schemas. This includes designing schemas from scratch, editing existing schemas, and editing the relationships between multiple tables contained in the same schema. When your application contains a DataSet schema, you can use that schema to construct a strongly typed DataSet. A strongly typed DataSet allows you to use an early-binding syntax to refer to data contained in the DataSet. This enables you to write code that is clearer and less error prone.
Access and manipulate data from a Microsoft SQL Server database by creating and using ad hoc queries and stored procedures.
· You also need to know how to manipulate the data at the database level. This objective tests your ability to work with data in a Microsoft SQL Server database. You'll learn how to use both SQL statements and stored procedures to work with this data.
OUTLINE
Introduction
Manipulating a DataSet Schema
Creating a DataSet Schema
Elements and Attributes
Using Simple Types
Using Server Explorer with the XML Designer
Manipulating DataSet Relationships
Adding Keys to a DataSet Schema
One-to-Many Relationships
Nested Relationships
Creating and Using Strongly Typed DataSet Objects
Using the Component Designer to Create a Strongly Typed DataSet Object
Creating a Strongly Typed DataSet from a DataSet Schema
Using a Strongly Typed DataSet Object
Accessing and Manipulating SQL Server Data
Using Ad Hoc Queries
Running Queries
The SELECT Statement
The INSERT Statement
The UPDATE Statement
The DELETE Statement
Using Stored Procedures
Creating a Stored Procedure
Running Stored Procedures from the .NET Framework
Using Parameters in Stored Procedures
Using the @@IDENTITY Variable
Chapter Summary
Apply Your Knowledge
STUDY STRATEGIES
· Create a DataSet schema by dragging and dropping a table from Server Explorer to a Visual C# .NET application. Then use the schema designer to edit the schema.
· Add an additional table to your test DataSet schema, and use the XML designer to create a relationship between the two tables.
· Write code to work with data in a generic DataSet object. Then create a strongly typed DataSet object to hold the same data and update the code to use early binding.
· You should understand how to construct and interpret simple Transact SQL statements including SELECT, INSERT, UPDATE, and DELETE, as well as SQL Server stored procedures. Spend some time practicing with the Visual Data Tools inside of .NET or with another query front-end such as SQL Server Query Analyzer. Make sure you work with the raw T-SQL, not just the graphical tools.
Introduction
Just about any application you can think of will need to store data. Database integration is important to many of these applications, and Web services and server applications are no exception to the rule. Fortunately, the .NET Framework and Visual C# .NET offer a rich and productive set of tools for working with data stored in data bases.
The Web services and server components exam presumes that you already have a working knowledge of ADO.NET. (If you need a refresher course, refer to Appendix A for the basics of ADO.NET.) The core object of ADO.NET is the DataSet, which provides a complete in-memory relational database. A DataSet object can contain multiple tables with keys and relationships, mimicking the structure of a relational database while abstracting away the implementation differences between various databases. You can use a DataSet object to hold data from Microsoft Jet, Microsoft SQL Server, Oracle, or many other databases, without changing any of your code.
In this chapter, you'll learn how to work with DataSet schemas. A DataSet schema is an XML representation of the metadata that defines a DataSet object's structure. Visual Studio .NET includes tools that enable you to work with this metadata directly, defining and altering the structure of DataSet objects.
When you've created a DataSet schema, you can use that schema to instantiate a strongly typed DataSet object. A strongly typed DataSet object behaves like any other DataSet object, but you can use an early-bound syntax to manipulate its contents. This makes it easier to write code, and makes the code less prone to errors.
After showing you how to work with DataSet schemas and strongly typed DataSet objects, I'll review the basics of the Structured Query Language (SQL) and show you how you can use SQL statements and stored procedures to interact with a Microsoft SQL Server database from within your .NET applications.
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.
STEP BY STEP
1.1 Creating a New DataSet Schema1. / Create a new Visual C# .NET Windows application. Name the application 320C01.
2. / Right-click on the project node in Solution Explorer and select Add, Add New Item.
3. / 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 1.1, and click OK.
Figure 1.1. The Add New Item dialog box enables you to create a new DataSet schema file.
4. / The new DataSet schema file is opened in the XML Designer, as shown in Figure 1.2. The Toolbox displays tools appropriate for creating new content in the schema file.
Figure 1.2. 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.
STEP BY STEP
1.2 Adding Elements to a DataSet Schema1. / Ensure that the DataSet schema file that you created in Step-by-Step 1.1 is open in the XML Designer.
2. / 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 1.3.
Figure 1.3. The top-level element—Customers—represents a table in a DataSet schema file.
3. / 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 1.4.
Figure 1.4. You can create a nested element by dropping a child element on top of the parent element.
4. / 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 1.5 shows the result of defining the CustomerID element to be a string.
Figure 1.5. You can create a simple element by selecting an elementary data type.
5. / Create a CompanyName element by dragging another element from the Toolbox, renaming it, and changing its data type to string.
6. / 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.
STEP BY STEP
1.3 Adding Attributes to a DataSet Schema1. / Ensure that the DataSet schema file that you edited in Step-by-Step 1.2 is open in the XML Designer.
2. / 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.
3. / Drag a second attribute to the Customers element. Rename the attribute ContactTitle. Figure 1.6 shows the state of the DataSet schema after this change.
Figure 1.6. 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.
EXAM TIP
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.
STEP BY STEP
1.4 Adding a Simple Type to a DataSet Schema1. / Ensure that the DataSet schema file that you edited in Step-by-Step 1.3 is open in the Designer.
2. / 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.
3. / 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.
4. / Add another facet to the simple type. Select the maxLength facet and set the maximum length to 10. Figure 1.7 shows the simple type at this point.
Figure 1.7. You can create a custom simple type in the XML Schema designer as per the requirements.
5. / 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 1.8.
Figure 1.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. Table 1.1 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.