|
0
|
1 using System;
|
|
|
2 using System.Text;
|
|
|
3
|
|
|
4 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
5 {
|
|
|
6 public class MsSql2012SqlProvider : MsSqlSqlProvider
|
|
|
7 {
|
|
|
8 protected override string LimitFormat { get { return SqlQuery.Select.SkipValue != null ? "FETCH NEXT {0} ROWS ONLY" : null; } }
|
|
|
9 protected override string OffsetFormat { get { return "OFFSET {0} ROWS"; } }
|
|
|
10 protected override bool OffsetFirst { get { return true; } }
|
|
|
11 protected override bool BuildAlternativeSql { get { return false; } }
|
|
|
12
|
|
|
13 protected override ISqlProvider CreateSqlProvider()
|
|
|
14 {
|
|
|
15 return new MsSql2012SqlProvider();
|
|
|
16 }
|
|
|
17
|
|
|
18 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
19 {
|
|
|
20 BuildInsertOrUpdateQueryAsMerge(sb, null);
|
|
|
21 sb.AppendLine(";");
|
|
|
22 }
|
|
|
23
|
|
|
24 protected override void BuildSql(StringBuilder sb)
|
|
|
25 {
|
|
|
26 if (NeedSkip && SqlQuery.OrderBy.IsEmpty)
|
|
|
27 {
|
|
|
28 for (var i = 0; i < SqlQuery.Select.Columns.Count; i++)
|
|
|
29 SqlQuery.OrderBy.ExprAsc(new SqlValue(i + 1));
|
|
|
30 }
|
|
|
31
|
|
|
32 base.BuildSql(sb);
|
|
|
33 }
|
|
|
34
|
|
|
35 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
36 {
|
|
|
37 func = ConvertFunctionParameters(func);
|
|
|
38
|
|
|
39 switch (func.Name)
|
|
|
40 {
|
|
|
41 case "CASE" : func = ConvertCase(func.SystemType, func.Parameters, 0); break;
|
|
|
42 case "Coalesce" :
|
|
|
43
|
|
|
44 if (func.Parameters.Length > 2)
|
|
|
45 {
|
|
|
46 var parms = new ISqlExpression[func.Parameters.Length - 1];
|
|
|
47
|
|
|
48 Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
|
|
|
49 BuildFunction(sb, new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
|
|
|
50 new SqlFunction(func.SystemType, func.Name, parms)));
|
|
|
51 return;
|
|
|
52 }
|
|
|
53
|
|
|
54 var sc = new SqlQuery.SearchCondition();
|
|
|
55
|
|
|
56 sc.Conditions.Add(new SqlQuery.Condition(false, new SqlQuery.Predicate.IsNull(func.Parameters[0], false)));
|
|
|
57
|
|
|
58 func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0]);
|
|
|
59
|
|
|
60 break;
|
|
|
61 }
|
|
|
62
|
|
|
63 base.BuildFunction(sb, func);
|
|
|
64 }
|
|
|
65
|
|
|
66 static SqlFunction ConvertCase(Type systemType, ISqlExpression[] parameters, int start)
|
|
|
67 {
|
|
|
68 var len = parameters.Length - start;
|
|
|
69 var name = start == 0 ? "IIF" : "CASE";
|
|
|
70 var cond = parameters[start];
|
|
|
71
|
|
|
72 if (start == 0 && SqlExpression.NeedsEqual(cond))
|
|
|
73 {
|
|
|
74 cond = new SqlQuery.SearchCondition(
|
|
|
75 new SqlQuery.Condition(
|
|
|
76 false,
|
|
|
77 new SqlQuery.Predicate.ExprExpr(cond, SqlQuery.Predicate.Operator.Equal, new SqlValue(1))));
|
|
|
78 }
|
|
|
79
|
|
|
80 if (len == 3)
|
|
|
81 return new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2]);
|
|
|
82
|
|
|
83 return new SqlFunction(systemType, name,
|
|
|
84 cond,
|
|
|
85 parameters[start + 1],
|
|
|
86 ConvertCase(systemType, parameters, start + 2));
|
|
|
87 }
|
|
|
88 }
|
|
|
89 }
|
