-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();
}
}
}
--测试结果: