|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3 using System.Text;
|
|
|
4
|
|
|
5 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
6 {
|
|
|
7 using DataProvider;
|
|
|
8 using Reflection;
|
|
|
9
|
|
|
10 public class AccessSqlProvider : BasicSqlProvider
|
|
|
11 {
|
|
|
12 public override int CommandCount(SqlQuery sqlQuery)
|
|
|
13 {
|
|
|
14 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
|
|
|
15 }
|
|
|
16
|
|
|
17 protected override void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
18 {
|
|
|
19 sb.AppendLine("SELECT @@IDENTITY");
|
|
|
20 }
|
|
|
21
|
|
|
22 //public override bool IsSkipSupported { get { return SqlQuery.Select.TakeValue != null; } }
|
|
|
23 public override bool IsSkipSupported { get { return false; } }
|
|
|
24 public override bool TakeAcceptsParameter { get { return false; } }
|
|
|
25 public override bool IsCountSubQuerySupported { get { return false; } }
|
|
|
26 public override bool IsNestedJoinSupported { get { return false; } }
|
|
|
27 public override bool IsInsertOrUpdateSupported { get { return false; } }
|
|
|
28
|
|
|
29 public override bool ConvertCountSubQuery(SqlQuery subQuery)
|
|
|
30 {
|
|
|
31 return !subQuery.Where.IsEmpty;
|
|
|
32 }
|
|
|
33
|
|
|
34 #region Skip / Take Support
|
|
|
35
|
|
|
36 protected override string FirstFormat { get { return "TOP {0}"; } }
|
|
|
37
|
|
|
38 protected override void BuildSql(StringBuilder sb)
|
|
|
39 {
|
|
|
40 if (NeedSkip)
|
|
|
41 {
|
|
|
42 AlternativeBuildSql2(sb, base.BuildSql);
|
|
|
43 return;
|
|
|
44 }
|
|
|
45
|
|
|
46 if (SqlQuery.From.Tables.Count == 0 && SqlQuery.Select.Columns.Count == 1)
|
|
|
47 {
|
|
|
48 if (SqlQuery.Select.Columns[0].Expression is SqlFunction)
|
|
|
49 {
|
|
|
50 var func = (SqlFunction)SqlQuery.Select.Columns[0].Expression;
|
|
|
51
|
|
|
52 if (func.Name == "Iif" && func.Parameters.Length == 3 && func.Parameters[0] is SqlQuery.SearchCondition)
|
|
|
53 {
|
|
|
54 var sc = (SqlQuery.SearchCondition)func.Parameters[0];
|
|
|
55
|
|
|
56 if (sc.Conditions.Count == 1 && sc.Conditions[0].Predicate is SqlQuery.Predicate.FuncLike)
|
|
|
57 {
|
|
|
58 var p = (SqlQuery.Predicate.FuncLike)sc.Conditions[0].Predicate;
|
|
|
59
|
|
|
60 if (p.Function.Name == "EXISTS")
|
|
|
61 {
|
|
|
62 BuildAnyAsCount(sb);
|
|
|
63 return;
|
|
|
64 }
|
|
|
65 }
|
|
|
66 }
|
|
|
67 }
|
|
|
68 else if (SqlQuery.Select.Columns[0].Expression is SqlQuery.SearchCondition)
|
|
|
69 {
|
|
|
70 var sc = (SqlQuery.SearchCondition)SqlQuery.Select.Columns[0].Expression;
|
|
|
71
|
|
|
72 if (sc.Conditions.Count == 1 && sc.Conditions[0].Predicate is SqlQuery.Predicate.FuncLike)
|
|
|
73 {
|
|
|
74 var p = (SqlQuery.Predicate.FuncLike)sc.Conditions[0].Predicate;
|
|
|
75
|
|
|
76 if (p.Function.Name == "EXISTS")
|
|
|
77 {
|
|
|
78 BuildAnyAsCount(sb);
|
|
|
79 return;
|
|
|
80 }
|
|
|
81 }
|
|
|
82 }
|
|
|
83 }
|
|
|
84
|
|
|
85 base.BuildSql(sb);
|
|
|
86 }
|
|
|
87
|
|
|
88 SqlQuery.Column _selectColumn;
|
|
|
89
|
|
|
90 void BuildAnyAsCount(StringBuilder sb)
|
|
|
91 {
|
|
|
92 SqlQuery.SearchCondition cond;
|
|
|
93
|
|
|
94 if (SqlQuery.Select.Columns[0].Expression is SqlFunction)
|
|
|
95 {
|
|
|
96 var func = (SqlFunction)SqlQuery.Select.Columns[0].Expression;
|
|
|
97 cond = (SqlQuery.SearchCondition)func.Parameters[0];
|
|
|
98 }
|
|
|
99 else
|
|
|
100 {
|
|
|
101 cond = (SqlQuery.SearchCondition)SqlQuery.Select.Columns[0].Expression;
|
|
|
102 }
|
|
|
103
|
|
|
104 var exist = ((SqlQuery.Predicate.FuncLike)cond.Conditions[0].Predicate).Function;
|
|
|
105 var query = (SqlQuery)exist.Parameters[0];
|
|
|
106
|
|
|
107 _selectColumn = new SqlQuery.Column(SqlQuery, new SqlExpression(cond.Conditions[0].IsNot ? "Count(*) = 0" : "Count(*) > 0"), SqlQuery.Select.Columns[0].Alias);
|
|
|
108
|
|
|
109 BuildSql(0, query, sb, 0, 0, false);
|
|
|
110
|
|
|
111 _selectColumn = null;
|
|
|
112 }
|
|
|
113
|
|
|
114 protected override IEnumerable<SqlQuery.Column> GetSelectedColumns()
|
|
|
115 {
|
|
|
116 if (_selectColumn != null)
|
|
|
117 return new[] { _selectColumn };
|
|
|
118
|
|
|
119 if (NeedSkip && !SqlQuery.OrderBy.IsEmpty)
|
|
|
120 return AlternativeGetSelectedColumns(base.GetSelectedColumns);
|
|
|
121
|
|
|
122 return base.GetSelectedColumns();
|
|
|
123 }
|
|
|
124
|
|
|
125 protected override void BuildSkipFirst(StringBuilder sb)
|
|
|
126 {
|
|
|
127 if (NeedSkip)
|
|
|
128 {
|
|
|
129 if (!NeedTake)
|
|
|
130 {
|
|
|
131 sb.AppendFormat(" TOP {0}", int.MaxValue);
|
|
|
132 }
|
|
|
133 else if (!SqlQuery.OrderBy.IsEmpty)
|
|
|
134 {
|
|
|
135 sb.Append(" TOP ");
|
|
|
136 BuildExpression(sb, Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue));
|
|
|
137 }
|
|
|
138 }
|
|
|
139 else
|
|
|
140 base.BuildSkipFirst(sb);
|
|
|
141 }
|
|
|
142
|
|
|
143 #endregion
|
|
|
144
|
|
|
145 protected override ISqlProvider CreateSqlProvider()
|
|
|
146 {
|
|
|
147 return new AccessSqlProvider();
|
|
|
148 }
|
|
|
149
|
|
|
150 protected override bool ParenthesizeJoin()
|
|
|
151 {
|
|
|
152 return true;
|
|
|
153 }
|
|
|
154
|
|
|
155 public override ISqlPredicate ConvertPredicate(ISqlPredicate predicate)
|
|
|
156 {
|
|
|
157 if (predicate is SqlQuery.Predicate.Like)
|
|
|
158 {
|
|
|
159 var l = (SqlQuery.Predicate.Like)predicate;
|
|
|
160
|
|
|
161 if (l.Escape != null)
|
|
|
162 {
|
|
|
163 if (l.Expr2 is SqlValue && l.Escape is SqlValue)
|
|
|
164 {
|
|
|
165 var text = ((SqlValue) l.Expr2).Value.ToString();
|
|
|
166 var val = new SqlValue(ReescapeLikeText(text, (char)((SqlValue)l.Escape).Value));
|
|
|
167
|
|
|
168 return new SqlQuery.Predicate.Like(l.Expr1, l.IsNot, val, null);
|
|
|
169 }
|
|
|
170
|
|
|
171 if (l.Expr2 is SqlParameter)
|
|
|
172 {
|
|
|
173 var p = (SqlParameter)l.Expr2;
|
|
|
174 var v = "";
|
|
|
175
|
|
|
176 if (p.ValueConverter != null)
|
|
|
177 v = p.ValueConverter(" ") as string;
|
|
|
178
|
|
|
179 p.SetLikeConverter(v.StartsWith("%") ? "%" : "", v.EndsWith("%") ? "%" : "");
|
|
|
180
|
|
|
181 return new SqlQuery.Predicate.Like(l.Expr1, l.IsNot, p, null);
|
|
|
182 }
|
|
|
183 }
|
|
|
184 }
|
|
|
185
|
|
|
186 return base.ConvertPredicate(predicate);
|
|
|
187 }
|
|
|
188
|
|
|
189 static string ReescapeLikeText(string text, char esc)
|
|
|
190 {
|
|
|
191 var sb = new StringBuilder(text.Length);
|
|
|
192
|
|
|
193 for (var i = 0; i < text.Length; i++)
|
|
|
194 {
|
|
|
195 var c = text[i];
|
|
|
196
|
|
|
197 if (c == esc)
|
|
|
198 {
|
|
|
199 sb.Append('[');
|
|
|
200 sb.Append(text[++i]);
|
|
|
201 sb.Append(']');
|
|
|
202 }
|
|
|
203 else if (c == '[')
|
|
|
204 sb.Append("[[]");
|
|
|
205 else
|
|
|
206 sb.Append(c);
|
|
|
207 }
|
|
|
208
|
|
|
209 return sb.ToString();
|
|
|
210 }
|
|
|
211
|
|
|
212 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
213 {
|
|
|
214 expr = base.ConvertExpression(expr);
|
|
|
215
|
|
|
216 if (expr is SqlBinaryExpression)
|
|
|
217 {
|
|
|
218 var be = (SqlBinaryExpression)expr;
|
|
|
219
|
|
|
220 switch (be.Operation[0])
|
|
|
221 {
|
|
|
222 case '%': return new SqlBinaryExpression(be.SystemType, be.Expr1, "MOD", be.Expr2, Precedence.Additive - 1);
|
|
|
223 case '&':
|
|
|
224 case '|':
|
|
|
225 case '^': throw new SqlException("Operator '{0}' is not supported by the {1}.", be.Operation, GetType().Name);
|
|
|
226 }
|
|
|
227 }
|
|
|
228 else if (expr is SqlFunction)
|
|
|
229 {
|
|
|
230 var func = (SqlFunction) expr;
|
|
|
231
|
|
|
232 switch (func.Name)
|
|
|
233 {
|
|
|
234 case "Coalesce":
|
|
|
235
|
|
|
236 if (func.Parameters.Length > 2)
|
|
|
237 {
|
|
|
238 var parms = new ISqlExpression[func.Parameters.Length - 1];
|
|
|
239
|
|
|
240 Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
|
|
|
241 return new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms));
|
|
|
242 }
|
|
|
243
|
|
|
244 var sc = new SqlQuery.SearchCondition();
|
|
|
245
|
|
|
246 sc.Conditions.Add(new SqlQuery.Condition(false, new SqlQuery.Predicate.IsNull(func.Parameters[0], false)));
|
|
|
247
|
|
|
248 return new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]);
|
|
|
249
|
|
|
250 case "CASE" : return ConvertCase(func.SystemType, func.Parameters, 0);
|
|
|
251 case "CharIndex" :
|
|
|
252 return func.Parameters.Length == 2?
|
|
|
253 new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)):
|
|
|
254 new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1));
|
|
|
255
|
|
|
256 case "Convert" :
|
|
|
257 {
|
|
|
258 switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
|
|
|
259 {
|
|
|
260 case TypeCode.String : return new SqlFunction(func.SystemType, "CStr", func.Parameters[1]);
|
|
|
261 case TypeCode.DateTime :
|
|
|
262 if (IsDateDataType(func.Parameters[0], "Date"))
|
|
|
263 return new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]);
|
|
|
264
|
|
|
265 if (IsTimeDataType(func.Parameters[0]))
|
|
|
266 return new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]);
|
|
|
267
|
|
|
268 return new SqlFunction(func.SystemType, "CDate", func.Parameters[1]);
|
|
|
269
|
|
|
270 default:
|
|
|
271 if (func.SystemType == typeof(DateTime))
|
|
|
272 goto case TypeCode.DateTime;
|
|
|
273 break;
|
|
|
274 }
|
|
|
275
|
|
|
276 return func.Parameters[1];
|
|
|
277 }
|
|
|
278
|
|
|
279 /*
|
|
|
280 case "Convert" :
|
|
|
281 {
|
|
|
282 string name = null;
|
|
|
283
|
|
|
284 switch (((SqlDataType)func.Parameters[0]).DbType)
|
|
|
285 {
|
|
|
286 case SqlDbType.BigInt : name = "CLng"; break;
|
|
|
287 case SqlDbType.TinyInt : name = "CByte"; break;
|
|
|
288 case SqlDbType.Int :
|
|
|
289 case SqlDbType.SmallInt : name = "CInt"; break;
|
|
|
290 case SqlDbType.Bit : name = "CBool"; break;
|
|
|
291 case SqlDbType.Char :
|
|
|
292 case SqlDbType.Text :
|
|
|
293 case SqlDbType.VarChar :
|
|
|
294 case SqlDbType.NChar :
|
|
|
295 case SqlDbType.NText :
|
|
|
296 case SqlDbType.NVarChar : name = "CStr"; break;
|
|
|
297 case SqlDbType.DateTime :
|
|
|
298 case SqlDbType.Date :
|
|
|
299 case SqlDbType.Time :
|
|
|
300 case SqlDbType.DateTime2 :
|
|
|
301 case SqlDbType.SmallDateTime :
|
|
|
302 case SqlDbType.DateTimeOffset : name = "CDate"; break;
|
|
|
303 case SqlDbType.Decimal : name = "CDec"; break;
|
|
|
304 case SqlDbType.Float : name = "CDbl"; break;
|
|
|
305 case SqlDbType.Money :
|
|
|
306 case SqlDbType.SmallMoney : name = "CCur"; break;
|
|
|
307 case SqlDbType.Real : name = "CSng"; break;
|
|
|
308 case SqlDbType.Image :
|
|
|
309 case SqlDbType.Binary :
|
|
|
310 case SqlDbType.UniqueIdentifier :
|
|
|
311 case SqlDbType.Timestamp :
|
|
|
312 case SqlDbType.VarBinary :
|
|
|
313 case SqlDbType.Variant :
|
|
|
314 case SqlDbType.Xml :
|
|
|
315 case SqlDbType.Udt :
|
|
|
316 case SqlDbType.Structured : name = "CVar"; break;
|
|
|
317 }
|
|
|
318
|
|
|
319 return new SqlFunction(name, func.Parameters[1]);
|
|
|
320 }
|
|
|
321 */
|
|
|
322 }
|
|
|
323 }
|
|
|
324
|
|
|
325 return expr;
|
|
|
326 }
|
|
|
327
|
|
|
328 SqlFunction ConvertCase(Type systemType, ISqlExpression[] parameters, int start)
|
|
|
329 {
|
|
|
330 var len = parameters.Length - start;
|
|
|
331
|
|
|
332 if (len < 3)
|
|
|
333 throw new SqlException("CASE statement is not supported by the {0}.", GetType().Name);
|
|
|
334
|
|
|
335 if (len == 3)
|
|
|
336 return new SqlFunction(systemType, "Iif", parameters[start], parameters[start + 1], parameters[start + 2]);
|
|
|
337
|
|
|
338 return new SqlFunction(systemType, "Iif", parameters[start], parameters[start + 1], ConvertCase(systemType, parameters, start + 2));
|
|
|
339 }
|
|
|
340
|
|
|
341 public override void BuildValue(StringBuilder sb, object value)
|
|
|
342 {
|
|
|
343 if (value is bool)
|
|
|
344 sb.Append(value);
|
|
|
345 else if (value is Guid)
|
|
|
346 sb.Append("'").Append(((Guid)value).ToString("B")).Append("'");
|
|
|
347 else
|
|
|
348 base.BuildValue(sb, value);
|
|
|
349 }
|
|
|
350
|
|
|
351 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
352 {
|
|
|
353 sqlQuery = base.Finalize(sqlQuery);
|
|
|
354
|
|
|
355 switch (sqlQuery.QueryType)
|
|
|
356 {
|
|
|
357 case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
|
|
|
358 default : return sqlQuery;
|
|
|
359 }
|
|
|
360 }
|
|
|
361
|
|
|
362 protected override void BuildUpdateClause(StringBuilder sb)
|
|
|
363 {
|
|
|
364 base.BuildFromClause(sb);
|
|
|
365 sb.Remove(0, 4).Insert(0, "UPDATE");
|
|
|
366 base.BuildUpdateSet(sb);
|
|
|
367 }
|
|
|
368
|
|
|
369 protected override void BuildFromClause(StringBuilder sb)
|
|
|
370 {
|
|
|
371 if (!SqlQuery.IsUpdate)
|
|
|
372 base.BuildFromClause(sb);
|
|
|
373 }
|
|
|
374
|
|
|
375 public override object Convert(object value, ConvertType convertType)
|
|
|
376 {
|
|
|
377 switch (convertType)
|
|
|
378 {
|
|
|
379 case ConvertType.NameToQueryParameter:
|
|
|
380 case ConvertType.NameToCommandParameter:
|
|
|
381 case ConvertType.NameToSprocParameter:
|
|
|
382 return "@" + value;
|
|
|
383
|
|
|
384 case ConvertType.NameToQueryField:
|
|
|
385 case ConvertType.NameToQueryFieldAlias:
|
|
|
386 case ConvertType.NameToQueryTableAlias:
|
|
|
387 {
|
|
|
388 var name = value.ToString();
|
|
|
389
|
|
|
390 if (name.Length > 0 && name[0] == '[')
|
|
|
391 return value;
|
|
|
392 }
|
|
|
393
|
|
|
394 return "[" + value + "]";
|
|
|
395
|
|
|
396 case ConvertType.NameToDatabase:
|
|
|
397 case ConvertType.NameToOwner:
|
|
|
398 case ConvertType.NameToQueryTable:
|
|
|
399 {
|
|
|
400 var name = value.ToString();
|
|
|
401
|
|
|
402 if (name.Length > 0 && name[0] == '[')
|
|
|
403 return value;
|
|
|
404
|
|
|
405 if (name.IndexOf('.') > 0)
|
|
|
406 value = string.Join("].[", name.Split('.'));
|
|
|
407 }
|
|
|
408
|
|
|
409 return "[" + value + "]";
|
|
|
410
|
|
|
411 case ConvertType.SprocParameterToName:
|
|
|
412 if (value != null)
|
|
|
413 {
|
|
|
414 var str = value.ToString();
|
|
|
415 return str.Length > 0 && str[0] == '@'? str.Substring(1): str;
|
|
|
416 }
|
|
|
417
|
|
|
418 break;
|
|
|
419 }
|
|
|
420
|
|
|
421 return value;
|
|
|
422 }
|
|
|
423
|
|
|
424 protected override void BuildDateTime(StringBuilder sb, object value)
|
|
|
425 {
|
|
|
426 sb.Append(string.Format("#{0:yyyy-MM-dd HH:mm:ss}#", value));
|
|
|
427 }
|
|
|
428 }
|
|
|
429 }
|
