Using Table value parameters in Stored Procedures (C #)

1 /// <summary>
2 // C # demo using the Stored Procedure Table value Parameter
3 /// </summary>
4 public class Structured
5 {
6 /// <summary>
7 // data
8 /// </summary>
9
10 public String [] Ary = new String [] {"DATA 0", "data 1", "Data 2", "data 3", "data 4 ", "data 5", "data 6", "data 7", "data 8", "Data 9 "};
11
12 /// <summary>
13 // table parameters
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 // construct 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 // Stored Procedure
51 /// </summary>
52 public String StoredProcedure ()
53
54 {
55
56 // check whether the data exists www.2cto.com
57
58 if (Ary = null) return "data does not exist ";
59
60 var ret = "";
61
62 var strConnection = System. Configuration. ConfigurationManager. deleetpipeline ["link string"]. 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 // set the parameter value
75 p. Value = GetTabValue ();
76 // set the Data Type of the Table value Parameter
77 p. TypeName = "dbo. TabType ";
78 cm. Parameters. Add (p );
79 // Return Value
80 cm. Parameters. Add ("@ RETURN_VALUE", SqlDbType. Int). Direction = ParameterDirection. ReturnValue;
81
82 cm. ExecuteNonQuery ();
83
84 // obtain the returned value
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 of Table value Parameter Storage Process
5
6 -- ============================================== ========
7
8 create procedure [dbo]. [p_demo_add]
9
10 @ demo TabType readonly, -- use the table value parameter. The parameter must be set to readonly.
11
12
13
14 -- add operation 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: custom table Type
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

From dancing