Working with the New Server-Side SQL Library

When writing stored procedures, types, functions, and more, you will need access to data and functionality provided by SQL Server. For example, when writing a C# stored procedure you need to be able to send results out on the "pipe." Also, you will want to be able to run your queries using the existing server-side connection and not have to resort to a client-side ADO.NET connection. All of this is available for you in a few classes, such as SqlContext and SqlPipe.

Table 21.1 shows some of the methods and properties available on the

Table 21.1. SqlPipe Properties and Methods

Property/Method	Description
IsSendingResults	Indicates whether the SqlPipe is currently sending results to the client. Read-only.
ExecuteAndSend()	Executes the specified command and sends the results on the pipe to the client.
Send()	Sends results to the client on the pipe.
SendResultsStart()	Indicates the beginning of results sent to the client, allowing you to send individual rows. When in this mode, the pipe will only accept calls from SendResultsRow and SendResultsEnd.
SendResultsRow()	Sends a row of data to the client.
SendResultsEnd()	Marks the end of a result set sent to the client.

The code in Listing 21.4 illustrates how to use some of the methods of the SqlPipe class to send results to the client as well as how to create a command parameter that is one of your own UDTs.

Listing 21.4. Using the SqlPipe Class and Creating a UDT Command Parameter

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void RadarScan(int objectID, int scanRadius) { using (SqlConnection conn = new SqlConnection("context connection=true")) {     conn.Open();     SqlCommand cmd = conn.CreateCommand();     cmd.CommandText = "SELECT Location FROM GameObjects WHERE ObjectID = " + objectID.ToString();     Point3D sourceLocation = Point3D.Null;     SqlDataReader rdr = cmd.ExecuteReader();     if (rdr.Read())    {         sourceLocation = (Point3D)rdr["Location"];    }    rdr.Close();    // now select all objects within radar range    SqlCommand scanCmd = conn.CreateCommand();    scanCmd.CommandText = "SELECT ObjectID, Name, Location.ToString() as CurrentLocation, " +         "Location.DistanceFromPoint(@pointRef) as DistanceToTarget FROM " +         "GameObjects WHERE Location.DistanceFromPoint(@pointRef) < " + scanRadius.ToString() +         " AND ObjectID != " + objectID.ToString();     scanCmd.Parameters.Add(new SqlParameter("@pointRef", SqlDbType.Udt));     scanCmd.Parameters[0].UdtTypeName = "Point3D";     scanCmd.Parameters[0].Value = sourceLocation;     // execute the command and send results to the calling client     SqlContext.Pipe.ExecuteAndSend(scanCmd);     rdr.Close(); } // no need to explicitly shut down connection because of 'using' statement } }; 

When this stored procedure is executed in a query environment, it gives the following results (for Object ID of 2, radar range of 20):

3    Battlecruiser DotNettica        9,7,3        19.7989898732233 

These results indicate that, according to the GameObjects table, there is a ship called the "Battlecruiser DotNettica" almost 20 units away, at location (9,7,3). Obviously this procedure could have been written in T-SQL, but if you needed to take into account other factors such as whether that vessel is cloaked, moving, and so on, the C# procedure might prove computationally faster than its T-SQL equivalent.

When you are working with a SQL Server project, you can't just add references as you see fit. When you go to add a reference, you are limited to other projects, and to assemblies already stored in SQL Server, as shown in Figure 21.3.

Figure 21.3. Adding a reference to a SQL Server project.

Another common task when working with the server-side SQL library is the creation and use of temporary tables. Often developers will need to create a temporary table to store dynamically generated results, and those results are then sent to the client. This same functionality is available to you in C# by giving you the ability to create your own metadata and send records that match that metadata.

The code in Listing 21.5 shows a C# stored procedure that utilizes the SqlPipe class to send its own arbitrary data as well as the accompanying metadata.

Listing 21.5. Sending Arbitrary Tabular Results

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void ArbitraryData() {     SqlDataRecord record = new SqlDataRecord(         new SqlMetaData("City", SqlDbType.NVarChar, 100),         new SqlMetaData("Temperature", SqlDbType.Int));     //  this sends the meta-data information, not the record itself     SqlContext.Pipe.SendResultsStart(record);     record.SetString(0, "Denver, CO");     record.SetInt32(1, 65);     SqlContext.Pipe.SendResultsRow(record);     record.SetString(0, "Durango, CO");     record.SetInt32(1, 43);     SqlContext.Pipe.SendResultsRow(record);     SqlContext.Pipe.SendResultsEnd(); } };