Programming Microsoft® ADO.NET 2.0 Applications: Advanced Topics

Programming Microsoft® ADO.NET 2.0 Applications: Advanced Topics

Released November 2005
Publisher(s): Microsoft Press
ISBN: 9780735621411

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Get in-depth coverage and expert insights on advanced ADO.NET programming topics such as optimization, DataView, and large objects (BLOBs and CLOBs). Targeting experienced, professional software developers who design and develop enterprise applications, this book assumes that the reader knows and understands the basic functionality and concepts of ADO.NET 2.0 and that he or she is ready to move to mastering data-manipulation skills in Microsoft Windows®. The book is structured so readers can jump in for reference on each topic as needed, complete with pragmatic and instructive code examples.

Table of contents

  1. Programming Microsoft® ADO.NET 2.0 Applications: Advanced Topics
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. Foreword
  5. Acknowledgments
  6. Introduction
    1. Who This Book Is For
    2. How This Book Is Organized
      1. Conventions
    3. System Requirements
      1. SQL Server 2005 vs. SQL Server 2005 Express Edition
      2. Configuring SQL Server 2005 Express Edition
    4. Prerelease Software
    5. Technology Updates
    6. Code Samples
    7. Support for This Book
      1. Questions and Comments
  7. 1. Overview of ADO.NET Disconnected Classes
    1. Getting Started with the DataTable Object
      1. Adding DataColumn Objects to Create a Schema
      2. Creating Primary Key Columns
      3. Creating DataRow Objects to Hold Data
        1. Adding Data to the DataTable
        2. Viewing the State of the DataRow Object Using DataRowState
        3. Managing Multiple Copies of Data Using the DataRowVersion
        4. Resetting the Slate Using the AcceptChanges and RejectChanges Methods
        5. Using SetAdded and SetModified to Change the RowState
        6. Deleting the DataRow, and What About Undeleting?
      4. Enumerating the DataTable
      5. Copying and Cloning the DataTable
        1. Importing DataRow Objects into a DataTable
      6. Using the DataTable with XML Data
      7. Using the DataView as a Window into a DataTable
        1. Ordering Data Using the Sort Property
        2. Narrowing the Search Using the RowFilter and RowStateFilter Properties
        3. Enumerating the DataView
        4. Exporting a DataView to a New DataTable
    2. Using a DataSet Object to Work with Lots of Data
      1. Being More Specific with Typed DataSet Objects
      2. Navigating the Family Tree with DataRelation Objects
        1. Creating Primary and Foreign Key Constraints
        2. Cascading Deletes and Cascading Updates
      3. Serializing and Deserializing DataSet Objects
        1. Serializing the DataSet Object as XML
        2. Serializing a Changed DataSet Object as a DiffGram
        3. Deserializing a DataSet from XML
        4. Serializing the DataSet Object as Binary Data
        5. Deserializing a DataSet from Binary Data
      4. Using Merge to Combine DataSet Data
    3. Looping Through Data with the DataTableReader
    4. Summary
  8. 2. Overview of ADO.NET Connected Classes
    1. Using Providers to Move Data
      1. Getting Started with the DbConnection Object
        1. Configuring an ODBC Connection String
          1. Sample ODBC Connection Strings
        2. OLEDB Connection String Configuration
          1. Sample OLEDB Connection Strings
        3. SQL Server Connection String Configuration
          1. Sample SQL Server Connection Strings
          2. Attaching to a Local SQL Database File with SQL Express
            1. How Is the DataDirectory Resolved?
        4. Storing the Connection String in the Application Configuration File
        5. GetSchema Method
      2. DbCommand Object
        1. DbParameter Objects
        2. ExecuteNonQuery Method
        3. ExecuteScalar Method
        4. ExecuteReader Method
      3. DbDataReader Object
      4. Using Multiple Active Result Sets (MARS) to Execute Multiple Commands on a Connection
      5. Performing Bulk Copy Operations with the SqlBulkCopy Object
      6. DbDataAdapter Object
        1. Using the Fill Method
        2. Saving Changes to the Database Using the Update Method
        3. Saving Changes to the Database in Batches
      7. DbProviderFactory Classes
      8. DbProviderFactories Class
      9. Enumerating Data Sources
      10. Using DbException to Catch Provider Exceptions
    2. Summary
  9. 3. ADO.NET Trace Logging
    1. Setting Up Tracing
      1. Using the logman.exe Utility
      2. Performance Logs And Alerts Snap-in
      3. Working with Event Trace Log Files
      4. Using the LogParser Utility
      5. Using Tracing as a Diagnostic Tool
    2. Summary
  10. 4. Advanced Connectivity to the Data Store
    1. Building Accurate Connection Strings
      1. Provider-Independent Data Access
    2. Connection Pooling
      1. Creating and Opening Connections
      2. Where’s the Pool?
      3. When Is the Pool Created?
      4. How Long Will the Connection Stay in the Pool?
        1. Load-Balancing Timeout (AKA Connection Lifetime)
      5. Exceeding the Pool Size
        1. Are You Exceeding the Pool Size Due to Connection Leaks?
        2. Goodbye, Close Method; Hello, Using Block
      6. When to Turn Off Pooling
      7. Clearing the Pool
      8. Working with a Failover Partner
    3. Asynchronous Data Access
      1. Synchronous vs. Asynchronous Access
    4. Working with SQL Server Provider Statistics
    5. Summary
  11. 5. Working with Disconnected Data
    1. Understanding Concurrency Issues
      1. Resolving Concurrency Conflicts
    2. Designing for Disconnected Data
      1. What Data Should Be Loaded?
        1. Data Selection
        2. Data Size
        3. Partitioning Data
      2. Choosing the Primary Key
        1. Intelligent, Natural Primary Keys vs. Surrogate Primary Keys
          1. Data Size
          2. Key Visibility
          3. Modifying Keys
          4. Quantity of Joins
          5. SQL Complexity
          6. Ensuring Uniqueness When Disconnected
          7. Migrating Data to Other Databases
        2. And the Winner Is...
    3. Who’s Afraid of the Big, Bad GUID?
      1. Copying/Pasting GUIDs
      2. Using the Same Name for the Primary Key Column on Non-Join Tables
      3. Finding a GUID in the Database
      4. Finding All Usages of a GUID in the Database
    4. Building a Conflict Resolution Screen
      1. Creating the Project
      2. Extending the Typed DataSet (CustomerDataSet) Class
      3. Extending the TableAdapter (TblCustomerTableAdapter) Class to Expose the ContinueUpdateOnError Property
      4. Synchronizing the Disconnected DataSet with the Database Server
      5. Creating the Conflict Resolution Screen
      6. Calling the Conflict Resolution Screen
      7. Correcting Concurrency Errors with the Conflict Resolution Screen
      8. Building a Better Conflict Resolution Screen
    5. Summary
  12. 6. Working with Relational Disconnected Data
    1. Navigating Relationships
    2. Creating Constraints
    3. Updating Data: The Beginning of the Data Access Layer
      1. Retrieving the Relationships
      2. Retrieving the List of Tables
      3. Ordering the Table List
      4. Using the OrderedTableList to Perform Updates
      5. Testing the Relational Update
      6. DAL Update Caveats
    4. Summary
  13. 7. Working with the Windows Data Grid Control
    1. Understanding the DataGridView Control
      1. Formatting with Styles
      2. DataGridView Modes of Operation
      3. Binding to a Data Source
      4. Resource Sharing
      5. DataGridView Setup
      6. Working with Cell Events
        1. Event Sequence
      7. Working with DataGridViewColumn Objects
        1. Editing the Column List
        2. Working with Column Events
        3. Using the DataGridViewTextBoxColumn
        4. Using the DataGridViewCheckBoxColumn
        5. Using the DataGridViewImageColumn
          1. Loading a New Image into the DataGridViewImageCell from a File
          2. Saving an Image from the DataGridViewImageCell to a File
        6. Using the DataGridViewButtonColumn
        7. Using the DataGridViewLinkColumn
        8. Using the DataGridViewComboBoxColumn
      8. Working with DataGridViewRow Objects
      9. Implementing Virtual Mode
    2. Summary
  14. 8. Working with the Web Data Grid Control
    1. Understanding the GridView Control
      1. Formatting with Styles
      2. Binding to a Data Source
    2. GridView Setup
      1. Viewing the Declarative Markup in the HTML Source
      2. Creating the GridView Object Programmatically
      3. Working with the GridView Object Events
        1. Event Sequence
      4. Working with Column Objects
        1. Editing the Column List
        2. Working with Column Templates
        3. Converting the ReportsTo Column to a TemplateField Column
          1. Displaying a Calendar Control for the BirthDate
        4. Retrieving and Displaying Images with the ImageField Column
          1. Building the Image Engine
          2. Adding the ImageField Column to Display the Photos
          3. Uploading New Employee Photos
    3. Summary
  15. 9. Working with the SQLCLR
    1. Does the SQLCLR Replace T-SQL?
    2. Creating a Stored Procedure Without Visual Studio
      1. Enabling the SQLCLR
      2. Creating the Source Code
      3. Using the Context Object
      4. Compiling the Code
      5. Loading the Assembly
      6. Changing the Execution Permission
      7. Registering the Stored Procedure
      8. Executing the Stored Procedure
      9. Refreshing the Assembly
      10. Viewing Installed Assemblies and Their Permissions
      11. Using Parameters to Transfer Data
    3. Creating a Stored Procedure by Using Visual Studio
    4. Passing Rowset Data
      1. Passing Data as a Produced Rowset
        1. Using the SqlConnection Object in the SQLCLR
        2. Context and Regular Connection Restrictions
        3. Connection Credentials for Regular Connections
      2. Passing Data from a Database Rowset
    5. Creating User-Defined Functions
      1. Using Scalar Functions
      2. Using a Streaming Table-Valued Function (TVF)
    6. Working with User-Defined Aggregates
    7. Working with Triggers
      1. Transactions in Triggers
    8. Working with User-Defined Types
      1. When Not to Use a UDT
      2. When to Use a UDT
    9. Accessing SQLCLR Features from the Client
    10. Summary
  16. 10. Understanding Transactions
    1. What Is a Transaction?
      1. Concurrency Models and Database Locking
      2. Transaction Isolation Levels
      3. Single Transactions and Distributed Transactions
    2. Creating a Transaction
      1. Creating a Transaction Using T-SQL
      2. Creating a Transaction Using the ADO.NET DbTransaction Object
    3. Setting the Transaction Isolation Level
    4. Introducing the System.Transactions Namespace
      1. Creating a Transaction Using the TransactionScope Class
      2. Setting the Transaction Options
      3. Working with Distributed Transactions
        1. Promotion Details
        2. Viewing Distributed Transactions
        3. Creating a Distributed Transaction
      4. Building Your Own Transactional Resource Manager
        1. Creating the Employee Class
        2. Implementing the IEnlistmentNotification Interface
        3. Implementing the ISinglePhaseNotification Interface
      5. Using System.Transactions with the SQLCLR
    5. Best Practices
    6. Summary
  17. 11. Retrieving Metadata
    1. Getting Started
    2. Retrieving the Metadata Collections
      1. Navigating the Schema
      2. Navigating a Metadata Collection
      3. Working with the Restrictions
      4. Changing and Extending the Metadata
      5. Understanding the Unique Identifier Parts
    3. Summary
  18. 12. Data Caching for Performance
    1. Using the SqlDependency Class
      1. What to Cache
      2. Is the SqlDependency Class for You?
      3. How Does SqlDependency Work?
      4. Query Considerations
      5. SqlDependency Setup in SQL Server
        1. Granting Permissions
        2. Enabling CLR Execution on the Database Server
        3. Enabling the Service Broker
      6. Using the SqlDependency Object
      7. Selecting the Communication Transport
    2. ASP.NET SQL Cache Invalidation
      1. Cache Invalidation by Polling
      2. Preparing SQL Server for Polling
      3. Creating a Web Site That Uses Polling
      4. Testing the Application Before Enabling Polling
      5. Enabling Polling in the Web Application
      6. Testing the Application with Polling Enabled
      7. Cache Invalidation by Command Notification
        1. Preparing SQL Server for Command Notification
        2. Creating a Web Site That Uses Command Notification
        3. Testing the Application Before Enabling Command Notification
        4. Enabling Command Notification in the Web Application
        5. Testing the Application with Command Notification Enabled
    3. Summary
  19. 13. Implementing Security
    1. Application Security Overview
      1. Authentication
      2. Authorization
      3. Impersonation
      4. Delegation
      5. Role-Based Security
        1. Workgroup Environment
        2. Domain Environment
      6. Code Access Security
        1. Evidence
        2. Code Access Permissions
        3. Working with CAS
        4. Code Groups
        5. Permission Sets
        6. Runtime Security Policy Levels
        7. Changing the Application Domain Security
          1. Changing ASP.NET Application Domain Security
          2. Changing the SQLCLR Application Domain Security
        8. Creating a Security Test Project
          1. Creating an XML Test File
          2. Assigning a Strong-Name Key File
          3. Reducing Permissions Granted to the Assembly
          4. Testing the Assembly
        9. Requested Permissions
          1. Implementing the Request for Permissions
            1. Testing the Permission Requests
        10. Placing Demands on the Callers
          1. Declarative Demands
          2. Implementing Declarative Permission Demands
            1. Using PermCalc.exe to Find Required Permissions
          3. Imperative Demands
          4. Implementing Imperative Permission Demands
        11. Being Assertive on Downstream Callers
        12. Using the AllowPartiallyTrustedCallersAttribute Attribute
    2. SQL Server Security
      1. SQL Server Authentication
        1. Using SQL Server Authentication
        2. Using SQL Server Authentication as a Form of Delegation
        3. Using Integrated Windows Security
      2. SQL Server Authorization
    3. ADO.NET Security
      1. Partial Trust Support
        1. Testing ADO.NET with a Partially Trusted Caller
      2. Storing Encrypted Connection Strings in Web Applications
        1. Implementing an Encrypted ConnectionString
      3. Preventing SQL Injection Attacks
        1. Creating the SqlInjectionTest Project
        2. Protecting Against SQL Injection
          1. Using Regular Expressions to Protect Against SQL Injection
      4. Using Stored Procedures
    4. Summary
  20. 14. Working with Large Objects (LOBs, BLOBs, and CLOBs)
    1. What Are LOBs, BLOBs, and CLOBs?
      1. Where Should LOBs Be Stored?
      2. Working with LOBs
      3. Reading BLOB Data
      4. Writing BLOB Data
    2. Summary
  21. 15. Working with XML Data
    1. Introducing XPath and XQuery
    2. Why Store XML Data in SQL Server 2005?
    3. The xml Data Type
      1. Using the Schema Collection to Implement "Typed" xml Columns
      2. Retrieving and Modifying XML Data
      3. Indexing the xml Column
    4. Getting Started with the xml Data Type
      1. Using the query Method with XPath
        1. Using a SQL Variable in the Filter Expression
        2. Performing General Comparisons
        3. Using Value Comparison Operators
        4. Using Node Comparison Operators
        5. Using Node Order Comparisons
        6. Using Logical Operators
      2. Using the query Method with XQuery
        1. Using Data Accessors
        2. Using Computed Element Constructors
        3. Using String Functions
        4. Using Aggregate Functions
        5. Using Context Functions
        6. Using XQuery FLWOR vs. XPath
        7. Using Namespaces in Your Queries
          1. Do You Really Want to Set the Default Namespace?
          2. Using the SQL WITH XMLNAMESPACES Clause
          3. Using Namespaces with Attributes
        8. Using Schemas to Work with Typed XML Data
          1. Why Add Schemas to a Schema Collection?
        9. Using the xml Data Type in SQL Tables
        10. Using Column Data in the XQuery Expression
      3. Using the exist Method with XQuery
      4. Using the modify Method to Change Data
        1. Replacing the Value of a Node
        2. Inserting a New Node
        3. Deleting a Node
        4. Moving Part of XML Data from One Row to Another
      5. Using the nodes Method to Change Data
        1. Using the FOR XML Clause
      6. Indexing the xml Column
    5. Using XML with ADO.NET
      1. Getting Started with the SqlXml Class
        1. Assigning and Retrieving the Schema
        2. Passing an SqlXml Object Parameter to Update the Database
    6. Summary
  22. A. About the Author
  23. B. Additional Resources for Visual Basic Developers
  24. C. Additional Resources for C# Developers
  25. D. Additional Resources for Web Developers
  26. E. Additional Resources for Database Developers
  27. Index
  28. About the Author
  29. Copyright

Product information

  • Title: Programming Microsoft® ADO.NET 2.0 Applications: Advanced Topics
  • Author(s):
  • Release date: November 2005
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621411