前言

這是最近工作上的一個問題：如何傳遞 Collection / Array / List 資料給  Stored Procedure。雖然過去曾經請教同事如何實作，了解透過 DataTable 的方式可以達到需求，但先前似乎沒有實作經驗。趁這次工作之餘，以這邊文章做個紀錄，提供未來的自己與有興趣的朋友參考。本篇文章若有錯誤或任何建議，請各位先進不吝指教。



實作環境：
Console Application (.NET Framework 4.6.1)
Entity Framework 6.2.0
Visual Studio 2017


介紹

前置作業
1. 我們的實作是透過 Entity Framework 實作，所以務必確認專案有安裝 Entity Framework

2. 確認有 MS SQL / Azure SQL 資料庫，且能透過帳號密碼驗帳方式登入

3. 確認您的連線字串與 BaseContext 類別建立完整，可以連線到資訊庫
using System.Data.Entity;
using System.Data.SqlClient;
using System.Configuration;
namespace SendCollectionToStoredProcedureExample
{
public class BaseContext: DbContext
{
protected string connectionName;
public BaseContext(string connName = "BaseConnection")
: base(connName)
{
connectionName = connName;
}
public BaseContext setDatabase(string databaseName)
{
var connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
//change the database before creating the new connection
builder.InitialCatalog = databaseName;
string sqlConnectionString = builder.ConnectionString;
return new BaseContext(sqlConnectionString);
}
}
}
view raw BaseContext.cs hosted with ❤ by GitHub

說明
在傳遞集合資料到預存程序之前，我們必須先建立使用者定義資料表類型。透過這個類型作為參數，即可傳遞集合至預存程序：

類型建立語法範例如下， PlayDataType 裡面只包含了一個 Id 資料欄位：
USE [duran]
GO
CREATE TYPE [dbo].[PlayerDataType] AS TABLE(
[Id] [uniqueidentifier] NOT NULL
)
GO
view raw PlayerDataType.sql hosted with ❤ by GitHub

接下來，我們在範例預存程序內的傳入參數加上 PlayDataType 這個類型：
USE [duran]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_PlayerData_GetPlayerData]
@playerDataType PlayerDataType Readonly
AS
BEGIN
SET NOCOUNT ON;
SELECT * INTO #playerDataTypeTemp FROM @playerDataType
SELECT Id, 'TEST' AS [Name]
FROM #playerDataTypeTemp
DROP TABLE #playerDataTypeTemp
END
view raw usp_PlayerData_GetPlayerData.sql hosted with ❤ by GitHub

回到專案，我們撰寫撰寫相關語法呼叫預存程序：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace SendCollectionToStoredProcedureExample
{
class Program
{
static async Task Main(string[] args)
{
MainAsync().Wait();
}
static async Task MainAsync()
{
var context = new BaseContext();
var db = context.setDatabase("duran");
var parameters = new SqlParameter[]
{
new SqlParameter("@playerDataType", SqlDbType.Structured)
{
Value = GetDataTable(),
TypeName = "PlayerDataType"
},
};
var sql = "dbo.usp_PlayerData_GetPlayerData @playerDataType";
var result = await db.Database.SqlQuery<PlayerData>(sql, parameters).ToArrayAsync();
foreach (var item in result)
{
Console.WriteLine($"{item.Id}:{item.Name}");
}
}
private static DataTable GetDataTable()
{
var playerDataType = new DataTable();
playerDataType.Columns.Add("Id", typeof(Guid));
playerDataType.Rows.Add(Guid.NewGuid());
playerDataType.Rows.Add(Guid.NewGuid());
return playerDataType;
}
}
class PlayerData
{
public Guid Id { get; set; }
public string Name { get; set; }
}
}
view raw Program.cs hosted with ❤ by GitHub

如上面程式碼所示，我們做了下列動作：
  1. 透過 SqlParameter 建立傳入參數，並建立一個 DataTable 存放資料，放入這個 SqlParameter，類型為 SqlDbType.Structured
  2. SQL 語法為 var sql = "dbo.usp_PlayerData_GetPlayerData @playerDataType";
  3. 傳回參數資料為 PlayerData 這個類別
  4. 執行 await db.Database.SqlQuery(sql, parameters).ToArrayAsync(); 取得結果

完整程式範例