- 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
Creating and Sending New Rowsets
You’ve already seen that you can execute commands that return rowsets and send these to the client. You might want to execute a command that returns a rowset and then augment or change the rowset before sending it on. Or you might get data that is not in the database, such as an RSS feed or other Web Service, and choose to expose that data as a set of columns and rows. This is similar to the functionality that you can expose using table-valued functions, but without the capability to perform SQL using a where clause on the result. Your rowset will appear as one of the outputs of the stored procedure just as though it came from SQL Server’s data.
You’d accomplish creating and sending a rowset by using the following steps:
- Create an array of SqlMetaData instances that describes the data in each column.
- Create an instance of SqlDataRecord. You must associate the array of SqlMetaData instances with the SqlDataRecord.
- Populate the values in the SqlDataRecord using either weakly or strongly typed setter methods.
- Call SqlPipe’s SendResultsStart method to send the first row. This sends the metadata back to the client.
- Populate the values in the SqlDataRecord using either weakly or strongly typed setter methods.
- Use SqlPipe’s SendResultsRow method to send the data.
- Use SqlPipe’s SendResultsEnd method to indicate that the rowset is complete.
First, we’ll talk about using the SqlMetaData class. SqlMetaData is a class that is used to describe completely a single column of data. It can be used with SqlDataRecord instances. SqlMetaData instances encapsulate the information in the extended metadata from new format-extended TDS describe packets used by SQL Server 2005, as well as work with earlier versions of TDS. Listing 4-16 lists the properties exposed by the SqlMetaData class.
Listing 4-16: Fields of the SqlMetaData class
class SqlMetaData {
//data type info
public SqlDbType SqlDbType; // SqlDbType enum value
public DbType DbType; // DbType enum value
public Type Type; // .NET Framework data type
public string TypeName; // .NET Framework type name
public string UdtTypeName; // SQL Server 3-part type name
//metadata info
public bool IsPartialLength;
public long LocaleId;
public long Max;
public long MaxLength;
public byte Precision;
public byte Scale;
public string Name; // column name
public SqlCompareOptions CompareOptions;
// XML schema info for XML data type
public string XmlSchemaCollectionDatabase;
public string XmlSchemaCollectionName;
public string XmlSchemaCollectionOwningSchema;
};
Let’s use SqlDataRecord, SqlMetaData, and SqlPipe to create and send rows from a simple synthesized rowset. The code for this is shown in Listing 4-17. The Thread.Sleep() calls are inserted so that you can observe pipelining in action with this type of rowset. Rows are sent as soon as the SQL engine has spare cycles to send them.
Listing 4-17: Synthesizing a rowset using SqlDataRecord and SqlMetaData
// other using statements elided for clarity
using System.Threading;
using Microsoft.SqlServer.Server;
public static void Pipeline()
{
SqlPipe p = SqlContext.Pipe;
// a single column in each row
SqlMetaData[] m = new SqlMetaData[1]
{new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");
p.SendResultsStart(rec);
for (int i=0;i<10000;i++)
p.SendResultsRow(rec);
Thread.Sleep(10000);
for (int i = 0; i < 10000; i++)
p.SendResultsRow(rec);
Thread.Sleep(10000);
for (int i = 0; i < 10000; i++)
p.SendResultsRow(rec);
Thread.Sleep(10000);
p.SendResultsEnd();
}