Mercurial > pub > bltoolkit

comparison Source/Data/Linq/Sql.cs @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
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 }