6
Consuming and
Manipulating
Data
CERTIFICATION OBJECTIVES
6.01 Consuming and Manipulating Data
6.02 Accessing and Manipulating Data from
a Microsoft SQL Server Database by
Creating and Using Ad Hoc Queries
and Stored Procedures
6.03 Creating and Manipulating DataSets
6.04 Accessing and Manipulating XML Data
✓
Two-Minute Drill
Q&A
Self Test
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:12 AM
Color profile: Generic CMYK printer profile
Composite Default screen
T
he data-driven application is the most common type of application that you will be
working with, and this importance is echoed in the XML Web Services exam. In this
chapter, you will round out your coverage of data technologies by looking at how
you can implement XML web services that both expose and consume data.
The move to use XML documents both as the source and the client storage of
data means that you need to look at how you can create an XML document from
an existing ADO.NET DataSet and directly from Microsoft SQL Server.
This chapter focuses mainly on the theory of ADO.NET and SQL. Exercises are

These are some of the advantages of ADO.NET:
■
Interoperability The format used to transfer data between the data source
and the in-memory copy of the data is the standard XML document, which
allows seamless data interoperability between dissimilar systems.
■
Maintainability ADO.NET maintains local in-memory caches of the data,
making it possible to spread the application logic between many tiers in an
n-tier application. This makes the application more scalable.
■
Programmability ADO.NET is based on the .NET Framework, which
uses strongly typed data types. Strongly typed data makes the source code
more concise and less prone to “undocumented features” (bugs).
■
Performance Because ADO.NET is strongly typed, it also helps you avoid
data conversions that can be costly to the performance of the application.
■
Scalability ADO.NET encourages programmers to minimize resource use
by maintaining a local in-memory copy (cache) of the data, enabling you to
disconnect from the data source. By doing so, ADO.NET avoids keeping
database locks and connections open between calls.
To use ADO.NET, you need to use its related namespaces, listed in Table 6-1.
Commit these namespaces to memory. They will be needed.
The Object Model
Just as there are multiple DataProvider classes, a number of classes are derived
(inherited) from the base DataSet class. The object model of ADO.NET contains
two major components: the DataSet class and the .NET data provider classes.
The DataSet class manages data storage in a disconnected in-memory cache.
The DataSet class is totally independent of the underlying data source. This way,
the application can use all the features of the DataSet regardless of where the data

