- •Contents at a Glance
- •Table of Contents
- •Acknowledgments
- •Introduction
- •Who This Book Is For
- •Finding Your Best Starting Point in This Book
- •Conventions and Features in This Book
- •Conventions
- •Other Features
- •System Requirements
- •Code Samples
- •Installing the Code Samples
- •Using the Code Samples
- •Support for This Book
- •Questions and Comments
- •Beginning Programming with the Visual Studio 2008 Environment
- •Writing Your First Program
- •Using Namespaces
- •Creating a Graphical Application
- •Chapter 1 Quick Reference
- •Understanding Statements
- •Identifying Keywords
- •Using Variables
- •Naming Variables
- •Declaring Variables
- •Working with Primitive Data Types
- •Displaying Primitive Data Type Values
- •Using Arithmetic Operators
- •Operators and Types
- •Examining Arithmetic Operators
- •Controlling Precedence
- •Using Associativity to Evaluate Expressions
- •Associativity and the Assignment Operator
- •Incrementing and Decrementing Variables
- •Declaring Implicitly Typed Local Variables
- •Chapter 2 Quick Reference
- •Declaring Methods
- •Specifying the Method Declaration Syntax
- •Writing return Statements
- •Calling Methods
- •Specifying the Method Call Syntax
- •Applying Scope
- •Overloading Methods
- •Writing Methods
- •Chapter 3 Quick Reference
- •Declaring Boolean Variables
- •Using Boolean Operators
- •Understanding Equality and Relational Operators
- •Understanding Conditional Logical Operators
- •Summarizing Operator Precedence and Associativity
- •Using if Statements to Make Decisions
- •Understanding if Statement Syntax
- •Using Blocks to Group Statements
- •Cascading if Statements
- •Using switch Statements
- •Understanding switch Statement Syntax
- •Following the switch Statement Rules
- •Chapter 4 Quick Reference
- •Using Compound Assignment Operators
- •Writing while Statements
- •Writing for Statements
- •Understanding for Statement Scope
- •Writing do Statements
- •Chapter 5 Quick Reference
- •Coping with Errors
- •Trying Code and Catching Exceptions
- •Handling an Exception
- •Using Multiple catch Handlers
- •Catching Multiple Exceptions
- •Using Checked and Unchecked Integer Arithmetic
- •Writing Checked Statements
- •Writing Checked Expressions
- •Throwing Exceptions
- •Chapter 6 Quick Reference
- •The Purpose of Encapsulation
- •Controlling Accessibility
- •Working with Constructors
- •Overloading Constructors
- •Understanding static Methods and Data
- •Creating a Shared Field
- •Creating a static Field by Using the const Keyword
- •Chapter 7 Quick Reference
- •Copying Value Type Variables and Classes
- •Understanding Null Values and Nullable Types
- •Using Nullable Types
- •Understanding the Properties of Nullable Types
- •Using ref and out Parameters
- •Creating ref Parameters
- •Creating out Parameters
- •How Computer Memory Is Organized
- •Using the Stack and the Heap
- •The System.Object Class
- •Boxing
- •Unboxing
- •Casting Data Safely
- •The is Operator
- •The as Operator
- •Chapter 8 Quick Reference
- •Working with Enumerations
- •Declaring an Enumeration
- •Using an Enumeration
- •Choosing Enumeration Literal Values
- •Choosing an Enumeration’s Underlying Type
- •Working with Structures
- •Declaring a Structure
- •Understanding Structure and Class Differences
- •Declaring Structure Variables
- •Understanding Structure Initialization
- •Copying Structure Variables
- •Chapter 9 Quick Reference
- •What Is an Array?
- •Declaring Array Variables
- •Creating an Array Instance
- •Initializing Array Variables
- •Creating an Implicitly Typed Array
- •Accessing an Individual Array Element
- •Iterating Through an Array
- •Copying Arrays
- •What Are Collection Classes?
- •The ArrayList Collection Class
- •The Queue Collection Class
- •The Stack Collection Class
- •The Hashtable Collection Class
- •The SortedList Collection Class
- •Using Collection Initializers
- •Comparing Arrays and Collections
- •Using Collection Classes to Play Cards
- •Chapter 10 Quick Reference
- •Using Array Arguments
- •Declaring a params Array
- •Using params object[ ]
- •Using a params Array
- •Chapter 11 Quick Reference
- •What Is Inheritance?
- •Using Inheritance
- •Base Classes and Derived Classes
- •Calling Base Class Constructors
- •Assigning Classes
- •Declaring new Methods
- •Declaring Virtual Methods
- •Declaring override Methods
- •Understanding protected Access
- •Understanding Extension Methods
- •Chapter 12 Quick Reference
- •Understanding Interfaces
- •Interface Syntax
- •Interface Restrictions
- •Implementing an Interface
- •Referencing a Class Through Its Interface
- •Working with Multiple Interfaces
- •Abstract Classes
- •Abstract Methods
- •Sealed Classes
- •Sealed Methods
- •Implementing an Extensible Framework
- •Summarizing Keyword Combinations
- •Chapter 13 Quick Reference
- •The Life and Times of an Object
- •Writing Destructors
- •Why Use the Garbage Collector?
- •How Does the Garbage Collector Work?
- •Recommendations
- •Resource Management
- •Disposal Methods
- •Exception-Safe Disposal
- •The using Statement
- •Calling the Dispose Method from a Destructor
- •Making Code Exception-Safe
- •Chapter 14 Quick Reference
- •Implementing Encapsulation by Using Methods
- •What Are Properties?
- •Using Properties
- •Read-Only Properties
- •Write-Only Properties
- •Property Accessibility
- •Understanding the Property Restrictions
- •Declaring Interface Properties
- •Using Properties in a Windows Application
- •Generating Automatic Properties
- •Initializing Objects by Using Properties
- •Chapter 15 Quick Reference
- •What Is an Indexer?
- •An Example That Doesn’t Use Indexers
- •The Same Example Using Indexers
- •Understanding Indexer Accessors
- •Comparing Indexers and Arrays
- •Indexers in Interfaces
- •Using Indexers in a Windows Application
- •Chapter 16 Quick Reference
- •Declaring and Using Delegates
- •The Automated Factory Scenario
- •Implementing the Factory Without Using Delegates
- •Implementing the Factory by Using a Delegate
- •Using Delegates
- •Lambda Expressions and Delegates
- •Creating a Method Adapter
- •Using a Lambda Expression as an Adapter
- •The Form of Lambda Expressions
- •Declaring an Event
- •Subscribing to an Event
- •Unsubscribing from an Event
- •Raising an Event
- •Understanding WPF User Interface Events
- •Using Events
- •Chapter 17 Quick Reference
- •The Problem with objects
- •The Generics Solution
- •Generics vs. Generalized Classes
- •Generics and Constraints
- •Creating a Generic Class
- •The Theory of Binary Trees
- •Building a Binary Tree Class by Using Generics
- •Creating a Generic Method
- •Chapter 18 Quick Reference
- •Enumerating the Elements in a Collection
- •Manually Implementing an Enumerator
- •Implementing the IEnumerable Interface
- •Implementing an Enumerator by Using an Iterator
- •A Simple Iterator
- •Chapter 19 Quick Reference
- •What Is Language Integrated Query (LINQ)?
- •Using LINQ in a C# Application
- •Selecting Data
- •Filtering Data
- •Ordering, Grouping, and Aggregating Data
- •Joining Data
- •Using Query Operators
- •Querying Data in Tree<TItem> Objects
- •LINQ and Deferred Evaluation
- •Chapter 20 Quick Reference
- •Understanding Operators
- •Operator Constraints
- •Overloaded Operators
- •Creating Symmetric Operators
- •Understanding Compound Assignment
- •Declaring Increment and Decrement Operators
- •Implementing an Operator
- •Understanding Conversion Operators
- •Providing Built-In Conversions
- •Creating Symmetric Operators, Revisited
- •Adding an Implicit Conversion Operator
- •Chapter 21 Quick Reference
- •Creating a WPF Application
- •Creating a Windows Presentation Foundation Application
- •Adding Controls to the Form
- •Using WPF Controls
- •Changing Properties Dynamically
- •Handling Events in a WPF Form
- •Processing Events in Windows Forms
- •Chapter 22 Quick Reference
- •Menu Guidelines and Style
- •Menus and Menu Events
- •Creating a Menu
- •Handling Menu Events
- •Shortcut Menus
- •Creating Shortcut Menus
- •Windows Common Dialog Boxes
- •Using the SaveFileDialog Class
- •Chapter 23 Quick Reference
- •Validating Data
- •Strategies for Validating User Input
- •An Example—Customer Information Maintenance
- •Performing Validation by Using Data Binding
- •Changing the Point at Which Validation Occurs
- •Chapter 24 Quick Reference
- •Querying a Database by Using ADO.NET
- •The Northwind Database
- •Creating the Database
- •Using ADO.NET to Query Order Information
- •Querying a Database by Using DLINQ
- •Creating and Running a DLINQ Query
- •Deferred and Immediate Fetching
- •Joining Tables and Creating Relationships
- •Deferred and Immediate Fetching Revisited
- •Using DLINQ to Query Order Information
- •Chapter 25 Quick Reference
- •Using Data Binding with DLINQ
- •Using DLINQ to Modify Data
- •Updating Existing Data
- •Adding and Deleting Data
- •Chapter 26 Quick Reference
- •Understanding the Internet as an Infrastructure
- •Understanding Web Server Requests and Responses
- •Managing State
- •Understanding ASP.NET
- •Creating Web Applications with ASP.NET
- •Building an ASP.NET Application
- •Understanding Server Controls
- •Creating and Using a Theme
- •Chapter 27 Quick Reference
- •Comparing Server and Client Validations
- •Validating Data at the Web Server
- •Validating Data in the Web Browser
- •Implementing Client Validation
- •Chapter 28 Quick Reference
- •Managing Security
- •Understanding Forms-Based Security
- •Implementing Forms-Based Security
- •Querying and Displaying Data
- •Understanding the Web Forms GridView Control
- •Displaying Customer and Order History Information
- •Paging Data
- •Editing Data
- •Updating Rows Through a GridView Control
- •Navigating Between Forms
- •Chapter 29 Quick Reference
- •What Is a Web Service?
- •The Role of SOAP
- •What Is the Web Services Description Language?
- •Nonfunctional Requirements of Web Services
- •The Role of Windows Communication Foundation
- •Building a Web Service
- •Creating the ProductsService Web Service
- •Web Services, Clients, and Proxies
- •Talking SOAP: The Easy Way
- •Consuming the ProductsService Web Service
- •Chapter 30 Quick Reference
Chapter 25
Querying Information in a Database
After completing this chapter, you will be able to:
Fetch and display data from a Microsoft SQL Server database by using
Microsoft ADO.NET.
Define entity classes for holding data retrieved from a database.
Use DLINQ to query a database and populate instances of entity classes.
Create a custom DataContext class for accessing a database in a typesafe manner.
In Part IV of this book, “Working with Windows Applications,” you learned how to use Microsoft Visual C# to build user interfaces and present and validate information. In Part V, you will learn about managing data by using the data access functionality available in Microsoft Visual Studio 2008 and the Microsoft .NET Framework. The chapters in this part of the book describe ADO.NET, a library of objects specifically designed to make it easy to
write applications that use databases. In this chapter, you will also learn how to query data by using DLINQ—extensions to LINQ based on ADO.NET that are designed for retrieving data from a database. In Chapter 26, “Displaying and Editing Data by Using Data Binding,” you will learn more about using ADO.NET and DLINQ for updating data.
Important To perform the exercises in this chapter, you must have installed Microsoft SQL Server 2005 Express Edition, Service Pack 2. This software is available on the retail DVD with Microsoft Visual Studio 2008 and Visual C# 2008 Express Edition and is installed by default.
Important It is recommended that you use an account that has Administrator privileges to perform the exercises in this chapter and the remainder of this book.
Querying a Database by Using ADO.NET
The ADO.NET class library contains a comprehensive framework for building applications that need to retrieve and update data held in a relational database. The model defined by ADO.NET is based on the notion of data providers. Each database management system (such as SQL Server, Oracle, IBM DB2, and so on) has its own data provider that implements an abstraction of the mechanisms for connecting to a database, issuing queries, and updating data. By using these abstractions, you can write portable code that is independent of the
499
500 |
Part V Managing Data |
|
underlying database management system. In this chapter, you will connect to a database |
|
managed by SQL Server 2005 Express Edition, but the techniques that you will learn are |
|
equally applicable when using a different database management system. |
The Northwind Database
Northwind Traders is a fictitious company that sells edible goods with exotic names. The Northwind database contains several tables with information about the goods that
Northwind Traders sells, the customers they sell to, orders placed by customers, suppliers from whom Northwind Traders obtains goods to resell, shippers that they use to send goods to customers, and employees who work for Northwind Traders. Figure 25-1 shows all the
tables in the Northwind database and how they are related to one another. The tables that you will be using in this chapter are Orders and Products.
Creating the Database
Before proceeding further, you need to create the Northwind database.
Chapter 25 Querying Information in a Database |
501 |
Granting Permissions for Creating a SQL Server 2005 Database
You must have administrative rights for SQL Server 2005 Express before you can cre-
ate a database. By default, if you are using the Windows Vista operating system, the computer Administrator account and members of the Administrators group do not have
these rights. You can easily grant these permissions by using the SQL Server 2005 User Provisioning Tool for Vista, as follows:
1.Log on to your computer as an account that has administrator access.
2.Run the sqlprov.exe program, located in the folder C:\Program Files\Microsoft SQL Server\90\Shared.
3.In the User Account Control dialog box, click Continue. A console window briefly appears, and then the SQL Server User Provisioning on Vista window is displayed.
4.In the User to provision text box, type the name of the account you are using to perform the exercises. (Replace YourComputer\YourAccount with the name of your computer and your account.)
5.In the Available privileges box, click Member of SQL Server SysAdmin role on SQLEXPRESS, and then click the >> button.
6.Click OK.
The permission will be granted to the specified user, and the SQL Server 2005 User Provisioning Tool for Vista will close automatically.
502 |
Part V Managing Data |
Create the Northwind database
1.On the Windows Start menu, click All Programs, click Accessories, and then click Command Prompt to open a command prompt window. If you are using Windows Vista, in the command prompt window type the following command to go to the
\Microsoft Press\Visual CSharp Step by Step\Chapter 25 folder under your Documents folder. Replace Name with your user name.
cd “\Users\Name\Documents\Microsoft Press\Visual CSharp Step by Step\Chapter 25”
If you are using Windows XP, type the following command to go to the \Microsoft Press\Visual CSharp Step by Step\Chapter 25 folder under your My Documents folder, replacing Name with your user name.
cd “\Documents and Settings\Name\My Documents\Microsoft Press\Visual CSharp Step by Step\Chapter 25”
2. In the command prompt window, type the following command:
sqlcmd –S YourComputer\SQLExpress –E –iinstnwnd.sql
Replace YourComputer with the name of your computer.
This command uses the sqlcmd utility to connect to your local instance of SQL Server 2005 Express and run the instnwnd.sql script. This script contains the SQL commands that create the Northwind Traders database and the tables in the database and fills them with some sample data.
Tip Ensure that SQL Server 2005 Express is running before you attempt to create the
Northwind database. (It is set to start automatically by default. You will simply receive an error message if it is not started when you execute the sqlcmd command.) You can check
the status of SQL Server 2005 Express, and start it running if necessary, by using the SQL Configuration Manager available in the Configuration Tools folder of the Microsoft SQL Server 2005 program group.
3. When the script finishes running, close the command prompt window.
Note You can run the command you executed in step 2 at any time if you need to reset the Northwind Traders database. The instnwnd.sql script automatically drops the database if it exists and then rebuilds it. See Chapter 26 for additional information.