Professional SQL Server™ 2005 CLR Programming

Acknowledgments xv
Introduction xvii
Chapter 1: Introducing SQL CLR 1
What is SQL CLR? 1
The Evolution of SQL CLR 2
Pre-SQL Server 2005 Extensibility Options 2
Why Does SQL CLR Exist? 2
Supported SQL CLR Objects 3
The .NET Architecture 4
CTS 4
CLS 5
CIL 5
VES 5
JIT 5
Managed Code and Managed Languages 6
Hosting the CLR 7
SQL CLR Architecture 7
Application Domains 8
The CLR Security Model 8
SQL CLR CAS Permission Sets 9
RBS/RBI 9
Key SQL CLR Decisions 9
Using SQL CLR or T-SQL 9
Using SQL CLR or Extended Stored Procedures 10
Using SQL CLR or OLE Automation Procedures 10
Using the Data Tier or Application Tier for Business Logic 11
SQL CLR Barriers of Entry 11
Security Considerations 11
The DBA Perspective on SQL CLR 12
Implementation Considerations 12
Performance Considerations 13
Maintenance Considerations 13
SQL Server 2005 SQL CLR support 14

Visual Studio 2005 SQL CLR support 14
Required Namespaces for SQL CLR Objects 15
Summary 15
Chapter 2: Your First CLR Stored Procedure 17
Setting Up SQL Server Express and the AdventureWorks Database 18
The Mechanics of a Creating and Deploying a CLR Routine 18
Enabling CLR in SQL Server 2005 20
Creating Your First SQL CLR Routine without VS2005 22
Coding the Class 22
Compiling the .NET DLL 24
Creating the Assembly 25
Choosing between User-Defined Functions and Stored Procedures 26
Creating the T-SQL Stored Procedure 27
How SQL Server Manages Assemblies 28
Assembly Metadata 29
Extracting Assembly DLL and Source Code 31
Creating Your First CLR User-Defined Function 32
Using Visual Studio to Create a CLR Routine 33
Creating the Class 34
Deploying the Class 36
What the Auto-Deployment Process Does 37
Suggested Conventions 38
Assemblies 38
Classes 38
Structures 38
Class and Structure Functions or Methods 38
Removing SQL CLR Objects 39
Reviewing Assembly Dependencies 39
Dropping an Assembly 40
Summary 40
Chapter 3: SQL CLR Structure and Common Tasks 43
SQL CLR Code Structure 43
SQL CLR Common Tasks 46
Using SQL CLR Data Type Classes 46
Detecting SQL CLR Availability 49
Performing Data Access 50

Returning Resultsets and Messages 53
Programming Transactions 58
Summary 67
Chapter 4: Creating SQL CLR Objects 69
Managed Stored Procedures 69
Managed Code 69
The SqlProcedure Attribute 70
Parameters 72
Return Values 74
Creating, Altering, and Dropping Managed Stored Procedures 75
Managed Functions 77
Scalar-Valued Functions 77
Creating, Altering, and Dropping, Managed Scalar-valued Functions 80
Table-Valued Functions 81
Managed Triggers 83
SqlTriggerContext Class 86
Accessing the Virtual Inserted and Deleted Temporary Tables 87
Enabling and Disabling Triggers 88
Managed DML Triggers 89
Managed DDL Triggers 92
Managed Aggregates 93
Managed Code 93
SqlUserDefinedAggregate Attribute 96
Creating, Altering, and Dropping UDAs 96
Managed Types 97
Managed Code 97
SqlUserDefinedType Attribute 101
Creating, Altering, and Dropping UDTs 101
Summary 101
Chapter 5: Comparing T-SQL with Managed Code 103
Syntactic Comparisons 104
Variable Declaration and Scope 104
Automatic Variables and System Functions 106
Flow of Control 107
Syntax Checking 111
Custom Attributes and Optimizers 111

Organizational Comparison 112
Structured Programming 112
Object-Oriented Programming? 116
Error Handling 118
Capability Comparison 119
T-SQL Data-Centric Built-Ins 119
Handling NULL Values 122
String Parsing 123
Array Handling 125
Computations and Calculations 126
Cursor Processing 131
New .NET Base Class Library (BCL) Capabilities 137
Commingling SQL CLR and T-SQL 138
Submitting Data to Database 141
Retrieve Data from Database 145
Set-Based Operations 148
Forward-Only, Read-Only Row Navigation Example 149
Performance and Other Comparisons 161
Methodology of Comparison 162
A Final Word on Performance 167
Maintainability 168
Portability 169
Summary 169
Chapter 6: Replacing Extended Stored Procedures,
Functions, and Other T-SQL Objects 171
Historical Perspective on Extended Stored Procedures 172
Why Replace XPs with SQL CLR? 172
Faster and Easier to Code 173
Plays Nice with SQL Server 173
Not Restricted to the Master Database 173
SQL CLR Replacements for XPs 174
Preparing SQL Server for External-Access Assemblies 174
Determining If a File Exists 175
Reading from the Registry 179
Replacing xp_CmdShell 182
Extended Stored Procedures for Mere Mortals 188
Writing Data to a CSV File 188
Retrieving HttpWebRequest Content 191
Calling Unmanaged Code API Functions 193

