Revisiting The TypeSafeDataReader: A Full Decorator With Ordinal Caching, etc.
In my previous post on this subject, several people mentioned changing the type safe methods for my data reader class into extension methods on IDataReader. I had planned on doing this, but then I ran into a situation where I needed to implement caching of the column ordinal positions to squeak out just a little bit more performance in a scenario that has 4,000+ queries into the database. Because I needed caching of the column ordinal positions, an extension method set would not cut it (I only want to cache columns for the current data reader, not store a static list of ordinals for all data readers that are used, like an extension method would force me to do). To do this with my TypeSafeDataReader, I had to change it up a bit in terms of how and where it’s used. The changes include turning it into a full decorator pattern and adding the ability to retrieve column information as more than just the raw data in the column, in addition to ability to do the caching of column ordinals when I know that I am looking for more than 1 result, but prevent caching when I know there I am looking for only 1 result.
The ITypeSafeDataReader Interface
I extracted an interface for this, and had it inherit from IDataReader so that I could decorate the IDataReader with my strongly typed methods.
1: public interface ITypeSafeDataReader : IDataReader
2: {
3: ColumnInformation<bool> GetBoolean(string columnName);
4: ColumnInformation<int> GetInt32(string columnName);
5: ColumnInformation<string> GetString(string columnName);
6: ColumnInformation<decimal> GetDecimal(string columnName);
7: ColumnInformation<DateTime> GetDateTime(string columnName);
8: ColumnInformation<T> GetColumnInformation<T>(string columnName);
9: }
</div> </div>
Using Data From The ColumnInformation Class
I use this class to give me all the information I need about a column. For example, there are scenarios where I need to know if the column data is DBNull or not, and do something different depending. The ColumnInformation class gives me a .HasValue property which tells me whether or not the column has a value (a column has a value when the column is not DBNull).
1: public class ColumnInformation<T>
2: {
3: public string ColumnName { get; set; }
4: public int ColumnIndex { get; set; }
5: public bool HasValue { get; set; }
6: public T Value { get; set; }
7:
8: public ColumnInformation(string columnName)
9: {
10: ColumnName = columnName;
11: }
12:
13: public static implicit operator T(ColumnInformation<T> ci)
14: {
15: T value = default(T);
16: if (ci != null && ci.HasValue)
17: value = ci.Value;
18: return value;
19: }
20: }
</div> </div>
Notice that I also supplied an implicit conversion operator to the generics type. This allows me to directly assign a ColumnInformation variable to a property or parameter of the generics type without having to call .Value. For example, assuming a property called SomeInt is an integer value, I can use this code:
1: var myColumn = tsdr.GetInt32("MyColumn");
2: someObject.SomeInt = myColumn;
</div> </div>
This ColumnInformation class also let’s me check for values and do something other than map to a property if I need to. For example, I may not want to set a property at all if a value is null:
1: var childIdColumn = tsdr.GetInt32("ChildIdColumn");
2: if (childIdColumn.HasValue)
3: {
4: someObject.SomeProperty = LoadAChildObjectById(childIdColumn);
5: }
</div> </div>
This would leave the SomeProperty null if the child id column does not have a value (is DBNull).
The TypeSafeDataReaderDecorator
Here’s my current implementation of the type safe data reader decorator.
It takes a standard IDataReader as a constructor parameter along with a boolean value that tells it whether or not to use column ordinal caching.I’ve used a few regions to wrap up the extraneous code that does nothing more than map to the underlying IDataReader calls. This makes it easier to hide that code and focus on the decorations that I’m adding in.
1: public class TypeSafeDataReaderDecorator : ITypeSafeDataReader
2: {
3: private readonly bool _useColumnOrdinalCaching;
4: private readonly IDataReader _dataReader;
5: private readonly IDictionary<string, int> _ordinalCache = new Dictionary<string, int>();
6:
7: public TypeSafeDataReaderDecorator(IDataReader dataReader, bool useColumnOrdinalCaching)
8: {
9: _dataReader = dataReader;
10: _useColumnOrdinalCaching = useColumnOrdinalCaching;
11: }
12:
13: public ColumnInformation<bool> GetBoolean(string columnName)
14: {
15: return GetColumnInformation<bool>(columnName);
16: }
17:
18: public ColumnInformation<int> GetInt32(string columnName)
19: {
20: return GetColumnInformation<int>(columnName);
21: }
22:
23: public ColumnInformation<long> GetInt64(string columnName)
24: {
25: return GetColumnInformation<long>(columnName);
26: }
27:
28: public ColumnInformation<string> GetString(string columnName)
29: {
30: return GetColumnInformation<string>(columnName);
31: }
32:
33: public ColumnInformation<decimal> GetDecimal(string columnName)
34: {
35: return GetColumnInformation<decimal>(columnName);
36: }
37:
38: public ColumnInformation<DateTime> GetDateTime(string columnName)
39: {
40: return GetColumnInformation<DateTime>(columnName);
41: }
42:
43: public ColumnInformation<T> GetColumnInformation<T>(string columnName)
44: {
45: var columnInfo = new ColumnInformation<T>(columnName);
46: columnInfo.ColumnIndex = GetColumnIndex(columnName);
47: if (columnInfo.ColumnIndex > -1)
48: {
49: if (!_dataReader.IsDBNull(columnInfo.ColumnIndex))
50: {
51: columnInfo.HasValue = true;
52: }
53: }
54:
55: if (columnInfo.HasValue)
56: {
57: columnInfo.Value = GetColumnValue(columnInfo);
58: }
59:
60: return columnInfo;
61: }
62:
63: private T GetColumnValue<T>(ColumnInformation<T> columnInfo)
64: {
65: var valueObject = _dataReader.GetValue(columnInfo.ColumnIndex);
66: T value = default(T);
67: if (valueObject != null)
68: value = (T)Convert.ChangeType(valueObject, typeof(T), null);
69: return value;
70: }
71:
72: private int GetColumnIndex(string columnName)
73: {
74: int columnIndex;
75: if (_useColumnOrdinalCaching)
76: {
77: if (_ordinalCache.ContainsKey(columnName))
78: columnIndex = _ordinalCache[columnName];
79: else
80: {
81: columnIndex = _dataReader.GetOrdinal(columnName);
82: _ordinalCache.Add(columnName, columnIndex);
83: }
84: }
85: else
86: {
87: columnIndex = _dataReader.GetOrdinal(columnName);
88: }
89: return columnIndex;
90: }
91:
92: #region Implementation of IDisposable
93:
94: public void Dispose()
95: {
96: _dataReader.Dispose();
97: }
98:
99: #endregion
100:
101: #region Implementation of IDataRecord
102:
103: public string GetName(int i)
104: {
105: return _dataReader.GetName(i);
106: }
107:
108: public string GetDataTypeName(int i)
109: {
110: return _dataReader.GetDataTypeName(i);
111: }
112:
113: public Type GetFieldType(int i)
114: {
115: return _dataReader.GetFieldType(i);
116: }
117:
118: public object GetValue(int i)
119: {
120: return _dataReader.GetValue(i);
121: }
122:
123: public int GetValues(object[] values)
124: {
125: return _dataReader.GetValues(values);
126: }
127:
128: public int GetOrdinal(string name)
129: {
130: return _dataReader.GetOrdinal(name);
131: }
132:
133: public bool GetBoolean(int i)
134: {
135: return _dataReader.GetBoolean(i);
136: }
137:
138: public byte GetByte(int i)
139: {
140: return _dataReader.GetByte(i);
141: }
142:
143: public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
144: {
145: return _dataReader.GetBytes(i, fieldOffset, buffer, bufferoffset, length);
146: }
147:
148: public char GetChar(int i)
149: {
150: return _dataReader.GetChar(i);
151: }
152:
153: public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
154: {
155: return _dataReader.GetChars(i, fieldoffset, buffer, bufferoffset, length);
156: }
157:
158: public Guid GetGuid(int i)
159: {
160: return _dataReader.GetGuid(i);
161: }
162:
163: public short GetInt16(int i)
164: {
165: return _dataReader.GetInt16(i);
166: }
167:
168: public int GetInt32(int i)
169: {
170: return _dataReader.GetInt32(i);
171: }
172:
173: public long GetInt64(int i)
174: {
175: return _dataReader.GetInt64(i);
176: }
177:
178: public float GetFloat(int i)
179: {
180: return _dataReader.GetFloat(i);
181: }
182:
183: public double GetDouble(int i)
184: {
185: return _dataReader.GetDouble(i);
186: }
187:
188: public string GetString(int i)
189: {
190: return _dataReader.GetString(i);
191: }
192:
193: public decimal GetDecimal(int i)
194: {
195: return _dataReader.GetDecimal(i);
196: }
197:
198: public DateTime GetDateTime(int i)
199: {
200: return _dataReader.GetDateTime(i);
201: }
202:
203: public IDataReader GetData(int i)
204: {
205: return _dataReader.GetData(i);
206: }
207:
208: public bool IsDBNull(int i)
209: {
210: return _dataReader.IsDBNull(i);
211: }
212:
213: public int FieldCount
214: {
215: get
216: {
217: return _dataReader.FieldCount;
218: }
219: }
220:
221: object IDataRecord.this[int i]
222: {
223: get { return _dataReader[i]; }
224: }
225:
226: object IDataRecord.this[string name]
227: {
228: get { return _dataReader[name]; }
229: }
230:
231: #endregion
232:
233: #region Implementation of IDataReader
234:
235: public void Close()
236: {
237: _dataReader.Close();
238: }
239:
240: public DataTable GetSchemaTable()
241: {
242: return _dataReader.GetSchemaTable();
243: }
244:
245: public bool NextResult()
246: {
247: return _dataReader.NextResult();
248: }
249:
250: public bool Read()
251: {
252: return _dataReader.Read();
253: }
254:
255: public int Depth
256: {
257: get { return _dataReader.Depth; }
258: }
259:
260: public bool IsClosed
261: {
262: get { return _dataReader.IsClosed; }
263: }
264:
265: public int RecordsAffected
266: {
267: get { return _dataReader.RecordsAffected; }
268: }
269:
270: #endregion
271: }
</div> </div>
My core data access layer has methods that know whether I’m looking for a single result vs a list of results (for example, a method to get one object by id vs a method to get all of the objects for a given type). When the method that returns a single object decorates the IDataReader with my TypeSafeDataReaderDecorator, I pass a value of false into the constructor to tell it not to use column ordinal caching. This will prevent a few extra calls that don’t need to happen when I know it’s only 1 object being read. When the method that returns a list of objects decorates the IDataReader, though, it passes a value of true to the constructor so that the caching will take effect. This prevents the code from having to call GetOridinal on the underlying data reader multiple times for the same column. Note that the interface knows nothing about caching, though. That is purely an implementation concern.
Convenience Methods vs GetColumnInformation
</h3>
The specific method overloads for GetInt32, GetString, GetBoolean, etc. are simple convenience methods that do nothing more than call into the GetColumnInformation method with the correct type. I added the convenience methods for the data types that our code is using the most often. It would be easy to add additional convenience methods as well, or you can just call GetColumnInformation directly, telling it the type of data that you expect to be returned:
</p>
1: int myInt = tsdr.GetColumnInformation<int>("MyIntColumn");
</div> </div>
Use ITypeSafeDataReader In Place Of IDataReader
I’ve updated my data access layer to always use the ITypeSafeDataReader specifically. This lets me have direct access to the extra functionality in the repository implementations without having to remember to decorate the IDataReader with the TypeSafeDataReaderDecorator where ever I need to use it. Since the ITypeSafeDataReader inherits from IDataReader, though, you can still pass it around as a standard IDataReader interface – you just lose the benefits of column ordinal caching, etc.
The specific method overloads for GetInt32, GetString, GetBoolean, etc. are simple convenience methods that do nothing more than call into the GetColumnInformation
</div> </div>
I’ve updated my data access layer to always use the ITypeSafeDataReader specifically. This lets me have direct access to the extra functionality in the repository implementations without having to remember to decorate the IDataReader with the TypeSafeDataReaderDecorator where ever I need to use it. Since the ITypeSafeDataReader inherits from IDataReader, though, you can still pass it around as a standard IDataReader interface – you just lose the benefits of column ordinal caching, etc.
1: int myInt = tsdr.GetColumnInformation<int>("MyIntColumn");
Use ITypeSafeDataReader In Place Of IDataReader