2006 by Robin Dewson and Julian Skinner
Contents at a Glance
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
CHAPTER 1 Introducing Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
CHAPTER 2 Writing a Simple SQL Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
CHAPTER 3 The SQL Server .NET Programming Model . . . . . . . . . . . . . . . . . . . . . 33
CHAPTER 4 CLR Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
CHAPTER 5 User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
CHAPTER 6 User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
CHAPTER 7 User-Defined Aggregates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
CHAPTER 8 CLR Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
CHAPTER 9 Error Handling and Debugging Strategies . . . . . . . . . . . . . . . . . . . . . 161
CHAPTER 10 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
CHAPTER 11 Integrating Assemblies with Other Technologies . . . . . . . . . . . . . . . 231
INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
iii
Contents
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
CHAPTER 1 Introducing Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Data Manipulation Prior to SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . 1
SQL Server Assemblies Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Common Language Runtime Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Compiling and Executing Assembly Code . . . . . . . . . . . . . . . . . . . . . . . 3
Code Access Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Threading Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Memory Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Application Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Using Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Application Tier vs. Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
T-SQL Code or .NET Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Migrating Extended Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . 8
ADO.NET and SQL Data Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Building Objects from Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
CLR Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
User-Defined Aggregates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
v
CHAPTER 2 Writing a Simple SQL Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
SQL Assembly Creation Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Writing .NET Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Coding the Simple Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . 15
Compiling .NET Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Registering the Assembly with SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . 22
Creating the Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
SQL Server Projects in Visual Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
CHAPTER 3 The SQL Server .NET Programming Model . . . . . . . . . . . . . . . . 33
The .NET Data Access Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Accessing SQL Server Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Establishing the Context of a SQL Assembly . . . . . . . . . . . . . . . . . . . 34
Creating the Context Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Making Requests Against SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . 36
Representing Row Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Working with Single Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Communicating with the Caller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
CLR Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
CHAPTER 4 CLR Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Why Use a CLR Stored Procedure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Migrating Complex T-SQL Procedures . . . . . . . . . . . . . . . . . . . . . . . . 54
Converting Extended Stored Procedures . . . . . . . . . . . . . . . . . . . . . . 55
Creating a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Creating XML Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The T-SQL Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Writing the Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Working with Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Storing and Retrieving Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Using Nonstandard Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Testing the Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Executing Operating System Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
vi CONTENTS
CHAPTER 5 User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Creating CLR UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Restrictions on UDF Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
The SqlFunction Attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Scalar-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Scalar-Valued UDF Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Table-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Table-Valued UDF Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Listing the Contents of a Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
CHAPTER 6 User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Traditional vs. CLR UDTs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Creating CLR-Based UDTs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Required Implementations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Optional Method Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Understanding the UDT Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Building and Using Example UDTs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Creating a Duration UDT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Creating an E-mail Address UDT . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
CHAPTER 7 User-Defined Aggregates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
UDA Benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
UDA Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Building a UDA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Building a Simple UDA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Using UDTs with a UDA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Creating the .NET Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Serialization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Building a Serialized UDA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
CONTENTS vii
CHAPTER 8 CLR Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
When to Use CLR Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
CLR DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Creating CLR DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
EventData . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Dropping DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Using CLR-Based DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
CLR DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Creating DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using CLR DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
CHAPTER 9 Error Handling and Debugging Strategies . . . . . . . . . . . . . . . 161
Debugging SQL Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Debugging with Visual Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Debugging from the Command Line . . . . . . . . . . . . . . . . . . . . . . . . . 166
Using Preprocessor Directives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
The Conditional Attribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
The Debug and Trace Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Debugging Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
.NET Exception Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Using SqlException . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Throwing Your Own Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Recording Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
E-mailing Error Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Writing to an Event Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
CHAPTER 10 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
.NET Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Role-Based Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Code Access Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Partially Trusted Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
SQL Server Assembly Permission Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
The Safe Permission Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
The External Access Permission Set . . . . . . . . . . . . . . . . . . . . . . . . . 227
The Unsafe Permission Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Installing Nonsafe Permission Sets . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
viii CONTENTS
CHAPTER 11 Integrating Assemblies with Other Technologies . . . . . . . 231
Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Storing Data from the Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Writing the .NET Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Generating the Custom Types Used by the Web Service . . . . . . . . 236
Compiling the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Deploying the Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Testing the Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
The .NET Service Broker Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Example Service Broker Application . . . . . . . . . . . . . . . . . . . . . . . . . 241
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Цена: 200руб.