Usage Scenarios and Examples for Common Language Runtime (CLR) Integration

SQL Server includes sample applications, package samples, and numerous coding samples that you can use to learn about the programmability features of common language runtime (CLR) integration.

For complete Visual Studio projects implementing these samples and additional materials, visit Microsoft SQL Server Community Projects & Samples on CodePlex.

Name

Description

Accessing Native Code from a CLR UDF

Shows how to invoke a function in native (unmanaged) C++ code from a user-defined function in an assembly, in your database.

Array Parameter Sample

Demonstrates how to create, update, or delete a set of rows in a database by passing an array of information from a client to a CLR integration stored procedure on the server. This is done using a UDT.

Calendar-Aware Date/Time UDT Sample

Defines two UDTs that provide calendar-aware handling of dates and times.

CLR Transactions Sample

Demonstrates controlling transactions by using the managed APIs located in the System.Transactions namespace.

Contact Creation using CLR and XML

The Contact sample for SQL Server provides some useful utilities that form an extra layer of functionality on top of the base AdventureWorks2012 sample database. The first utility creates contact records for various types of people involved in the AdventureWorks2012 database. The contact information is specified by using XML and is passed to a C#-based or VB stored procedure to create the XML and place it in the proper tables with the database.

Currency Type and Conversion Function

Defines a Currency user-defined data type using C#.

Handling Large Objects Using CLR

Demonstrates transferring large binary objects (LOBs) between SQL Server and a file system accessible to the server using CLR stored procedures.

Hello World Ready Sample

Demonstrates the basic operations to create, deploy, and test a simple world ready CLR integration-based stored procedure.

Hello World Sample

Demonstrates the basic operations to create, deploy, and test a simple CLR integration-based stored procedure.

In-Process Data Access Sample

Contains a number of simple functions demonstrating various features of the CLR in-process data access provider.

Result Set Sample

Demonstrates how to execute commands while reading through results of a query, without opening a new connection and without reading all results into memory.

Send DataSet Sample

Demonstrates how to return an ADO.NET based DataSet within a server side CLR-based stored procedure as a result set to the client.

String Utility Functions Sample

Contains a streaming table-valued function (TVF), written in Visual C# and Visual Basic, which splits a comma-separated string into a table with one column.

Supplementary-Aware String Manipulation Sample

Shows the implementation of five supplementary-aware Transact-SQL string functions that can handle both Unicode and surrogate strings.

UDT Utilities

Contains a number of user-defined data type (UDT) utility functions.

Unused Assembly Cleanup

Contains a .NET stored procedure that deletes unused assemblies in the current database by querying the metadata catalogs.

User Defined Type

Shows the creation and use of a simple UDT from both Transact-SQL and a client application using System.Data.SqlClient.

UTF8 String User-Defined Data Type (UDT)

Demonstrates the implementation of a UDT that extends the type system of the database to provide storage for UTF8 encoded values.