Working with ADO.NET from Within CLR Executables

Before wading into a hip-deep discussion of CLR stored procedures, we need to take a short side trip to acquaint you with ADO.NET and how it's used (or abused) in a CLR executable. Yes, there are times when your CLR Executable code needs to access data from the local SQL Server or perhaps from an external source like another SQL Server instance or data in another format, in another database, or in Cleveland. The engineers at Microsoft experimented with this interface for quite some time during the beta cycle, and we all saw a number of implementations come and go. You'll see evidence of these tried-and-abandoned approaches in a number of magazine articles and stale Google content.

Using the Context Connection

To simplify your job, when it comes time to connect to a data source, you have two fundamental choices, as illustrated in Figure 13.31:

• Use the SqlContext connection. In this case, the CLR executable leverages the fact that the code is being executed in the process space of a local SQL Server instance. If this is where your data is located, it makes abundant sense to use this approach to connect to SQL Server. This approach bypasses the entire protocol and transport layers because, essentially, SQL Server is talking to itself to get the datajust as it does when it executes T-SQL queries.

• Use a "normal" ADO.NET SqlClient connection. If you need to access data from an external source, you can choose to open a SqlConnection that routes your connection out through the protocol and transport, and back in through the matching protocol layers on the target server. This is the same technique used by other clients accessing SQL Server over the network or from the same system^[9].

  ^[9] Yes, you can connect to SQL Server using the shared memory provider if the executable is running on the same system as SQL Server. This interface bypasses the transport layers.

Figure 13.31. Connecting to SQL Server via a "context" or "normal" connection.

A context connection is created when you specify "context connection=true" in the ConnectionStringyou won't be permitted to add any other keywords. Just a few caveats when using a context connection:

• You can open only one context connection. Yes, multiple instances of your executable will each get their own context connectionin their own context.

• No, you can't use MARS with a context connection, nor can you use SqlBulkCopy, update batching (as supported in ADO.NET 2.0), or SqlNotificationRequest.

• You also cannot cancel an operation in a context connection. SqlCommand.Cancel() is ignored.

There are also a couple of restrictions on using a "normal" connection from within a CLR stored procedureno asynchronous operations, and the SqlDependency object is not supported. You'll also want to stay away from Integrated Security (SSPI) authentication, as the "identity" used for the connection will be the identity of the SQL Server servicenot yours. I'll show some examples of using a context connection later in this chapterseveral of the CLR executables use it.

Sure, you can use a "normal" connection to connect to other data sourceseven Oracle and JET. Yes, these connections will limit the performance of your SQL Server, as it has to wait for the source database engine to be instantiated, accessed, and queried.

When you access an external database, you'll have to mark your CLR executable Permission Level to "EXTERNAL_ACCESS". The implications of this setting are discussed later in this chapterit's set via the Project Properties, shown in Figure 13.32. A context connection can run with the default setting"Safe".

Figure 13.32. Setting the Permission Level with the Project Settings dialog.

Managing Transactions

CLR Executables have been integrated with the 2.0 Framework's System.Transactions. You'll have to manually add a reference to System.Transactions.DLL in your CLR executable References list to get access to this functionality in your code. To get a better idea of how the CLR works with SQL Server to implement transaction support, I suggest reading Pablo Castro's article^[10] "Managed Data Access Inside SQL Server with ADO.NET and SQLCLR," posted on MSDN. Generally, it's recommended that you create your own transaction scope within the context of your CLR executable. The CLR and SQL Server work together to coordinate your transaction scope with the transaction context used to execute the executable.

^[10] http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/mandataaccess.asp#mandataac_topic10

Querying Data

Once the ADO.NET Connection is open (yes, you still have to open and close the Connectioneven a context connection), you use it as you would any normal connection to SQL Server. You still use the SqlClient namespace classes to create SqlCommand instances, populate their Parameters collection, and execute them. You can create SqlDataAdapter instances or simply create a SqlDataReader to return single or multiple resultsets. The next section has plenty of examples of executing SqlCommand objects and working with the resultsets.

Returning Data and Messages

Unlike a "normal" Windows application, a CLR stored procedure or other executable often has to return special structures like Tables (in a Table-value function), one or more resultsets (that might contain one or more rowsets), messages, and exceptions. The following example illustrates how to use the SqlContext.Pipe to create these resultsets that are sent directly back to SQL Server. All data returned is sent to the local SQL Server instance hosting the CLR executable through this context pipe.

When implementing a CLR User-Defined type (UDT) or aggregate, there are special interfaces used to expose the data you build to SQL Server. These are also explained in great detail later in this chapter.