发送数据集示例

项目
05/08/2013

发送 DataSet 示例说明如何在服务器端基于公共语言运行时 (CLR) 的存储过程中将基于 ADO.NET 的 DataSet 作为结果集返回到客户端。例如，当此类存储过程使用查询结果填充 DataSet，然后操作该 DataSet 中包含的数据时，此操作很有用。另外，在存储过程从头创建并填充 DataSet 时，此操作也很有用。该示例由 DataSetUtilities 和 TestSendDataSet 这两个类构成。针对 DataSetUtilities 类的方法 SendDataSet 实现了将 DataSet 实例的内容传输到客户端的通用方式。对 TestSendDataSet 类定义的 DoTest 方法通过创建 DataSet 并用 uspGetTwoBOMTestData Transact-SQL 存储过程中的数据进行填充来验证 SendDataSet 方法是否起作用。 uspGetTwoBOMTestData 两次运行 Transact-SQL 存储过程 uspGetBillOfMaterials，以通过递归方式查询两种产品的物料清单，这两种产品已被指定为 usp_GetTwoBOMTestData 存储过程的参数。通常在填充数据集后，在调用 SendDataSet 前会对数据进行修改，然后才将数据集中的数据作为结果集传输到客户端。简单地说，此示例返回的数据未经修改。

先决条件

若要创建和运行此项目，必须安装下列软件：

SQL Server 或 SQL Server Express。您可以从 SQL Server Express Documentation and Samples （Express 文档和示例）网站免费获取 SQL Server Express。
SQL Server 开发人员网站提供的 AdventureWorks 数据库
.NET Framework SDK 2.0 或更高版本，或 Microsoft Visual Studio 2005 或更高版本。您可以免费获取 .NET Framework SDK。
此外，还必须满足以下条件：
您使用的 SQL Server 实例必须已启用 CLR 集成。
若要启用 CLR 集成，请执行以下步骤：

启用 CLR 集成
- 执行以下 Transact-SQL 命令：
sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

注意

若要启用 CLR，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。
必须在您使用的 SQL Server 实例上安装 AdventureWorks 数据库。
如果您不是要使用的 SQL Server 实例的管理员，则必须让管理员授予您 CreateAssembly 权限，才能完成安装。

注意
若要启用 CLR，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。

生成示例

按照以下说明创建和运行该示例：

打开 Visual Studio 或 .NET Framework 命令提示符。
如有必要，为您的示例创建目录。对于此示例，我们将使用 C:\MySample。
在 c:\MySample 中，创建 SendDataSet.vb（用于 Visual Basic 示例）或 SendDataSet.cs（用于 C# 示例），并将相应的 Visual Basic 或 C# 示例代码（如下所示）复制到该文件中。
从命令行提示符执行以下代码之一（具体取决于所选的语言)，将示例代码编译为所需的程序集。
- Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library SendDataSet.vb
- Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library SendDataSet.cs
将 Transact-SQL 安装代码复制到一个文件中，并在示例目录中将其另存为 Install.sql。
如果该示例安装在 C:\MySample\ 之外的目录中，请按说明编辑文件 Install.sql 以指向该位置。
通过执行以下命令部署程序集、存储过程和函数：
- sqlcmd -E -I -i install.sql
将 Transact-SQL 测试脚本复制到一个文件中，并在示例目录中将其另存为 test.sql。
- sqlcmd -E -I -i test.sql
将 Transact-SQL 清除脚本复制到一个文件中，并在示例目录中将其另存为 cleanup.sql。
使用以下命令执行该脚本：
- sqlcmd -E -I -i cleanup.sql

示例代码

