存储过程中使用表值参数（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 //检查数据是否存在www.zzzyk.com
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
摘自 跳舞
补充：软件开发 , C# ,
