Pro SQL Server 2005 Assemblies

by Dewson, Robin

• ISBN13: 9781590595664
• ISBN10: 1590595661
• Format: Paperback
• Copyright: 2005-12-28
• Publisher: Springer-Verlag New York Inc

Summary

This book is a preview of Microsoft's SQL Server 2005 database. SQL Server 2005 is completely .NET integrated, allowing developers to program in any .NET language, conveniently inside the database. This book is a first look at and illustrates the new .NET capabilities of the database. This book has strong appeal to three core audiences: SQL Server developers, .NET developers, and database administrators. After reading this book, developers will be able to build robust, useful and intelligent assemblies that are safe to execute and secure for the organization.

Table of Contents

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