Professional SQL Server 2005 CLR Programming: with Stored Procedures, Functions, Triggers, Aggregates, and Types

Derek Comingore, Douglas Hinson9780470054031, 0470054034

SQL Server 2005 offers the capability to write code in a .NET language that can be compiled and run inside SQL Server. CLR Integration, or SQL CLR, lets you create stored procedures, user-defined types, triggers, table valued functions, and aggregates using a .NET managed language. You can read and write to resources outside of SQL Server and enjoy a tighter integration with XML, web services, and simple file and logging capabilities.Here’s the reference you’ll want on your desk as you develop SQL CLR solutions. It helps you decide whether to use SQL CLR, how to lock down security, and learn from real examples. If you want to develop stored procedures or other objects in .NET for SQL Server 2005, this book offers exactly what you need.What you will learn from this book * The concepts and architecture of SQL CLR * Uses of .NET namespaces in SQL Server programming tasks * How to develop and benchmark routines in T-SQL and .NET to determine when CLR-based solutions are advantageous * How to replace extended stored procedures using SQL CLR stored procedures * How to use SQL CLR objects in external applications * How to restrict and secure SQL CLR object capabilities * Processes and procedures for deploying SQL CLR objects Who this book is forThis book is for developers and architects who are familiar with .NET concepts as well as DBAs who, although developers in their own right, may be slightly less up to date on .NET. A solid grounding in T-SQL is necessary.Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

---