Replacing OLE Automation 195
Creating System SPs and UDFs 199
Summary 200
Chapter 7: The .NET Framework’s Base Class Library 201
Accessing External Data Sources 201
Performing File IO 201
Creating and Consuming XML Documents 207
Consuming Web Services 213
Building Effective Monitoring Tools 223
Auditing Server Processes 223
Auditing Installed Software 227
Summary 230
Chapter 8: Using SQL CLR Stored Procedures in Your Applications 231
SQL CLR Application Usage Architecture 231
SQL Server Connections and SQL CLR Stored Procedures 232
Connection Conclusions 238
Console Apps and SQL CLR Extended Procs 238
WinForms and SQL CLR UDTs 241
Creating the Point SQL CLR UDT 241
Creating a Windows Form to Display a SQL CLR UDT 242
Accepting WinForm Input to Save a SQL CLR UDT 245
Web Forms and SQL CLR Aggregates 246
Integration Services and SQL CLR TVFs 248
Building the SQL CLR TVF 249
Using the SQL CLR TVF in an SSIS Package 253
Reporting Services and SQL CLR TVFs 255
Summary 256
Chapter 9: Handling Errors in CLR Stored Procedures 257
Exception-Handling Mechanisms Used in SQL CLR 258
How T-SQL @@Error Exception-Handling Works 258
How T-SQL Try . . . Catch Error-handling Works 262
How .NET Structured Error-Handling Works 266
How SQL CLR Error Handling Works 270
SQL CLR Error-Handling Strategies 272
SQL CLR Objects Throw Errors 272
Exception Handling at Design Time 274

Clean Up after Exceptions 276
SQL CLR Handling T-SQL Object Errors 277
T-SQL Objects Handling SQL CLR Exceptions 280
Summary 281
Chapter 10: CLR Administration and Security 283
.NET for the DBA and System Admin 284
Where the .NET Framework Is Located on Your Server 284
Only One Version of CLR Can Be Hosted in SQL Server 284
How SQL Server Manages Memory Usage in SQL CLR 286
How to Look inside a Compiled Assembly 287
Security and SQL Server–Hosted CLR 288
Security Starts with Surface Area Configuration 288
Security by Identifying SQL CLR Code and .NET Framework Usage 288
Securing the Loading of Assemblies 291
Adding Role-Based User Access to the T-SQL Prototype 293
Restricting Code at the Assembly Level 293
SQL CLR Assembly Metadata 302
Where are Noncore Assemblies Stored? 302
Where Are Core .NET Assemblies Stored? 304
Managing the SQL CLR Lifecycle 305
Start by Limiting the .NET Language 305
Setting Up the Environments 305
Promoting Source through the Environments 306
Backups 321
Monitoring and Troubleshooting SQL CLR 321
Monitoring with SQL Server Profiler 321
Using Dynamic Management Views 322
Monitoring Counters with Performance Monitor 325
Troubleshooting SQL CLR 326
Summary 329
Chapter 11: Case Study 331
Business Case 332
DateTime Calculation Background 334
Implicit Casting Issues 334
Storing Time in Date-Only Fields 335
The .NET DateTime Class 336
The .NET TimeSpan Class 338
Solution Approach 340

Solution Prerequisites 342
T-SQL-Based Solution 343
T-SQL Object Type Decision 344
T-SQL Optional Parameters 344
Validation of Date and Time Input Parameters 345
Designing around Variable-Based Cursors 346
Creating the Table-Valued Functions 348
Creating and Calculating the Business Default Hours Structure 352
Altering Business Default Hours Structure with Exceptions 354
Considering the Parameterized Time Period 356
Calculating the Business Availability 356
SQL CLR-Based Solution 357
Setting up a .Net Studio Solution and Database Projects 358
Adding Nondatabase Projects to SQL CLR Solutions 358
Creating the Corp.SQLCLR.Scheduling Assembly 365
Creating the SQL CLR Object 383
Calculating the Business Availability 385
Solution Comparisons 386
Testing and Debugging 386
Adding a .NET Console Test Harness 388
Deployment and Administration 391
Performance 391
Development Perspective 392
Summary 392
Index 393