1 使用表参数
2 表变量（Table Parameters）可以将整个表数据汇集成一个参数传递给存储过程或SQL语句。它的注意性能开销是将数据汇集成参数(O(数据量))。
3 定义了一个表参数jk_users_bulk_insert
4
5 CREATE TYPE jk_users_bulk_insert AS TABLE (
6 user_login varchar(60),
7 user_pass varchar(64),
8 user_nicename varchar(50),
9 user_email varchar(100),
10 user_url varchar(100),
11 user_activation_key varchar(60),
12 user_status int,
13 display_name varchar(250)
14 )
15 定义一个存储过程接受表参数jk_users_bulk_insert
16
17
18 CREATE PROCEDURE sp_insert_jk_users
19 @usersTable jk_users_bulk_insert READONLY
20 AS
21
22 INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_url,
23 user_activation_key, user_status, display_name, user_registered)
24
25 SELECT user_login, user_pass, user_nicename, user_email, user_url,
26 user_activation_key, user_status, display_name, GETDATE()
27 FROM @usersTable
28
29 客户端代码中，调用存储过程并且将表作为参数方式传递给存储过程。
30
31
32 var sw = Stopwatch.StartNew();
33 using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
34 {
35 conn.Open();
36 //// Invokes the stored procedure.
37 using (var cmd = new SqlCommand("sp_insert_jk_users", conn))
38 {
39 cmd.CommandType = CommandType.StoredProcedure;
40
41 //// Adding a "structured" parameter allows you to insert tons of data with low overhead
42 var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt };
43 cmd.Parameters.Add(param);
44 cmd.ExecuteNonQuery();
45 }
46 }
47 sw.Stop();
