Professional SQL Server™ 2005 CLR Programming with Stored Procedures, Functions, Triggers,

www.free-ebooks-download.org

Professional SQL Server™ 2005

CLR Programming

with Stored Procedures,

Functions, Triggers,

Aggregates, and Types

Derek Comingore

Douglas Hinson

01_054034 ffirs.qxp 11/2/06 11:52 AM Page i

www.free-ebooks-download.org

Professional SQL Server™ 2005 CLR Programming with Stored

Procedures, Functions, Triggers, Aggregates, and Types

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-470-05403-1

ISBN-10: 0-470-05403-4

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1MA/RX/RR/QW/IN

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,

electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108

of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization

through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA

01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal

Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355,

or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTA￾TIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK

AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR

A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS.

THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS

SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING,

OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT

PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR

DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS

A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR

THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDA￾TIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY

HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services or to obtain technical support, please contact our Customer

Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.

Library of Congress Cataloging-in-Publication Data

Comingore, Derek, 1978-

Professional SQL server 2005 CLR programming : with stored procedures, functions, triggers, aggregates, and types /

Derek Comingore and Douglas Hinson.

p. cm.

Includes indexes.

ISBN-13: 978-0-470-05403-1 (paper/website)

ISBN-10: 0-470-05403-4 (paper/website)

1. SQL (Computer program language) 2. Database management--Computer programs. 3. Microsoft .NET Framework. I.

Hinson, Douglas, 1968- II. Title.

QA76.73.S67C655 2007

005.75’85--dc22

2006031028

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trade￾marks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries,

and may not be used without written permission. SQL Server is a trademark of Microsoft Corporation in the United States

and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not

associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available

in electronic books.

01_054034 ffirs.qxp 11/2/06 11:52 AM Page ii

To my son, Derek Steven Comingore, and the next generation

of programmers.

—Derek Comingore

To Misty, Mariah, and Kyle, for your love, support, and patience.

—Douglas Hinson

01_054034 ffirs.qxp 11/2/06 11:52 AM Page iii

About the Authors

Derek Comingore is an independent consultant, trainer, and speaker specializing in SQL Server and .NET

technologies. He has recently started two ventures, an online Microsoft community for colleagues to learn

from one another, located at www.RedmondSociety.com, and a second for SQL Server consulting at

www.SQLServerDBAs.com. Derek holds the MCAD/MCDBA Microsoft certifications, the Carnegie Mellon

Personal Software Process (PSP) credential, and an AAS/BS in Computer Information Systems from

University of North Dakota. Derek’s blog is located at http://RedmondSociety.com/blogs/Derek.

Douglas Hinson has worked as an independent software and database consultant in the logistics and

financial industries, with an extensive SQL Server background. He has coauthored several Wrox books,

including Professional SQL Server 2005 Integration Services.

01_054034 ffirs.qxp 11/2/06 11:52 AM Page iv

Credits

Executive Editor

Bob Elliott

Development Editor

Brian MacDonald

Technical Editor

Dan Meyers

Production Editor

Felicia Robinson

Copy Editor

Foxxe Editorial Services

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project Coordinators

Patrick Redmond

Ryan Steffen

Graphics and Production Specialists

Carrie A. Foster

Peter Gaunt

Denny Hager

Alicia B. South

Ronald Terry

Quality Control Technician

Laura Albert

John Greenough

Proofreading and Indexing

Techbooks

01_054034 ffirs.qxp 11/2/06 11:52 AM Page v

01_054034 ffirs.qxp 11/2/06 11:52 AM Page vi

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page vii

viii

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page viii

ix

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page ix

x

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page x

xi

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page xi

xii

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page xii

xiii

Contents

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

02_054034 ftoc.qxp 11/2/06 11:53 AM Page xiii

02_054034 ftoc.qxp 11/2/06 11:53 AM Page xiv