- Programming with SqlClient
- Context: The SqlContext Class
- Connections
- Commands: Making Things Happen
- Obtaining Results
- Transactions
- Pipe
- Creating and Sending New Rowsets
- Using the WindowsIdentity
- Calling a Web Service from SQLCLR
- Exception Handling
- SqlTriggerContext
- SqlClient Classes That You Can’t Use on the Server
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
Context: The SqlContext Class
The SqlContext class is one of the new classes that are available only if you’re running inside the server. When a procedure or function is executed, it is executed as part of the user’s connection. Whether that user connection comes from ODBC, ADO.NET, or T-SQL doesn’t really matter. You are in a connection that has specific properties, environment variables, and so on, and you are executing within that connection; you are in the context of the user’s connection.
A command is executed within the context of the connection, but it also has an execution context, which consists of data related to the command. The same goes for triggers, which are executed within a trigger context.
Prior to SQL Server 2005, the closest we came to being able to write code in another language that executed within the process space of SQL Server was writing extended stored procedures. An extended stored procedure is a C or C++ DLL that has been catalogued in SQL Server and therefore can be executed in the same manner as a "normal" SQL Server stored procedure. The extended stored procedure is executed in process with SQL Server and on the same Windows thread1 as the user’s connection.
Note, however, that if you need to do any kind of database access—even within the database to which the user is connected—from the extended stored procedure, you still need to connect to the database explicitly through ODBC, OLE DB, or even DBLib exactly as you would do from a client, as Figure 4-2 illustrates. Furthermore, when you have created the connection from the procedure, you may want to share a common transaction lock space with the client. Because you now have a separate connection, you need to ensure explicitly that you share the transaction lock space by using the srv_getbindtoken call and the stored procedure sp_bindsession.
)
Figure 4-2 Connections from extended stored procedures
In SQL Server 2005, when you use the .NET Framework to write procedures, functions, and triggers, the SqlContext is available. The original program can now be rewritten in Listing 4-4 so that the same code works either on the client/middle tier or in the server if it’s called as part of a stored procedure using the SqlContext static IsAvailable property.
Listing 4-4: Using IsAvailable to determine whether the code is running on the server
// other using statements elided for clarity
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; // for SqlContext
public static void InsertRowOfTestData2()
{
string connStr;
if (SqlContext.IsAvailable)
connStr = "context connection=true";
else
connStr = GetConnectionStringFromConfigFile();
// the rest of the code is identical
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd =
new SqlCommand("insert into test values (’testdata’)", conn))
{
conn.Open();
// The value of i is the number of rows affected
int i = cmd.ExecuteNonQuery();
}
}
You can see the SqlContext as a helper class; static read-only properties that allow you to access the class encapsulate functionality that exists only on the server. These properties are shown in Table 4-1.
Table 4-1: SqlContext Static Properties
|
Property |
Return Value |
|
IsAvailable |
Boolean |
|
WindowsIdentity |
System.Security.Principal.WindowsIdentity |
|
Pipe |
Microsoft.SqlServer.Server.SqlPipe |
|
TriggerContext |
Microsoft.SqlServer.Server.SqlTriggerContext |
Using SqlContext is the only way to get an instance of the classes in Table 4-1; you cannot create them by using a constructor (New in Visual Basic .NET). You can create the other classes that are part of the SqlClient provider in the same way that you normally would create them if used from an ADO.NET client. Some of the classes and methods in SqlClient act a little differently if you use them on the server, however.
SQLCLR stored procedures can do data access by default, but this is not the case with a SQLCLR user-defined function. As was discussed in the previous chapter, unless DataAccessKind or SystemDataAccessKind is set to DataAccessKind.Read/SystemDataAccessKind.Read, any attempt to do data access using the SqlClient provider will fail. Even if DataAccessKind is set to DataAccessKind.None (the default), however, SqlContext.Is-Available returns true. SqlContext.IsAvailable is an indication of whether you’re running in the server, rather than whether data access is permitted.
By now, you may be wondering: If some of the managed classes are calling into SQL Server, does that mean that the internals of SQL Server are managed as well, and if not, are interoperability calls between managed and native code space happening? The answers are no and yes. No, the internals of SQL Server are not managed; Microsoft did not rewrite the whole of SQL Server in managed code. And yes, interoperability calls happen. The managed classes are making Platform Invoke (PInvoke) calls against the executable of SQL Server, sqlservr.exe, as shown in Figure 4-3, which exposes a couple dozen methods for the CLR to call into.
)
Figure 4-3 Interop between .NET framework and SQL Server code in process
When you read this about interop, you may become concerned about performance. Theoretically, a performance hit is possible, but because SQL Server hosts the CLR (as discussed in Chapter 2), and the SqlClient provider runs in process with SQL Server, the hit is minimal. In the last sentence, notice that we said theoretically. Remember that when you execute CLR code, you will run machine-compiled code, which is not the case when you run T-SQL. Therefore, for some code executing in the CLR, the result may be a performance improvement compared with pure T-SQL code.
Now that we have discussed the SqlContext class, let’s see how we go about using it.