傳送資料集範例
這個「傳送 DataSet」範例會示範如何在伺服器端以 Common Language Runtime (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 文件集和範例網站 (英文) 免費取得 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) 固定伺服器角色的成員以隱含方式持有。
AdventureWorks 資料庫必須安裝在您所使用的 SQL Server 執行個體上。
如果您不是正在使用之 SQL Server 執行個體的管理員,則必須讓管理員授與您 CreateAssembly 權限來完成安裝。
建立範例
使用下列指示來建立並執行範例:
開啟 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
範例程式碼
下面是此範例的程式碼清單。
C#
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