annotate Source/Data/Sql/SqlProvider/MsSqlSqlProvider.cs @ 9:1e85f66cf767 default tip

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