| Foreword | xxiii |
| Acknowledgements | xv |
| Introduction | xvii |
| PART I VISUAL BASIC .NET AND SQL SERVER 2000 FUNDAMENTALS | |
| 1 Getting Started with Visual Basic .NET for SQL Server 2000 | 3 |
| Visual Studio .NET, the Visual Basic .NET IDE | 4 |
| Starting Visual Studio .NET | 5 |
| Configuring Visual Studio .NET for Visual Basic .NET | 5 |
| Using the Start Page | 6 |
| Creating and Running a Console Application | 8 |
| An Overview of ADO.NET Capabilities | 9 |
| .NET Data Provider Types | 10 |
| SqlConnection Class | 11 |
| SqlCommand and SqlDataReader Classes | 12 |
| The SqlDataAdapter Class and the DataSet Class | 14 |
| A Starter ADO.NET Sample | 16 |
| Adding a SqlDataAdapter, SqlConnection, and DataSet | 17 |
| Filling a Data Set and Binding Controls to It | 20 |
| Navigating Through Rows | 21 |
| Using Query Analyzer | 23 |
| What's Query Analyzer For? | 23 |
| Making a Connection with Query Analyzer | 24 |
| Running, Saving, and Opening T-SQL Scripts | 25 |
| Selected Other Topics | 28 |
| PART II SQL SERVER 2000 DATA ACCESS, DATA MANIPULATION, AND DATA DEFINITION | |
| 2 Tables and Data Types | 33 |
| Chapter Resources | 34 |
| The Chapter's T-SQL Sample Scripts | 34 |
| The Chapter's Sample Database | 34 |
| Data Types for Tables | 36 |
| System Data Types | 37 |
| Special System Data Types | 45 |
| User-Defined Data Types | 47 |
| Scripting Tables | 48 |
| Creating a Table | 49 |
| Viewing Metadata | 51 |
| Working with Column Data Types | 55 |
| Adding Check Constraints | 62 |
| Scripting Keys and Indexes | 64 |
| Indexes | 71 |
| 3 Programming Data Access with T-SQL | 77 |
| Introduction to Data Access with T-SQL | 78 |
| Overview of the SELECT Statement | 78 |
| Specifying Columns and Rows | 81 |
| Calculated Columns | 85 |
| Aggregating and Grouping Rows | 87 |
| Summary of Aggregate Functions | 88 |
| Aggregating Without Grouping | 88 |
| Aggregating with Grouping | 89 |
| Processing Dates | 92 |
| Counting by Year and Month | 93 |
| Performing Date Arithmetic | 96 |
| Joins and Subqueries | 99 |
| An Inner Join Between Two Tables | 99 |
| Using Aliases Within an Inner Join | 101 |
| An Inner Join Between Three Tables | 102 |
| Outer Joins | 104 |
| Self Joins and Cross Joins | 105 |
| Subqueries | 107 |
| 4 Programming Views and Stored Procedures | 109 |
| Introduction to Views | 110 |
| Uses for Views | 110 |
| Statements for Creating and Altering Views | 112 |
| Restrictions on SELECT Statements for Views | 113 |
| View Attributes | 113 |
| Creating and Using Views | 114 |
| Creating and Selecting from a View | 114 |
| Contrasting Unencrypted and Encrypted Views | 116 |
| Sorting and Grouping Within a View | 119 |
| Views for Remote and Heterogeneous Sources | 120 |
| Creating a View for Another SQL Server Instance | 120 |
| Creating a View for an Access Database | 122 |
| Creating a View for an ODBC Row Source | 123 |
| Joining Row Sources for a View | 124 |
| Introduction to Stored Procedures | 126 |
| Uses for Stored Procedures | 127 |
| Reusing T-SQL Statements with Stored Procedures | 128 |
| Using Parameters, Local Variables, and Global Variables | 129 |
| Creating and Using Stored Procedures | 130 |
| Dynamically Selecting from a Row Source | 131 |
| Returning a Sorted Result Set | 132 |
| Returning the Script for a View | 133 |
| Processing Stored Procedure Outputs | 134 |
| Returning Two Result Sets from a Stored Procedure | 135 |
| Returning One Result Set and One Parameter Value | 136 |
| Returning One String Parameter | 138 |
| Working with Return Status Values | 139 |
| Inserting, Updating, and Deleting Rows | 141 |
| Altering a Stored Procedure for Data Manipulation | 141 |
| Performing Database Maintenance with Parameters | 146 |
| Programming Conditional Result Sets | 148 |
| Conditionally Listing Objects | 148 |
| Returning X Items with the TOP Predicate | 151 |
| Returning X Items with SET ROWCOUNT | 152 |
| 5 Programming User-Defined Functions and Triggers | 155 |
| Introduction to User-Defined Functions | 156 |
| Overview of UDF Types | 156 |
| Statements for Creating and Managing UDFs | 158 |
| Comparing UDFs with Views and Stored Procedures | 160 |
| Creating and Invoking Scalar UDFs | 161 |
| Creating a Scalar UDF Without Parameters | 162 |
| Creating a Scalar UDF with a Parameter | 164 |
| Using Scalar UDFs in T-SQL Scripts | 165 |
| Creating and Invoking Table-Valued UDFs | 169 |
| Providing Parametric Views | 169 |
| Using a Scalar UDF in the List for an Inline UDF | 170 |
| Encapsulating More Logic with Multistatement UDFs | 174 |
| Introduction to Triggers | 176 |
| Triggers Are like Event Procedures | 176 |
| Types of Triggers | 177 |
| inserted and deleted Tables | 178 |
| Statements for Creating and Dropping Triggers | 178 |
| Creating and Managing Triggers | 180 |
| Protecting and Unprotecting a Table from Changes | 180 |
| Archiving Changes to a Table | 183 |
| Enforcing a Business Rule on a Table | 186 |
| Enforcing a Business Rule on a View | 189 |
| 6 SQL Server 2000 XML Functionality | 193 |
| Overview of XML Support | 194 |
| Summary of XML Features by SQL Server Release | 194 |
| Web Release Installation | 197 |
| XML Formats and Schemas | 197 |
| XML Documents | 198 |
| XML Schemas | 201 |
| Annotated Schemas | 204 |
| URL Access to SQL Server | 211 |
| Virtual Directory Management | 211 |
| Overview of FOR XML in SELECT Statements | 213 |
| RAW vs. AUTO Mode Samples | 215 |
| AUTO vs. NESTED Mode Samples | 220 |
| Template Access to SQL Server | 223 |
| Templates with T-SQL Statements | 224 |
| Templates Enhanced with Database Objects | 228 |
| Updategrams Are like Templates | 231 |
| 7 SQL Server 2000 Security | 237 |
| Overview of SQL Server Security | 238 |
| Security Accounts | 238 |
| Authentication | 239 |
| Roles and Permissions | 240 |
| Introduction to Special Security Issues | 243 |
| Application Roles | 244 |
| Linked Servers | 244 |
| Security for Virtual Directories | 245 |
| Samples for Logins and Users | 247 |
| Add a SQL Server Login and User | 247 |
| Remove a SQL Server Login and User | 249 |
| Adding and Removing Logins for a Windows User | 251 |
| Processing Logins Based on Windows Groups | 253 |
| Samples for Assigning Permissions | 255 |
| Select, Insert, and Delete Permissions for a Table | 256 |
| Permission to Create a Table | 258 |
| Windows Users and Groups | 261 |
| PART III USING VISUAL BASIC .NET AND RELATED TECHNOLOGIES WITH SQL SERVER 2000 | |
| 8 Overview of the .NET Framework | 267 |
| An Introduction to the .NET Framework | 268 |
| .NET Framework Architecture | 268 |
| Compiling Source Code | 270 |
| Assemblies and Manifests | 272 |
| Selected .NET Framework Features | 274 |
| An Overview of ASP.NET | 276 |
| How Does ASP.NET Relate to ASP? | 276 |
| Creating an ASP.NET Web Application | 278 |
| Adding Controls to an .aspx Page | 279 |
| Adding Code Behind an .aspx Web Page | 280 |
| XML Web Services | 280 |
| What Can XML Web Services Do for Me? | 281 |
| Overview of the XML Web Services Infrastructure | 282 |
| A Closer Look at the Underlying Technologies | 282 |
| 9 Creating Windows Applications | 285 |
| Getting Started with Windows Forms | 285 |
| Start with Form1 in a Windows Application | 286 |
| Managing Windows Forms | 288 |
| A Windows Form with Two Button Controls | 290 |
| Opening One Windows Form with Another | 292 |
| Creating and Using Class References | 296 |
| Creating a Class to Perform Calculations | 297 |
| Referencing a Class from a Windows Application | 299 |
| .NET Namespace Architecture | 303 |
| Converting Between Value Types | 308 |
| From Long to Hexadecimal and Back Again | 310 |
| Inheriting Classes | 313 |
| Overview of Inheritance | 313 |
| An Inheriting and Overriding Sample | 316 |
| A Shadowing Sample | 321 |
| An Overloading Sample | 324 |
| Programming Events | 326 |
| Event Programming Concepts | 326 |
| Using Built-In Form Events | 328 |
| Processing Events Using the WithEvents Keyword | 331 |
| Processing Events with the AddHandler Statement | 336 |
| Processing Events from Server-Based Timers | 342 |
| Exception Handling for Run-Time Errors | 347 |
| Overview of Structured Exception Handling | 347 |
| Catching Errors | 348 |
| Catching and Fixing Run-Time Errors | 352 |
| Best Practices for Exception Handling | 355 |
| 10 Programming Windows Solutions with ADO.NET | 357 |
| An Overview of ADO.NET Objects | 358 |
| The Connection Class | 358 |
| The Command Class | 359 |
| The DataReader Class | 360 |
| The DataAdapter Class | 361 |
| The DataSet Class | 363 |
| The DataView Class | 366 |
| Making Connections | 366 |
| Logging In with Integrated Security | 367 |
| Logging In with SQL Server Security | 369 |
| Catching SqlConnection Exceptions | 371 |
| Connecting from a Windows Form | 373 |
| Working with Command and DataReader Objects | 378 |
| Displaying Results in a Message Box or the Output Window | 378 |
| Displaying Rows in Blocks from a DataReader | 382 |
| Invoking a Stored Procedure with a Parameter by a SQL String | 385 |
| Invoking a Stored Procedure with a Parameter by Its Name | 388 |
| Creating a Database Object with a Command Object | 390 |
| DataAdapters, Data Sets, Forms, and Form Controls | 393 |
| Adding a Data Set to a Form | 393 |
| Binding Controls on a Form to Data | 396 |
| Reporting DataBindings | 399 |
| Using a Data Set with Tables in a Parent-Child Relationship | 402 |
| Creating Data-Aware Forms | 408 |
| Modifying, Inserting, and Deleting Rows | 418 |
| The Data Manipulation Sample in Action | 418 |
| Code Associated with the Form Load Event | 423 |
| Code for the Navigation Buttons | 428 |
| Code for the Modify Button | 429 |
| Code for the Clear and Insert Buttons | 430 |
| Code for the Delete Button | 433 |
| 11 Programming ASP.NET Solutions | 435 |
| Review of ASP.NET Design Issues | 436 |
| Round-Trips | 436 |
| Pages and the Page Class | 439 |
| Controls on Pages | 440 |
| ASP.NET Design Interfaces | 443 |
| Creating and Running ASP.NET Solutions | 448 |
| Compiling and Viewing Web Pages | 448 |
| Remembering the State of a Page | 450 |
| A Multifunction Calculator Web Page | 453 |
| A Multifunction Calculator in a Table | 457 |
| Sniffing the Browser | 461 |
| Session State Management | 463 |
| Overview of Session State Management Issues | 463 |
| Using a Session Variable | 466 |
| Using a View State Variable | 471 |
| Using the QueryString Property to Identify a User | 474 |
| Using Query Strings Across Pages | 478 |
| Data on Web Pages | 482 |
| Managing Database Solutions with ASP.NET | 482 |
| Populating ListBox and DataGrid Controls | 483 |
| Navigating Text Boxes Through a Data Set | 489 |
| Creating Database Objects from Web Pages | 494 |
| Data Manipulation from ASP.NET | 498 |
| Validating the Data on a Web Page | 509 |
| Built-In Data Validation Tools | 509 |
| Using Validator Controls on Web Pages | 511 |
| Programming the Page IsValid Property | 514 |
| Dynamically Disabling a Validator Control | 517 |
| 12 Managing XML with Visual Basic .NET | 521 |
| SQL Server Web Releases | 522 |
| Overview of First and Second Web Releases | 523 |
| Overview of Web Release 3 | 523 |
| SQLXML Managed Classes | 525 |
| DiffGrams Let You Modify Data | 529 |
| Overview of XML Technologies | 531 |
| XML Data Formats | 531 |
| XPath Queries | 533 |
| XSLT Formatting | 534 |
| Generating XML Documents with the .NET Framework | 534 |
| Creating an XML Document with T-SQL | 535 |
| Creating an XML Document with an Annotated Schema | 538 |
| Designing Annotated Schemas | 540 |
| Dynamically Setting an XML Result Set | 545 |
| Running an XPath Query for a Specific XML Document | 545 |
| Running an XPath Query for Any XML Document | 548 |
| Running Parameterized SQL Server Queries | 550 |
| Parameterizing Any SQL Query | 552 |
| The Interplay Between XML and Data Sets | 553 |
| Creating Hierarchical XML Documents | 554 |
| Querying Descendants in a Data Set with XPath | 560 |
| Querying Descendants in an XML Document with XPath | 563 |
| Using Data Sets to Update Databases via DiffGrams | 566 |
| Using DiffGrams on the Web Without Virtual Directories | 570 |
| Creating HTML Pages with XSLT | 572 |
| Formatting Two Columns from the Employees Table | 573 |
| Formatting Three Columns from the Shippers Table | 577 |
| 13 Creating Solutions with XML Web Services | 581 |
| Overview of Web Services | 582 |
| Elements of Web Services Design | 582 |
| Typical XML Web Service Applications | 584 |
| Web Services from Visual Studio .NET | 585 |
| Web Services from the Web Services Toolkit | 588 |
| A Web Service to Return a Computed Result | 590 |
| Starting to Build a Web Service Application | 590 |
| Configuring and Coding Your Web Service Application | 593 |
| Testing a Web Service | 594 |
| Building a Client Application for a Web Service | 595 |
| Deploying and Discovering a Web Service | 599 |
| A Web Service to Return Values from Tables | 602 |
| Counting and Returning Rows from Any Table in Any Database | 602 |
| Testing the Web Service in the TableProcessor Folder | 605 |
| Building a Client Application | 606 |
| The SQL Server 2000 Web Services Toolkit | 612 |
| Scripting a SQL Server User for a Virtual Directory | 613 |
| Building a Web Service in an IIS Virtual Directory | 614 |
| Building a Client Application to Show an XML Fragment | 617 |
| Populating a ListBox Control with an XML Service | 620 |
| More on Populating Controls with Web Services | 623 |
| Adding Custom Database Objects as Web Methods | 623 |
| Populating a DataGrid and a ListBox with a Web Method | 626 |
| Dynamically Populating a Control with a Web Method | 630 |
| INDEX | 635 |
