|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3
|
|
|
4 using NUnit.Framework;
|
|
|
5
|
|
|
6 using BLToolkit.Data;
|
|
|
7
|
|
|
8 namespace HowTo.Data
|
|
|
9 {
|
|
|
10 using DataAccess;
|
|
|
11
|
|
|
12 [TestFixture]
|
|
|
13 public class Parameter
|
|
|
14 {
|
|
|
15 [Test]
|
|
|
16 public void AssignParameterTest()
|
|
|
17 {
|
|
|
18 using (DbManager db = new DbManager())
|
|
|
19 {
|
|
|
20 int n = db
|
|
|
21 .SetCommand("SELECT @par1 + @par2",
|
|
|
22 db./*[a]*/Parameter/*[/a]*/("@par1", 2),
|
|
|
23 db./*[a]*/Parameter/*[/a]*/("@par2", 2))
|
|
|
24 .ExecuteScalar<int>();
|
|
|
25
|
|
|
26 Assert.AreEqual(4, n);
|
|
|
27 }
|
|
|
28 }
|
|
|
29
|
|
|
30 [Test]
|
|
|
31 public void SetValueTest()
|
|
|
32 {
|
|
|
33 using (DbManager db = new DbManager())
|
|
|
34 {
|
|
|
35 db.SetCommand("SELECT @par * 2",
|
|
|
36 db./*[a]*/Parameter/*[/a]*/("@par", DbType.Int32));
|
|
|
37
|
|
|
38 db./*[a]*/Parameter("@par").Value/*[/a]*/ = 2;
|
|
|
39
|
|
|
40 Assert.AreEqual(4, db.ExecuteScalar<int>());
|
|
|
41 }
|
|
|
42 }
|
|
|
43
|
|
|
44 [Test]
|
|
|
45 public void ReturnValueTest()
|
|
|
46 {
|
|
|
47 using (DbManager db = new DbManager())
|
|
|
48 {
|
|
|
49 /*
|
|
|
50 * CREATE Function Scalar_ReturnParameter()
|
|
|
51 * RETURNS int
|
|
|
52 * AS
|
|
|
53 * BEGIN
|
|
|
54 * RETURN 12345
|
|
|
55 * END
|
|
|
56 */
|
|
|
57 db
|
|
|
58 .SetSpCommand("Scalar_ReturnParameter")
|
|
|
59 .ExecuteNonQuery();
|
|
|
60
|
|
|
61 int n = (int)db./*[a]*/Parameter("@RETURN_VALUE").Value/*[/a]*/;
|
|
|
62
|
|
|
63 Assert.AreEqual(12345, n);
|
|
|
64 }
|
|
|
65 }
|
|
|
66
|
|
|
67 [Test]
|
|
|
68 public void ReturnValueTest2()
|
|
|
69 {
|
|
|
70 using (DbManager db = new DbManager())
|
|
|
71 {
|
|
|
72 int n = db
|
|
|
73 .SetSpCommand("Scalar_ReturnParameter")
|
|
|
74 .ExecuteScalar<int>(ScalarSourceType.ReturnValue);
|
|
|
75
|
|
|
76 Assert.AreEqual(12345, n);
|
|
|
77 }
|
|
|
78 }
|
|
|
79
|
|
|
80 [Test]
|
|
|
81 public void OutputParameterTest()
|
|
|
82 {
|
|
|
83 using (DbManager db = new DbManager())
|
|
|
84 {
|
|
|
85 /*
|
|
|
86 * CREATE Procedure Scalar_OutputParameter
|
|
|
87 * @outputInt int = 0 output,
|
|
|
88 * @outputString varchar(50) = '' output
|
|
|
89 * AS
|
|
|
90 * BEGIN
|
|
|
91 * SET @outputInt = 12345
|
|
|
92 * SET @outputString = '54321'
|
|
|
93 * END
|
|
|
94 */
|
|
|
95
|
|
|
96 db
|
|
|
97 .SetSpCommand("Scalar_OutputParameter",
|
|
|
98 db./*[a]*/OutputParameter/*[/a]*/("@outputInt", 1),
|
|
|
99 db./*[a]*/OutputParameter/*[/a]*/("@outputString", "1"))
|
|
|
100 .ExecuteNonQuery();
|
|
|
101
|
|
|
102 Assert.AreEqual(12345, (int) db./*[a]*/Parameter("@outputInt"). Value/*[/a]*/);
|
|
|
103 Assert.AreEqual("54321", (string)db./*[a]*/Parameter("@outputString").Value/*[/a]*/);
|
|
|
104 }
|
|
|
105 }
|
|
|
106
|
|
|
107 [Test]
|
|
|
108 public void OutputParameterAsReturnValueTest()
|
|
|
109 {
|
|
|
110 using (DbManager db = new DbManager())
|
|
|
111 {
|
|
|
112 string returnValue = db
|
|
|
113 .SetSpCommand("Scalar_OutputParameter")
|
|
|
114 .ExecuteScalar<string>(/*[a]*/ScalarSourceType.OutputParameter/*[/a]*/, /*[a]*/"outputString"/*[/a]*/);
|
|
|
115
|
|
|
116 Assert.AreEqual("54321", returnValue);
|
|
|
117 }
|
|
|
118 }
|
|
|
119
|
|
|
120 [Test]
|
|
|
121 public void CreateParametersTest()
|
|
|
122 {
|
|
|
123 Person person = new Person();
|
|
|
124
|
|
|
125 person.FirstName = "John";
|
|
|
126 person.LastName = "Smith";
|
|
|
127 person.Gender = Gender.Male;
|
|
|
128
|
|
|
129 using (DbManager db = new DbManager())
|
|
|
130 {
|
|
|
131 db.BeginTransaction();
|
|
|
132
|
|
|
133 // Prepare command.
|
|
|
134 //
|
|
|
135 int id = db
|
|
|
136 .SetSpCommand("Person_Insert",
|
|
|
137 db./*[a]*/CreateParameters/*[/a]*/(person))
|
|
|
138 .ExecuteScalar<int>();
|
|
|
139
|
|
|
140 // Check the result.
|
|
|
141 //
|
|
|
142 person = db
|
|
|
143 .SetCommand(
|
|
|
144 "SELECT * FROM Person WHERE PersonID = @id",
|
|
|
145 db.Parameter("@id", id))
|
|
|
146 .ExecuteObject<Person>();
|
|
|
147
|
|
|
148 Assert.IsNotNull(person);
|
|
|
149
|
|
|
150 // Cleanup.
|
|
|
151 //
|
|
|
152 db
|
|
|
153 .SetCommand(
|
|
|
154 "DELETE FROM Person WHERE PersonID = @id",
|
|
|
155 db.Parameter("@id", id))
|
|
|
156 .ExecuteNonQuery();
|
|
|
157
|
|
|
158 db.CommitTransaction();
|
|
|
159 }
|
|
|
160 }
|
|
|
161 }
|
|
|
162 }
|
