|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3 using System.Configuration;
|
|
|
4 using System.Data.SqlClient;
|
|
|
5
|
|
|
6 using NUnit.Framework;
|
|
|
7
|
|
|
8 namespace HowTo.Data
|
|
|
9 {
|
|
|
10 [TestFixture]
|
|
|
11 public class AdoDemo
|
|
|
12 {
|
|
|
13 // Typified definition of the Gender database field.
|
|
|
14 //
|
|
|
15 public enum Gender
|
|
|
16 {
|
|
|
17 Female,
|
|
|
18 Male,
|
|
|
19 Unknown,
|
|
|
20 Other
|
|
|
21 }
|
|
|
22
|
|
|
23 // Business object.
|
|
|
24 //
|
|
|
25 public class Person
|
|
|
26 {
|
|
|
27 public int ID { get; set; }
|
|
|
28 public string FirstName { get; set; }
|
|
|
29 public string MiddleName { get; set; }
|
|
|
30 public string LastName { get; set; }
|
|
|
31 public /*[a]*/Gender/*[/a]*/ Gender { get; set; }
|
|
|
32 }
|
|
|
33
|
|
|
34 // ADO.NET data access method.
|
|
|
35 //
|
|
|
36 public List<Person> /*[a]*/GetList/*[/a]*/(Gender gender)
|
|
|
37 {
|
|
|
38 // Map the typified parameter value to its database representation.
|
|
|
39 //
|
|
|
40 string paramValue = "";
|
|
|
41
|
|
|
42 switch (gender)
|
|
|
43 {
|
|
|
44 case Gender.Female: paramValue = "F"; break;
|
|
|
45 case Gender.Male: paramValue = "M"; break;
|
|
|
46 case Gender.Unknown: paramValue = "U"; break;
|
|
|
47 case Gender.Other: paramValue = "O"; break;
|
|
|
48 }
|
|
|
49
|
|
|
50 // Read a database configuration string.
|
|
|
51 //
|
|
|
52 string cs = ConfigurationManager.ConnectionStrings["DemoConnection"].ConnectionString;
|
|
|
53
|
|
|
54 // Create and open a database connection.
|
|
|
55 //
|
|
|
56 using (SqlConnection con = new SqlConnection(cs))
|
|
|
57 {
|
|
|
58 con.Open();
|
|
|
59
|
|
|
60 // Create and initialize a Command object.
|
|
|
61 //
|
|
|
62 using (SqlCommand cmd = con.CreateCommand())
|
|
|
63 {
|
|
|
64 cmd.CommandText = "SELECT * FROM Person WHERE Gender = @gender";
|
|
|
65 cmd.Parameters.AddWithValue("@gender", paramValue);
|
|
|
66
|
|
|
67 // Execute query.
|
|
|
68 //
|
|
|
69 using (SqlDataReader rd = cmd.ExecuteReader())
|
|
|
70 {
|
|
|
71 List<Person> list = new List<Person>();
|
|
|
72
|
|
|
73 while (rd.Read())
|
|
|
74 {
|
|
|
75 Person person = new Person();
|
|
|
76
|
|
|
77 // Map a data reader row to a business object.
|
|
|
78 //
|
|
|
79 person.ID = Convert.ToInt32 (rd["PersonID"]);
|
|
|
80 person.FirstName = Convert.ToString(rd["FirstName"]);
|
|
|
81 person.MiddleName = Convert.ToString(rd["MiddleName"]);
|
|
|
82 person.LastName = Convert.ToString(rd["LastName"]);
|
|
|
83
|
|
|
84 switch (rd["Gender"].ToString())
|
|
|
85 {
|
|
|
86 case "F": person.Gender = Gender.Female; break;
|
|
|
87 case "M": person.Gender = Gender.Male; break;
|
|
|
88 case "U": person.Gender = Gender.Unknown; break;
|
|
|
89 case "O": person.Gender = Gender.Other; break;
|
|
|
90 }
|
|
|
91
|
|
|
92 list.Add(person);
|
|
|
93 }
|
|
|
94
|
|
|
95 return list;
|
|
|
96 }
|
|
|
97 }
|
|
|
98 }
|
|
|
99 }
|
|
|
100
|
|
|
101 [Test]
|
|
|
102 public void Test()
|
|
|
103 {
|
|
|
104 List<Person> list = GetList(Gender.Male);
|
|
|
105 Assert.Greater(list.Count, 0);
|
|
|
106 }
|
|
|
107 }
|
|
|
108 }
|
