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

--打开Visual Studio—创建项目—选择【控制台应用程序】

 

 

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();  
        }
    }
}

--测试结果: