Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/AccessSqlProvider.cs @ 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 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 }