--创建测试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();
}
}
}