下面是此示例的代码列表。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


    public static class DataSetUtilities
    {

        public static void SendDataSet(DataSet ds)
        {
            if (ds == null)
            {
                throw new ArgumentException("SendDataSet requires a non-null data set.");
            }
            else
            {
                foreach (DataTable dt in ds.Tables)
                {
                    SendDataTable(dt);
                }
            }
        }


        public static void SendDataTable(DataTable dt)
        {
            bool[] coerceToString;  // Do we need to coerce this column to string?
            SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);

            SqlDataRecord record = new SqlDataRecord(metaData);
            SqlPipe pipe = SqlContext.Pipe;
            pipe.SendResultsStart(record);
            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    for (int index = 0; index < record.FieldCount; index++)
                    {
                        object value = row[index];
                        if (null != value && coerceToString[index])
                            value = value.ToString();
                        record.SetValue(index, value);
                    }

                    pipe.SendResultsRow(record);
                }
            }
            finally
            {
                pipe.SendResultsEnd();
            }
        }

        private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] coerceToString)
        {
            SqlMetaData[] metaDataResult = new SqlMetaData[dt.Columns.Count];
            coerceToString = new bool[dt.Columns.Count];
            for (int index = 0; index < dt.Columns.Count; index++)
            {
                DataColumn column = dt.Columns[index];
                metaDataResult[index] = SqlMetaDataFromColumn(column, out coerceToString[index]);
            }

            return metaDataResult;
        }

        private static Exception InvalidDataTypeCode(TypeCode code)
        {
            return new ArgumentException("Invalid type: " + code);
        }

        private static Exception UnknownDataType(Type clrType)
        {
            return new ArgumentException("Unknown type: " + clrType);
        }

        private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool coerceToString)
        {
            coerceToString = false;
            SqlMetaData sql_md = null;
            Type clrType = column.DataType;
            string name = column.ColumnName;
            switch (Type.GetTypeCode(clrType))
            {
                case TypeCode.Boolean: sql_md = new SqlMetaData(name, SqlDbType.Bit); break;
                case TypeCode.Byte: sql_md = new SqlMetaData(name, SqlDbType.TinyInt); break;
                case TypeCode.Char: sql_md = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;
                case TypeCode.DateTime: sql_md = new SqlMetaData(name, SqlDbType.DateTime); break;
                case TypeCode.DBNull: throw InvalidDataTypeCode(TypeCode.DBNull);
                case TypeCode.Decimal: sql_md = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;
                case TypeCode.Double: sql_md = new SqlMetaData(name, SqlDbType.Float); break;
                case TypeCode.Empty: throw InvalidDataTypeCode(TypeCode.Empty);
                case TypeCode.Int16: sql_md = new SqlMetaData(name, SqlDbType.SmallInt); break;
                case TypeCode.Int32: sql_md = new SqlMetaData(name, SqlDbType.Int); break;
                case TypeCode.Int64: sql_md = new SqlMetaData(name, SqlDbType.BigInt); break;
                case TypeCode.SByte: throw InvalidDataTypeCode(TypeCode.SByte);
                case TypeCode.Single: sql_md = new SqlMetaData(name, SqlDbType.Real); break;
                case TypeCode.String: sql_md = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
                    break;
                case TypeCode.UInt16: throw InvalidDataTypeCode(TypeCode.UInt16);
                case TypeCode.UInt32: throw InvalidDataTypeCode(TypeCode.UInt32);
                case TypeCode.UInt64: throw InvalidDataTypeCode(TypeCode.UInt64);
                case TypeCode.Object:
                    sql_md = SqlMetaDataFromObjectColumn(name, column, clrType);
                    if (sql_md == null)
                    {
                        // Unknown type, try to treat it as string;
                        sql_md = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
                        coerceToString = true;
                    }
                    break;

                default: throw UnknownDataType(clrType);
            }

            return sql_md;
        }

        private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)
        {
            SqlMetaData sql_md = null;
            if (clrType == typeof(System.Byte[]) || clrType == typeof(SqlBinary) || clrType == typeof(SqlBytes) ||
        clrType == typeof(System.Char[]) || clrType == typeof(SqlString) || clrType == typeof(SqlChars))
                sql_md = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);
            else if (clrType == typeof(System.Guid))
                sql_md = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
            else if (clrType == typeof(System.Object))
                sql_md = new SqlMetaData(name, SqlDbType.Variant);
            else if (clrType == typeof(SqlBoolean))
                sql_md = new SqlMetaData(name, SqlDbType.Bit);
            else if (clrType == typeof(SqlByte))
                sql_md = new SqlMetaData(name, SqlDbType.TinyInt);
            else if (clrType == typeof(SqlDateTime))
                sql_md = new SqlMetaData(name, SqlDbType.DateTime);
            else if (clrType == typeof(SqlDouble))
                sql_md = new SqlMetaData(name, SqlDbType.Float);
            else if (clrType == typeof(SqlGuid))
                sql_md = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
            else if (clrType == typeof(SqlInt16))
                sql_md = new SqlMetaData(name, SqlDbType.SmallInt);
            else if (clrType == typeof(SqlInt32))
                sql_md = new SqlMetaData(name, SqlDbType.Int);
            else if (clrType == typeof(SqlInt64))
                sql_md = new SqlMetaData(name, SqlDbType.BigInt);
            else if (clrType == typeof(SqlMoney))
                sql_md = new SqlMetaData(name, SqlDbType.Money);
            else if (clrType == typeof(SqlDecimal))
                sql_md = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);
            else if (clrType == typeof(SqlSingle))
                sql_md = new SqlMetaData(name, SqlDbType.Real);
            else if (clrType == typeof(SqlXml))
                sql_md = new SqlMetaData(name, SqlDbType.Xml);
            else
                sql_md = null;

            return sql_md;
        }
                  
    }
 public static class TestSendDataSet
    {
        private const string TestConnectionString = "context connection=true";
        const int prod1ID = 750; //Product ID of Road-150 Red, 44 bicycle
        const int prod2ID = 751; //Product ID of Road-150 Red, 48 bicycle

        /// <summary>
        /// Invoke a stored procedure to get some bill of material information and 
        /// fill a data set with the two result sets.  Return the data set to the client.
        /// </summary>
        public static void DoTest()
        {
            using (SqlConnection conn = new SqlConnection(TestConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "usp_GetTwoBOMTestData";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter prod1Param = new SqlParameter("@ProductID1", SqlDbType.Int);
                prod1Param.Value = prod1ID;
                cmd.Parameters.Add(prod1Param);

                SqlParameter prod2Param = new SqlParameter("@ProductID2", SqlDbType.Int);
                prod2Param.Value = prod2ID;
                cmd.Parameters.Add(prod2Param);

                SqlParameter asOfDateParam = new SqlParameter("@AsOfDate", SqlDbType.DateTime);
                asOfDateParam.Value = DateTime.Now;
                cmd.Parameters.Add(asOfDateParam);

                DataSet ds = new DataSet("TestData");
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                conn.Open();
                sda.Fill(ds);

// Normally, after filling the data set, rather than immediately returning it, 
// the data would be modified before invoking SendDataSet to deliver 
// the data within the data set as a result set to the client.  For simplicity 
// this sample simply returns the data.

                DataSetUtilities.SendDataSet(ds);

            }
        }

    }

Visual Basic

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Diagnostics
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices

Public Class DataSetUtilities

    Public Shared Sub SendDataSet(ByVal ds As DataSet)
        If ds Is Nothing Then
            Throw New ArgumentException("SendDataSet requires a non-null data set.")
        Else
            For Each dt As DataTable In ds.Tables
                SendDataTable(dt)
            Next
        End If
    End Sub

    Public Shared Sub SendDataTable(ByVal dt As DataTable)
        Dim coerceToString() As Boolean = Nothing ' Do we need to coerce this column to string?
        Dim metaData As SqlMetaData() = ExtractDataTableColumnMetaData(dt, coerceToString)

        Dim record As New SqlDataRecord(metaData)
        Dim pipe As SqlPipe = SqlContext.Pipe
        pipe.SendResultsStart(record)

        Try
            For Each row As DataRow In dt.Rows
                For index As Integer = 0 To record.FieldCount - 1
                    Dim value As Object = row(index)
                    If Nothing Is value AndAlso coerceToString(index) Then
                        value = value.ToString()
                    End If

                    record.SetValue(index, value)
                Next

                pipe.SendResultsRow(record)
            Next
        Finally
            pipe.SendResultsEnd()
        End Try
    End Sub

    Private Shared Function ExtractDataTableColumnMetaData(ByVal dt As DataTable, <Out()> ByRef coerceToString() As Boolean) As SqlMetaData()
        Dim metaDataResult(dt.Columns.Count - 1) As SqlMetaData
        coerceToString = New Boolean(dt.Columns.Count - 1) {}
        For index As Integer = 0 To dt.Columns.Count - 1
            Dim column As DataColumn = dt.Columns(index)
            metaDataResult(index) = SqlMetaDataFromColumn(column, coerceToString(index))
        Next

        Return metaDataResult
    End Function
    Private Shared Function InvalidDataTypeCode(ByVal code As TypeCode) As Exception
        Return New ArgumentException("Invalid type: " & code.ToString())
    End Function

    Private Shared Function UnknownDataType(ByVal clrType As Type) As Exception
        Return New ArgumentException("Unknown type: " & clrType.ToString())
    End Function

    Private Shared Function SqlMetaDataFromColumn(ByVal column As DataColumn, ByRef coerceToString As Boolean) As SqlMetaData
        coerceToString = False
        Dim sql_md As SqlMetaData = Nothing
        Dim clrType As Type = column.DataType
        Dim name As String = column.ColumnName
        Select Case Type.GetTypeCode(clrType)
            Case TypeCode.Boolean
                sql_md = New SqlMetaData(name, SqlDbType.Bit)
            Case TypeCode.Byte
                sql_md = New SqlMetaData(name, SqlDbType.TinyInt)
            Case TypeCode.Char
                sql_md = New SqlMetaData(name, SqlDbType.NVarChar, 1)
            Case TypeCode.DateTime
                sql_md = New SqlMetaData(name, SqlDbType.DateTime)
            Case TypeCode.DBNull
                Throw InvalidDataTypeCode(TypeCode.DBNull)
            Case TypeCode.Decimal
                sql_md = New SqlMetaData(name, SqlDbType.Decimal)
            Case TypeCode.Double
                sql_md = New SqlMetaData(name, SqlDbType.Float)
            Case TypeCode.Empty
                Throw InvalidDataTypeCode(TypeCode.Empty)
            Case TypeCode.Int16
                sql_md = New SqlMetaData(name, SqlDbType.SmallInt)
            Case TypeCode.Int32
                sql_md = New SqlMetaData(name, SqlDbType.Int)
            Case TypeCode.Int64
                sql_md = New SqlMetaData(name, SqlDbType.BigInt)
            Case TypeCode.SByte
                Throw InvalidDataTypeCode(TypeCode.SByte)
            Case TypeCode.Single
                sql_md = New SqlMetaData(name, SqlDbType.Real)
            Case TypeCode.String
                sql_md = New SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength)
            Case TypeCode.UInt16
                Throw InvalidDataTypeCode(TypeCode.UInt16)
            Case TypeCode.UInt32
                Throw InvalidDataTypeCode(TypeCode.UInt32)
            Case TypeCode.UInt64
                Throw InvalidDataTypeCode(TypeCode.UInt64)
            Case TypeCode.Object
                sql_md = SqlMetaDataFromObjectColumn(name, column, clrType)
                If sql_md Is Nothing Then
                    ' Unknown type, try to treat it as string
                    sql_md = New SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength)
                    coerceToString = True
                End If
            Case Else
                Throw UnknownDataType(clrType)
        End Select

        Return sql_md
    End Function

    Private Shared Function SqlMetaDataFromObjectColumn(ByVal name As String, ByVal column As DataColumn, ByVal clrType As Type) As SqlMetaData
        Dim sql_md As SqlMetaData = Nothing

        If (clrType Is GetType(System.Byte()) OrElse clrType Is GetType(SqlBinary) OrElse clrType Is GetType(SqlBytes) _
            OrElse clrType Is GetType(System.Char()) OrElse clrType Is GetType(SqlString) OrElse clrType Is GetType(SqlChars)) Then
            sql_md = New SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength)
        ElseIf (clrType Is GetType(System.Guid)) Then
            sql_md = New SqlMetaData(name, SqlDbType.UniqueIdentifier)
        ElseIf (clrType Is GetType(System.Object)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Variant)
        ElseIf (clrType Is GetType(SqlBoolean)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Bit)
        ElseIf (clrType Is GetType(SqlByte)) Then
            sql_md = New SqlMetaData(name, SqlDbType.TinyInt)
        ElseIf (clrType Is GetType(SqlDateTime)) Then
            sql_md = New SqlMetaData(name, SqlDbType.DateTime)
        ElseIf (clrType Is GetType(SqlDouble)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Float)
        ElseIf (clrType Is GetType(SqlGuid)) Then
            sql_md = New SqlMetaData(name, SqlDbType.UniqueIdentifier)
        ElseIf (clrType Is GetType(SqlInt16)) Then
            sql_md = New SqlMetaData(name, SqlDbType.SmallInt)
        ElseIf (clrType Is GetType(SqlInt32)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Int)
        ElseIf (clrType Is GetType(SqlInt64)) Then
            sql_md = New SqlMetaData(name, SqlDbType.BigInt)
        ElseIf (clrType Is GetType(SqlMoney)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Money)
        ElseIf (clrType Is GetType(SqlDecimal)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0)
        ElseIf (clrType Is GetType(SqlSingle)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Real)
        ElseIf (clrType Is GetType(SqlXml)) Then
            sql_md = New SqlMetaData(name, SqlDbType.Xml)
        Else
            sql_md = Nothing
        End If

        Return sql_md
    End Function
