要操作数据库，当然少不了增删改查了。
首先新建一个Person的实体类：
public class Person { private int id; private string username; private string password; private int age; public int Id { get { return id; } set { id = value; } } public string Username { get { return username; } set { username = value; } } public string Password { get { return password; } set { password = value; } } public int Age { get { return age; } set { age = value; } } }
接下来新建一个对数据库操作类：
1: public void Insert(Person person)
2: {
3: string sql = "insert into person(username,password,age) values(@username,@password,@age)";
4:
5: SqlConnection conn = Connection.GetConnection();
6:
7: SqlCommand command = new SqlCommand(sql,conn);
8:
9: command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar));
10: command.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar));
11: command.Parameters.Add(new SqlParameter("@age",SqlDbType.Int));
12:
13: command.Parameters["@username"].Value = person.Username;
14: command.Parameters["@password"].Value = person.Password;
15: command.Parameters["@age"].Value = person.Age;
16:
17: try
18: {
19: command.ExecuteNonQuery();
20: }
21: catch (Exception ex)
22: {
23: Console.WriteLine(ex.Message);
24: }
25: finally
26: {
27: conn.Close();
28: }
29: }
30:
31: public void Update(Person person)
32: {
33: string sql = "update person set username=@username,password=@password,age=@age where id=@id";
34:
35: SqlConnection conn = Connection.GetConnection();
36:
37: SqlCommand command = new SqlCommand(sql,conn);
38:
39: command.Parameters.Add(new SqlParameter("@username",SqlDbType.VarChar));
40: command.Parameters.Add(new SqlParameter("@password",SqlDbType.VarChar));
41: command.Parameters.Add(new SqlParameter("@age",SqlDbType.Int));
42: command.Parameters.Add(new SqlParameter("@id",SqlDbType.Int));
43:
44: command.Parameters["@username"].Value = person.Username;
45: command.Parameters["@password"].Value = person.Password;
46: command.Parameters["@age"].Value = person.Age;
47: command.Parameters["@id"].Value = person.Id;
48:
49: try
50: {
51: command.ExecuteNonQuery();
52: }
53: catch (Exception ex)
54: {
55: Console.WriteLine(ex.Message);
56: }
57: finally
58: {
59: conn.Close();
60: }
61: }
62:
63: public Person GetById(int id)
64: {
65: string sql = "select * from person where id = @id";
66:
67: SqlConnection conn = Connection.GetConnection();
68:
69: SqlCommand command = new SqlCommand(sql,conn);
70:
71: command.Parameters.Add(new SqlParameter("@id",SqlDbType.Int));
72:
73: command.Parameters["@id"].Value = id;
74:
75: SqlDataReader reader = command.ExecuteReader();
76:
77: Person person = null;
78:
79: if (reader.Read())
80: {
81: person = new Person();
82:
83: person.Id = id;
84: person.Username = reader["username"].ToString();
85: person.Password = reader["password"].ToString();
86: person.Age = Convert.ToInt32(reader["age"]);
87: }
88:
89: reader.Close();
90: conn.Close();
91:
92: return person;
93: }
94:
95: public void RemoveById(int id)
96: {
97: string sql = "delete from person where id = @id";
98:
99: SqlConnection conn = Connection.GetConnection();
100:
101: SqlCommand command = new SqlCommand(sql,conn);
102:
103: command.Parameters.Add(new SqlParameter("@id",SqlDbType.Int));
104:
105: command.Parameters["@id"].Value = id;
106:
107: try
108: {
109: command.ExecuteNonQuery();
110: }
111: catch (Exception ex)
112: {
113: Console.WriteLine(ex.Message);
114: }
115: finally
116: {
117: conn.Close();
118: }
119: }
接下来当然是要对我们这个数据操作类进行测试咯。
新建测试类 DBPersonTest.cs
private DBPerson dbPerson; [TestFixtureSetUp] public void Init() { dbPerson = new DBPerson(); }
在TestFixtureSetUp 中新建DBPerson对象。
下面我们首先测试 Insert 方法吧。
怎么测试Insert操作呢，要查看我们要插入的数据是否插入数据库成功当然是要看数据库中有没有这个数据了。
在前面我们建的数据库person表中ID是自动增长的，所以我们需要添加一个获取最大值的方法
private int GetMaxId() { string sql = "select max(id) as maxId from person"; SqlConnection conn = Connection.GetConnection(); SqlCommand command = new SqlCommand(sql,conn); SqlDataReader reader = command.ExecuteReader(); int maxId = 0; if (reader.Read()) { maxId = Convert.ToInt32(reader["maxId"]); } reader.Close(); conn.Close(); return maxId; }
接下来我们来测试插入方法：
[Test]
public void TestInsert()
{
Person person = new Person();
person.Username = "zhangsan";
person.Password = "123456";
person.Age = 30;
dbPerson.Insert(person);
int maxId = this.GetMaxId();
person.Id = maxId;
Person person2 = dbPerson.GetById(maxId);
this.Compare(person,person2); //做完测试后记得删除数据库中的数据 dbPerson.RemoveById(maxId); }
this.Compare方法是用来比较获取的Person对象和我们期望的Person对象是不是一致：
private void Compare(Person person1, Person person2) { Assert.AreEqual(person1.Id, person2.Id); Assert.AreEqual(person1.Username, person2.Username); Assert.AreEqual(person1.Password, person2.Password); Assert.AreEqual(person1.Age, person2.Age); }
在测试的过程中，我们是不能更改数据库的数据的，以免无意影响到项目。所以我们在测试中做了什么更改都要在测试后更改回去……保持测试前的数据库。
其他的数据库操作与此测试好像也类似就不在多言。贴出测试代码：
更新测试：
[Test] public void TestUpdate() { Person person = new Person(); person.Username = "zhangsan"; person.Password = "123456"; person.Age = 10; dbPerson.Insert(person); int maxId = this.GetMaxId(); Person person2 = dbPerson.GetById(maxId); person2.Username = "lisi"; person2.Password = "654321"; person2.Age = 30; dbPerson.Update(person2); Person person3 = dbPerson.GetById(maxId); this.Compare(person2,person3); //clear db dbPerson.RemoveById(maxId); }
获取数据测试：
[Test] public void TestGetById() { Person person = new Person(); person.Username = "zhangsan"; person.Password = "123456"; person.Age = 10; dbPerson.Insert(person); int maxId = this.GetMaxId(); person.Id = maxId; Person person2 = dbPerson.GetById(maxId); this.Compare(person,person2); //clear db dbPerson.RemoveById(maxId); }
删除数据测试：
[Test] public void TestRemoveById() { Person person = new Person(); person.Username = "zhangsan"; person.Password = "123456"; person.Age = 10; dbPerson.Insert(person); int maxId = this.GetMaxId(); dbPerson.RemoveById(maxId); Person person2 = dbPerson.GetById(maxId); Assert.IsNull(person2); }