C#調用自定義表類型參數

C#調用自定義表類型參數

-SQL SERVER生成測試環境:

--創建測試DB
CREATE database Sales;
go
USE Sales
GO
--創建表類型
IF TYPE_ID('LocalDT') IS NOT NULL
	DROP TYPE LocalDT
GO 
CREATE TYPE LocalDT AS TABLE
(
	ID INT NOT NULL,
	Name NVARCHAR(50)
)
GO
--創建存儲過程
IF OBJECT_ID('P_DataTable','P') IS NOT NULL
	DROP PROC P_DataTable;
GO

CREATE PROCEDURE P_DataTable
(
	@LocalDT LocalDT READONLY
)
AS
SELECT * FROM @LocalDT
GO

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ProcDataTable
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable("LocalDT");
            dt.Columns.Add("ID",typeof(int));
            dt.Columns.Add("Name", typeof(string));
            DataRow dr = dt.NewRow();
            dr[0] = 1;
            dr[1] = "Roy";
            dt.Rows.Add(dr);
            SqlConnection thisConnection = new SqlConnection(@"Server=實例名;Database=Sales;User ID=sa;Password=1");
            thisConnection.Open();
            SqlCommand sqlcmd = thisConnection.CreateCommand();
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = "P_DataTable";
            SqlParameter param = sqlcmd.Parameters.AddWithValue("@LocalDT", dt);
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                Console.WriteLine("ID:{0}\tName:{1}",sdr[0],sdr[1]);
            }
            thisConnection.Close();  
            Console.ReadKey();  
        }
    }
}