|
2
|
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 abstract class MsSqlSqlProvider : BasicSqlProvider
|
|
|
11 {
|
|
|
12 public override bool IsApplyJoinSupported { get { return true; } }
|
|
|
13
|
|
|
14 protected virtual bool BuildAlternativeSql { get { return true; } }
|
|
|
15
|
|
|
16 protected override string FirstFormat
|
|
|
17 {
|
|
|
18 get { return SqlQuery.Select.SkipValue == null ? "TOP ({0})" : null; }
|
|
|
19 }
|
|
|
20
|
|
|
21 protected override void BuildSql(StringBuilder sb)
|
|
|
22 {
|
|
|
23 if (BuildAlternativeSql)
|
|
|
24 AlternativeBuildSql(sb, true, base.BuildSql);
|
|
|
25 else
|
|
|
26 base.BuildSql(sb);
|
|
|
27 }
|
|
|
28
|
|
|
29 protected override void BuildGetIdentity(StringBuilder sb)
|
|
|
30 {
|
|
|
31 sb
|
|
|
32 .AppendLine()
|
|
|
33 .AppendLine("SELECT SCOPE_IDENTITY()");
|
|
|
34 }
|
|
|
35
|
|
|
36 protected override void BuildOrderByClause(StringBuilder sb)
|
|
|
37 {
|
|
|
38 if (!BuildAlternativeSql || !NeedSkip)
|
|
|
39 base.BuildOrderByClause(sb);
|
|
|
40 }
|
|
|
41
|
|
|
42 protected override IEnumerable<SqlQuery.Column> GetSelectedColumns()
|
|
|
43 {
|
|
|
44 if (BuildAlternativeSql && NeedSkip && !SqlQuery.OrderBy.IsEmpty)
|
|
|
45 return AlternativeGetSelectedColumns(base.GetSelectedColumns);
|
|
|
46 return base.GetSelectedColumns();
|
|
|
47 }
|
|
|
48
|
|
|
49 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
50 {
|
|
|
51 expr = base.ConvertExpression(expr);
|
|
|
52
|
|
|
53 switch (expr.ElementType)
|
|
|
54 {
|
|
|
55 case QueryElementType.SqlBinaryExpression:
|
|
|
56 {
|
|
|
57 var be = (SqlBinaryExpression)expr;
|
|
|
58
|
|
|
59 switch (be.Operation)
|
|
|
60 {
|
|
|
61 case "%":
|
|
|
62 {
|
|
|
63 var type1 = TypeHelper.GetUnderlyingType(be.Expr1.SystemType);
|
|
|
64
|
|
|
65 if (type1 == typeof(double) || type1 == typeof(float))
|
|
|
66 {
|
|
|
67 return new SqlBinaryExpression(
|
|
|
68 be.Expr2.SystemType,
|
|
|
69 new SqlFunction(typeof(int), "Convert", SqlDataType.Int32, be.Expr1),
|
|
|
70 be.Operation,
|
|
|
71 be.Expr2);
|
|
|
72 }
|
|
|
73
|
|
|
74 break;
|
|
|
75 }
|
|
|
76 }
|
|
|
77
|
|
|
78 break;
|
|
|
79 }
|
|
|
80
|
|
|
81 case QueryElementType.SqlFunction:
|
|
|
82 {
|
|
|
83 var func = (SqlFunction)expr;
|
|
|
84
|
|
|
85 switch (func.Name)
|
|
|
86 {
|
|
|
87 case "Convert" :
|
|
|
88 {
|
|
|
89 if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(ulong) &&
|
|
|
90 TypeHelper.IsFloatType(func.Parameters[1].SystemType))
|
|
|
91 return new SqlFunction(
|
|
|
92 func.SystemType,
|
|
|
93 func.Name,
|
|
|
94 func.Precedence,
|
|
|
95 func.Parameters[0],
|
|
|
96 new SqlFunction(func.SystemType, "Floor", func.Parameters[1]));
|
|
|
97
|
|
|
98 break;
|
|
|
99 }
|
|
|
100 }
|
|
|
101
|
|
|
102 break;
|
|
|
103 }
|
|
|
104 }
|
|
|
105
|
|
|
106 return expr;
|
|
|
107 }
|
|
|
108
|
|
|
109 protected override void BuildDeleteClause(StringBuilder sb)
|
|
|
110 {
|
|
|
111 var table = SqlQuery.Delete.Table != null ?
|
|
|
112 (SqlQuery.From.FindTableSource(SqlQuery.Delete.Table) ?? SqlQuery.Delete.Table) :
|
|
|
113 SqlQuery.From.Tables[0];
|
|
|
114
|
|
|
115 AppendIndent(sb)
|
|
|
116 .Append("DELETE ")
|
|
|
117 .Append(Convert(GetTableAlias(table), ConvertType.NameToQueryTableAlias))
|
|
|
118 .AppendLine();
|
|
|
119 }
|
|
|
120
|
|
|
121 protected override void BuildUpdateTableName(StringBuilder sb)
|
|
|
122 {
|
|
|
123 var table = SqlQuery.Update.Table != null ?
|
|
|
124 (SqlQuery.From.FindTableSource(SqlQuery.Update.Table) ?? SqlQuery.Update.Table) :
|
|
|
125 SqlQuery.From.Tables[0];
|
|
|
126
|
|
|
127 if (table is SqlTable)
|
|
|
128 BuildPhysicalTable(sb, table, null);
|
|
|
129 else
|
|
|
130 sb.Append(Convert(GetTableAlias(table), ConvertType.NameToQueryTableAlias));
|
|
|
131 }
|
|
|
132
|
|
|
133 protected override void BuildString(StringBuilder sb, string value)
|
|
|
134 {
|
|
|
135 foreach (var ch in value)
|
|
|
136 {
|
|
|
137 if (ch > 127)
|
|
|
138 {
|
|
|
139 sb.Append("N");
|
|
|
140 break;
|
|
|
141 }
|
|
|
142 }
|
|
|
143
|
|
|
144 base.BuildString(sb, value);
|
|
|
145 }
|
|
|
146
|
|
|
147 protected override void BuildChar(StringBuilder sb, char value)
|
|
|
148 {
|
|
|
149 if (value > 127)
|
|
|
150 sb.Append("N");
|
|
|
151
|
|
|
152 base.BuildChar(sb, value);
|
|
|
153 }
|
|
|
154
|
|
|
155 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
156 {
|
|
|
157 var wrap = false;
|
|
|
158
|
|
|
159 if (expr.SystemType == typeof(bool))
|
|
|
160 {
|
|
|
161 if (expr is SqlQuery.SearchCondition)
|
|
|
162 wrap = true;
|
|
|
163 else
|
|
|
164 {
|
|
|
165 var ex = expr as SqlExpression;
|
|
|
166 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
|
|
|
167 }
|
|
|
168 }
|
|
|
169
|
|
|
170 if (wrap) sb.Append("CASE WHEN ");
|
|
|
171 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
|
|
|
172 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
|
|
|
173 }
|
|
|
174
|
|
|
175 public override object Convert(object value, ConvertType convertType)
|
|
|
176 {
|
|
|
177 switch (convertType)
|
|
|
178 {
|
|
|
179 case ConvertType.NameToQueryParameter:
|
|
|
180 case ConvertType.NameToCommandParameter:
|
|
|
181 case ConvertType.NameToSprocParameter:
|
|
|
182 return "@" + value;
|
|
|
183
|
|
|
184 case ConvertType.NameToQueryField:
|
|
|
185 case ConvertType.NameToQueryFieldAlias:
|
|
|
186 case ConvertType.NameToQueryTableAlias:
|
|
|
187 {
|
|
|
188 var name = value.ToString();
|
|
|
189
|
|
|
190 if (name.Length > 0 && name[0] == '[')
|
|
|
191 return value;
|
|
|
192 }
|
|
|
193
|
|
|
194 return "[" + value + "]";
|
|
|
195
|
|
|
196 case ConvertType.NameToDatabase:
|
|
|
197 case ConvertType.NameToOwner:
|
|
|
198 case ConvertType.NameToQueryTable:
|
|
|
199 {
|
|
|
200 var name = value.ToString();
|
|
|
201
|
|
|
202 if (name.Length > 0 && name[0] == '[')
|
|
|
203 return value;
|
|
|
204
|
|
|
205 if (name.IndexOf('.') > 0)
|
|
|
206 value = string.Join("].[", name.Split('.'));
|
|
|
207 }
|
|
|
208
|
|
|
209 return "[" + value + "]";
|
|
|
210
|
|
|
211 case ConvertType.SprocParameterToName:
|
|
|
212 if (value != null)
|
|
|
213 {
|
|
|
214 var str = value.ToString();
|
|
|
215 return str.Length > 0 && str[0] == '@'? str.Substring(1): str;
|
|
|
216 }
|
|
|
217 break;
|
|
|
218 }
|
|
|
219
|
|
|
220 return value;
|
|
|
221 }
|
|
|
222
|
|
|
223 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
224 {
|
|
|
225 BuildInsertOrUpdateQueryAsUpdateInsert(sb);
|
|
|
226 }
|
|
|
227
|
|
|
228 protected override void BuildDateTime(StringBuilder sb, object value)
|
|
|
229 {
|
|
|
230 sb.Append(string.Format("'{0:yyyy-MM-ddTHH:mm:ss.fff}'", value));
|
|
|
231 }
|
|
|
232
|
|
|
233 public override void BuildValue(StringBuilder sb, object value)
|
|
|
234 {
|
|
|
235 if (value is sbyte) sb.Append((byte)(sbyte)value);
|
|
|
236 else if (value is ushort) sb.Append((short)(ushort)value);
|
|
|
237 else if (value is uint) sb.Append((int)(uint)value);
|
|
|
238 else if (value is ulong) sb.Append((long)(ulong)value);
|
|
|
239 else base.BuildValue(sb, value);
|
|
|
240 }
|
|
|
241 }
|
|
|
242 }
|
