|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3
|
|
|
4 using NUnit.Framework;
|
|
|
5
|
|
|
6 using BLToolkit.Data;
|
|
|
7 using BLToolkit.Mapping;
|
|
|
8
|
|
|
9 namespace HowTo.Data
|
|
|
10 {
|
|
|
11 using DataAccess;
|
|
|
12
|
|
|
13 [TestFixture]
|
|
|
14 public class SetCommand
|
|
|
15 {
|
|
|
16 // Select a person list.
|
|
|
17 //
|
|
|
18 public IList<Person> GetPersonList()
|
|
|
19 {
|
|
|
20 using (DbManager db = new DbManager())
|
|
|
21 {
|
|
|
22 return db
|
|
|
23 ./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person")
|
|
|
24 .ExecuteList<Person>();
|
|
|
25 }
|
|
|
26 }
|
|
|
27
|
|
|
28 [Test]
|
|
|
29 public void Test1()
|
|
|
30 {
|
|
|
31 IList<Person> list = GetPersonList();
|
|
|
32
|
|
|
33 Assert.AreNotEqual(0, list.Count);
|
|
|
34 }
|
|
|
35
|
|
|
36 // Select a person.
|
|
|
37 //
|
|
|
38 public Person GetPersonByID(int id)
|
|
|
39 {
|
|
|
40 using (DbManager db = new DbManager())
|
|
|
41 {
|
|
|
42 return db
|
|
|
43 ./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person WHERE PersonID = @id",
|
|
|
44 db./*[a]*/Parameter/*[/a]*/("@id", id))
|
|
|
45 .ExecuteObject<Person>();
|
|
|
46 }
|
|
|
47 }
|
|
|
48
|
|
|
49 [Test]
|
|
|
50 public void Test2()
|
|
|
51 {
|
|
|
52 Person person = GetPersonByID(1);
|
|
|
53
|
|
|
54 Assert.IsNotNull(person);
|
|
|
55 }
|
|
|
56
|
|
|
57 // Insert, Update, and Delete a person.
|
|
|
58 //
|
|
|
59 public Person GetPersonByID(DbManager db, int id)
|
|
|
60 {
|
|
|
61 return db
|
|
|
62 ./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person WHERE PersonID = @id",
|
|
|
63 db./*[a]*/Parameter/*[/a]*/("@id", id))
|
|
|
64 .ExecuteObject<Person>();
|
|
|
65 }
|
|
|
66
|
|
|
67 public Person CreatePerson(DbManager db)
|
|
|
68 {
|
|
|
69 int id = db
|
|
|
70 ./*[a]*/SetCommand/*[/a]*/(@"
|
|
|
71 INSERT INTO Person ( LastName, FirstName, Gender)
|
|
|
72 VALUES (@LastName, @FirstName, @Gender)
|
|
|
73
|
|
|
74 SELECT Cast(SCOPE_IDENTITY() as int) PersonID",
|
|
|
75 db./*[a]*/Parameter/*[/a]*/("@LastName", "Frog"),
|
|
|
76 db./*[a]*/Parameter/*[/a]*/("@FirstName", "Crazy"),
|
|
|
77 db./*[a]*/Parameter/*[/a]*/("@Gender", Map.EnumToValue(Gender.Male)))
|
|
|
78 .ExecuteScalar<int>();
|
|
|
79
|
|
|
80 return GetPersonByID(db, id);
|
|
|
81 }
|
|
|
82
|
|
|
83 public Person UpdatePerson(DbManager db, Person person)
|
|
|
84 {
|
|
|
85 db
|
|
|
86 ./*[a]*/SetCommand/*[/a]*/(@"
|
|
|
87 UPDATE
|
|
|
88 Person
|
|
|
89 SET
|
|
|
90 LastName = @LastName,
|
|
|
91 FirstName = @FirstName,
|
|
|
92 Gender = @Gender
|
|
|
93 WHERE
|
|
|
94 PersonID = @PersonID",
|
|
|
95 db./*[a]*/CreateParameters/*[/a]*/(person))
|
|
|
96 .ExecuteNonQuery();
|
|
|
97
|
|
|
98 return GetPersonByID(db, person.ID);
|
|
|
99 }
|
|
|
100
|
|
|
101 public Person DeletePerson(DbManager db, Person person)
|
|
|
102 {
|
|
|
103 db
|
|
|
104 ./*[a]*/SetCommand/*[/a]*/("DELETE FROM Person WHERE PersonID = @id",
|
|
|
105 db./*[a]*/Parameter/*[/a]*/("@id", person.ID))
|
|
|
106 .ExecuteNonQuery();
|
|
|
107
|
|
|
108 return GetPersonByID(db, person.ID);
|
|
|
109 }
|
|
|
110
|
|
|
111 [Test]
|
|
|
112 public void Test3()
|
|
|
113 {
|
|
|
114 using (DbManager db = new DbManager())
|
|
|
115 {
|
|
|
116 db.BeginTransaction();
|
|
|
117
|
|
|
118 // Insert.
|
|
|
119 //
|
|
|
120 Person person = CreatePerson(db);
|
|
|
121
|
|
|
122 Assert.IsNotNull(person);
|
|
|
123
|
|
|
124 // Update.
|
|
|
125 //
|
|
|
126 Assert.AreEqual(Gender.Male, person.Gender);
|
|
|
127
|
|
|
128 person.Gender = Gender.Female;
|
|
|
129
|
|
|
130 person = UpdatePerson(db, person);
|
|
|
131
|
|
|
132 Assert.AreEqual(Gender.Female, person.Gender);
|
|
|
133
|
|
|
134 // Delete.
|
|
|
135 //
|
|
|
136 person = DeletePerson(db, person);
|
|
|
137
|
|
|
138 Assert.IsNull(person);
|
|
|
139
|
|
|
140 db.CommitTransaction();
|
|
|
141 }
|
|
|
142 }
|
|
|
143 }
|
|
|
144 }
|
