|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Text;
|
|
|
4
|
|
|
5 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
6 {
|
|
|
7 using DataProvider;
|
|
|
8 using Reflection;
|
|
|
9
|
|
|
10 public class SqlCeSqlProvider : BasicSqlProvider
|
|
|
11 {
|
|
|
12 const int Version = 4;
|
|
|
13
|
|
|
14 public override bool IsSkipSupported { get { return Version == 4; } }
|
|
|
15 public override bool IsTakeSupported { get { return Version == 4; } }
|
|
|
16 public override bool IsSubQueryTakeSupported { get { return Version == 4; } }
|
|
|
17 public override bool IsSubQueryColumnSupported { get { return false; } }
|
|
|
18 public override bool IsCountSubQuerySupported { get { return false; } }
|
|
|
19 public override bool IsApplyJoinSupported { get { return true; } }
|
|
|
20 public override bool IsInsertOrUpdateSupported { get { return false; } }
|
|
|
21
|
|
|
22 protected override string FirstFormat { get { return SqlQuery.Select.SkipValue == null ? "TOP ({0})" : null; } }
|
|
|
23 protected override string LimitFormat { get { return SqlQuery.Select.SkipValue != null ? "FETCH NEXT {0} ROWS ONLY" : null; } }
|
|
|
24 protected override string OffsetFormat { get { return "OFFSET {0} ROWS"; } }
|
|
|
25 protected override bool OffsetFirst { get { return true; } }
|
|
|
26
|
|
|
27 public override int CommandCount(SqlQuery sqlQuery)
|
|
|
28 {
|
|
|
29 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
|
|
|
30 }
|
|
|
31
|
|
|
32 protected override void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
33 {
|
|
|
34 sb.AppendLine("SELECT @@IDENTITY");
|
|
|
35 }
|
|
|
36
|
|
|
37 protected override ISqlProvider CreateSqlProvider()
|
|
|
38 {
|
|
|
39 return new SqlCeSqlProvider();
|
|
|
40 }
|
|
|
41
|
|
|
42 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
43 {
|
|
|
44 expr = base.ConvertExpression(expr);
|
|
|
45
|
|
|
46 if (expr is SqlBinaryExpression)
|
|
|
47 {
|
|
|
48 var be = (SqlBinaryExpression)expr;
|
|
|
49
|
|
|
50 switch (be.Operation)
|
|
|
51 {
|
|
|
52 case "%":
|
|
|
53 return TypeHelper.IsIntegerType(be.Expr1.SystemType)?
|
|
|
54 be :
|
|
|
55 new SqlBinaryExpression(
|
|
|
56 typeof(int),
|
|
|
57 new SqlFunction(typeof(int), "Convert", SqlDataType.Int32, be.Expr1),
|
|
|
58 be.Operation,
|
|
|
59 be.Expr2,
|
|
|
60 be.Precedence);
|
|
|
61 }
|
|
|
62 }
|
|
|
63 else if (expr is SqlFunction)
|
|
|
64 {
|
|
|
65 var func = (SqlFunction)expr;
|
|
|
66
|
|
|
67 switch (func.Name)
|
|
|
68 {
|
|
|
69 case "Convert" :
|
|
|
70 switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
|
|
|
71 {
|
|
|
72 case TypeCode.UInt64 :
|
|
|
73 if (TypeHelper.IsFloatType(func.Parameters[1].SystemType))
|
|
|
74 return new SqlFunction(
|
|
|
75 func.SystemType,
|
|
|
76 func.Name,
|
|
|
77 func.Precedence,
|
|
|
78 func.Parameters[0],
|
|
|
79 new SqlFunction(func.SystemType, "Floor", func.Parameters[1]));
|
|
|
80
|
|
|
81 break;
|
|
|
82
|
|
|
83 case TypeCode.DateTime :
|
|
|
84 var type1 = TypeHelper.GetUnderlyingType(func.Parameters[1].SystemType);
|
|
|
85
|
|
|
86 if (IsTimeDataType(func.Parameters[0]))
|
|
|
87 {
|
|
|
88 if (type1 == typeof(DateTime) || type1 == typeof(DateTimeOffset))
|
|
|
89 return new SqlExpression(
|
|
|
90 func.SystemType, "Cast(Convert(NChar, {0}, 114) as DateTime)", Precedence.Primary, func.Parameters[1]);
|
|
|
91
|
|
|
92 if (func.Parameters[1].SystemType == typeof(string))
|
|
|
93 return func.Parameters[1];
|
|
|
94
|
|
|
95 return new SqlExpression(
|
|
|
96 func.SystemType, "Convert(NChar, {0}, 114)", Precedence.Primary, func.Parameters[1]);
|
|
|
97 }
|
|
|
98
|
|
|
99 if (type1 == typeof(DateTime) || type1 == typeof(DateTimeOffset))
|
|
|
100 {
|
|
|
101 if (IsDateDataType(func.Parameters[0], "Datetime"))
|
|
|
102 return new SqlExpression(
|
|
|
103 func.SystemType, "Cast(Floor(Cast({0} as Float)) as DateTime)", Precedence.Primary, func.Parameters[1]);
|
|
|
104 }
|
|
|
105
|
|
|
106 break;
|
|
|
107 }
|
|
|
108
|
|
|
109 break;
|
|
|
110 }
|
|
|
111 }
|
|
|
112
|
|
|
113 return expr;
|
|
|
114 }
|
|
|
115
|
|
|
116 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
117 {
|
|
|
118 func = ConvertFunctionParameters(func);
|
|
|
119 base.BuildFunction(sb, func);
|
|
|
120 }
|
|
|
121
|
|
|
122 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
123 {
|
|
|
124 sqlQuery = base.Finalize(sqlQuery);
|
|
|
125
|
|
|
126 new QueryVisitor().Visit(sqlQuery.Select, element =>
|
|
|
127 {
|
|
|
128 if (element.ElementType == QueryElementType.SqlParameter)
|
|
|
129 {
|
|
|
130 ((SqlParameter)element).IsQueryParameter = false;
|
|
|
131 sqlQuery.IsParameterDependent = true;
|
|
|
132 }
|
|
|
133 });
|
|
|
134
|
|
|
135 switch (sqlQuery.QueryType)
|
|
|
136 {
|
|
|
137 case QueryType.Delete :
|
|
|
138 sqlQuery = GetAlternativeDelete(sqlQuery);
|
|
|
139 sqlQuery.From.Tables[0].Alias = "$";
|
|
|
140 break;
|
|
|
141
|
|
|
142 case QueryType.Update :
|
|
|
143 sqlQuery = GetAlternativeUpdate(sqlQuery);
|
|
|
144 break;
|
|
|
145 }
|
|
|
146
|
|
|
147 return sqlQuery;
|
|
|
148 }
|
|
|
149
|
|
|
150 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
151 {
|
|
|
152 switch (type.SqlDbType)
|
|
|
153 {
|
|
|
154 case SqlDbType.Char : base.BuildDataType(sb, new SqlDataType(SqlDbType.NChar, type.Length)); break;
|
|
|
155 case SqlDbType.VarChar : base.BuildDataType(sb, new SqlDataType(SqlDbType.NVarChar, type.Length)); break;
|
|
|
156 case SqlDbType.SmallMoney : sb.Append("Decimal(10,4)"); break;
|
|
|
157 #if !MONO
|
|
|
158 case SqlDbType.DateTime2 :
|
|
|
159 #endif
|
|
|
160 case SqlDbType.Time :
|
|
|
161 case SqlDbType.Date :
|
|
|
162 case SqlDbType.SmallDateTime : sb.Append("DateTime"); break;
|
|
|
163 default : base.BuildDataType(sb, type); break;
|
|
|
164 }
|
|
|
165 }
|
|
|
166
|
|
|
167 protected override void BuildFromClause(StringBuilder sb)
|
|
|
168 {
|
|
|
169 if (!SqlQuery.IsUpdate)
|
|
|
170 base.BuildFromClause(sb);
|
|
|
171 }
|
|
|
172
|
|
|
173 protected override void BuildOrderByClause(StringBuilder sb)
|
|
|
174 {
|
|
|
175 if (SqlQuery.OrderBy.Items.Count == 0 && SqlQuery.Select.SkipValue != null)
|
|
|
176 {
|
|
|
177 AppendIndent(sb);
|
|
|
178
|
|
|
179 sb.Append("ORDER BY").AppendLine();
|
|
|
180
|
|
|
181 Indent++;
|
|
|
182
|
|
|
183 AppendIndent(sb);
|
|
|
184
|
|
|
185 BuildExpression(sb, SqlQuery.Select.Columns[0].Expression);
|
|
|
186 sb.AppendLine();
|
|
|
187
|
|
|
188 Indent--;
|
|
|
189 }
|
|
|
190 else
|
|
|
191 base.BuildOrderByClause(sb);
|
|
|
192 }
|
|
|
193
|
|
|
194 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
195 {
|
|
|
196 var wrap = false;
|
|
|
197
|
|
|
198 if (expr.SystemType == typeof(bool))
|
|
|
199 {
|
|
|
200 if (expr is SqlQuery.SearchCondition)
|
|
|
201 wrap = true;
|
|
|
202 else
|
|
|
203 {
|
|
|
204 var ex = expr as SqlExpression;
|
|
|
205 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
|
|
|
206 }
|
|
|
207 }
|
|
|
208
|
|
|
209 if (wrap) sb.Append("CASE WHEN ");
|
|
|
210 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
|
|
|
211 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
|
|
|
212 }
|
|
|
213
|
|
|
214 public override object Convert(object value, ConvertType convertType)
|
|
|
215 {
|
|
|
216 switch (convertType)
|
|
|
217 {
|
|
|
218 case ConvertType.NameToQueryParameter:
|
|
|
219 case ConvertType.NameToCommandParameter:
|
|
|
220 case ConvertType.NameToSprocParameter:
|
|
|
221 return "@" + value;
|
|
|
222
|
|
|
223 case ConvertType.NameToQueryField:
|
|
|
224 case ConvertType.NameToQueryFieldAlias:
|
|
|
225 case ConvertType.NameToQueryTableAlias:
|
|
|
226 {
|
|
|
227 var name = value.ToString();
|
|
|
228
|
|
|
229 if (name.Length > 0 && name[0] == '[')
|
|
|
230 return value;
|
|
|
231 }
|
|
|
232
|
|
|
233 return "[" + value + "]";
|
|
|
234
|
|
|
235 case ConvertType.NameToDatabase:
|
|
|
236 case ConvertType.NameToOwner:
|
|
|
237 case ConvertType.NameToQueryTable:
|
|
|
238 {
|
|
|
239 var name = value.ToString();
|
|
|
240
|
|
|
241 if (name.Length > 0 && name[0] == '[')
|
|
|
242 return value;
|
|
|
243
|
|
|
244 if (name.IndexOf('.') > 0)
|
|
|
245 value = string.Join("].[", name.Split('.'));
|
|
|
246 }
|
|
|
247
|
|
|
248 return "[" + value + "]";
|
|
|
249
|
|
|
250 case ConvertType.SprocParameterToName:
|
|
|
251 if (value != null)
|
|
|
252 {
|
|
|
253 var str = value.ToString();
|
|
|
254 return str.Length > 0 && str[0] == '@'? str.Substring(1): str;
|
|
|
255 }
|
|
|
256 break;
|
|
|
257 }
|
|
|
258
|
|
|
259 return value;
|
|
|
260 }
|
|
|
261 }
|
|
|
262 }
|