222653-6 / Chapter 6
Namespace Description
System.Data Contains the core classes of ADO.NET, including
the classes that enable disconnected data (such as
the DataSet class).
System.Data.Common Contains utility classes and interfaces that the data
providers inherit and implement.
System.Data.SqlClient Contains the SQL Server .NET data provider.
System.Data.OleDb Contains the OLE DB .NET data provider.
System.Data.SqlTypes Contains classes and structures that encapsulate
the native SQL Server data types. This is a type-safe
faster alternative to native data types.
System.Xml Contains the support for the XML standard,
including classes for processing and encapsulating
an XML document (such as the
XmlDataDocument class).
TABLE 6-1
ADO.NET
Namespaces
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:13 AM
Color profile: Generic CMYK printer profile
Composite Default screen
.NET Data Providers The ADO.NET classes contain .NET data providers
that encapsulate a connection to a data source and the functionality to read, change,
and update data in the data source. The .NET data providers are designed to be
lightweight and include a minimal abstraction layer between the data source and
your code. Microsoft supplies three .NET data providers for your use, as listed in
Table 6-3.
There are four objects in each of the .NET data providers, as listed here (the

columns in different tables. Use a
DataRelation object to link
(join) two tables on the primary
and foreign keys.
TABLE 6-2
Collections in the DataTable Object
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:13 AM
Color profile: Generic CMYK printer profile
Composite Default screen
■
XxxDataReader (for example, SqlDataReader or
OleDbDataReader)
■
XxxDataAdapter (for example, SqlDataAdapter or
OleDbDataAdapter)
Table 6-4 provides a description of the objects.
The different providers and the products they service will be tested
in the exam.
6
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
Data Provider Description
SQL Server .NET This is an optimized provider for use with Microsoft SQL Server
7.0 or higher databases.
OLE DB .NET This is the provider for all OLE DB provider connections. You
can use this .NET data provider for connections to Oracle, DB/2,
Informix, and Access. This is actually the .NET data provider
that uses any traditional OLE DB provider.

DataSet objects. You use the XxxDataAdapter to populate the DataSet
and to update the data source with any changes that have been made to the
DataSet.
Some objects also have child objects associated with them. For example, the
XxxConnection object has an XxxTransaction object and an XxxError
object that expose underlying functionality. The XxxTransaction object represents
a transaction of the underlying database, while the XxxError object represents any
errors of warnings from the data source; this object is created by the .NET data provider
when an error or warning is raised by the data source.
XML and ADO.NET
Over the last couple of years, the XML standard has emerged as the most important
standard to date. It provides for the exchange of data, and most importantly, of
metadata, between components. ADO.NET is tightly incorporated with XML. Both
the object model and the services have XML at their core rather than as an add-on.
With ADO.NET, you can easily convert from relational data to XML and back again.
XML is text-based, making it instantly portable and universal. It is an open
extensible standard that can be used for many different purposes. The following list
identifies just some of the things you can do with XML support in ADO.NET:
■
Read data from an XML document.
■
Fill a DataSet with data from an XML document.
■
Create an XML schema for the data in a DataSet, and then use the
XML schema to write the data as XML.
■
Use the XML schema to programmatically treat the XML document
as a DataSet.
The most exciting fact about XML is that it is the standard format for exchanging
data between dissimilar environments. XML is the basis for B2B (business-to-business)

conditions and sorting instructions. The full syntax for the SELECT statement is
rather complex; look at a shorter syntax listing with the most commonly used
options:
SELECT [ALL | DISTINCT] select_list
FROM table_source
[ WHERE search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
8
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:13 AM
Color profile: Generic CMYK printer profile
Composite Default screen
The columns to be returned are listed in the select_list parameter. Use a
comma to separate the column names or use the column wildcard character (*)
to select all columns in the table. The ALL argument specifies that all rows in
the table_source should be returned, even if there are duplicate rows. The
DISTINCT argument removes all duplicates in the returned data. ALL is the default.
The FROM clause specifies the tables that the columns will be returned from. The
FROM clause is mandatory, and you must provide at least one table name.
The following example returns all the staff from the Northwind Trading database
(the query is executed against a SQL Server 2000 database):
/* Retrieve the First Name, Last Name, City and Country
for all the staff */
USE Northwind
SELECT FirstName, LastName, City, Country
FROM Employees
The preceding SELECT statement produced the following result:

The result of this
SELECT
statement is as follows:
FirstName LastName City Country
---------- -------------------- --------------- ---------------
Steven Buchanan London UK
Michael Suyama London UK
Robert King London UK
Anne Dodsworth London UK
The WHERE clause can compare columns against literal values using the logical
operators listed in Table 6-5. String literals in SQL are enclosed in single quotes (‘).
The WHERE clause has some additional features you can take advantage of. For
example, to search for records where you know only part of the data in a column,
you can use the LIKE argument, which lets us write string search patterns. The
10
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
Logical Operator Description Sample and Explanation
= Equality WHERE City = ‘London’
Returns all records where the City is London.
< Less than WHERE Day < 21
Returns all records where Day is less than 21.
> Greater than WHERE Day > 5
Returns all records where Day is greater than 5.
<= Less than or equal WHERE Day <= 21
Returns all records where Day is less than or equal to 21.
>= Greater than or equal WHERE Day >= 5
Returns all records where Day is greater than or
equal to 5.

FirstName LastName City Country
---------- -------------------- --------------- ---------------
Andrew Fuller Tacoma USA
Anne Dodsworth London UK
In your next example, you want to list all employees that have “ll” in their
last names:
/* Retrieve the First Name, Last Name, City and Country
for all the staff that have
First Names that start with 'An'*/
USE Northwind
SELECT FirstName, LastName, City, Country
FROM Employees
WHERE LastName LIKE '%ll%'
This query results in the following output:
FirstName LastName City Country
---------- -------------------- --------------- ---------------
Andrew Fuller Tacoma USA
Laura Callahan Seattle USA
Access and Manipulate Data from a Microsoft SQL Server
11
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:14 AM
Color profile: Generic CMYK printer profile
Composite Default screen
The other clause you haven’t looked at yet is the ORDER BY clause. If you look
back at the first result you received in this section, when you selected all the staff,
you will find that it is not sorted on any of the columns, and it seems to have been
returned in a random order. If you go back again and run the same query, you might

Always include a WHERE clause to limit the number of rows returned.
■
If you need the data sorted, use the ORDER BY clause.
12
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:14 AM
Color profile: Generic CMYK printer profile
Composite Default screen
JOIN
You will often need to combine data from two or more tables, and the JOIN clause
allows you to perform this task. JOIN statements are used to query any number of
tables and return a single result set that contains merged data from these tables. Joins
are a central part of relational database theory and are used in the real world to
implement relations between entities in a normalized data model.
There are three types of joins in SQL: inner joins, outer joins, and cross joins. These
joins are described in Table 6-6.
The syntax for an inner join is as follows:
SELECT select_list
FROM first_table_name
[INNER] JOIN join_table_name
ON join_condition
The ON keyword defines the comparison that must be true for the inner join to
return the row. The INNER keyword is optional, as it is the default join in the
ANSI92 SQL standard.
Let’s look at an example. Figure 6-1 shows the relationships between three tables.
The relationship is set up to enable us to join the three tables together. The
EmployeeID column is used to connect the Employees and EmployeeTerritories

and LastName for an employee with a last name of Buchanan, you could use the
following query:
USE Northwind
SELECT TerritoryDescription, FirstName, LastName
FROM Employees
JOIN EmployeeTerritories
ON Employees.EmployeeID = EmployeeTerritories.EmployeeID
JOIN Territories
ON EmployeeTerritories.TerritoryID = Territories.TerritoryID
WHERE LastName = 'Buchanan'
This query will return all records for employees named Buchanan where there is
an entry for a territory.
TerritoryDescription FirstName LastName
------------------------------ ---------- --------------------
Providence Steven Buchanan
Morristown Steven Buchanan
Edison Steven Buchanan
New York Steven Buchanan
New York Steven Buchanan
Mellvile Steven Buchanan
Fairport Steven Buchanan
Let’s look at what happened. The SELECT line specifies the columns that you
need. Notice that you used the name of the column from the Territories table
without specifying what table it came from, and as long as the column names are
unique, you do not have to specify the table name as well. In the FROM clause, you
added the JOIN clause to specify that you want the tables on either side of the JOIN
clause to be connected. The ON statement sets the rules of the connection; in this
14
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /

WHERE CategoryName = 'Beverages'
ORDER BY ProductName
The biggest difference between this example and the preceding one is that you
used aliases to identify the tables rather than long table names that can be hard to
Access and Manipulate Data from a Microsoft SQL Server
15
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
FIGURE 6-2
Table relationships for an aliasing example
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:15 AM
Color profile: Generic CMYK printer profile
Composite Default screen
16
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
read in complicated join statements. The following code segment defines c as the
alias for the Categories table and p as the alias for the Products table:
FROM Categories c
JOIN Products p
You can now use c and p to refer to the tables, simplifying the query.
The result of the preceding query is as follows:
CategoryName ProductName CompanyName
------------- --------------------------
--------------------------------
Beverages Chai Exotic Liquids
Beverages Chang Exotic Liquids
Beverages Chartreuse verte Aux joyeux ecclésiastiques

17
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
The column list of the
INSERT
statement is optional. If it is not used, the
order of the values in the
VALUE
clause must match the column order of
the table.
To insert data from a query into an existing table, you can use the INSERT ...
SELECT statement. The syntax is as follows:
INSERT table_name
SELECT select_list
FROM table_source
[WHERE condition]
The resulting set from the SELECT statement will be added to the table_name
table. There are some rules that you need to follow when using this technique:
■
The data types of the columns in the result set should match the data types
of the columns in the table.
■
The result set must have data for all required columns in the destination table.
The following example takes all your employees and adds them to the Customers
table so that your staff can also be your customers. You will build the CustomerID
column data by taking the first three characters from the first name and the first two
characters from the last name and concatenating them. The employee’s first name is
used as the contact name, and the last name as the company name:
USE Northwind
INSERT Customers

INTO #SalesTaxTable
FROM Products
The preceding example created a new local table named #SalesTaxTable. To query
the new table, you could execute this query:
USE Northwind
SELECT *
FROM #SalesTaxTable
The partial result set is seen here:
Product Price SalesTax NewPrice
----------------------------- ----------- -------------- ----------
Chai 18.0000 2.880000 20.880000
Chang 19.0000 3.040000 22.040000
Aniseed Syrup 10.0000 1.600000 11.600000
Chef Anton's Cajun Seasoning 22.0000 3.520000 25.520000
...
(77 row(s) affected)
UPDATE
You can use the UPDATE statement to make changes to one or more rows at a time.
The syntax for the UPDATE statement is as follows:
UPDATE table_name
SET column_name = expression, ...
[WHERE condition]
18
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:15 AM
Color profile: Generic CMYK printer profile
Composite Default screen

you could use this code:
USE Northwind
DELETE Orders
WHERE shippeddate < '11/1/2001'
Always include a
WHERE
clause in the
DELETE
statement; otherwise, all rows
in the table will be deleted.
Access and Manipulate Data from a Microsoft SQL Server
19
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:15 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Stored Procedures
The stored procedure is very much like a method in Visual Basic .NET except that it
is built from Transact-SQL statements (the language of Microsoft SQL Server) and
stored in the database for reuse. Stored procedures are like methods in a database;
they can receive parameters and then return values to the caller. There are a number
of reasons for using stored procedures:
■
Stored procedures are stored in the procedure cache of the database server,
resulting in faster execution if the stored procedure is called repeatedly.
■
Stored procedures are used to centralize the data-access logic on the server.
■

Color profile: Generic CMYK printer profile
Composite Default screen
In the preceding example, the parameter @C_NAME was added to the stored
procedure. Parameters must have a data type; I used an nvarchar with a default value
of ‘%’ (the wildcard for searches).
Stored procedures are usually more involved than what you saw in this section,
typically involving CURSORS, IN, and OUT parameters. The exam will not test you
on building complicated stored procedures, but you will need to remember that they
are server based and are built using the CREATE PROCEDURE statement.
The Transact-SQL language requires that the CREATE PROCEDURE
statement be written as CREATE PROC.
Transactions
When you work with data on central database servers, there are two main areas of
concern: allowing multiple users access to the same data in a safe way, and guaranteeing
that any data modification performed will maintain the integrity of the data. To
attempt to solve both these issues, use transactions.
A transaction is a group of related operations that either succeed or fail as
one unit—the transaction will either commit or roll back, respectively. In order
for a transaction to commit, all parts of the transaction must succeed. In other
words, transactions provide all-or-nothing processing.
A popular example of a transaction is the ATM (automatic teller machine), where
you might transfer $100.00 from your checking account to your savings account. You
can reasonably expect that the $100.00 was transferred, or if something went wrong
with the ATM or the banking system that the money would still be in the checking
account. Either the transfer takes place (commits), or if there is a problem (any problem)
with the transfer, the accounts are returned to the original state (the transaction
rolls back).
A transaction is tested against its ACID properties, named for the four key
concepts of a transaction (Atomicity, Consistency, Isolation, and Durability):
■

In SQL, you can control transactions using the transaction control statements
shown in Table 6-7. They can be used as part of any SQL process.
The following example uses the traditional bank example—you are going to move
$100.00 from one account to another. This example will not execute against the
Northwind database, because the BankAccount table is not part of the database, but
the code is include as an example of transactions:
BEGIN TRANSACTION
INSERT INTO BankAccount (AccountNUM, Amount, Type)
VALUES (424242, 100, 'debit')
INSERT INTO BankAccount (AccountNUM, Amount, Type)
VALUES (121212, 100, 'credit')
IF (@@ERROR > 0) ROLLBACK TRANSACTION
ELSE COMMIT TRANSACTION
Statement Description
BEGIN TRANSACTION Starts the transaction; all statements after the BEGIN
TRANSACTION statement are part of the transaction.
COMMIT TRANSACTION Ends the transaction, indicating success; all processing
will be persisted in the database.
ROLLBACK TRANSACTION Ends the transaction, indicating failure; all processing
will be rolled back to the state it was in when the
transaction started.
TABLE 6-7
SQL Transaction
Control
Statements
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:15 AM
Color profile: Generic CMYK printer profile
Composite Default screen
There are two data-modification statements in this example that insert debit and

23
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:16 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Database Objects
The basic objects you need to work with are the DataSet, DataTable, and DataColumn
objects. Using these three objects, you can implement any schema. Let’s start by
looking at where the DataSet fits into the scheme of things. Figure 6-3 shows the
relationship between the database and the DataSet object that is built from Data
Columns.
When you model (design the schema for) a DataSet, you can use constraints to
guarantee that the data that is inserted into or deleted from a DataTable meets the
business rules for that data. Two types of constraints are available: a UniqueConstraint
ensures that the data entered into a DataColumn of a DataTable is unique, and the
ForeignKeyConstraint verifies that data entered in the DataColumn already exists in
a referenced DataColumn.
24
Chapter 6: Consuming and Manipulating Data
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind /
222653-6 / Chapter 6
FIGURE 6-3
The relationship between the database and the DataSet object
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:16 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Constraints

in the next section. In this DataSet, you have removed the relationships between the
tables to make the model easier to read.
The four tables contain the core data describing a customer order. Each table has
columns with properties like name, data type, and length, and you will use these
values when you build the objects.
Create and Manipulate DataSets
25
CertPrs8 / MCAD/MCSD XML Web Services and Server Components Development with Visual Basic .NET / Lind / 222653-6 /
Chapter 6
P:\010Comp\CertPrs8\653-6\ch06.vp
Wednesday, October 30, 2002 9:51:16 AM
Color profile: Generic CMYK printer profile
Composite Default screen