Continuation of part 3a
Code sample 2: Lookup Key Table (locations)
Lookup Key tables are very similar to Lookup Tables except that when you access them, you normally pass in a foreign key from a parent table. That is to say the data in the table is 99.9% of the time only useful as a subset. In the last post, I mentioned how in Easy Assets .NET users can define three levels of information about the physical location of an asset. The top level is referred to as a site. If you were managing a school district, a site would be the most general location, in this case I'll use the High School as the site. The next level, which I will post today, is a location. If the high school is the site, then locations within that site could be the Gymnasium, Auditorium, Administrative building, English building, etc.
First we have to build our locations collection:
1 using System;
2 using System.Collections;
3 using System.Data;
4 using System.Data.SqlClient;
5 using Microsoft.ApplicationBlocks.Data;
6
7 namespace EasyAssets.DAC
8 {
9 /// <summary>
10 /// Summary description for Locations.
11 /// </summary>
12 public class Locations : CollectionBase
13 {
14 private string _connectstring;
15
16 public Locations(string connectstring)
17 {
18 _connectstring = connectstring;
19 }
20
21 public int Add(Location location)
22 {
23 return List.Add(location);
24 }
25
26 public void Remove(Location location)
27 {
28 List.Remove(location);
29 }
30
31 public Location this[int index]
32 {
33 get
34 {
35 return((Location)List[index]);
36 }
37 set
38 {
39 List[index] = value;
40 }
41 }
42
43 public void GetList(int siteid)
44 {
45 SqlParameter[] sqlparams = new SqlParameter[1];
46
47 SqlParameter param = new SqlParameter("@SiteID", SqlDbType.Int);
48 param.Value = siteid;
49 sqlparams[0] = param;
50
51 using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_location_getlist", sqlparams))
52 {
53 while(dr.Read())
54 {
55 Location l = new Location();
56 l.LocationID = (int)dr["LocationID"];
57 l.LocationDescription = dr["LocationDescription"].ToString();
58 l.SiteID = siteid;
59 this.Add(l);
60 }
61 }
62 }
63 }
64 }
Notice that the only real difference between this collection and the sites collection is that GetList() takes a parameter of site id. This of course could be overloaded with a GetList() with no parameters if I ever needed to load all locations regardless of site.
The location object should be nothing new at this point, but I'll post it anyway.
1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using Microsoft.ApplicationBlocks.Data;
5
6 namespace EasyAssets.DAC
7 {
8 /// <summary>
9 /// Summary description for Location.
10 /// </summary>
11 public class Location
12 {
13 #region private members
14
15 //property accessors
16 private int _locationid = -1;
17 private int _siteid;
18 private string _locationdescription;
19
20 //Database key
21 private string _connectstring;
22
23 #endregion private members
24
25 #region Properties
26
27 public int LocationID
28 {
29 get
30 {
31 return _locationid;
32 }
33 set
34 {
35 _locationid = value;
36 }
37 }
38
39 public int SiteID
40 {
41 get
42 {
43 return _siteid;
44 }
45 set
46 {
47 _siteid = value;
48 }
49 }
50
51 public string LocationDescription
52 {
53 get
54 {
55 return _locationdescription;
56 }
57 set
58 {
59 _locationdescription = value;
60 }
61 }
62
63 #endregion Properties
64
65 public Location()
66 {
67
68 }
69
70 /// <summary>
71 /// Creates a new location
72 /// </summary>
73 /// <param name="connectstring">database connection string</param>
74 public Location(string connectstring)
75 {
76 _connectstring = connectstring;
77 }
78
79 /// <summary>
80 /// Loads a location by id
81 /// </summary>
82 /// <param name="id">pk</param>
83 /// <param name="connectstring">database connection string</param>
84 public Location(int id, string connectstring)
85 {
86 _connectstring = connectstring;
87
88 SqlParameter[] sqlparams = new SqlParameter[1];
89
90 SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);
91 param.Value = id;
92 sqlparams[0] = param;
93
94 using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_location_get", sqlparams))
95 {
96 if(dr.Read())
97 {
98 _locationid = dr["LocationID"] == DBNull.Value ? -1 : (int)dr["LocationID"];
99 _siteid = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];
100 _locationdescription = dr["LocationDescription"] == DBNull.Value ? "" : dr["LocationDescription"].ToString();
101 }
102 else
103 throw new Exception("Location ID not found!");
104 }
105 }
106
107 /// <summary>
108 /// Does insert if key is -1 (default)
109 /// Updates if key is not default.
110 /// </summary>
111 public void Save()
112 {
113 if(_locationid == -1)
114 {
115 //Do Insert
116 SqlParameter[] sqlparams = new SqlParameter[2];
117
118 SqlParameter param = new SqlParameter("@LocationDescription", SqlDbType.NVarChar);
119 if(_locationdescription != "") param.Value = _locationdescription.Replace("'","''");
120 else param.Value = DBNull.Value;
121 sqlparams[0] = param;
122
123 param = new SqlParameter("@SiteID", SqlDbType.Int);
124 if(_siteid != -1) param.Value = _siteid;
125 else param.Value = DBNull.Value;
126 sqlparams[1] = param;
127
128 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_insert", sqlparams) == -1)
129 throw new Exception("Location create failed!");
130 }
131 else
132 {
133 //Do Update
134 SqlParameter[] sqlparams = new SqlParameter[2];
135
136 SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);
137 if(_locationid != -1) param.Value = _locationid;
138 else param.Value = DBNull.Value;
139 sqlparams[0] = param;
140
141 param = new SqlParameter("@LocationDescription", SqlDbType.NVarChar);
142 if(_locationdescription != "") param.Value = _locationdescription.Replace("'","''");
143 else param.Value = DBNull.Value;
144 sqlparams[1] = param;
145
146 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_update", sqlparams) == -1)
147 throw new Exception("Location update failed!");
148 }
149 }
150
151 public void Delete()
152 {
153 SqlParameter[] sqlparams = new SqlParameter[1];
154
155 SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);
156 param.Value = _locationid;
157 sqlparams[0] = param;
158
159 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_delete", sqlparams) == -1)
160 throw new Exception("Location delete failed!");
161 }
162 }
163 }
In part 3c, I will introduce you to an object that can search on multiple fields.