Home > Articles > Data > SQL Server

  • Print
  • + Share This
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:

  1. Create an array of SqlMetaData instances that describes the data in each column.
  2. Create an instance of SqlDataRecord. You must associate the array of SqlMetaData instances with the SqlDataRecord.
  3. Populate the values in the SqlDataRecord using either weakly or strongly typed setter methods.
  4. Call SqlPipe’s SendResultsStart method to send the first row. This sends the metadata back to the client.
  5. Populate the values in the SqlDataRecord using either weakly or strongly typed setter methods.
  6. Use SqlPipe’s SendResultsRow method to send the data.
  7. 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();
}
  • + Share This
  • 🔖 Save To Your Account