Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/MsSqlSqlProvider.cs @ 2:79a04c6442bf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
file name case fix
author cin
date Fri, 22 Aug 2014 13:41:57 +0400
parents
children
comparison
equal deleted inserted replaced
1:8f65451dc28f 2:79a04c6442bf
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 }