Professional SQL Server 2005 CLR Programming: with Stored Procedures, Functions, Triggers, Aggregates and Types by Derek Comingore, Douglas Hinson

Acknowledgments     xv
Introduction     xvii
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
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
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
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
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-lns     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
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
The .NET Framework's Base Class Library     201
Accessing External Data Sources     201
Performing File 1O     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
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
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
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
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