Table of contents :
Professional SQL Server 2005 CLR Programming……Page 1
Contents……Page 8
Acknowledgments……Page 16
Who This Book Is For……Page 18
How This Book Is Structured……Page 19
What You Need to Use This Book……Page 20
Source Code……Page 21
p2p.wrox.com……Page 22
What is SQL CLR?……Page 24
Why Does SQL CLR Exist?……Page 25
Supported SQL CLR Objects……Page 26
CTS……Page 27
JIT……Page 28
Managed Code and Managed Languages……Page 29
SQL CLR Architecture……Page 30
The CLR Security Model……Page 31
Using SQL CLR or T-SQL……Page 32
Using SQL CLR or OLE Automation Procedures……Page 33
Security Considerations……Page 34
Implementation Considerations……Page 35
Maintenance Considerations……Page 36
Visual Studio 2005 SQL CLR support……Page 37
Summary……Page 38
Your First CLR Stored Procedure……Page 40
The Mechanics of a Creating and Deploying a CLR Routine……Page 41
Enabling CLR in SQL Server 2005……Page 43
Coding the Class……Page 45
Compiling the .NET DLL……Page 47
Creating the Assembly……Page 48
Choosing between User-Defined Functions and Stored Procedures……Page 49
Creating the T-SQL Stored Procedure……Page 50
How SQL Server Manages Assemblies……Page 51
Assembly Metadata……Page 52
Extracting Assembly DLL and Source Code……Page 54
Creating Your First CLR User-Defined Function……Page 55
Using Visual Studio to Create a CLR Routine……Page 56
Creating the Class……Page 57
Deploying the Class……Page 59
What the Auto-Deployment Process Does……Page 60
Class and Structure Functions or Methods……Page 61
Reviewing Assembly Dependencies……Page 62
Summary……Page 63
SQL CLR Code Structure……Page 66
Using SQL CLR Data Type Classes……Page 69
Detecting SQL CLR Availability……Page 72
Performing Data Access……Page 73
Returning Resultsets and Messages……Page 76
Programming Transactions……Page 81
Summary……Page 90
Managed Code……Page 92
The SqlProcedure Attribute……Page 93
Parameters……Page 95
Return Values……Page 97
Creating, Altering, and Dropping Managed Stored Procedures……Page 98
Scalar-Valued Functions……Page 100
Creating, Altering, and Dropping, Managed Scalar-valued Functions……Page 103
Table-Valued Functions……Page 104
Managed Triggers……Page 106
SqlTriggerContext Class……Page 109
Accessing the Virtual Inserted and Deleted Temporary Tables……Page 110
Enabling and Disabling Triggers……Page 111
Managed DML Triggers……Page 112
Managed DDL Triggers……Page 115
Managed Code……Page 116
Creating, Altering, and Dropping UDAs……Page 119
Managed Code……Page 120
Summary……Page 124
Comparing T-SQL with Managed Code……Page 126
Variable Declaration and Scope……Page 127
Automatic Variables and System Functions……Page 129
Flow of Control……Page 130
Custom Attributes and Optimizers……Page 134
Structured Programming……Page 135
Object-Oriented Programming?……Page 139
Error Handling……Page 141
T-SQL Data-Centric Built-Ins……Page 142
Handling NULL Values……Page 145
String Parsing……Page 146
Array Handling……Page 148
Computations and Calculations……Page 149
Cursor Processing……Page 154
New .NET Base Class Library (BCL) Capabilities……Page 160
Commingling SQL CLR and T-SQL……Page 161
Submitting Data to Database……Page 164
Retrieve Data from Database……Page 168
Set-Based Operations……Page 171
Forward-Only, Read-Only Row Navigation Example……Page 172
Performance and Other Comparisons……Page 184
Methodology of Comparison……Page 185
A Final Word on Performance……Page 190
Maintainability……Page 191
Summary……Page 192
Replacing Extended Stored Procedures, Functions, and Other T-SQL Objects……Page 194
Why Replace XPs with SQL CLR?……Page 195
Not Restricted to the Master Database……Page 196
Preparing SQL Server for External-Access Assemblies……Page 197
Determining If a File Exists……Page 198
Reading from the Registry……Page 202
Replacing xp_CmdShell……Page 205
Writing Data to a CSV File……Page 211
Retrieving HttpWebRequest Content……Page 214
Calling Unmanaged Code API Functions……Page 216
Replacing OLE Automation……Page 218
Creating System SPs and UDFs……Page 222
Summary……Page 223
Performing File IO……Page 224
Creating and Consuming XML Documents……Page 230
Consuming Web Services……Page 236
Auditing Server Processes……Page 246
Auditing Installed Software……Page 250
Summary……Page 253
SQL CLR Application Usage Architecture……Page 254
SQL Server Connections and SQL CLR Stored Procedures……Page 255
Console Apps and SQL CLR Extended Procs……Page 261
Creating the Point SQL CLR UDT……Page 264
Creating a Windows Form to Display a SQL CLR UDT……Page 265
Accepting WinForm Input to Save a SQL CLR UDT……Page 268
Web Forms and SQL CLR Aggregates……Page 269
Integration Services and SQL CLR TVFs……Page 271
Building the SQL CLR TVF……Page 272
Using the SQL CLR TVF in an SSIS Package……Page 276
Reporting Services and SQL CLR TVFs……Page 278
Summary……Page 279
Handling Errors in CLR Stored Procedures……Page 280
How T-SQL @@Error Exception-Handling Works……Page 281
How T-SQL Try . . . Catch Error-handling Works……Page 285
How .NET Structured Error-Handling Works……Page 289
How SQL CLR Error Handling Works……Page 293
SQL CLR Objects Throw Errors……Page 295
Exception Handling at Design Time……Page 297
Clean Up after Exceptions……Page 299
SQL CLR Handling T-SQL Object Errors……Page 300
T-SQL Objects Handling SQL CLR Exceptions……Page 303
Summary……Page 304
CLR Administration and Security……Page 306
Only One Version of CLR Can Be Hosted in SQL Server……Page 307
How SQL Server Manages Memory Usage in SQL CLR……Page 309
How to Look inside a Compiled Assembly……Page 310
Security by Identifying SQL CLR Code and .NET Framework Usage……Page 311
Securing the Loading of Assemblies……Page 314
Restricting Code at the Assembly Level……Page 316
Where are Noncore Assemblies Stored?……Page 325
Where Are Core .NET Assemblies Stored?……Page 327
Setting Up the Environments……Page 328
Promoting Source through the Environments……Page 329
Monitoring with SQL Server Profiler……Page 344
Using Dynamic Management Views……Page 345
Monitoring Counters with Performance Monitor……Page 348
Troubleshooting SQL CLR……Page 349
Summary……Page 352
Case Study……Page 354
Business Case……Page 355
Implicit Casting Issues……Page 357
Storing Time in Date-Only Fields……Page 358
The .NET DateTime Class……Page 359
The .NET TimeSpan Class……Page 361
Solution Approach……Page 363
Solution Prerequisites……Page 365
T-SQL-Based Solution……Page 366
T-SQL Optional Parameters……Page 367
Validation of Date and Time Input Parameters……Page 368
Designing around Variable-Based Cursors……Page 369
Creating the Table-Valued Functions……Page 371
Creating and Calculating the Business Default Hours Structure……Page 375
Altering Business Default Hours Structure with Exceptions……Page 377
Calculating the Business Availability……Page 379
SQL CLR-Based Solution……Page 380
Adding Nondatabase Projects to SQL CLR Solutions……Page 381
Creating the Corp.SQLCLR.Scheduling Assembly……Page 388
Creating the SQL CLR Object……Page 406
Calculating the Business Availability……Page 408
Testing and Debugging……Page 409
Adding a .NET Console Test Harness……Page 411
Performance……Page 414
Summary……Page 415
Index……Page 416