NHibernate 联合查询,解决方法
------------------------------------------------------------------------------------------------
1.单表查询用.AddEntity(),即可返回model类型.
ISession session = this._sessionManager.OpenSession();
IQuery query = session.CreateSQLQuery(sql).AddEntity("EntityName");
2.多表查询时,默认只能返回List 数据类型,然后通过 Object[] 来绑定值,这样带来了很多的不方便.
通过查询发现有2种方法,1.重写一个model 2.手动创建datatable.
发现重写比较麻烦,手动创建又太累.于是乎我把2种方法结合了一下:
1.在一个实体层中,添加另一个表中需要查询出来的字段,如下(红色字段)
1 namespace Equipment.Modules.API.Domain
2 {
3 //sb_sbinfo
4 public class sb_sbinfo
5 {
6
7 /// <summary>
8 /// id
9 /// </summary>
10 public virtual int id
11 {
12 get;
13 set;
14 }
15 /// <summary>
16 /// sb__id
17 /// </summary>
18 public virtual int? sb_id
19 {
20 get;
21 set;
22 }
23 /// <summary>
24 /// sbName
25 /// </summary>
26 public virtual string sbName
27 {
28 get;
29 set;
30 }
31 /// <summary>
32 /// sbCode
33 /// </summary>
34 public virtual int? sbCode
35 {
36 get;
37 set;
38 }
39 /// <summary>
40 /// sbType
41 /// </summary>
42 public virtual string sbType
43 {
44 get;
45 set;
46 }
47 /// <summary>
48 /// sbpp
49 /// </summary>
50 public virtual int? sbpp
51 {
52 get;
53 set;
54 }
55 /// <summary>
56 /// sbDj
57 /// </summary>
58 public virtual decimal? sbDj
59 {
60 get;
61 set;
62 }
63 /// <summary>
64 /// sbdw
65 /// </summary>
66 public virtual string sbdw
67 {
68 get;
69 set;
70 }
71 /// <summary>
72 /// sbggxhao
73 /// </summary>
74 public virtual string sbggxhao
75 {
76 get;
77 set;
78 }
79 /// <summary>
80 /// sbzjfs
81 /// </summary>
82 public virtual int? sbzjfs
83 {
84 get;
85 set;
86 }
87 /// <summary>
88 /// sbstatu
89 /// </summary>
90 public virtual int? sbstatu
91 {
92 get;
93 set;
94 }
95 /// <summary>
96 /// sbSccj
97 /// </summary>
98 public virtual string sbSccj
99 {
100 get;
101 set;
102 }
103 /// <summary>
104 /// sbCcTime
105 /// </summary>
106 public virtual DateTime? sbCcTime
107 {
108 get;
109 set;
110 }
111 /// <summary>
112 /// sbBuyTime
113 /// </summary>
114 public virtual DateTime? sbBuyTime
115 {
116 get;
117 set;
118 }
119 /// <summary>
120 /// sbYt
121 /// </summary>
122 public virtual string sbYt
123 {
124 get;
125 set;
126 }
127 /// <summary>
128 /// sbBzq
129 /// </summary>
130 public virtual string sbBzq
131 {
132 get;
133 set;
134 }
135 /// <summary>
136 /// sbly
137 /// </summary>
138 public virtual string sbly
139 {
140 get;
141 set;
142 }
143 /// <summary>
144 /// sbRkTime
145 /// </summary>
146 public virtual DateTime? sbRkTime
147 {
148 get;
149 set;
150 }
151 /// <summary>
152 /// sbRkry
153 /// </summary>
154 public virtual string sbRkry
155 {
156 get;
157 set;
158 }
159 /// <summary>
160 /// sbUptime
161 /// </summary>
162 public virtual DateTime? sbUptime
163 {
164 get;
165 set;
166 }
167 /// <summary>
168 /// t1
169 /// </summary>
170 public virtual string t1
171 {
172 get;
173 set;
174 }
175 /// <summary>
176 /// t2
177 /// </summary>
178 public virtual string t2
179 {
180 get;
181 set;
182 }
183 /// <summary>
184 /// t3
185 /// </summary>
186 public virtual string t3
187 {
188 get;
189 set;
190 }
191 /// <summary>
192 /// t4
193 /// </summary>
194 public virtual string t4
195 {
196 get;
197 set;
198 }
199 /// <summary>
200 /// t5
201 /// </summary>
202 public virtual string t5
203 {
204 get;
205 set;
206 }
207
208 /// <summary>
209 /// 在联合查询终另一个表终的id
210 /// </summary>
211 public virtual int ot_id
212 {
213 get;
214 set;
215 }
216
217
218 }
219 } 2.在执行SQL查询语句时,按上面实体的先后顺序写出查询语句,如(其中b.id是另一个表中的字段,转换成刚刚实体层中添加的字段):
SELECT ss.id, ss.sb_id, ss.sbName, ss.sbCode, ss.sbType, ss.sbpp, ss.sbDj, ss.sbdw,ss.sbggxhao, ss.sbzjfs, ss.sbstatu, ss.sbSccj, ss.sbCcTime, ss.sbBuyTime, ss.sbYt, ss.sbBzq, ss.sbly, ss.sbRkTime, ss.sbRkry, ss.sbUptime, ss.t1, ss.t2, ss.t3, ss.t4, ss.t5,b.id as ot_id From sb_sbinfo ss , sb_detailInfo b where ss.id=b.sbid
3.把查询出来的ILIST通过反射查询实体类中的字段,再转换成DATATABLE,最后返回一个DATATABLE.
ISession session = this._sessionManager.OpenSession();IQuery query = session.CreateSQLQuery(sql);
Assembly assembly = Assembly.Load("Equipment.Modules.API");Type type = assembly.GetType("Equipment.Modules.API.Domain.sb_sbinfo");PropertyInfo[] myPropertyInfo = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);IList li = query.List();
DataTable dt= getPropertyInfo(myPropertyInfo, li);return dt;
自动生成DATAtable类如下:
public DataTable getPropertyInfo(PropertyInfo[] myPropertyInfo,IList li){DataTable dt = new DataTable();string propStr = "";DataColumn column;DataRow row;// Display information for all properties.for (int j = 0 ; j < li.Count ; j++){row = dt.NewRow();for (int i = 0; i < myPropertyInfo.Length; i++){PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo[i];if (dt.Columns[myPropInfo.Name] == null){if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]"){column = new DataColumn(myPropInfo.Name, typeof(Int32));dt.Columns.Add(column);}else if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Decimal]"){column = new DataColumn(myPropInfo.Name, typeof(Decimal));dt.Columns.Add(column);}else if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.DateTime]"){column = new DataColumn(myPropInfo.Name, typeof(DateTime));dt.Columns.Add(column);}else{column = new DataColumn(myPropInfo.Name, myPropInfo.PropertyType);dt.Columns.Add(column);}Object[] obj = (Object[])li[j];//添加值if (obj[i] != null){row[myPropInfo.Name] = obj[i];}else{row[myPropInfo.Name] = System.DBNull.Value;}}else{Object[] obj = (Object[])li[j];//添加值if (obj[i] != null){row[myPropInfo.Name] = obj[i];}else{row[myPropInfo.Name] = System.DBNull.Value;}}}dt.Rows.Add(row);}return dt;} 通过以上可以生成完美的DataTable，但是需要注意的是,实体类字段的顺序,必须保持和查询语句中的查询顺序一样..
转载于:.html
NHibernate 联合查询,解决方法
------------------------------------------------------------------------------------------------
1.单表查询用.AddEntity(),即可返回model类型.
ISession session = this._sessionManager.OpenSession();
IQuery query = session.CreateSQLQuery(sql).AddEntity("EntityName");
2.多表查询时,默认只能返回List 数据类型,然后通过 Object[] 来绑定值,这样带来了很多的不方便.
通过查询发现有2种方法,1.重写一个model 2.手动创建datatable.
发现重写比较麻烦,手动创建又太累.于是乎我把2种方法结合了一下:
1.在一个实体层中,添加另一个表中需要查询出来的字段,如下(红色字段)
1 namespace Equipment.Modules.API.Domain
2 {
3 //sb_sbinfo
4 public class sb_sbinfo
5 {
6
7 /// <summary>
8 /// id
9 /// </summary>
10 public virtual int id
11 {
12 get;
13 set;
14 }
15 /// <summary>
16 /// sb__id
17 /// </summary>
18 public virtual int? sb_id
19 {
20 get;
21 set;
22 }
23 /// <summary>
24 /// sbName
25 /// </summary>
26 public virtual string sbName
27 {
28 get;
29 set;
30 }
31 /// <summary>
32 /// sbCode
33 /// </summary>
34 public virtual int? sbCode
35 {
36 get;
37 set;
38 }
39 /// <summary>
40 /// sbType
41 /// </summary>
42 public virtual string sbType
43 {
44 get;
45 set;
46 }
47 /// <summary>
48 /// sbpp
49 /// </summary>
50 public virtual int? sbpp
51 {
52 get;
53 set;
54 }
55 /// <summary>
56 /// sbDj
57 /// </summary>
58 public virtual decimal? sbDj
59 {
60 get;
61 set;
62 }
63 /// <summary>
64 /// sbdw
65 /// </summary>
66 public virtual string sbdw
67 {
68 get;
69 set;
70 }
71 /// <summary>
72 /// sbggxhao
73 /// </summary>
74 public virtual string sbggxhao
75 {
76 get;
77 set;
78 }
79 /// <summary>
80 /// sbzjfs
81 /// </summary>
82 public virtual int? sbzjfs
83 {
84 get;
85 set;
86 }
87 /// <summary>
88 /// sbstatu
89 /// </summary>
90 public virtual int? sbstatu
91 {
92 get;
93 set;
94 }
95 /// <summary>
96 /// sbSccj
97 /// </summary>
98 public virtual string sbSccj
99 {
100 get;
101 set;
102 }
103 /// <summary>
104 /// sbCcTime
105 /// </summary>
106 public virtual DateTime? sbCcTime
107 {
108 get;
109 set;
110 }
111 /// <summary>
112 /// sbBuyTime
113 /// </summary>
114 public virtual DateTime? sbBuyTime
115 {
116 get;
117 set;
118 }
119 /// <summary>
120 /// sbYt
121 /// </summary>
122 public virtual string sbYt
123 {
124 get;
125 set;
126 }
127 /// <summary>
128 /// sbBzq
129 /// </summary>
130 public virtual string sbBzq
131 {
132 get;
133 set;
134 }
135 /// <summary>
136 /// sbly
137 /// </summary>
138 public virtual string sbly
139 {
140 get;
141 set;
142 }
143 /// <summary>
144 /// sbRkTime
145 /// </summary>
146 public virtual DateTime? sbRkTime
147 {
148 get;
149 set;
150 }
151 /// <summary>
152 /// sbRkry
153 /// </summary>
154 public virtual string sbRkry
155 {
156 get;
157 set;
158 }
159 /// <summary>
160 /// sbUptime
161 /// </summary>
162 public virtual DateTime? sbUptime
163 {
164 get;
165 set;
166 }
167 /// <summary>
168 /// t1
169 /// </summary>
170 public virtual string t1
171 {
172 get;
173 set;
174 }
175 /// <summary>
176 /// t2
177 /// </summary>
178 public virtual string t2
179 {
180 get;
181 set;
182 }
183 /// <summary>
184 /// t3
185 /// </summary>
186 public virtual string t3
187 {
188 get;
189 set;
190 }
191 /// <summary>
192 /// t4
193 /// </summary>
194 public virtual string t4
195 {
196 get;
197 set;
198 }
199 /// <summary>
200 /// t5
201 /// </summary>
202 public virtual string t5
203 {
204 get;
205 set;
206 }
207
208 /// <summary>
209 /// 在联合查询终另一个表终的id
210 /// </summary>
211 public virtual int ot_id
212 {
213 get;
214 set;
215 }
216
217
218 }
219 } 2.在执行SQL查询语句时,按上面实体的先后顺序写出查询语句,如(其中b.id是另一个表中的字段,转换成刚刚实体层中添加的字段):
SELECT ss.id, ss.sb_id, ss.sbName, ss.sbCode, ss.sbType, ss.sbpp, ss.sbDj, ss.sbdw,ss.sbggxhao, ss.sbzjfs, ss.sbstatu, ss.sbSccj, ss.sbCcTime, ss.sbBuyTime, ss.sbYt, ss.sbBzq, ss.sbly, ss.sbRkTime, ss.sbRkry, ss.sbUptime, ss.t1, ss.t2, ss.t3, ss.t4, ss.t5,b.id as ot_id From sb_sbinfo ss , sb_detailInfo b where ss.id=b.sbid
3.把查询出来的ILIST通过反射查询实体类中的字段,再转换成DATATABLE,最后返回一个DATATABLE.
ISession session = this._sessionManager.OpenSession();IQuery query = session.CreateSQLQuery(sql);
Assembly assembly = Assembly.Load("Equipment.Modules.API");Type type = assembly.GetType("Equipment.Modules.API.Domain.sb_sbinfo");PropertyInfo[] myPropertyInfo = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);IList li = query.List();
DataTable dt= getPropertyInfo(myPropertyInfo, li);return dt;
自动生成DATAtable类如下:
public DataTable getPropertyInfo(PropertyInfo[] myPropertyInfo,IList li){DataTable dt = new DataTable();string propStr = "";DataColumn column;DataRow row;// Display information for all properties.for (int j = 0 ; j < li.Count ; j++){row = dt.NewRow();for (int i = 0; i < myPropertyInfo.Length; i++){PropertyInfo myPropInfo = (PropertyInfo)myPropertyInfo[i];if (dt.Columns[myPropInfo.Name] == null){if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]"){column = new DataColumn(myPropInfo.Name, typeof(Int32));dt.Columns.Add(column);}else if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Decimal]"){column = new DataColumn(myPropInfo.Name, typeof(Decimal));dt.Columns.Add(column);}else if (myPropInfo.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.DateTime]"){column = new DataColumn(myPropInfo.Name, typeof(DateTime));dt.Columns.Add(column);}else{column = new DataColumn(myPropInfo.Name, myPropInfo.PropertyType);dt.Columns.Add(column);}Object[] obj = (Object[])li[j];//添加值if (obj[i] != null){row[myPropInfo.Name] = obj[i];}else{row[myPropInfo.Name] = System.DBNull.Value;}}else{Object[] obj = (Object[])li[j];//添加值if (obj[i] != null){row[myPropInfo.Name] = obj[i];}else{row[myPropInfo.Name] = System.DBNull.Value;}}}dt.Rows.Add(row);}return dt;} 通过以上可以生成完美的DataTable，但是需要注意的是,实体类字段的顺序,必须保持和查询语句中的查询顺序一样..
转载于:.html