存储过程中使用表值参数（C#）

 1     /// <summary>
 2 /// C#使用存储过程表值参数demo
 3 /// </summary>
 4     public class Structured
 5     {
 6         /// <summary>
 7 /// 数据
 8 /// </summary>
 9 
10         public String[] Ary = new String[] { "数据0", "数据1", "数据2", "数据3", "数据4", "数据5", "数据6", "数据7", "数据8", "数据9" };
11 
12         /// <summary>
13 /// 表参数
14 /// </summary>
15 
16         private static SqlMetaData[] MetaData = new SqlMetaData[] { new SqlMetaData("column1", SqlDbType.Int), new SqlMetaData("column2", SqlDbType.NVarChar, 20) };
17 
18         /// <summary>
19 /// 构造IList
20 /// </summary>
21 /// <returns></returns>
22         private IList<SqlDataRecord> GetTabValue()
23 
24         {
25 
26             List<SqlDataRecord> r = new List<SqlDataRecord>();
27 
28 
29 
30 
31             for (var i = 0; i < Ary.Length; i++)
32 
33             {
34 
35                 var record = new SqlDataRecord(MetaData);
36 
37                 record.SetInt32(0, i);
38 
39                 record.SetString(1, Ary[i]);
40 
41                 r.Add(record);
42 
43             }
44 
45             return r;
46 
47         }
48 
49         /// <summary>
50 /// 存储过程
51 /// </summary>
52         public String StoredProcedure()
53 
54         {
55 
56             //检查数据是否存在
57 
58             if (Ary == null) return "数据不存在";
59 
60             var ret = "";
61 
62             var strConnection=System.Configuration.ConfigurationManager.AppSettings["链接字符串"].ToString();
63 
64             SqlConnection cn = new SqlConnection(strConnection);
65 
66             SqlCommand cm = new SqlCommand("p_demo_add", cn);
67 
68             try
69             {
70                 cm.CommandType = CommandType.StoredProcedure;
71 
72                 SqlParameter p = new SqlParameter("@demo", SqlDbType.Structured);
73 
74                 //设置参数的值
75                 p.Value = GetTabValue();
76                 //设置表值参数的数据类型
77                 p.TypeName = "dbo.TabType";
78                 cm.Parameters.Add(p);
79                 //返回值
80                 cm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
81 
82                 cm.ExecuteNonQuery();
83 
84                 //获取返回值
85 
86                 ret = cm.Parameters["@RETURN_VALUE"].Value.ToString();
87 
88             }
89 
90             finally
91             {
92                 cm.Connection.Close();
93             }
94             return ret;
95         }
96     }

 1 -- =============================================
 2 
 3 
 4 -- Description:表值参数存数过程demo
 5 
 6 -- =============================================
 7 
 8 CREATE PROCEDURE [dbo].[p_demo_add]
 9 
10 @demo TabType readonly,--使用表值参数，参数必须设置readonly
11 
12 AS
13 
14 -- 添加操作demo
15 
16 insert into [Table](column1,column2) select t.column1,t.column1 from @demo as t
17 
18 
19 
20 
21 GO
22 
23 -- =============================================
24 
25 -- Description:自定义表类型
26 
27 -- =============================================
28 
29 create type [dbo].[TabType] as table(
30 
31 [column1] [int] not null,
32 
33 [column2] [nvarchar](20) not null
34 
35 )
36 
37 go

转载于:https://www.cnblogs.com/qingiqng99/archive/2012/02/28/2370984.html