End Class

Public Class TestSendDataSet
    Private Const TestConnectionString As String = "context connection=true"
    Private Const prod1ID As Integer = 750  'Product ID of Road-150 Red, 44 bicycle
    Private Const prod2ID As Integer = 751  'Product ID of Road-150 Red, 48 bicycle

    ''' <summary>
    ''' Invoke a stored procedure to get some bill of material information and 
    ''' fill a data set with the two result sets.  Return the data set to the client.

    <SqlProcedure(Name:="usp_TestSendDataSet")> _
    Public Shared Sub DoTest()
        Dim conn As New SqlConnection(TestConnectionString)
        Try
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "usp_GetTwoBOMTestData"
            cmd.CommandType = CommandType.StoredProcedure

            Dim prod1Param As New SqlParameter("@ProductID1", SqlDbType.Int)
            prod1Param.Value = prod1ID
            cmd.Parameters.Add(prod1Param)

            Dim prod2Param As New SqlParameter("@ProductID2", SqlDbType.Int)
            prod2Param.Value = prod2ID
            cmd.Parameters.Add(prod2Param)

            Dim asOfDateParam As New SqlParameter("@AsOfDate", SqlDbType.DateTime)
            asOfDateParam.Value = DateTime.Now
            cmd.Parameters.Add(asOfDateParam)

            Dim ds As New DataSet("TestData")
            Dim sda As New SqlDataAdapter(cmd)
            conn.Open()
            sda.Fill(ds)

            ' Normally, after filling the data set, rather than immediately returning it, 
            ' the data would be modified before invoking SendDataSet to deliver 
            ' the data within the data set as a result set to the client.  For simplicity 
            ' this sample simply returns the data.
            DataSetUtilities.SendDataSet(ds)
        Finally
            conn.Dispose()
        End Try
    End Sub
End Class

这是 Transact-SQL 安装脚本 (Install.sql)，该脚本部署程序集并创建存储过程。

USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_GetTwoBOMTestData')
DROP PROCEDURE usp_GetTwoBOMTestData;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_TestSendDataSet')
DROP PROCEDURE usp_TestSendDataSet;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SendDataSet') 
DROP ASSEMBLY SendDataSet;
GO

-- Procedure used to generate test data to fill the data set being returned to the client
CREATE PROCEDURE usp_GetTwoBOMTestData
(
@ProductID1 int,
@ProductID2 int,
@AsOfDate DateTime
)
AS
BEGIN
EXEC uspGetBillOfMaterials @ProductID1, @AsOfDate;
EXEC uspGetBillOfMaterials @ProductID2, @AsOfDate;
END;
GO


DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
Set @SamplesPath = N'C:\MySample\'

CREATE ASSEMBLY SendDataSet from @SamplesPath +'SendDataSet.dll'
WITH PERMISSION_SET = Safe;
GO

CREATE PROCEDURE usp_TestSendDataSet
AS
EXTERNAL NAME [SendDataSet].[TestSendDataSet].[DoTest];
GO

这是用于测试该示例的 Transact-SQL 测试脚本 (test.sql)。

USE AdventureWorks
GO

EXEC usp_TestSendDataSet
GO

下面的 Transact-SQL 从数据库中删除程序集和存储过程。

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_GetTwoBOMTestData')
DROP PROCEDURE usp_GetTwoBOMTestData;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_TestSendDataSet')
DROP PROCEDURE usp_TestSendDataSet;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SendDataSet') 
DROP ASSEMBLY SendDataSet;
GO

请参阅

概念

公共语言运行时 (CLR) 集成的使用方案和示例

通过

发送数据集示例

先决条件

启用 CLR 集成

生成示例

按照以下说明创建和运行该示例：

示例代码

请参阅

概念

其他资源