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.2cto.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
摘自 跳舞
