|
0
|
1 <% title # Introduction to abstract data accessors %>
|
|
|
2 <% order # 1 %>
|
|
|
3 <h4>Introduction</h4>
|
|
|
4
|
|
|
5 <p class='j'>
|
|
|
6 Before we start talking about the data accessors, let us create a few examples of
|
|
|
7 typical data accessor methods.
|
|
|
8 </p>
|
|
|
9 <p class='j'>
|
|
|
10 The following table contains three stored procedures and
|
|
|
11 three data access methods implementing the stored procedure calls.
|
|
|
12 </p>
|
|
|
13
|
|
|
14 <table width='100%'>
|
|
|
15 <tr><th>Stored procedure</th><th>Data access method</th></tr>
|
|
|
16 <tr><td colspan=2 style='padding-top:7px'>
|
|
|
17 The first stored procedure takes filter and page parameters and returns recordset from the Person table.
|
|
|
18 </td></tr>
|
|
|
19 <tr>
|
|
|
20 <td width=50% height=100%><% sql #
|
|
|
21 CREATE Procedure GetPersonListByName(
|
|
|
22 @firstName varchar(50),
|
|
|
23 @lastName varchar(50),
|
|
|
24 @pageNumber int,
|
|
|
25 @pageSize int)
|
|
|
26 AS
|
|
|
27 -- stored procedure implementation
|
|
|
28 --
|
|
|
29 %></td>
|
|
|
30 <td width=50%><% cs #
|
|
|
31 public List<Person> GetPersonListByName(
|
|
|
32 string firstName,
|
|
|
33 string lastName,
|
|
|
34 int pageNumber,
|
|
|
35 int pageSize)
|
|
|
36 {
|
|
|
37 // method implementation.
|
|
|
38 }
|
|
|
39 %></td>
|
|
|
40 </tr>
|
|
|
41 <tr><td colspan=2>
|
|
|
42 Second example will return single <b>Person</b> record by <b>id</b>.
|
|
|
43 </td></tr>
|
|
|
44 <tr>
|
|
|
45 <td width=50% height=100%><% sql #
|
|
|
46 CREATE Procedure GetPersonByID(@id int)
|
|
|
47 AS
|
|
|
48 -- stored procedure implementation
|
|
|
49 --
|
|
|
50 %>
|
|
|
51 </td>
|
|
|
52 <td width=50%><% cs #
|
|
|
53 public Person GetPersonByID(int id)
|
|
|
54 {
|
|
|
55 // method implementation.
|
|
|
56 }
|
|
|
57 %></td>
|
|
|
58 </tr>
|
|
|
59 <tr><td colspan=2>
|
|
|
60 The last example will delete a record from the database by <b>id</b>.
|
|
|
61 </td></tr>
|
|
|
62 <tr>
|
|
|
63 <td width=50% height=100%><% sql #
|
|
|
64 CREATE Procedure DeletePersonByID(@id int)
|
|
|
65 AS
|
|
|
66 -- stored procedure implementation
|
|
|
67 --
|
|
|
68 %></td>
|
|
|
69 <td width=50%><% cs #
|
|
|
70 public void DeletePersonByID(int id)
|
|
|
71 {
|
|
|
72 // method implementation.
|
|
|
73 }
|
|
|
74 %></td>
|
|
|
75 </tr>
|
|
|
76 </table>
|
|
|
77
|
|
|
78 <p class='j'>Now lets see what we can say if we compare the stored procedure and C# method signatures.
|
|
|
79 <ol>
|
|
|
80 <li>Stored procedure and method names match up.</li>
|
|
|
81 <li>Sequential order, method parameter types and names correspond to stored procedure parameters.</li>
|
|
|
82 <li>Methods' return values can give us an idea what <b>Execute</b> method we
|
|
|
83 should utilize and what object type has to be used to map data from recordset if needed.</li>
|
|
|
84 </ol>
|
|
|
85 </p>
|
|
|
86
|
|
|
87 <p class='j'>
|
|
|
88 As demonstrated above method definition contains all the information we need
|
|
|
89 to implement the method body. Actually, by defining the method signatures, we
|
|
|
90 completed the most intelligent part of data accessor development. The rest of
|
|
|
91 work is definitely a monkey's job. Honestly, I got bored of being just a coding
|
|
|
92 machine writing the same data access code over and over again, especially
|
|
|
93 understanding that this process can be automated.
|
|
|
94 </p>
|
|
|
95 <p class='j'>
|
|
|
96 This introduction shows how to avoid the implementation step of data access
|
|
|
97 development and how to reduce this routine process to the method declaration.
|
|
|
98 </p>
|
|
|
99
|
|
|
100 <h4>Abstract classes</h4>
|
|
|
101
|
|
|
102 <p class='j'>
|
|
|
103 Unfortunately, mainstream .NET languages still do not have a compile-time
|
|
|
104 transformation system like some functional or <a href="http://nemerle.org/Macros">hybrid</a> languages do.
|
|
|
105 All we have today is pre-compile- and run-time code generation.
|
|
|
106 </p>
|
|
|
107 <p class='j'>
|
|
|
108 This introduction concentrates on run-time code generation and its support by
|
|
|
109 <a href="http://www.bltoolkit.net/">Business Logic Toolkit for .NET</a>.
|
|
|
110 </p>
|
|
|
111 <p class='j'>
|
|
|
112 Let us step back and bring the methods from the previous examples together in one class.
|
|
|
113 Ideally, this data accessor class could look like the following:
|
|
|
114 </p>
|
|
|
115 <% cs #
|
|
|
116 using System;
|
|
|
117 using System.Collections.Generic;
|
|
|
118
|
|
|
119 public class PersonAccessor
|
|
|
120 {
|
|
|
121 public List<Person> GetPersonListByName(
|
|
|
122 string firstName, string lastName, int pageNumber, int pageSize);
|
|
|
123
|
|
|
124 public Person GetPersonByID (int id);
|
|
|
125 public void DeletePersonByID(int id);
|
|
|
126 }
|
|
|
127 %>
|
|
|
128
|
|
|
129 <p class='j'>
|
|
|
130 The bad news about this sample is that we cannot use such syntax as the
|
|
|
131 compiler expects the method's body implementation.
|
|
|
132 </p>
|
|
|
133 <p class='j'>
|
|
|
134 The good news is we can use abstract classes and methods that give us quite
|
|
|
135 similar, compilable source code.
|
|
|
136 </p>
|
|
|
137 <% cs #
|
|
|
138 using System;
|
|
|
139 using System.Collections.Generic;
|
|
|
140
|
|
|
141 public /*[a]*/abstract/*[/a]*/ class PersonAccessor
|
|
|
142 {
|
|
|
143 public /*[a]*/abstract/*[/a]*/ List<Person> GetPersonListByName(
|
|
|
144 string firstName, string lastName, int pageNumber, int pageSize);
|
|
|
145
|
|
|
146 public /*[a]*/abstract/*[/a]*/ Person GetPersonByID (int id);
|
|
|
147 public /*[a]*/abstract/*[/a]*/ void DeletePersonByID(int id);
|
|
|
148 }
|
|
|
149 %>
|
|
|
150
|
|
|
151 <p class='j'>This code is 100% valid and our next step is to make it workable.</p>
|
|
|
152
|
|
|
153 <H4>Abstract DataAccessor</H4>
|
|
|
154
|
|
|
155 <p class='j'>
|
|
|
156 Business Logic Toolkit provides the <b>DataAccessor</b> class, which is
|
|
|
157 used as a base class to develop data accessor classes. If we add
|
|
|
158 <b>DataAccessor</b> to our previous example, it will look like the following:
|
|
|
159 </p>
|
|
|
160 <% cs #
|
|
|
161 using System;
|
|
|
162 using System.Collections.Generic;
|
|
|
163
|
|
|
164 public abstract class PersonAccessor : /*[a]*/DataAccessor/*[/a]*/
|
|
|
165 {
|
|
|
166 public abstract List<Person> GetPersonListByName(
|
|
|
167 string firstName, string lastName, int pageNumber, int pageSize);
|
|
|
168
|
|
|
169 public abstract Person GetPersonByID (int id);
|
|
|
170 public abstract void DeletePersonByID(int id);
|
|
|
171 }
|
|
|
172 %>
|
|
|
173
|
|
|
174 <p class='j'>That's it! Now this class is complete and fully functional. The code below shows how to use it:</p>
|
|
|
175
|
|
|
176 <% cs #
|
|
|
177 using System;
|
|
|
178 using System.Collections.Generic;
|
|
|
179
|
|
|
180 using BLToolkit.Reflection;
|
|
|
181
|
|
|
182 namespace DataAccess
|
|
|
183 {
|
|
|
184 class Program
|
|
|
185 {
|
|
|
186 static void Main(string[] args)
|
|
|
187 {
|
|
|
188 PersonAccessor pa = /*[a]*/TypeAccessor/*[/a]*/<PersonAccessor>./*[a]*/CreateInstance/*[/a]*/();
|
|
|
189
|
|
|
190 List<Person> list = pa.GetPersonListByName("Crazy", "Frog", 0, 20);
|
|
|
191
|
|
|
192 foreach (Person p in list)
|
|
|
193 Console.Write("{0} {1}", p.FirstName, p.LastName);
|
|
|
194 }
|
|
|
195 }
|
|
|
196 }
|
|
|
197 %>
|
|
|
198
|
|
|
199 <p class='j'>
|
|
|
200 The only magic here is the <b>TypeAccessor.CreateInstance</b> method. First of all this
|
|
|
201 method creates a new class inherited from the <b>PersonAccessor</b> class and
|
|
|
202 then generates abstract method bodies depending on each method declaration.
|
|
|
203 If we wrote those methods manually, we could get something like the following:
|
|
|
204 </p>
|
|
|
205 <% cs #
|
|
|
206 using System;
|
|
|
207 using System.Collections.Generic;
|
|
|
208
|
|
|
209 using BLToolkit.Data;
|
|
|
210
|
|
|
211 namespace Example.BLToolkitExtension
|
|
|
212 {
|
|
|
213 public sealed class PersonAccessor : Example.PersonAccessor
|
|
|
214 {
|
|
|
215 public override List<Person> GetPersonListByName(
|
|
|
216 string firstName,
|
|
|
217 string lastName,
|
|
|
218 int pageNumber,
|
|
|
219 int pageSize)
|
|
|
220 {
|
|
|
221 using (DbManager db = GetDbManager())
|
|
|
222 {
|
|
|
223 return db
|
|
|
224 .SetSpCommand("GetPersonListByName",
|
|
|
225 db.Parameter("@firstName", firstName),
|
|
|
226 db.Parameter("@lastName", lastName),
|
|
|
227 db.Parameter("@pageNumber", pageNumber),
|
|
|
228 db.Parameter("@pageSize", pageSize))
|
|
|
229 .ExecuteList<Person>();
|
|
|
230 }
|
|
|
231 }
|
|
|
232
|
|
|
233 public override Person GetPersonByID(int id)
|
|
|
234 {
|
|
|
235 using (DbManager db = GetDbManager())
|
|
|
236 {
|
|
|
237 return db
|
|
|
238 .SetSpCommand("GetPersonByID", db.Parameter("@id", id))
|
|
|
239 .ExecuteObject<Person>();
|
|
|
240 }
|
|
|
241 }
|
|
|
242
|
|
|
243 public override void DeletePersonByID(int id)
|
|
|
244 {
|
|
|
245 using (DbManager db = GetDbManager())
|
|
|
246 {
|
|
|
247 db
|
|
|
248 .SetSpCommand("DeletePersonByID", db.Parameter("@id", id))
|
|
|
249 .ExecuteNonQuery();
|
|
|
250 }
|
|
|
251 }
|
|
|
252 }
|
|
|
253 }
|
|
|
254 %>
|
|
|
255
|
|
|
256 <p class='j'>
|
|
|
257 (The <a href='..\Data\index.htm'>DbManager</a> class is another BLToolkit class used for 'low-level' database access).
|
|
|
258 </p>
|
|
|
259
|
|
|
260 <p class="j">
|
|
|
261 Every part of the method declaration is important.
|
|
|
262 Method's return value specifies one of the Execute methods in the following way:
|
|
|
263 <table class='data'>
|
|
|
264 <tr><th>Return Type</th><th>Execute Method</th></tr>
|
|
|
265 <tr><td><i>IDataReader</i> interface</td><td>ExecuteReader</td></tr>
|
|
|
266 <tr><td>Subclass of <i>DataSet</i></td><td>ExecuteDataSet</td></tr>
|
|
|
267 <tr><td>Subclass of <i>DataTable</i></td><td>ExecuteDataTable</td></tr>
|
|
|
268 <tr><td>Class implementing the <i>IList</i> interface</td><td><a href="ExecuteList.htm">ExecuteList</a> or <a href="ExecuteList.htm">ExecuteScalarList</a></td></tr>
|
|
|
269 <tr><td>Class implementing the <i>IDictionary</i> interface</td><td><a href="ExecuteDictionary.htm">ExecuteDictionary</a> or <a href="ExecuteDictionary.htm">ExecuteScalarDictionary</a></td></tr>
|
|
|
270 <tr><td><i>void</i></td><td>ExecuteNonQuery</td></tr>
|
|
|
271 <tr><td><i>string</i>, <i>byte[]</i> or value type</td><td><a href="ExecuteScalar.htm">ExecuteScalar</a></td></tr>
|
|
|
272 <tr><td>In any other case</td><td><a href="ExecuteObject.htm">ExecuteObject</a></td></tr>
|
|
|
273 </table>
|
|
|
274 The method name explicitly defines the action name, which is converted to the stored procedure name.
|
|
|
275 Type, sequential order, and name of the method parameters are mapped to the command parameters.
|
|
|
276 Exceptions from this rule are:
|
|
|
277 <br>
|
|
|
278 <div style='margin:-10px 0px -0px -10px'><ul compact="compact">
|
|
|
279 <li>a parameter of <a href="../Data/index.htm"><i>DbManager</i></a> type. In this case generator uses provided <a href="../Data/index.htm"><i>DbManager</i></a> to call the command.</li>
|
|
|
280 <li>parameters decorated with attribute <a href='#FormatAttribute'>FormatAttribute</a>, <a href='#DestinationAttribute'>DestinationAttribute</a>.</li>
|
|
|
281 </ul></div>
|
|
|
282 </p>
|
|
|
283
|
|
|
284 <h4>Generating process control</h4>
|
|
|
285
|
|
|
286 <p class='j'>
|
|
|
287 The <b>PersonAccessor</b> class above is a very simple example and, of
|
|
|
288 course, it seems too ideal to be real. In real life, we need more flexibility
|
|
|
289 and more control over the generated code. BLToolkit contains a bunch of
|
|
|
290 attributes to control DataAccessor generation in addition to <b>DataAccessor</b> virtual members.
|
|
|
291 </p>
|
|
|
292
|
|
|
293 <h5>Method CreateDbManager</h5>
|
|
|
294
|
|
|
295 <% cs #
|
|
|
296 protected virtual DbManager CreateDbManager()
|
|
|
297 {
|
|
|
298 return new DbManager();
|
|
|
299 }
|
|
|
300 %>
|
|
|
301
|
|
|
302 <p class='j'>
|
|
|
303 By default, this method creates a new instance of <b>DbManager</b> that uses default database configuration.
|
|
|
304 You can change this behavior by overriding this method. For example:
|
|
|
305 </p>
|
|
|
306 <% cs #
|
|
|
307 public abstract class OracleDataAccessor : DataAccessor
|
|
|
308 {
|
|
|
309 protected override DbManager CreateDbManager()
|
|
|
310 {
|
|
|
311 return new DbManager("Oracle", "Production");
|
|
|
312 }
|
|
|
313 }
|
|
|
314 %>
|
|
|
315
|
|
|
316 <p class='j'>
|
|
|
317 This code will use the <i>Oracle</i> data provider and <i>Production</i> configuration.
|
|
|
318 </p>
|
|
|
319
|
|
|
320 <a name='GetDefaultSpName'></a><h5>Method GetDefaultSpName</h5>
|
|
|
321
|
|
|
322 <% cs #
|
|
|
323 protected virtual string GetDefaultSpName(string typeName, string actionName)
|
|
|
324 {
|
|
|
325 return typeName == null?
|
|
|
326 actionName:
|
|
|
327 string.Format("{0}_{1}", typeName, actionName);
|
|
|
328 }
|
|
|
329 %>
|
|
|
330
|
|
|
331 <p class='j'>
|
|
|
332 As I mentioned, the method name explicitly defines the so-called action name.
|
|
|
333 The final stored procedure name is created by the <b>GetDefaultSpName</b>
|
|
|
334 method. The default implementation uses the following naming convention:
|
|
|
335 </p>
|
|
|
336
|
|
|
337 <ul>
|
|
|
338 <li>
|
|
|
339 If type name is provided, the method constructs the stored proc name by
|
|
|
340 concatenating the type and action names. Thus, if the type name is "Person" and
|
|
|
341 the action name is "GetAll", the resulting sproc name will be "Person_GetAll".
|
|
|
342 </li>
|
|
|
343 <li>
|
|
|
344 If the type name is NOT provided, the stored procedure name will equal the
|
|
|
345 action name.</li>
|
|
|
346 </ul>
|
|
|
347
|
|
|
348 <p class='j'>
|
|
|
349 You can easily change this behavior. For example, for the naming convention "p_Person_GetAll",
|
|
|
350 the method implementation can be the following:
|
|
|
351 </p>
|
|
|
352 <% cs #
|
|
|
353 public abstract class MyBaseDataAccessor<T,A> : DataAccessor<T,A>
|
|
|
354 where A : DataAccessor<T,A>
|
|
|
355 {
|
|
|
356 protected override string GetDefaultSpName(string typeName, string actionName)
|
|
|
357 {
|
|
|
358 return string.Format("p_{0}_{1}", typeName, actionName);
|
|
|
359 }
|
|
|
360 }
|
|
|
361 %>
|
|
|
362
|
|
|
363 <h5>Method GetTableName</h5>
|
|
|
364
|
|
|
365 <% cs #
|
|
|
366 protected virtual string GetTableName(Type type)
|
|
|
367 {
|
|
|
368 // ...
|
|
|
369 return type.Name;
|
|
|
370 }
|
|
|
371 %>
|
|
|
372
|
|
|
373 <p class='j'>
|
|
|
374 By default, the table name is the associated object type name (<i>Person</i> in our examples).
|
|
|
375 There are two ways to associate an object type with an accessor. By providing generic parameter:
|
|
|
376 </p>
|
|
|
377 <% cs #
|
|
|
378 public abstract class PersonAccessor : DataAccessor<Person>
|
|
|
379 {
|
|
|
380 }
|
|
|
381 %>
|
|
|
382
|
|
|
383 <p class='j'>
|
|
|
384 And by the <b>ObjectType</b> attribute:
|
|
|
385 </p>
|
|
|
386 <% cs #
|
|
|
387 [ObjectType(typeof(Person))]
|
|
|
388 public abstract class PersonAccessor : DataAccessor
|
|
|
389 {
|
|
|
390 }
|
|
|
391 %>
|
|
|
392
|
|
|
393 <p class='j'>
|
|
|
394 If you want to have different table and type names in your application, you may override the <b>GetTableName</b> method:
|
|
|
395 </p>
|
|
|
396 <% cs #
|
|
|
397 public abstract class OracleDataAccessor<T,A> : DataAccessor<T,A>
|
|
|
398 where A : DataAccessor<T,A>
|
|
|
399 {
|
|
|
400 protected override string GetTableName(Type type)
|
|
|
401 {
|
|
|
402 return base.GetTableName(type).ToUpper();
|
|
|
403 }
|
|
|
404 }
|
|
|
405 %>
|
|
|
406
|
|
|
407 <h5>TableNameAttribute</h5>
|
|
|
408
|
|
|
409 <p class='j'>
|
|
|
410 Also, you can change the table name for a particular object type by decorating this object
|
|
|
411 with the <b>TableNameAttribute</b> attribute:
|
|
|
412 </p>
|
|
|
413 <% cs #
|
|
|
414 [TableName("PERSON")]
|
|
|
415 public class Person
|
|
|
416 {
|
|
|
417 public int ID;
|
|
|
418 public string FirstName;
|
|
|
419 public string LastName;
|
|
|
420 }
|
|
|
421 %>
|
|
|
422
|
|
|
423 <h5>ActionNameAttribute</h5>
|
|
|
424
|
|
|
425 <p class='j'>
|
|
|
426 This attribute allows changing the action name.
|
|
|
427 </p>
|
|
|
428 <% cs #
|
|
|
429 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
430 {
|
|
|
431 [ActionName("GetByID")]
|
|
|
432 protected abstract IDataReader GetByIDInternal(DbManager db, int id);
|
|
|
433
|
|
|
434 public Person GetByID(int id)
|
|
|
435 {
|
|
|
436 using (DbManager db = GetDbManager())
|
|
|
437 using (IDataReader rd = GetByIDInternal(db, id))
|
|
|
438 {
|
|
|
439 Person p = new Person();
|
|
|
440
|
|
|
441 // do something complicated.
|
|
|
442
|
|
|
443 return p;
|
|
|
444 }
|
|
|
445 }
|
|
|
446 }
|
|
|
447 %>
|
|
|
448
|
|
|
449 <h5>ActionSprocNameAttribute</h5>
|
|
|
450
|
|
|
451 <p class='j'>
|
|
|
452 This attribute associates the action name with a stored procedure name:
|
|
|
453 </p>
|
|
|
454 <% cs#
|
|
|
455 [ActionSprocName("Insert", sp_Person_Insert")]
|
|
|
456 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
457 {
|
|
|
458 public abstract void Insert(Person p);
|
|
|
459 }
|
|
|
460 %>
|
|
|
461
|
|
|
462 <p class='j'>
|
|
|
463 This attribute can be useful when you need to reassign a stored procedure name for a method defined in your base class.
|
|
|
464 </p>
|
|
|
465
|
|
|
466 <h5>SprocNameAttribute</h5>
|
|
|
467
|
|
|
468 <p class='j'>
|
|
|
469 The regular way to assign deferent from default sproc name for a method is the <b>SprocName</b> attribute.
|
|
|
470 </p>
|
|
|
471 <% cs #
|
|
|
472 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
473 {
|
|
|
474 [SprocName("sp_Person_Insert")]
|
|
|
475 public abstract void Insert(Person p);
|
|
|
476 }
|
|
|
477 %>
|
|
|
478
|
|
|
479 <a name='DestinationAttribute'></a><h5>DestinationAttribute</h5>
|
|
|
480
|
|
|
481 <p class='j'>
|
|
|
482 By default, the DataAccessor generator uses method's return value to determine which <i>Execute</i> method
|
|
|
483 should be used to perform the current operation.
|
|
|
484 The <b>DestinationAttribute</b> indicates that target object is a parameter decorated with this attribute:
|
|
|
485 </p>
|
|
|
486 <% cs #
|
|
|
487 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
488 {
|
|
|
489 public abstract void GetAll([Destination] List<Person> list);
|
|
|
490 }
|
|
|
491 %>
|
|
|
492
|
|
|
493 <h5>DirectionAttributes</h5>
|
|
|
494
|
|
|
495 <p class='j'>
|
|
|
496 <i>DataAccessor</i> generator can map provided business object to stored
|
|
|
497 procedure parameters. <b>Direction</b> attributes allow controlling this process more precisely.
|
|
|
498 </p>
|
|
|
499 <% cs #
|
|
|
500 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
501 {
|
|
|
502 public abstract void Insert(
|
|
|
503 [Direction.Output("ID"), Direction.Ignore("LastName")] Person person);
|
|
|
504 }
|
|
|
505 %>
|
|
|
506
|
|
|
507 <p class='j'>
|
|
|
508 In addition, BLToolkit provides two more direction attributes:
|
|
|
509 <b>Direction.InputOutputAttribute</b> and <b>Direction.ReturnValueAttribute</b>.
|
|
|
510 </p>
|
|
|
511
|
|
|
512 <h5>DiscoverParametersAttribute</h5>
|
|
|
513 <p class='j'>
|
|
|
514 By default, BLToolkit expects method parameter names to match stored procedure
|
|
|
515 parameter names. The sequential order of parameters is not important in this
|
|
|
516 case. This attribute enforces BLToolkit to retrieve parameter information from
|
|
|
517 the sproc and to assign method parameters in the order they go. Parameter names
|
|
|
518 are ignored.
|
|
|
519 </p>
|
|
|
520
|
|
|
521 <a name='FormatAttribute'></a><h5>FormatAttribute</h5>
|
|
|
522
|
|
|
523 <p class='j'>
|
|
|
524 This attribute indicates that the specified parameter is used to construct the stored procedure name or SQL statement:
|
|
|
525 </p>
|
|
|
526 <% cs #
|
|
|
527 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
528 {
|
|
|
529 [SqlQuery("SELECT {0} FROM {1} WHERE {2}")]
|
|
|
530 public abstract List<string> GetStrings(
|
|
|
531 [Format(0)] string fieldName,
|
|
|
532 [Format(1)] string tableName,
|
|
|
533 [Format(2)] string whereClause);
|
|
|
534 }
|
|
|
535 %>
|
|
|
536
|
|
|
537 <h5>IndexAttribute</h5>
|
|
|
538
|
|
|
539 <p class='j'>
|
|
|
540 If you want your method to return a dictionary, you will have to specify fields to build the dictionary key.
|
|
|
541 The Index attribute allows you to do that:
|
|
|
542 </p>
|
|
|
543 <% cs #
|
|
|
544 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
545 {
|
|
|
546 [SqlQuery("SELECT * FROM Person")]
|
|
|
547 [Index("ID")]
|
|
|
548 public abstract Dictionary<int, Person> SelectAll1();
|
|
|
549
|
|
|
550 [SqlQuery("SELECT * FROM Person")]
|
|
|
551 [Index("@PersonID", "LastName")]
|
|
|
552 public abstract Dictionary<CompoundValue, Person> SelectAll2();
|
|
|
553 }
|
|
|
554 %>
|
|
|
555 <p class='j'>Note: if your key has more than one field, the type of this key should be <b>CompoundValue</b>.</p>
|
|
|
556 <p class='j'>If the field name starts from '@' symbol, BLToolkit reads the field value from data source,
|
|
|
557 otherwise from an object property/field.</p>
|
|
|
558
|
|
|
559 <h5>ParamNameAttribute</h5>
|
|
|
560
|
|
|
561 <p class='j'>
|
|
|
562 By default, the method parameter name should match the stored procedure parameter name.
|
|
|
563 This attribute specifies the sproc parameter name explicitly.
|
|
|
564 </p>
|
|
|
565 <% cs #
|
|
|
566 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
567 {
|
|
|
568 public abstract Person SelectByName(
|
|
|
569 [ParamName("FirstName")] string name1,
|
|
|
570 [ParamName("@LastName")] string name2);
|
|
|
571 }
|
|
|
572 %>
|
|
|
573
|
|
|
574 <h5>ScalarFieldNameAttribute</h5>
|
|
|
575
|
|
|
576 <p class='j'>
|
|
|
577 If your method returns a dictionary of scalar values, you will have to specify the name or index of the field
|
|
|
578 used to populate the scalar list. The <b>ScalarFieldName</b> attribute allows you to do that:
|
|
|
579 </p>
|
|
|
580 <% cs #
|
|
|
581 public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
582 {
|
|
|
583 [SqlQuery("SELECT * FROM Person")]
|
|
|
584 [Index("@PersonID")]
|
|
|
585 [ScalarFieldName("FirstName")]
|
|
|
586 public abstract Dictionary<int, string> SelectAll1();
|
|
|
587
|
|
|
588 [SqlQuery("SELECT * FROM Person")]
|
|
|
589 [Index("PersonID", "LastName")]
|
|
|
590 [ScalarFieldName("FirstName")]
|
|
|
591 public abstract Dictionary<CompoundValue, string> SelectAll2();
|
|
|
592 }
|
|
|
593 %>
|
|
|
594
|
|
|
595 <h5>ScalarSourceAttribute</h5>
|
|
|
596
|
|
|
597 <p class='j'>
|
|
|
598 If a method returns a scalar value, this attribute can be used to specify how database returns this value.
|
|
|
599 The <b>ScalarSource</b> attribute take a parameter of the <b>ScalarSourceType</b> type:
|
|
|
600 </p>
|
|
|
601
|
|
|
602 <table class='data'>
|
|
|
603 <tr><th>ScalarSourceType</th><th>Description</th></tr>
|
|
|
604 <tr><td>DataReader</td><td>Calls the <b>DbManager.ExecuteReader</b> method, and then calls <b>IDataReader.GetValue</b> method to read the value.</td></tr>
|
|
|
605 <tr><td>OutputParameter</td><td>Calls the <b>DbManager.ExecuteNonQuery</b> method, and then reads value from the <b>IDbDataParameter.Value</b> property.</td></tr>
|
|
|
606 <tr><td>ReturnValue</td><td>Calls the <b>DbManager.ExecuteNonQuery</b> method, and then reads return value from command parameter collection.</td></tr>
|
|
|
607 <tr><td>AffectedRows</td><td>Calls the <b>DbManager.ExecuteNonQuery</b> method, and then returns its return value.</td></tr>
|
|
|
608 </table>
|
|
|
609
|
|
|
610 <h5>SqlQueryAttribute</h5>
|
|
|
611
|
|
|
612 <p class='j'>
|
|
|
613 This attribute allows specifying SQL statement.
|
|
|
614 </p>
|
|
|
615 <% cs # public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
|
|
|
616 {
|
|
|
617 [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
|
|
|
618 public abstract Person GetByID(int @id);
|
|
|
619 }
|
|
|
620 %>
|
|
|
621
|
|
|
622 <h4>Conclusion</h4>
|
|
|
623
|
|
|
624 <p class='j'>
|
|
|
625 I hope this brief tutorial demonstrates one of the simplest, quickest and
|
|
|
626 most low-maintenance ways to develop your data access layer. In addition, you
|
|
|
627 will get one more benefit, which is incredible object mapping performance. But
|
|
|
628 that is a topic we will discuss later.
|
|
|
629 </p>
|
