EnterpriseLibrary数据访问(4)使用数据访问器接收数据
代码下载： 敏捷学院技术资源库
1.引用
1 |
// TODO: Use Enterprise Library Data Block |
2 |
|
3 |
using Microsoft.Practices.EnterpriseLibrary.Data; |
4 |
|
5 |
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration; |
2.创建访问器
1 |
// TODO: Create private fields for Data accessors |
2 |
|
3 |
private DataAccessor<Category> categoryAccessor; |
4 |
|
5 |
private DataAccessor<Product> productAccessor; |
3. 初始化访问器
001 |
private void InitializeAccessors() |
002 |
{ |
003 |
|
004 |
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>(); |
005 |
|
006 |
// TODO: Create category accessor 使用存储过程 |
007 |
|
008 |
categoryAccessor = db.CreateSprocAccessor("GetCategories", |
009 |
|
010 |
MapBuilder<Category>.MapAllProperties() |
011 |
|
012 |
.Map(p => p.Name).ToColumn("CategoryName") |
013 |
|
014 |
.Map(p => p.ID).ToColumn("CategoryID") |
015 |
|
016 |
.Build()); |
017 |
|
018 |
|
019 |
// TODO: Create product accessor 使用sql语句 |
020 |
|
021 |
productAccessor = |
022 |
|
023 |
db.CreateSqlStringAccessor( |
024 |
|
025 |
"SELECT ProductID,ProductName,UnitPrice,LastUpdate FROM Products WHERE CategoryID=@CategoryID", |
026 |
|
027 |
new GetProductsByIdParameterMapper(db), |
028 |
|
029 |
MapBuilder<Product> |
030 |
|
031 |
.MapAllProperties() |
032 |
|
033 |
.Map(p => p.Name).ToColumn("ProductName") |
034 |
|
035 |
.Map(p => p.ID).ToColumn("ProductID") |
036 |
|
037 |
.Map(p => p.UnitPrice).WithFunc(ApplyTax) //对价格的单独处理 |
038 |
|
039 |
.DoNotMap(p => p.LastReviewed)//不映射这个字段 |
040 |
|
041 |
|
042 |
|
043 |
.Build()); |
044 |
|
045 |
} |
046 |
|
047 |
|
048 |
private decimal ApplyTax(IDataRecord record) |
049 |
|
050 |
{ |
051 |
|
052 |
var unitPrice = (decimal)record["UnitPrice"]; |
053 |
|
054 |
if (numTax.Value > 0) |
055 |
|
056 |
{ |
057 |
|
058 |
unitPrice += unitPrice * numTax.Value / 100; |
059 |
|
060 |
} |
061 |
|
062 |
return unitPrice; |
063 |
|
064 |
} |
065 |
|
066 |
|
067 |
|
068 |
//生成sql参数的类 |
069 |
|
070 |
public class GetProductsByIdParameterMapper : IParameterMapper |
071 |
|
072 |
{ |
073 |
|
074 |
private readonly Database db; |
075 |
|
076 |
public GetProductsByIdParameterMapper(Database db) |
077 |
|
078 |
{ |
079 |
|
080 |
this.db = db; |
081 |
|
082 |
} |
083 |
|
084 |
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues) |
085 |
|
086 |
{ |
087 |
|
088 |
InitializeParameters(command); |
089 |
|
090 |
db.SetParameterValue(command, "@CategoryID", parameterValues[0]); |
091 |
|
092 |
|
093 |
|
094 |
} |
095 |
|
096 |
|
097 |
private void InitializeParameters(System.Data.Common.DbCommand command) |
098 |
|
099 |
{ |
100 |
|
101 |
db.AddInParameter(command, "@CategoryID", System.Data.DbType.Int32); |
102 |
|
103 |
} |
104 |
|
105 |
} |
4. 执行返回数据
01 |
// TODO: Use a Data Accessor to retrieve Categories |
02 |
|
03 |
var categories = categoryAccessor.Execute(); |
04 |
|
05 |
cmbCategory.DataSource = categories.ToList(); |
06 |
|
07 |
// TODO: Retrieve Products by Category |
08 |
|
09 |
var selectedCategory = (Category)cmbCategory.SelectedItem; |
10 |
|
11 |
if (selectedCategory == null) |
12 |
|
13 |
return; |
14 |
|
15 |
var products = productAccessor.Execute(selectedCategory.ID); |
16 |
|
17 |
dgProducts.DataSource = products.ToList(); |
附 简单总结：如果数据库中的字段和实体字段的名称相同
01 |
//#1 |
02 |
|
03 |
using Microsoft.Practices.EnterpriseLibrary.Data; |
04 |
|
05 |
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration; |
06 |
|
07 |
//#2 |
08 |
|
09 |
private Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>(); |
10 |
|
11 |
private DataAccessor<Product> productAccessor; |
12 |
|
13 |
//#3 |
14 |
|
15 |
private List<Product> GetProducts() |
16 |
|
17 |
{ |
18 |
|
19 |
|
20 |
|
21 |
productAccessor = |
22 |
|
23 |
db.CreateSqlStringAccessor( |
24 |
|
25 |
"SELECT ProductID as id,ProductName,UnitPrice,LastUpdate FROM Products", |
26 |
|
27 |
MapBuilder<Product> |
28 |
|
29 |
.MapAllProperties() |
30 |
|
31 |
//.Map(p=>p.ID).ToColumn("ProductID") |
32 |
|
33 |
.DoNotMap(p=>p.CategoryID) //不映射这个字段 |
34 |
|
35 |
.Build() |
36 |
|
37 |
); |
38 |
|
39 |
|
40 |
|
41 |
return productAccessor.Execute().ToList(); |
42 |
|
43 |
} |
