|
0
|
1 using System;
|
|
|
2 using System.Data.Linq;
|
|
|
3 using System.Globalization;
|
|
|
4 using System.Reflection;
|
|
|
5
|
|
|
6 namespace BLToolkit.Data.Linq
|
|
|
7 {
|
|
|
8 using Data.Sql;
|
|
|
9 using Reflection;
|
|
|
10
|
|
|
11 public static class Sql
|
|
|
12 {
|
|
|
13 #region Common Functions
|
|
|
14
|
|
|
15 [CLSCompliant(false)]
|
|
|
16 [SqlExpression("{0}", 0, ServerSideOnly = true)]
|
|
|
17 public static T AsSql<T>(T obj)
|
|
|
18 {
|
|
|
19 return obj;
|
|
|
20 }
|
|
|
21
|
|
|
22 [Obsolete("Use AsSql instead.")]
|
|
|
23 [CLSCompliant(false)]
|
|
|
24 [SqlExpression("{0}", 0, ServerSideOnly = true)]
|
|
|
25 public static T OnServer<T>(T obj)
|
|
|
26 {
|
|
|
27 return obj;
|
|
|
28 }
|
|
|
29
|
|
|
30 [CLSCompliant(false)]
|
|
|
31 [SqlExpression("{0}", 0)]
|
|
|
32 public static T ConvertNullable<T>(T? value)
|
|
|
33 where T : struct
|
|
|
34 {
|
|
|
35 return value.Value;
|
|
|
36 }
|
|
|
37
|
|
|
38 #endregion
|
|
|
39
|
|
|
40 #region Guid Functions
|
|
|
41
|
|
|
42 [SqlFunction ("Oracle", "Sys_Guid", ServerSideOnly=true)]
|
|
|
43 [SqlFunction ("Firebird", "Gen_Uuid", ServerSideOnly=true)]
|
|
|
44 [SqlFunction ("MySql", "Uuid", ServerSideOnly=true)]
|
|
|
45 [SqlExpression("Sybase", "NewID(1)", ServerSideOnly=true)]
|
|
|
46 [SqlFunction ( "NewID", ServerSideOnly=true)]
|
|
|
47 public static Guid NewGuid()
|
|
|
48 {
|
|
|
49 return Guid.NewGuid();
|
|
|
50 }
|
|
|
51
|
|
|
52 #endregion
|
|
|
53
|
|
|
54 #region Convert Functions
|
|
|
55
|
|
|
56 [CLSCompliant(false)]
|
|
|
57 [SqlFunction("Convert", 0, 1, ServerSideOnly = true)]
|
|
|
58 public static TTo Convert<TTo,TFrom>(TTo to, TFrom from)
|
|
|
59 {
|
|
|
60 var dt = Common.ConvertTo<TTo>.From(from);
|
|
|
61 return dt;
|
|
|
62 }
|
|
|
63
|
|
|
64 [CLSCompliant(false)]
|
|
|
65 [SqlFunction("Convert", 0, 1, 2, ServerSideOnly = true)]
|
|
|
66 public static TTo Convert<TTo, TFrom>(TTo to, TFrom from, int format)
|
|
|
67 {
|
|
|
68 var dt = Common.ConvertTo<TTo>.From(from);
|
|
|
69 return dt;
|
|
|
70 }
|
|
|
71
|
|
|
72 [CLSCompliant(false)]
|
|
|
73 [SqlFunction("Convert", 0, 1)]
|
|
|
74 public static TTo Convert2<TTo,TFrom>(TTo to, TFrom from)
|
|
|
75 {
|
|
|
76 return Common.ConvertTo<TTo>.From(from);
|
|
|
77 }
|
|
|
78
|
|
|
79 [CLSCompliant(false)]
|
|
|
80 [SqlFunction("$Convert$", 1, 2, 0)]
|
|
|
81 public static TTo Convert<TTo,TFrom>(TFrom obj)
|
|
|
82 {
|
|
|
83 return Common.ConvertTo<TTo>.From(obj);
|
|
|
84 }
|
|
|
85
|
|
|
86 public static class ConvertTo<TTo>
|
|
|
87 {
|
|
|
88 [CLSCompliant(false)]
|
|
|
89 [SqlFunction("$Convert$", 1, 2, 0)]
|
|
|
90 public static TTo From<TFrom>(TFrom obj)
|
|
|
91 {
|
|
|
92 return Common.ConvertTo<TTo>.From(obj);
|
|
|
93 }
|
|
|
94 }
|
|
|
95
|
|
|
96 [SqlExpression("{0}")]
|
|
|
97 public static TimeSpan? DateToTime(DateTime? date)
|
|
|
98 {
|
|
|
99 return date == null ? null : (TimeSpan?)new TimeSpan(date.Value.Ticks);
|
|
|
100 }
|
|
|
101
|
|
|
102 [SqlProperty("Informix", "Boolean", ServerSideOnly=true)]
|
|
|
103 [SqlProperty("PostgreSQL", "Boolean", ServerSideOnly=true)]
|
|
|
104 [SqlProperty("MySql", "Boolean", ServerSideOnly=true)]
|
|
|
105 [SqlProperty("SQLite", "Boolean", ServerSideOnly=true)]
|
|
|
106 [SqlProperty( "Bit", ServerSideOnly=true)] public static Boolean Bit { get { return false; } }
|
|
|
107
|
|
|
108 [SqlProperty("Oracle", "Number(19)", ServerSideOnly=true)]
|
|
|
109 [SqlProperty( "BigInt", ServerSideOnly=true)] public static Int64 BigInt { get { return 0; } }
|
|
|
110
|
|
|
111 [SqlProperty("MySql", "Signed", ServerSideOnly=true)]
|
|
|
112 [SqlProperty( "Int", ServerSideOnly=true)] public static Int32 Int { get { return 0; } }
|
|
|
113
|
|
|
114 [SqlProperty("MySql", "Signed", ServerSideOnly=true)]
|
|
|
115 [SqlProperty( "SmallInt", ServerSideOnly=true)] public static Int16 SmallInt { get { return 0; } }
|
|
|
116
|
|
|
117 [SqlProperty("DB2", "SmallInt", ServerSideOnly=true)]
|
|
|
118 [SqlProperty("Informix", "SmallInt", ServerSideOnly=true)]
|
|
|
119 [SqlProperty("Oracle", "Number(3)", ServerSideOnly=true)]
|
|
|
120 [SqlProperty("DB2", "SmallInt", ServerSideOnly=true)]
|
|
|
121 [SqlProperty("Firebird", "SmallInt", ServerSideOnly=true)]
|
|
|
122 [SqlProperty("PostgreSQL", "SmallInt", ServerSideOnly=true)]
|
|
|
123 [SqlProperty("MySql", "Unsigned", ServerSideOnly=true)]
|
|
|
124 [SqlProperty( "TinyInt", ServerSideOnly=true)] public static Byte TinyInt { get { return 0; } }
|
|
|
125
|
|
|
126 [SqlProperty( "Decimal", ServerSideOnly=true)] public static Decimal DefaultDecimal { get { return 0; } }
|
|
|
127 [SqlFunction( ServerSideOnly=true)] public static Decimal Decimal(int precision) { return 0; }
|
|
|
128 [SqlFunction( ServerSideOnly=true)] public static Decimal Decimal(int precision, int scale) { return 0; }
|
|
|
129
|
|
|
130 [SqlProperty("Oracle", "Number(19,4)", ServerSideOnly=true)]
|
|
|
131 [SqlProperty("Firebird", "Decimal(18,4)", ServerSideOnly=true)]
|
|
|
132 [SqlProperty("PostgreSQL", "Decimal(19,4)", ServerSideOnly=true)]
|
|
|
133 [SqlProperty("MySql", "Decimal(19,4)", ServerSideOnly=true)]
|
|
|
134 [SqlProperty( "Money", ServerSideOnly=true)] public static Decimal Money { get { return 0; } }
|
|
|
135
|
|
|
136 [SqlProperty("Informix", "Decimal(10,4)", ServerSideOnly=true)]
|
|
|
137 [SqlProperty("Oracle", "Number(10,4)", ServerSideOnly=true)]
|
|
|
138 [SqlProperty("Firebird", "Decimal(10,4)", ServerSideOnly=true)]
|
|
|
139 [SqlProperty("PostgreSQL", "Decimal(10,4)", ServerSideOnly=true)]
|
|
|
140 [SqlProperty("MySql", "Decimal(10,4)", ServerSideOnly=true)]
|
|
|
141 [SqlProperty("SqlCe", "Decimal(10,4)", ServerSideOnly=true)]
|
|
|
142 [SqlProperty( "SmallMoney", ServerSideOnly=true)] public static Decimal SmallMoney { get { return 0; } }
|
|
|
143
|
|
|
144 [SqlProperty("MySql", "Decimal(29,10)", ServerSideOnly=true)]
|
|
|
145 [SqlProperty( "Float", ServerSideOnly=true)] public static Double Float { get { return 0; } }
|
|
|
146
|
|
|
147 [SqlProperty("MySql", "Decimal(29,10)", ServerSideOnly=true)]
|
|
|
148 [SqlProperty( "Real", ServerSideOnly=true)] public static Single Real { get { return 0; } }
|
|
|
149
|
|
|
150 [SqlProperty("PostgreSQL", "TimeStamp", ServerSideOnly=true)]
|
|
|
151 [SqlProperty("Firebird", "TimeStamp", ServerSideOnly=true)]
|
|
|
152 [SqlProperty( "DateTime", ServerSideOnly=true)] public static DateTime DateTime { get { return DateTime.Now; } }
|
|
|
153
|
|
|
154 [SqlProperty("MsSql2000", "DateTime", ServerSideOnly=true)]
|
|
|
155 [SqlProperty("MsSql2005", "DateTime", ServerSideOnly=true)]
|
|
|
156 [SqlProperty("PostgreSQL", "TimeStamp", ServerSideOnly=true)]
|
|
|
157 [SqlProperty("Firebird", "TimeStamp", ServerSideOnly=true)]
|
|
|
158 [SqlProperty("MySql", "DateTime", ServerSideOnly=true)]
|
|
|
159 [SqlProperty("SqlCe", "DateTime", ServerSideOnly=true)]
|
|
|
160 [SqlProperty("Sybase", "DateTime", ServerSideOnly=true)]
|
|
|
161 [SqlProperty( "DateTime2", ServerSideOnly=true)] public static DateTime DateTime2 { get { return DateTime.Now; } }
|
|
|
162
|
|
|
163 [SqlProperty("PostgreSQL", "TimeStamp", ServerSideOnly=true)]
|
|
|
164 [SqlProperty("Firebird", "TimeStamp", ServerSideOnly=true)]
|
|
|
165 [SqlProperty("MySql", "DateTime", ServerSideOnly=true)]
|
|
|
166 [SqlProperty("SqlCe", "DateTime", ServerSideOnly=true)]
|
|
|
167 [SqlProperty( "SmallDateTime", ServerSideOnly=true)] public static DateTime SmallDateTime { get { return DateTime.Now; } }
|
|
|
168
|
|
|
169 [SqlProperty("MsSql2000", "Datetime", ServerSideOnly=true)]
|
|
|
170 [SqlProperty("MsSql2005", "Datetime", ServerSideOnly=true)]
|
|
|
171 [SqlProperty("SqlCe", "Datetime", ServerSideOnly=true)]
|
|
|
172 [SqlProperty( "Date", ServerSideOnly=true)] public static DateTime Date { get { return DateTime.Now; } }
|
|
|
173
|
|
|
174 [SqlProperty( "Time", ServerSideOnly=true)] public static DateTime Time { get { return DateTime.Now; } }
|
|
|
175
|
|
|
176 [SqlProperty("PostgreSQL", "TimeStamp", ServerSideOnly=true)]
|
|
|
177 [SqlProperty("Firebird", "TimeStamp", ServerSideOnly=true)]
|
|
|
178 [SqlProperty("MsSql2008", "DateTimeOffset", ServerSideOnly=true)]
|
|
|
179 [SqlProperty("MsSql2012", "DateTimeOffset", ServerSideOnly=true)]
|
|
|
180 [SqlProperty( "DateTime", ServerSideOnly=true)] public static DateTimeOffset DateTimeOffset { get { return DateTimeOffset.Now; } }
|
|
|
181
|
|
|
182 [SqlFunction("SqlCe", "NChar", ServerSideOnly=true)]
|
|
|
183 [SqlFunction( ServerSideOnly=true)] public static String Char(int length) { return ""; }
|
|
|
184
|
|
|
185 [SqlProperty("SqlCe", "NChar", ServerSideOnly=true)]
|
|
|
186 [SqlProperty( "Char", ServerSideOnly=true)] public static String DefaultChar { get { return ""; } }
|
|
|
187
|
|
|
188 [SqlFunction("MySql", "Char", ServerSideOnly=true)]
|
|
|
189 [SqlFunction("SqlCe", "NVarChar", ServerSideOnly=true)]
|
|
|
190 [SqlFunction( ServerSideOnly=true)] public static String VarChar(int length) { return ""; }
|
|
|
191
|
|
|
192 [SqlProperty("MySql", "Char", ServerSideOnly=true)]
|
|
|
193 [SqlProperty("SqlCe", "NVarChar", ServerSideOnly=true)]
|
|
|
194 [SqlProperty( "VarChar", ServerSideOnly=true)] public static String DefaultVarChar { get { return ""; } }
|
|
|
195
|
|
|
196 [SqlFunction("DB2", "Char", ServerSideOnly=true)]
|
|
|
197 [SqlFunction( ServerSideOnly=true)] public static String NChar(int length) { return ""; }
|
|
|
198
|
|
|
199 [SqlProperty("DB2", "Char", ServerSideOnly=true)]
|
|
|
200 [SqlProperty( "NChar", ServerSideOnly=true)] public static String DefaultNChar { get { return ""; } }
|
|
|
201
|
|
|
202 [SqlFunction("DB2", "Char", ServerSideOnly=true)]
|
|
|
203 [SqlFunction("Oracle", "VarChar2", ServerSideOnly=true)]
|
|
|
204 [SqlFunction("Firebird", "VarChar", ServerSideOnly=true)]
|
|
|
205 [SqlFunction("PostgreSQL", "VarChar", ServerSideOnly=true)]
|
|
|
206 [SqlFunction("MySql", "Char", ServerSideOnly=true)]
|
|
|
207 [SqlFunction( ServerSideOnly=true)] public static String NVarChar(int length) { return ""; }
|
|
|
208
|
|
|
209 [SqlProperty("DB2", "Char", ServerSideOnly=true)]
|
|
|
210 [SqlProperty("Oracle", "VarChar2", ServerSideOnly=true)]
|
|
|
211 [SqlProperty("Firebird", "VarChar", ServerSideOnly=true)]
|
|
|
212 [SqlProperty("PostgreSQL", "VarChar", ServerSideOnly=true)]
|
|
|
213 [SqlProperty("MySql", "Char", ServerSideOnly=true)]
|
|
|
214 [SqlProperty( "NVarChar", ServerSideOnly=true)] public static String DefaultNVarChar { get { return ""; } }
|
|
|
215
|
|
|
216 #endregion
|
|
|
217
|
|
|
218 #region String Functions
|
|
|
219
|
|
|
220 [SqlFunction( PreferServerSide = true)]
|
|
|
221 [SqlFunction("Access", "Len", PreferServerSide = true)]
|
|
|
222 [SqlFunction("Firebird", "Char_Length", PreferServerSide = true)]
|
|
|
223 [SqlFunction("MsSql2000", "Len", PreferServerSide = true)]
|
|
|
224 [SqlFunction("MsSql2005", "Len", PreferServerSide = true)]
|
|
|
225 [SqlFunction("MsSql2008", "Len", PreferServerSide = true)]
|
|
|
226 [SqlFunction("MsSql2012", "Len", PreferServerSide = true)]
|
|
|
227 [SqlFunction("SqlCe", "Len", PreferServerSide = true)]
|
|
|
228 [SqlFunction("Sybase", "Len", PreferServerSide = true)]
|
|
|
229 public static int? Length(string str)
|
|
|
230 {
|
|
|
231 return str == null ? null : (int?)str.Length;
|
|
|
232 }
|
|
|
233
|
|
|
234 [SqlFunction]
|
|
|
235 [SqlFunction ("Access", "Mid")]
|
|
|
236 [SqlFunction ("DB2", "Substr")]
|
|
|
237 [SqlFunction ("Informix", "Substr")]
|
|
|
238 [SqlFunction ("Oracle", "Substr")]
|
|
|
239 [SqlFunction ("SQLite", "Substr")]
|
|
|
240 [SqlExpression("Firebird", "Substring({0} from {1} for {2})")]
|
|
|
241 public static string Substring(string str, int? startIndex, int? length)
|
|
|
242 {
|
|
|
243 return str == null || startIndex == null || length == null ? null : str.Substring(startIndex.Value, length.Value);
|
|
|
244 }
|
|
|
245
|
|
|
246 [SqlFunction(ServerSideOnly = true)]
|
|
|
247 public static bool Like(string matchExpression, string pattern)
|
|
|
248 {
|
|
|
249 #if SILVERLIGHT
|
|
|
250 throw new InvalidOperationException();
|
|
|
251 #else
|
|
|
252 return matchExpression == null || pattern == null ? false : System.Data.Linq.SqlClient.SqlMethods.Like(matchExpression, pattern);
|
|
|
253 #endif
|
|
|
254 }
|
|
|
255
|
|
|
256 [SqlFunction(ServerSideOnly = true)]
|
|
|
257 public static bool Like(string matchExpression, string pattern, char? escapeCharacter)
|
|
|
258 {
|
|
|
259 #if SILVERLIGHT
|
|
|
260 throw new InvalidOperationException();
|
|
|
261 #else
|
|
|
262 return matchExpression == null || pattern == null || escapeCharacter == null ?
|
|
|
263 false :
|
|
|
264 System.Data.Linq.SqlClient.SqlMethods.Like(matchExpression, pattern, escapeCharacter.Value);
|
|
|
265 #endif
|
|
|
266 }
|
|
|
267
|
|
|
268 [SqlFunction]
|
|
|
269 [SqlFunction("DB2", "Locate")]
|
|
|
270 [SqlFunction("MySql", "Locate")]
|
|
|
271 public static int? CharIndex(string value, string str)
|
|
|
272 {
|
|
|
273 if (str == null || value == null)
|
|
|
274 return null;
|
|
|
275
|
|
|
276 return str.IndexOf(value) + 1;
|
|
|
277 }
|
|
|
278
|
|
|
279 [SqlFunction]
|
|
|
280 public static int? ContainsExactly(string value, string str)
|
|
|
281 {
|
|
|
282 if (str == null || value == null)
|
|
|
283 return null;
|
|
|
284
|
|
|
285 return str.ContainsExactly(value);
|
|
|
286 }
|
|
|
287
|
|
|
288 [SqlFunction]
|
|
|
289 [SqlFunction("DB2", "Locate")]
|
|
|
290 [SqlFunction("MySql", "Locate")]
|
|
|
291 public static int? CharIndex(string value, string str, int? startLocation)
|
|
|
292 {
|
|
|
293 if (str == null || value == null || startLocation == null)
|
|
|
294 return null;
|
|
|
295
|
|
|
296 return str.IndexOf(value, startLocation.Value - 1) + 1;
|
|
|
297 }
|
|
|
298
|
|
|
299 [SqlFunction]
|
|
|
300 [SqlFunction("DB2", "Locate")]
|
|
|
301 [SqlFunction("MySql", "Locate")]
|
|
|
302 public static int? CharIndex(char? value, string str)
|
|
|
303 {
|
|
|
304 if (value == null || str == null)
|
|
|
305 return null;
|
|
|
306
|
|
|
307 return str.IndexOf(value.Value) + 1;
|
|
|
308 }
|
|
|
309
|
|
|
310 [SqlFunction]
|
|
|
311 [SqlFunction("DB2", "Locate")]
|
|
|
312 [SqlFunction("MySql", "Locate")]
|
|
|
313 public static int? CharIndex(char? value, string str, int? startLocation)
|
|
|
314 {
|
|
|
315 if (str == null || value == null || startLocation == null)
|
|
|
316 return null;
|
|
|
317
|
|
|
318 return str.IndexOf(value.Value, startLocation.Value - 1) + 1;
|
|
|
319 }
|
|
|
320
|
|
|
321 [SqlFunction]
|
|
|
322 public static string Reverse(string str)
|
|
|
323 {
|
|
|
324 if (string.IsNullOrEmpty(str))
|
|
|
325 return str;
|
|
|
326
|
|
|
327 var chars = str.ToCharArray();
|
|
|
328 Array.Reverse(chars);
|
|
|
329 return new string(chars);
|
|
|
330 }
|
|
|
331
|
|
|
332 [SqlFunction]
|
|
|
333 [SqlFunction("SQLite", "LeftStr")]
|
|
|
334 public static string Left(string str, int? length)
|
|
|
335 {
|
|
|
336 return length == null || str == null || str.Length < length? null: str.Substring(1, length.Value);
|
|
|
337 }
|
|
|
338
|
|
|
339 [SqlFunction]
|
|
|
340 [SqlFunction("SQLite", "RightStr")]
|
|
|
341 public static string Right(string str, int? length)
|
|
|
342 {
|
|
|
343 return length == null || str == null || str.Length < length?
|
|
|
344 null :
|
|
|
345 str.Substring(str.Length - length.Value);
|
|
|
346 }
|
|
|
347
|
|
|
348 [SqlFunction]
|
|
|
349 public static string Stuff(string str, int? startLocation, int? length, string value)
|
|
|
350 {
|
|
|
351 return str == null || value == null || startLocation == null || length == null ?
|
|
|
352 null :
|
|
|
353 str.Remove(startLocation.Value - 1, length.Value).Insert(startLocation.Value - 1, value);
|
|
|
354 }
|
|
|
355
|
|
|
356 [SqlFunction]
|
|
|
357 public static string Space(int? length)
|
|
|
358 {
|
|
|
359 return length == null ? null : "".PadRight(length.Value);
|
|
|
360 }
|
|
|
361
|
|
|
362 [SqlFunction(Name = "LPad")]
|
|
|
363 public static string PadLeft(string str, int? totalWidth, char? paddingChar)
|
|
|
364 {
|
|
|
365 return str == null || totalWidth == null || paddingChar == null ?
|
|
|
366 null :
|
|
|
367 str.PadLeft(totalWidth.Value, paddingChar.Value);
|
|
|
368 }
|
|
|
369
|
|
|
370 [SqlFunction(Name = "RPad")]
|
|
|
371 public static string PadRight(string str, int? totalWidth, char? paddingChar)
|
|
|
372 {
|
|
|
373 return str == null || totalWidth == null || paddingChar == null ?
|
|
|
374 null :
|
|
|
375 str.PadRight(totalWidth.Value, paddingChar.Value);
|
|
|
376 }
|
|
|
377
|
|
|
378 [SqlFunction]
|
|
|
379 [SqlFunction("Sybase", "Str_Replace")]
|
|
|
380 public static string Replace(string str, string oldValue, string newValue)
|
|
|
381 {
|
|
|
382 return str == null || oldValue == null || newValue == null ?
|
|
|
383 null :
|
|
|
384 str.Replace(oldValue, newValue);
|
|
|
385 }
|
|
|
386
|
|
|
387 [SqlFunction]
|
|
|
388 [SqlFunction("Sybase", "Str_Replace")]
|
|
|
389 public static string Replace(string str, char? oldValue, char? newValue)
|
|
|
390 {
|
|
|
391 return str == null || oldValue == null || newValue == null ?
|
|
|
392 null :
|
|
|
393 str.Replace(oldValue.Value, newValue.Value);
|
|
|
394 }
|
|
|
395
|
|
|
396 [SqlFunction]
|
|
|
397 public static string Trim(string str)
|
|
|
398 {
|
|
|
399 return str == null ? null : str.Trim();
|
|
|
400 }
|
|
|
401
|
|
|
402 [SqlFunction("LTrim")]
|
|
|
403 public static string TrimLeft(string str)
|
|
|
404 {
|
|
|
405 return str == null ? null : str.TrimStart();
|
|
|
406 }
|
|
|
407
|
|
|
408 [SqlFunction("RTrim")]
|
|
|
409 public static string TrimRight(string str)
|
|
|
410 {
|
|
|
411 return str == null ? null : str.TrimEnd();
|
|
|
412 }
|
|
|
413
|
|
|
414 [SqlExpression("DB2", "Strip({0}, B, {1})")]
|
|
|
415 [SqlFunction]
|
|
|
416 public static string Trim(string str, char? ch)
|
|
|
417 {
|
|
|
418 return str == null || ch == null ? null : str.Trim(ch.Value);
|
|
|
419 }
|
|
|
420
|
|
|
421 [SqlExpression("DB2", "Strip({0}, L, {1})")]
|
|
|
422 [SqlFunction ( "LTrim")]
|
|
|
423 public static string TrimLeft(string str, char? ch)
|
|
|
424 {
|
|
|
425 return str == null || ch == null ? null : str.TrimStart(ch.Value);
|
|
|
426 }
|
|
|
427
|
|
|
428 [SqlExpression("DB2", "Strip({0}, T, {1})")]
|
|
|
429 [SqlFunction ( "RTrim")]
|
|
|
430 public static string TrimRight(string str, char? ch)
|
|
|
431 {
|
|
|
432 return str == null || ch == null ? null : str.TrimEnd(ch.Value);
|
|
|
433 }
|
|
|
434
|
|
|
435 [SqlFunction]
|
|
|
436 [SqlFunction("Access", "LCase")]
|
|
|
437 public static string Lower(string str)
|
|
|
438 {
|
|
|
439 return str == null ? null : str.ToLower();
|
|
|
440 }
|
|
|
441
|
|
|
442 [SqlFunction]
|
|
|
443 [SqlFunction("Access", "UCase")]
|
|
|
444 public static string Upper(string str)
|
|
|
445 {
|
|
|
446 return str == null ? null : str.ToUpper();
|
|
|
447 }
|
|
|
448
|
|
|
449 #endregion
|
|
|
450
|
|
|
451 #region Binary Functions
|
|
|
452
|
|
|
453 [SqlFunction( PreferServerSide = true)]
|
|
|
454 [SqlFunction("Access", "Len", PreferServerSide = true)]
|
|
|
455 [SqlFunction("Firebird", "Octet_Length", PreferServerSide = true)]
|
|
|
456 [SqlFunction("MsSql2000", "DataLength", PreferServerSide = true)]
|
|
|
457 [SqlFunction("MsSql2005", "DataLength", PreferServerSide = true)]
|
|
|
458 [SqlFunction("MsSql2008", "DataLength", PreferServerSide = true)]
|
|
|
459 [SqlFunction("MsSql2012", "DataLength", PreferServerSide = true)]
|
|
|
460 [SqlFunction("SqlCe", "DataLength", PreferServerSide = true)]
|
|
|
461 [SqlFunction("Sybase", "DataLength", PreferServerSide = true)]
|
|
|
462 public static int? Length(Binary value)
|
|
|
463 {
|
|
|
464 return value == null ? null : (int?)value.Length;
|
|
|
465 }
|
|
|
466
|
|
|
467 #endregion
|
|
|
468
|
|
|
469 #region DateTime Functions
|
|
|
470
|
|
|
471 [SqlProperty("CURRENT_TIMESTAMP")]
|
|
|
472 [SqlProperty("Informix", "CURRENT")]
|
|
|
473 [SqlProperty("Access", "Now")]
|
|
|
474 public static DateTime GetDate()
|
|
|
475 {
|
|
|
476 return DateTime.Now;
|
|
|
477 }
|
|
|
478
|
|
|
479 [SqlProperty("CURRENT_TIMESTAMP", ServerSideOnly = true)]
|
|
|
480 [SqlProperty("Informix", "CURRENT", ServerSideOnly = true)]
|
|
|
481 [SqlProperty("Access", "Now", ServerSideOnly = true)]
|
|
|
482 [SqlFunction("SqlCe", "GetDate", ServerSideOnly = true)]
|
|
|
483 [SqlFunction("Sybase", "GetDate", ServerSideOnly = true)]
|
|
|
484 public static DateTime CurrentTimestamp
|
|
|
485 {
|
|
|
486 get { throw new LinqException("The 'CurrentTimestamp' is server side only property."); }
|
|
|
487 }
|
|
|
488
|
|
|
489 [SqlProperty("CURRENT_TIMESTAMP")]
|
|
|
490 [SqlProperty("Informix", "CURRENT")]
|
|
|
491 [SqlProperty("Access", "Now")]
|
|
|
492 [SqlFunction("SqlCe", "GetDate")]
|
|
|
493 [SqlFunction("Sybase", "GetDate")]
|
|
|
494 public static DateTime CurrentTimestamp2
|
|
|
495 {
|
|
|
496 get { return DateTime.Now; }
|
|
|
497 }
|
|
|
498
|
|
|
499 [SqlFunction]
|
|
|
500 public static DateTime? ToDate(int? year, int? month, int? day, int? hour, int? minute, int? second, int? millisecond)
|
|
|
501 {
|
|
|
502 return year == null || month == null || day == null || hour == null || minute == null || second == null || millisecond == null ?
|
|
|
503 (DateTime?)null :
|
|
|
504 new DateTime(year.Value, month.Value, day.Value, hour.Value, minute.Value, second.Value, millisecond.Value);
|
|
|
505 }
|
|
|
506
|
|
|
507 [SqlFunction]
|
|
|
508 public static DateTime? ToDate(int? year, int? month, int? day, int? hour, int? minute, int? second)
|
|
|
509 {
|
|
|
510 return year == null || month == null || day == null || hour == null || minute == null || second == null ?
|
|
|
511 (DateTime?)null :
|
|
|
512 new DateTime(year.Value, month.Value, day.Value, hour.Value, minute.Value, second.Value);
|
|
|
513 }
|
|
|
514
|
|
|
515 [SqlFunction]
|
|
|
516 public static DateTime? ToDate(int? year, int? month, int? day)
|
|
|
517 {
|
|
|
518 return year == null || month == null || day == null ?
|
|
|
519 (DateTime?)null :
|
|
|
520 new DateTime(year.Value, month.Value, day.Value);
|
|
|
521 }
|
|
|
522
|
|
|
523 [SqlEnum]
|
|
|
524 public enum DateParts
|
|
|
525 {
|
|
|
526 Year = 0,
|
|
|
527 Quarter = 1,
|
|
|
528 Month = 2,
|
|
|
529 DayOfYear = 3,
|
|
|
530 Day = 4,
|
|
|
531 Week = 5,
|
|
|
532 WeekDay = 6,
|
|
|
533 Hour = 7,
|
|
|
534 Minute = 8,
|
|
|
535 Second = 9,
|
|
|
536 Millisecond = 10,
|
|
|
537 }
|
|
|
538
|
|
|
539 class DatePartAttribute : SqlExpressionAttribute
|
|
|
540 {
|
|
|
541 public DatePartAttribute(string sqlProvider, string expression, int datePartIndex, params int[] argIndices)
|
|
|
542 : this(sqlProvider, expression, Data.Sql.Precedence.Primary, false, null, datePartIndex, argIndices)
|
|
|
543 {
|
|
|
544 }
|
|
|
545
|
|
|
546 public DatePartAttribute(string sqlProvider, string expression, bool isExpression, int datePartIndex, params int[] argIndices)
|
|
|
547 : this(sqlProvider, expression, Data.Sql.Precedence.Primary, isExpression, null, datePartIndex, argIndices)
|
|
|
548 {
|
|
|
549 }
|
|
|
550
|
|
|
551 public DatePartAttribute(string sqlProvider, string expression, bool isExpression, string[] partMapping, int datePartIndex, params int[] argIndices)
|
|
|
552 : this(sqlProvider, expression, Data.Sql.Precedence.Primary, isExpression, partMapping, datePartIndex, argIndices)
|
|
|
553 {
|
|
|
554 }
|
|
|
555
|
|
|
556 public DatePartAttribute(string sqlProvider, string expression, int precedence, bool isExpression, string[] partMapping, int datePartIndex, params int[] argIndices)
|
|
|
557 : base(sqlProvider, expression, argIndices)
|
|
|
558 {
|
|
|
559 _isExpression = isExpression;
|
|
|
560 _partMapping = partMapping;
|
|
|
561 _datePartIndex = datePartIndex;
|
|
|
562 Precedence = precedence;
|
|
|
563 }
|
|
|
564
|
|
|
565 readonly bool _isExpression;
|
|
|
566 readonly string[] _partMapping;
|
|
|
567 readonly int _datePartIndex;
|
|
|
568
|
|
|
569 public override ISqlExpression GetExpression(MemberInfo member, params ISqlExpression[] args)
|
|
|
570 {
|
|
|
571 var part = (DateParts)((SqlValue)args[_datePartIndex]).Value;
|
|
|
572 var pstr = _partMapping != null ? _partMapping[(int)part] : part.ToString();
|
|
|
573 var str = string.Format(Expression, pstr ?? part.ToString());
|
|
|
574 var type = TypeHelper.GetMemberType(member);
|
|
|
575
|
|
|
576
|
|
|
577 return _isExpression ?
|
|
|
578 new SqlExpression(type, str, Precedence, ConvertArgs(member, args)) :
|
|
|
579 (ISqlExpression)new SqlFunction (type, str, ConvertArgs(member, args));
|
|
|
580 }
|
|
|
581 }
|
|
|
582
|
|
|
583 [CLSCompliant(false)]
|
|
|
584 [SqlFunction]
|
|
|
585 [DatePart("Oracle", "Add{0}", false, 0, 2, 1)]
|
|
|
586 [DatePart("DB2", "{{1}} + {0}", Precedence.Additive, true, new[] { "{0} Year", "({0} * 3) Month", "{0} Month", "{0} Day", "{0} Day", "({0} * 7) Day", "{0} Day", "{0} Hour", "{0} Minute", "{0} Second", "({0} * 1000) Microsecond" }, 0, 1, 2)]
|
|
|
587 [DatePart("Informix", "{{1}} + Interval({0}", Precedence.Additive, true, new[] { "{0}) Year to Year", "{0}) Month to Month * 3", "{0}) Month to Month", "{0}) Day to Day", "{0}) Day to Day", "{0}) Day to Day * 7", "{0}) Day to Day", "{0}) Hour to Hour", "{0}) Minute to Minute", "{0}) Second to Second", null }, 0, 1, 2)]
|
|
|
588 [DatePart("PostgreSQL", "{{1}} + Interval '{{0}} {0}", Precedence.Additive, true, new[] { "Year'", "Month' * 3", "Month'", "Day'", "Day'", "Day' * 7", "Day'", "Hour'", "Minute'", "Second'", "Millisecond'" }, 0, 1, 2)]
|
|
|
589 [DatePart("MySql", "Date_Add({{1}}, Interval {{0}} {0})", true, new[] { null, null, null, "Day", null, null, "Day", null, null, null, null }, 0, 1, 2)]
|
|
|
590 [DatePart("SQLite", "DateTime({{1}}, '{{0}} {0}')", true, new[] { null, null, null, "Day", null, null, "Day", null, null, null, null }, 0, 1, 2)]
|
|
|
591 [DatePart("Access", "DateAdd({0}, {{0}}, {{1}})", true, new[] { "'yyyy'", "'q'", "'m'", "'y'", "'d'", "'ww'", "'w'", "'h'", "'n'", "'s'", null }, 0, 1, 2)]
|
|
|
592 public static DateTime? DateAdd(DateParts part, double? number, DateTime? date)
|
|
|
593 {
|
|
|
594 if (number == null || date == null)
|
|
|
595 return null;
|
|
|
596
|
|
|
597 switch (part)
|
|
|
598 {
|
|
|
599 case DateParts.Year : return date.Value.AddYears ((int)number);
|
|
|
600 case DateParts.Quarter : return date.Value.AddMonths ((int)number * 3);
|
|
|
601 case DateParts.Month : return date.Value.AddMonths ((int)number);
|
|
|
602 case DateParts.DayOfYear : return date.Value.AddDays (number.Value);
|
|
|
603 case DateParts.Day : return date.Value.AddDays (number.Value);
|
|
|
604 case DateParts.Week : return date.Value.AddDays (number.Value * 7);
|
|
|
605 case DateParts.WeekDay : return date.Value.AddDays (number.Value);
|
|
|
606 case DateParts.Hour : return date.Value.AddHours (number.Value);
|
|
|
607 case DateParts.Minute : return date.Value.AddMinutes (number.Value);
|
|
|
608 case DateParts.Second : return date.Value.AddSeconds (number.Value);
|
|
|
609 case DateParts.Millisecond : return date.Value.AddMilliseconds(number.Value);
|
|
|
610 }
|
|
|
611
|
|
|
612 throw new InvalidOperationException();
|
|
|
613 }
|
|
|
614
|
|
|
615 [CLSCompliant(false)]
|
|
|
616 [SqlFunction]
|
|
|
617 [DatePart("DB2", "{0}", false, new[] { null, null, null, null, null, null, "DayOfWeek", null, null, null, null }, 0, 1)]
|
|
|
618 [DatePart("Informix", "{0}", 0, 1)]
|
|
|
619 [DatePart("MySql", "Extract({0} from {{0}})", true, 0, 1)]
|
|
|
620 [DatePart("PostgreSQL", "Extract({0} from {{0}})", true, new[] { null, null, null, "DOY", null, null, "DOW", null, null, null, null }, 0, 1)]
|
|
|
621 [DatePart("Firebird", "Extract({0} from {{0}})", true, new[] { null, null, null, "YearDay", null, null, null, null, null, null, null }, 0, 1)]
|
|
|
622 [DatePart("Oracle", "To_Number(To_Char({{0}}, {0}))", true, new[] { "'YYYY'", "'Q'", "'MM'", "'DDD'", "'DD'", "'WW'", "'D'", "'HH24'", "'MI'", "'SS'", "'FF'" }, 0, 1)]
|
|
|
623 [DatePart("SQLite", "Cast(StrFTime({0}, {{0}}) as int)", true, new[] { "'%Y'", null, "'%m'", "'%j'", "'%d'", "'%W'", "'%w'", "'%H'", "'%M'", "'%S'", "'%f'" }, 0, 1)]
|
|
|
624 [DatePart("Access", "DatePart({0}, {{0}})", true, new[] { "'yyyy'", "'q'", "'m'", "'y'", "'d'", "'ww'", "'w'", "'h'", "'n'", "'s'", null }, 0, 1)]
|
|
|
625 public static int? DatePart(DateParts part, DateTime? date)
|
|
|
626 {
|
|
|
627 if (date == null)
|
|
|
628 return null;
|
|
|
629
|
|
|
630 switch (part)
|
|
|
631 {
|
|
|
632 case DateParts.Year : return date.Value.Year;
|
|
|
633 case DateParts.Quarter : return (date.Value.Month - 1) / 3 + 1;
|
|
|
634 case DateParts.Month : return date.Value.Month;
|
|
|
635 case DateParts.DayOfYear : return date.Value.DayOfYear;
|
|
|
636 case DateParts.Day : return date.Value.Day;
|
|
|
637 case DateParts.Week : return CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(date.Value, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
|
|
|
638 case DateParts.WeekDay : return ((int)date.Value.DayOfWeek + 1 + DateFirst + 6) % 7 + 1;
|
|
|
639 case DateParts.Hour : return date.Value.Hour;
|
|
|
640 case DateParts.Minute : return date.Value.Minute;
|
|
|
641 case DateParts.Second : return date.Value.Second;
|
|
|
642 case DateParts.Millisecond : return date.Value.Millisecond;
|
|
|
643 }
|
|
|
644
|
|
|
645 throw new InvalidOperationException();
|
|
|
646 }
|
|
|
647
|
|
|
648 [CLSCompliant(false)]
|
|
|
649 [SqlFunction]
|
|
|
650 [SqlFunction("MySql", "TIMESTAMPDIFF")]
|
|
|
651 public static int? DateDiff(DateParts part, DateTime? startDate, DateTime? endDate)
|
|
|
652 {
|
|
|
653 if (startDate == null || endDate == null)
|
|
|
654 return null;
|
|
|
655
|
|
|
656 switch (part)
|
|
|
657 {
|
|
|
658 case DateParts.Day : return (int)(endDate - startDate).Value.TotalDays;
|
|
|
659 case DateParts.Hour : return (int)(endDate - startDate).Value.TotalHours;
|
|
|
660 case DateParts.Minute : return (int)(endDate - startDate).Value.TotalMinutes;
|
|
|
661 case DateParts.Second : return (int)(endDate - startDate).Value.TotalSeconds;
|
|
|
662 case DateParts.Millisecond : return (int)(endDate - startDate).Value.TotalMilliseconds;
|
|
|
663 }
|
|
|
664
|
|
|
665 throw new InvalidOperationException();
|
|
|
666 }
|
|
|
667
|
|
|
668 [SqlProperty("@@DATEFIRST")]
|
|
|
669 public static int DateFirst
|
|
|
670 {
|
|
|
671 get { return 7; }
|
|
|
672 }
|
|
|
673
|
|
|
674 [SqlFunction]
|
|
|
675 public static DateTime? MakeDateTime(int? year, int? month, int? day)
|
|
|
676 {
|
|
|
677 return year == null || month == null || day == null ?
|
|
|
678 (DateTime?)null :
|
|
|
679 new DateTime(year.Value, month.Value, day.Value);
|
|
|
680 }
|
|
|
681
|
|
|
682 [SqlFunction]
|
|
|
683 public static DateTime? MakeDateTime(int? year, int? month, int? day, int? hour, int? minute, int? second)
|
|
|
684 {
|
|
|
685 return year == null || month == null || day == null || hour == null || minute == null || second == null ?
|
|
|
686 (DateTime?)null :
|
|
|
687 new DateTime(year.Value, month.Value, day.Value, hour.Value, minute.Value, second.Value);
|
|
|
688 }
|
|
|
689
|
|
|
690 #endregion
|
|
|
691
|
|
|
692 #region Math Functions
|
|
|
693
|
|
|
694 [SqlFunction] public static Decimal? Abs (Decimal? value) { return value == null ? null : (Decimal?)Math.Abs (value.Value); }
|
|
|
695 [SqlFunction] public static Double? Abs (Double? value) { return value == null ? null : (Double?) Math.Abs (value.Value); }
|
|
|
696 [SqlFunction] public static Int16? Abs (Int16? value) { return value == null ? null : (Int16?) Math.Abs (value.Value); }
|
|
|
697 [SqlFunction] public static Int32? Abs (Int32? value) { return value == null ? null : (Int32?) Math.Abs (value.Value); }
|
|
|
698 [SqlFunction] public static Int64? Abs (Int64? value) { return value == null ? null : (Int64?) Math.Abs (value.Value); }
|
|
|
699 [CLSCompliant(false)]
|
|
|
700 [SqlFunction] public static SByte? Abs (SByte? value) { return value == null ? null : (SByte?) Math.Abs (value.Value); }
|
|
|
701 [SqlFunction] public static Single? Abs (Single? value) { return value == null ? null : (Single?) Math.Abs (value.Value); }
|
|
|
702
|
|
|
703 [SqlFunction] public static Double? Acos (Double? value) { return value == null ? null : (Double?) Math.Acos (value.Value); }
|
|
|
704 [SqlFunction] public static Double? Asin (Double? value) { return value == null ? null : (Double?) Math.Asin (value.Value); }
|
|
|
705
|
|
|
706 [SqlFunction("Access", "Atn")]
|
|
|
707 [SqlFunction] public static Double? Atan (Double? value) { return value == null ? null : (Double?) Math.Atan (value.Value); }
|
|
|
708
|
|
|
709 [CLSCompliant(false)]
|
|
|
710 [SqlFunction( "MsSql2012", "Atn2")]
|
|
|
711 [SqlFunction( "MsSql2008", "Atn2")]
|
|
|
712 [SqlFunction( "MsSql2000", "Atn2")]
|
|
|
713 [SqlFunction( "MsSql2005", "Atn2")]
|
|
|
714 [SqlFunction( "DB2", "Atan2", 1, 0)]
|
|
|
715 [SqlFunction( "SqlCe", "Atn2")]
|
|
|
716 [SqlFunction( "Sybase", "Atn2")]
|
|
|
717 [SqlFunction] public static Double? Atan2 (Double? x, Double? y) { return x == null || y == null? null : (Double?)Math.Atan2(x.Value, y.Value); }
|
|
|
718
|
|
|
719 [SqlFunction("Informix", "Ceil")]
|
|
|
720 [SqlFunction("Oracle", "Ceil")]
|
|
|
721 [SqlFunction] public static Decimal? Ceiling(Decimal? value) { return value == null ? null : (Decimal?)decimal.Ceiling(value.Value); }
|
|
|
722 [SqlFunction("Informix", "Ceil")]
|
|
|
723 [SqlFunction("Oracle", "Ceil")]
|
|
|
724 [SqlFunction] public static Double? Ceiling(Double? value) { return value == null ? null : (Double?)Math.Ceiling(value.Value); }
|
|
|
725
|
|
|
726 [SqlFunction] public static Double? Cos (Double? value) { return value == null ? null : (Double?)Math.Cos (value.Value); }
|
|
|
727
|
|
|
728 [SqlFunction] public static Double? Cosh (Double? value) { return value == null ? null : (Double?)Math.Cosh (value.Value); }
|
|
|
729
|
|
|
730 [SqlFunction] public static Double? Cot (Double? value) { return value == null ? null : (Double?)Math.Cos(value.Value) / Math.Sin(value.Value); }
|
|
|
731
|
|
|
732 [SqlFunction] public static Decimal? Degrees(Decimal? value) { return value == null ? null : (Decimal?)(value.Value * 180m / (Decimal)Math.PI); }
|
|
|
733 [SqlFunction] public static Double? Degrees(Double? value) { return value == null ? null : (Double?) (value * 180 / Math.PI); }
|
|
|
734 [SqlFunction] public static Int16? Degrees(Int16? value) { return value == null ? null : (Int16?) (value * 180 / Math.PI); }
|
|
|
735 [SqlFunction] public static Int32? Degrees(Int32? value) { return value == null ? null : (Int32?) (value * 180 / Math.PI); }
|
|
|
736 [SqlFunction] public static Int64? Degrees(Int64? value) { return value == null ? null : (Int64?) (value * 180 / Math.PI); }
|
|
|
737 [CLSCompliant(false)]
|
|
|
738 [SqlFunction] public static SByte? Degrees(SByte? value) { return value == null ? null : (SByte?) (value * 180 / Math.PI); }
|
|
|
739 [SqlFunction] public static Single? Degrees(Single? value) { return value == null ? null : (Single?) (value * 180 / Math.PI); }
|
|
|
740
|
|
|
741 [SqlFunction] public static Double? Exp (Double? value) { return value == null ? null : (Double?)Math.Exp (value.Value); }
|
|
|
742
|
|
|
743 [SqlFunction("Access", "Int")]
|
|
|
744 [SqlFunction] public static Decimal? Floor (Decimal? value) { return value == null ? null : (Decimal?)decimal.Floor(value.Value); }
|
|
|
745 [SqlFunction("Access", "Int")]
|
|
|
746 [SqlFunction] public static Double? Floor (Double? value) { return value == null ? null : (Double?) Math. Floor(value.Value); }
|
|
|
747
|
|
|
748 [SqlFunction("Informix", "LogN")]
|
|
|
749 [SqlFunction("Oracle", "Ln")]
|
|
|
750 [SqlFunction("Firebird", "Ln")]
|
|
|
751 [SqlFunction("PostgreSQL", "Ln")]
|
|
|
752 [SqlFunction] public static Decimal? Log (Decimal? value) { return value == null ? null : (Decimal?)Math.Log ((Double)value.Value); }
|
|
|
753 [SqlFunction("Informix", "LogN")]
|
|
|
754 [SqlFunction("Oracle", "Ln")]
|
|
|
755 [SqlFunction("Firebird", "Ln")]
|
|
|
756 [SqlFunction("PostgreSQL", "Ln")]
|
|
|
757 [SqlFunction] public static Double? Log (Double? value) { return value == null ? null : (Double?) Math.Log (value.Value); }
|
|
|
758
|
|
|
759 [SqlFunction("PostgreSQL", "Log")]
|
|
|
760 [SqlFunction] public static Double? Log10 (Double? value) { return value == null ? null : (Double?) Math.Log10 (value.Value); }
|
|
|
761
|
|
|
762 [SqlFunction]
|
|
|
763 public static double? Log(double? newBase, double? value)
|
|
|
764 {
|
|
|
765 return value == null || newBase == null ? null : (Double?)Math.Log(value.Value, newBase.Value);
|
|
|
766 }
|
|
|
767
|
|
|
768 [SqlFunction]
|
|
|
769 public static decimal? Log(decimal? newBase, decimal? value)
|
|
|
770 {
|
|
|
771 return value == null || newBase == null ? null : (decimal?)Math.Log((double)value.Value, (double)newBase.Value);
|
|
|
772 }
|
|
|
773
|
|
|
774 [SqlExpression("Access", "{0} ^ {1}", Precedence = Precedence.Multiplicative)]
|
|
|
775 [SqlFunction]
|
|
|
776 public static Double? Power(Double? x, Double? y)
|
|
|
777 {
|
|
|
778 return x == null || y == null ? null : (Double?)Math.Pow(x.Value, y.Value);
|
|
|
779 }
|
|
|
780
|
|
|
781 [SqlFunction]
|
|
|
782 public static Decimal? RoundToEven(Decimal? value)
|
|
|
783 {
|
|
|
784 #if SILVERLIGHT
|
|
|
785 return value == null ? null : (Decimal?)Math.Round(value.Value);
|
|
|
786 #else
|
|
|
787 return value == null ? null : (Decimal?)Math.Round(value.Value, MidpointRounding.ToEven);
|
|
|
788 #endif
|
|
|
789 }
|
|
|
790
|
|
|
791 [SqlFunction]
|
|
|
792 public static Double? RoundToEven(Double? value)
|
|
|
793 {
|
|
|
794 #if SILVERLIGHT
|
|
|
795 return value == null ? null : (Double?) Math.Round(value.Value);
|
|
|
796 #else
|
|
|
797 return value == null ? null : (Double?) Math.Round(value.Value, MidpointRounding.ToEven);
|
|
|
798 #endif
|
|
|
799 }
|
|
|
800
|
|
|
801 [SqlFunction] public static Decimal? Round(Decimal? value) { return Round(value, 0); }
|
|
|
802 [SqlFunction] public static Double? Round(Double? value) { return Round(value, 0); }
|
|
|
803
|
|
|
804 [SqlFunction]
|
|
|
805 public static Decimal? Round(Decimal? value, int? precision)
|
|
|
806 {
|
|
|
807 #if SILVERLIGHT
|
|
|
808 throw new InvalidOperationException();
|
|
|
809 #else
|
|
|
810 return value == null || precision == null? null : (Decimal?)Math.Round(value.Value, precision.Value, MidpointRounding.AwayFromZero);
|
|
|
811 #endif
|
|
|
812 }
|
|
|
813
|
|
|
814 [SqlFunction]
|
|
|
815 public static Double? Round(Double? value, int? precision)
|
|
|
816 {
|
|
|
817 #if SILVERLIGHT
|
|
|
818 throw new InvalidOperationException();
|
|
|
819 #else
|
|
|
820 return value == null || precision == null? null : (Double?) Math.Round(value.Value, precision.Value, MidpointRounding.AwayFromZero);
|
|
|
821 #endif
|
|
|
822 }
|
|
|
823
|
|
|
824 [SqlFunction]
|
|
|
825 public static Decimal? RoundToEven(Decimal? value, int? precision)
|
|
|
826 {
|
|
|
827 #if SILVERLIGHT
|
|
|
828 return value == null || precision == null? null : (Decimal?)Math.Round(value.Value, precision.Value);
|
|
|
829 #else
|
|
|
830 return value == null || precision == null? null : (Decimal?)Math.Round(value.Value, precision.Value, MidpointRounding.ToEven);
|
|
|
831 #endif
|
|
|
832 }
|
|
|
833
|
|
|
834 [SqlFunction]
|
|
|
835 public static Double? RoundToEven(Double? value, int? precision)
|
|
|
836 {
|
|
|
837 #if SILVERLIGHT
|
|
|
838 return value == null || precision == null? null : (Double?) Math.Round(value.Value, precision.Value);
|
|
|
839 #else
|
|
|
840 return value == null || precision == null? null : (Double?) Math.Round(value.Value, precision.Value, MidpointRounding.ToEven);
|
|
|
841 #endif
|
|
|
842 }
|
|
|
843
|
|
|
844 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Decimal? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
845 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Double? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
846 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Int16? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
847 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Int32? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
848 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Int64? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
849 [CLSCompliant(false)]
|
|
|
850 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(SByte? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
851 [SqlFunction("Access", "Sgn"), SqlFunction] public static int? Sign(Single? value) { return value == null ? null : (int?)Math.Sign(value.Value); }
|
|
|
852
|
|
|
853 [SqlFunction] public static Double? Sin (Double? value) { return value == null ? null : (Double?)Math.Sin (value.Value); }
|
|
|
854 [SqlFunction] public static Double? Sinh (Double? value) { return value == null ? null : (Double?)Math.Sinh(value.Value); }
|
|
|
855 [SqlFunction("Access", "Sqr")]
|
|
|
856 [SqlFunction] public static Double? Sqrt (Double? value) { return value == null ? null : (Double?)Math.Sqrt(value.Value); }
|
|
|
857 [SqlFunction] public static Double? Tan (Double? value) { return value == null ? null : (Double?)Math.Tan (value.Value); }
|
|
|
858 [SqlFunction] public static Double? Tanh (Double? value) { return value == null ? null : (Double?)Math.Tanh(value.Value); }
|
|
|
859
|
|
|
860 [SqlExpression("MsSql2012", "Round({0}, 0, 1)")]
|
|
|
861 [SqlExpression("MsSql2008", "Round({0}, 0, 1)")]
|
|
|
862 [SqlExpression("MsSql2005", "Round({0}, 0, 1)")]
|
|
|
863 [SqlExpression("MsSql2000", "Round({0}, 0, 1)")]
|
|
|
864 [SqlExpression("DB2", "Truncate({0}, 0)")]
|
|
|
865 [SqlExpression("Informix", "Trunc({0}, 0)")]
|
|
|
866 [SqlExpression("Oracle", "Trunc({0}, 0)")]
|
|
|
867 [SqlExpression("Firebird", "Trunc({0}, 0)")]
|
|
|
868 [SqlExpression("PostgreSQL", "Trunc({0}, 0)")]
|
|
|
869 [SqlExpression("MySql", "Truncate({0}, 0)")]
|
|
|
870 [SqlExpression("SqlCe", "Round({0}, 0, 1)")]
|
|
|
871 [SqlFunction]
|
|
|
872 public static Decimal? Truncate(Decimal? value)
|
|
|
873 {
|
|
|
874 #if SILVERLIGHT
|
|
|
875 throw new InvalidOperationException();
|
|
|
876 #else
|
|
|
877 return value == null ? null : (Decimal?)decimal.Truncate(value.Value);
|
|
|
878 #endif
|
|
|
879 }
|
|
|
880
|
|
|
881 [SqlExpression("MsSql2012", "Round({0}, 0, 1)")]
|
|
|
882 [SqlExpression("MsSql2008", "Round({0}, 0, 1)")]
|
|
|
883 [SqlExpression("MsSql2005", "Round({0}, 0, 1)")]
|
|
|
884 [SqlExpression("MsSql2000", "Round({0}, 0, 1)")]
|
|
|
885 [SqlExpression("DB2", "Truncate({0}, 0)")]
|
|
|
886 [SqlExpression("Informix", "Trunc({0}, 0)")]
|
|
|
887 [SqlExpression("Oracle", "Trunc({0}, 0)")]
|
|
|
888 [SqlExpression("Firebird", "Trunc({0}, 0)")]
|
|
|
889 [SqlExpression("PostgreSQL", "Trunc({0}, 0)")]
|
|
|
890 [SqlExpression("MySql", "Truncate({0}, 0)")]
|
|
|
891 [SqlExpression("SqlCe", "Round({0}, 0, 1)")]
|
|
|
892 [SqlFunction]
|
|
|
893 public static Double? Truncate(Double? value)
|
|
|
894 {
|
|
|
895 #if SILVERLIGHT
|
|
|
896 throw new InvalidOperationException();
|
|
|
897 #else
|
|
|
898 return value == null ? null : (Double?) Math.Truncate(value.Value);
|
|
|
899 #endif
|
|
|
900 }
|
|
|
901
|
|
|
902 #endregion
|
|
|
903 }
|
|
|
904 }
|
