Mercurial > pub > bltoolkit

comparison Tools/DocGen/Content/Doc/DataAccess/Introduction.htm @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
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>