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 MicrosoftSQLServer 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 ManipulatingSQLServer 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.