msSQL使用表参数

使用表参数 
 表变量（Table Parameters）可以将整个表数据汇集成一个参数传递给存储过程或SQL语句。它的注意性能开销是将数据汇集成参数(O(数据量))。 
定义了一个表参数jk_users_bulk_insert

CREATE TYPE jk_users_bulk_insert AS TABLE ( 
user_login varchar(60), 
user_pass varchar(64), 
user_nicename varchar(50), 
user_email varchar(100), 
user_url varchar(100), 
user_activation_key varchar(60), 
user_status int, 
display_name varchar(250) 
) 
 定义一个存储过程接受表参数jk_users_bulk_insert  

  
CREATE PROCEDURE sp_insert_jk_users 
@usersTable jk_users_bulk_insert READONLY 
AS 

INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, user_registered) 

SELECT user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, GETDATE() 
FROM @usersTable

 客户端代码中，调用存储过程并且将表作为参数方式传递给存储过程。 

 
var sw = Stopwatch.StartNew(); 
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString())) 
{ 
conn.Open(); 
//// Invokes the stored procedure. 
using (var cmd = new SqlCommand("sp_insert_jk_users", conn)) 
{ 
cmd.CommandType = CommandType.StoredProcedure; 

//// Adding a "structured" parameter allows you to insert tons of data with low overhead 
var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt }; 
cmd.Parameters.Add(param); 
cmd.ExecuteNonQuery(); 
} 
} 
sw.Stop();