|
0
|
1 using System;
|
|
|
2 using System.Collections;
|
|
|
3 using System.Collections.Generic;
|
|
|
4 using System.Text;
|
|
|
5
|
|
|
6 using BLToolkit.Aspects;
|
|
|
7 using BLToolkit.Data;
|
|
|
8 using BLToolkit.Data.DataProvider;
|
|
|
9 using BLToolkit.Mapping;
|
|
|
10 using BLToolkit.Reflection;
|
|
|
11 using BLToolkit.Reflection.Extension;
|
|
|
12
|
|
|
13 namespace BLToolkit.DataAccess
|
|
|
14 {
|
|
|
15 public abstract class SqlQueryBase : DataAccessorBase
|
|
|
16 {
|
|
|
17 #region Constructors
|
|
|
18
|
|
|
19 protected SqlQueryBase()
|
|
|
20 {
|
|
|
21 }
|
|
|
22
|
|
|
23 protected SqlQueryBase(DbManager dbManager)
|
|
|
24 : base(dbManager)
|
|
|
25 {
|
|
|
26 }
|
|
|
27
|
|
|
28 protected SqlQueryBase(DbManager dbManager, bool dispose)
|
|
|
29 : base(dbManager, dispose)
|
|
|
30 {
|
|
|
31 }
|
|
|
32
|
|
|
33 #endregion
|
|
|
34
|
|
|
35 #region Protected Members
|
|
|
36
|
|
|
37 [NoInterception]
|
|
|
38 protected virtual MemberMapper[] GetFieldList(ObjectMapper om)
|
|
|
39 {
|
|
|
40 var list = new List<MemberMapper>(om.Count);
|
|
|
41
|
|
|
42 foreach (MemberMapper mm in om)
|
|
|
43 if (mm.MapMemberInfo.SqlIgnore == false)
|
|
|
44 list.Add(mm);
|
|
|
45
|
|
|
46 return list.ToArray();
|
|
|
47 }
|
|
|
48
|
|
|
49 [NoInterception]
|
|
|
50 protected virtual MemberMapper[] GetNonKeyFieldList(ObjectMapper om)
|
|
|
51 {
|
|
|
52 var typeExt = TypeExtension.GetTypeExtension(om.TypeAccessor.OriginalType, Extensions);
|
|
|
53 var list = new List<MemberMapper>();
|
|
|
54
|
|
|
55 foreach (MemberMapper mm in om)
|
|
|
56 {
|
|
|
57 if (mm.MapMemberInfo.SqlIgnore)
|
|
|
58 continue;
|
|
|
59
|
|
|
60 var ma = mm.MapMemberInfo.MemberAccessor;
|
|
|
61
|
|
|
62 bool isSet;
|
|
|
63 MappingSchema.MetadataProvider.GetPrimaryKeyOrder(om.TypeAccessor.OriginalType, typeExt, ma, out isSet);
|
|
|
64
|
|
|
65 if (!isSet)
|
|
|
66 list.Add(mm);
|
|
|
67 }
|
|
|
68
|
|
|
69 return list.ToArray();
|
|
|
70 }
|
|
|
71
|
|
|
72 struct MemberOrder
|
|
|
73 {
|
|
|
74 public MemberOrder(MemberMapper memberMapper, int order)
|
|
|
75 {
|
|
|
76 MemberMapper = memberMapper;
|
|
|
77 Order = order;
|
|
|
78 }
|
|
|
79
|
|
|
80 public readonly MemberMapper MemberMapper;
|
|
|
81 public readonly int Order;
|
|
|
82 }
|
|
|
83
|
|
|
84 private static readonly Hashtable _keyList = new Hashtable();
|
|
|
85
|
|
|
86 [NoInterception]
|
|
|
87 protected internal virtual MemberMapper[] GetKeyFieldList(DbManager db, Type type)
|
|
|
88 {
|
|
|
89 var key = type.FullName + "$" + db.DataProvider.UniqueName;
|
|
|
90 var mmList = (MemberMapper[])_keyList[key];
|
|
|
91
|
|
|
92 if (mmList == null)
|
|
|
93 {
|
|
|
94 var typeExt = TypeExtension.GetTypeExtension(type, Extensions);
|
|
|
95 var list = new List<MemberOrder>();
|
|
|
96
|
|
|
97 foreach (MemberMapper mm in db.MappingSchema.GetObjectMapper(type))
|
|
|
98 {
|
|
|
99 if (mm.MapMemberInfo.SqlIgnore)
|
|
|
100 continue;
|
|
|
101
|
|
|
102 var ma = mm.MapMemberInfo.MemberAccessor;
|
|
|
103
|
|
|
104 if (TypeHelper.IsScalar(ma.Type))
|
|
|
105 {
|
|
|
106 bool isSet;
|
|
|
107 var order = MappingSchema.MetadataProvider.GetPrimaryKeyOrder(type, typeExt, ma, out isSet);
|
|
|
108
|
|
|
109 if (isSet)
|
|
|
110 list.Add(new MemberOrder(mm, order));
|
|
|
111 }
|
|
|
112 }
|
|
|
113
|
|
|
114 list.Sort((x, y) => x.Order - y.Order);
|
|
|
115
|
|
|
116 _keyList[key] = mmList = new MemberMapper[list.Count];
|
|
|
117
|
|
|
118 for (var i = 0; i < list.Count; i++)
|
|
|
119 mmList[i] = list[i].MemberMapper;
|
|
|
120 }
|
|
|
121
|
|
|
122 return mmList;
|
|
|
123 }
|
|
|
124
|
|
|
125 [NoInterception]
|
|
|
126 protected virtual void AddWherePK(DbManager db, SqlQueryInfo query, StringBuilder sb, int nParameter)
|
|
|
127 {
|
|
|
128 sb.Append("WHERE\n");
|
|
|
129
|
|
|
130 var memberMappers = GetKeyFieldList(db, query.ObjectType);
|
|
|
131
|
|
|
132 if (memberMappers.Length == 0)
|
|
|
133 throw new DataAccessException(
|
|
|
134 string.Format("No primary key field(s) in the type '{0}'.", query.ObjectType.FullName));
|
|
|
135
|
|
|
136 foreach (var mm in memberMappers)
|
|
|
137 {
|
|
|
138 var p = query.AddParameter(
|
|
|
139 db.DataProvider.Convert(mm.Name + "_W", ConvertType.NameToQueryParameter).ToString(),
|
|
|
140 mm.Name);
|
|
|
141
|
|
|
142 sb.AppendFormat("\t{0} = ", db.DataProvider.Convert(p.FieldName, ConvertType.NameToQueryField));
|
|
|
143
|
|
|
144 if (nParameter < 0)
|
|
|
145 sb.AppendFormat("{0} AND\n", p.ParameterName);
|
|
|
146 else
|
|
|
147 sb.AppendFormat("{{{0}}} AND\n", nParameter++);
|
|
|
148 }
|
|
|
149
|
|
|
150 sb.Remove(sb.Length - 5, 5);
|
|
|
151 }
|
|
|
152
|
|
|
153 protected SqlQueryInfo CreateSelectByKeySqlText(DbManager db, Type type)
|
|
|
154 {
|
|
|
155 var om = db.MappingSchema.GetObjectMapper(type);
|
|
|
156 var sb = new StringBuilder();
|
|
|
157 var query = new SqlQueryInfo(om);
|
|
|
158
|
|
|
159 sb.Append("SELECT\n");
|
|
|
160
|
|
|
161 foreach (var mm in GetFieldList(om))
|
|
|
162 sb.AppendFormat("\t{0},\n",
|
|
|
163 db.DataProvider.Convert(mm.Name, ConvertType.NameToQueryField));
|
|
|
164
|
|
|
165 sb.Remove(sb.Length - 2, 1);
|
|
|
166
|
|
|
167 sb.Append("FROM\n\t");
|
|
|
168
|
|
|
169 AppendTableName(sb, db, type);
|
|
|
170
|
|
|
171 AddWherePK(db, query, sb, -1);
|
|
|
172
|
|
|
173 query.QueryText = sb.ToString();
|
|
|
174
|
|
|
175 return query;
|
|
|
176 }
|
|
|
177
|
|
|
178 // NOTE changed to virtual
|
|
|
179 protected virtual void AppendTableName(StringBuilder sb, DbManager db, Type type)
|
|
|
180 {
|
|
|
181 var database = GetDatabaseName(type);
|
|
|
182 var owner = GetOwnerName (type);
|
|
|
183 var name = GetTableName (type);
|
|
|
184
|
|
|
185 db.DataProvider.CreateSqlProvider().BuildTableName(sb,
|
|
|
186 database == null ? null : db.DataProvider.Convert(database, ConvertType.NameToDatabase). ToString(),
|
|
|
187 owner == null ? null : db.DataProvider.Convert(owner, ConvertType.NameToOwner). ToString(),
|
|
|
188 name == null ? null : db.DataProvider.Convert(name, ConvertType.NameToQueryTable).ToString());
|
|
|
189
|
|
|
190 sb.AppendLine();
|
|
|
191 }
|
|
|
192
|
|
|
193 protected SqlQueryInfo CreateSelectAllSqlText(DbManager db, Type type)
|
|
|
194 {
|
|
|
195 var om = db.MappingSchema.GetObjectMapper(type);
|
|
|
196 var sb = new StringBuilder();
|
|
|
197 var query = new SqlQueryInfo(om);
|
|
|
198
|
|
|
199 sb.Append("SELECT\n");
|
|
|
200
|
|
|
201 foreach (var mm in GetFieldList(om))
|
|
|
202 sb.AppendFormat("\t{0},\n",
|
|
|
203 db.DataProvider.Convert(mm.Name, ConvertType.NameToQueryField));
|
|
|
204
|
|
|
205 sb.Remove(sb.Length - 2, 1);
|
|
|
206
|
|
|
207 sb.Append("FROM\n\t");
|
|
|
208 AppendTableName(sb, db, type);
|
|
|
209
|
|
|
210 query.QueryText = sb.ToString();
|
|
|
211
|
|
|
212 return query;
|
|
|
213 }
|
|
|
214
|
|
|
215 protected SqlQueryInfo CreateInsertSqlText(DbManager db, Type type, int nParameter)
|
|
|
216 {
|
|
|
217 var typeExt = TypeExtension.GetTypeExtension(type, Extensions);
|
|
|
218 var om = db.MappingSchema.GetObjectMapper(type);
|
|
|
219 var list = new List<MemberMapper>();
|
|
|
220 var sb = new StringBuilder();
|
|
|
221 var query = new SqlQueryInfo(om);
|
|
|
222 var mp = MappingSchema.MetadataProvider;
|
|
|
223
|
|
|
224 sb.Append("INSERT INTO ");
|
|
|
225 AppendTableName(sb, db, type);
|
|
|
226 sb.Append(" (\n");
|
|
|
227
|
|
|
228 foreach (var mm in GetFieldList(om))
|
|
|
229 {
|
|
|
230 // IT: This works incorrectly for complex mappers.
|
|
|
231 //
|
|
|
232 // [2009-03-24] ili: use mm.MemberAccessor instead of mm.ComplexMemberAccessor
|
|
|
233 // as in CreateUpdateSqlText
|
|
|
234 //
|
|
|
235
|
|
|
236 bool isSet;
|
|
|
237 var nonUpdatableAttribute = mp.GetNonUpdatableAttribute(type, typeExt, mm.MapMemberInfo.MemberAccessor, out isSet);
|
|
|
238
|
|
|
239 if (nonUpdatableAttribute == null || !isSet || nonUpdatableAttribute.OnInsert == false)
|
|
|
240 {
|
|
|
241 sb.AppendFormat("\t{0},\n",
|
|
|
242 db.DataProvider.Convert(mm.Name, ConvertType.NameToQueryField));
|
|
|
243 list.Add(mm);
|
|
|
244 }
|
|
|
245 }
|
|
|
246
|
|
|
247 sb.Remove(sb.Length - 2, 1);
|
|
|
248
|
|
|
249 sb.Append(") VALUES (\n");
|
|
|
250
|
|
|
251 foreach (var mm in list)
|
|
|
252 {
|
|
|
253 var p = query.AddParameter(
|
|
|
254 db.DataProvider.Convert(mm.Name + "_P", ConvertType.NameToQueryParameter).ToString(),
|
|
|
255 mm.Name);
|
|
|
256
|
|
|
257 if (nParameter < 0)
|
|
|
258 sb.AppendFormat("\t{0},\n", p.ParameterName);
|
|
|
259 //sb.AppendFormat("\t{0},\n", db.DataProvider.Convert(p.ParameterName, ConvertType.NameToQueryParameter));
|
|
|
260 else
|
|
|
261 sb.AppendFormat("\t{{{0}}},\n", nParameter++);
|
|
|
262 }
|
|
|
263
|
|
|
264 sb.Remove(sb.Length - 2, 1);
|
|
|
265
|
|
|
266 sb.Append(")");
|
|
|
267
|
|
|
268 query.QueryText = sb.ToString();
|
|
|
269
|
|
|
270 return query;
|
|
|
271 }
|
|
|
272
|
|
|
273 protected SqlQueryInfo CreateUpdateSqlText(DbManager db, Type type, int nParameter)
|
|
|
274 {
|
|
|
275 var typeExt = TypeExtension.GetTypeExtension(type, Extensions);
|
|
|
276 var om = db.MappingSchema.GetObjectMapper(type);
|
|
|
277 var sb = new StringBuilder();
|
|
|
278 var query = new SqlQueryInfo(om);
|
|
|
279 var mp = MappingSchema.MetadataProvider;
|
|
|
280
|
|
|
281 sb.Append("UPDATE\n\t");
|
|
|
282 AppendTableName(sb, db, type);
|
|
|
283 sb.Append("\nSET\n");
|
|
|
284
|
|
|
285 var fields = GetFieldList(om);
|
|
|
286 var hasFields = false;
|
|
|
287
|
|
|
288 foreach (var mm in fields)
|
|
|
289 {
|
|
|
290 bool isSet;
|
|
|
291
|
|
|
292 var nonUpdatableAttribute = mp.GetNonUpdatableAttribute(type, typeExt, mm.MapMemberInfo.MemberAccessor, out isSet);
|
|
|
293
|
|
|
294 if (nonUpdatableAttribute != null && isSet && nonUpdatableAttribute.OnUpdate == true)
|
|
|
295 continue;
|
|
|
296
|
|
|
297 mp.GetPrimaryKeyOrder(type, typeExt, mm.MapMemberInfo.MemberAccessor, out isSet);
|
|
|
298
|
|
|
299 if (isSet)
|
|
|
300 continue;
|
|
|
301
|
|
|
302 hasFields = true;
|
|
|
303
|
|
|
304 var p = query.AddParameter(
|
|
|
305 db.DataProvider.Convert(mm.Name + "_P", ConvertType.NameToQueryParameter).ToString(),
|
|
|
306 mm.Name);
|
|
|
307
|
|
|
308 sb.AppendFormat("\t{0} = ", db.DataProvider.Convert(p.FieldName, ConvertType.NameToQueryField));
|
|
|
309
|
|
|
310 if (nParameter < 0)
|
|
|
311 sb.AppendFormat("{0},\n", p.ParameterName);
|
|
|
312 else
|
|
|
313 sb.AppendFormat("\t{{{0}}},\n", nParameter++);
|
|
|
314 }
|
|
|
315
|
|
|
316 if (!hasFields)
|
|
|
317 throw new DataAccessException(
|
|
|
318 string.Format("There are no fields to update in the type '{0}'.", query.ObjectType.FullName));
|
|
|
319
|
|
|
320 sb.Remove(sb.Length - 2, 1);
|
|
|
321
|
|
|
322 AddWherePK(db, query, sb, nParameter);
|
|
|
323
|
|
|
324 query.QueryText = sb.ToString();
|
|
|
325
|
|
|
326 return query;
|
|
|
327 }
|
|
|
328
|
|
|
329 protected SqlQueryInfo CreateDeleteSqlText(DbManager db, Type type, int nParameter)
|
|
|
330 {
|
|
|
331 var om = db.MappingSchema.GetObjectMapper(type);
|
|
|
332 var sb = new StringBuilder();
|
|
|
333 var query = new SqlQueryInfo(om);
|
|
|
334
|
|
|
335 sb.Append("DELETE FROM\n\t");
|
|
|
336 AppendTableName(sb, db, type);
|
|
|
337 sb.AppendLine();
|
|
|
338
|
|
|
339 AddWherePK(db, query, sb, nParameter);
|
|
|
340
|
|
|
341 query.QueryText = sb.ToString();
|
|
|
342
|
|
|
343 return query;
|
|
|
344 }
|
|
|
345
|
|
|
346 [NoInterception]
|
|
|
347 protected virtual SqlQueryInfo CreateSqlText(DbManager db, Type type, string actionName)
|
|
|
348 {
|
|
|
349 switch (actionName)
|
|
|
350 {
|
|
|
351 case "SelectByKey": return CreateSelectByKeySqlText(db, type);
|
|
|
352 case "SelectAll": return CreateSelectAllSqlText (db, type);
|
|
|
353 case "Insert": return CreateInsertSqlText (db, type, -1);
|
|
|
354 case "InsertBatch": return CreateInsertSqlText (db, type, 0);
|
|
|
355 case "Update": return CreateUpdateSqlText (db, type, -1);
|
|
|
356 case "UpdateBatch": return CreateUpdateSqlText (db, type, 0);
|
|
|
357 case "Delete": return CreateDeleteSqlText (db, type, -1);
|
|
|
358 case "DeleteBatch": return CreateDeleteSqlText (db, type, 0);
|
|
|
359 default:
|
|
|
360 throw new DataAccessException(
|
|
|
361 string.Format("Unknown action '{0}'.", actionName));
|
|
|
362 }
|
|
|
363 }
|
|
|
364
|
|
|
365 private static readonly Hashtable _actionSqlQueryInfo = new Hashtable();
|
|
|
366
|
|
|
367 [NoInterception]
|
|
|
368 public virtual SqlQueryInfo GetSqlQueryInfo(DbManager db, Type type, string actionName)
|
|
|
369 {
|
|
|
370 var key = type.FullName + "$" + actionName + "$" + db.DataProvider.UniqueName + "$" + GetTableName(type);
|
|
|
371 var query = (SqlQueryInfo)_actionSqlQueryInfo[key];
|
|
|
372
|
|
|
373 if (query == null)
|
|
|
374 {
|
|
|
375 query = CreateSqlText(db, type, actionName);
|
|
|
376 _actionSqlQueryInfo[key] = query;
|
|
|
377 }
|
|
|
378
|
|
|
379 return query;
|
|
|
380 }
|
|
|
381
|
|
|
382 #endregion
|
|
|
383 }
|
|
|
384 }
|
