PageRenderTime 2ms CodeModel.GetById 3ms app.highlight 16ms RepoModel.GetById 1ms app.codeStats 0ms

/mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/ReadTests_DateTimeFunctions.cs

https://github.com/ztfuqingvip/mono
C# | 510 lines | 384 code | 99 blank | 27 comment | 46 complexity | c15cc40d29bf52df25df50a9b496624a MD5 | raw file
  1#region MIT license
  2// 
  3// MIT license
  4//
  5// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
  6// 
  7// Permission is hereby granted, free of charge, to any person obtaining a copy
  8// of this software and associated documentation files (the "Software"), to deal
  9// in the Software without restriction, including without limitation the rights
 10// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 11// copies of the Software, and to permit persons to whom the Software is
 12// furnished to do so, subject to the following conditions:
 13// 
 14// The above copyright notice and this permission notice shall be included in
 15// all copies or substantial portions of the Software.
 16// 
 17// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 18// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 19// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 20// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 21// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 22// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 23// THE SOFTWARE.
 24// 
 25#endregion
 26using System;
 27using System.Globalization;
 28using System.Collections.Generic;
 29using System.Text;
 30using System.Linq;
 31using System.Linq.Expressions;
 32using NUnit.Framework;
 33using Test_NUnit;
 34
 35using nwind;
 36
 37#if MONO_STRICT
 38using System.Data.Linq;
 39#else
 40using DbLinq.Data.Linq;
 41#endif
 42
 43// test ns 
 44#if MYSQL
 45    namespace Test_NUnit_MySql
 46#elif ORACLE && ODP
 47    namespace Test_NUnit_OracleODP
 48#elif ORACLE
 49    namespace Test_NUnit_Oracle
 50#elif POSTGRES
 51    namespace Test_NUnit_PostgreSql
 52#elif SQLITE
 53    namespace Test_NUnit_Sqlite
 54#elif INGRES
 55    namespace Test_NUnit_Ingres
 56#elif MSSQL && L2SQL
 57    namespace Test_NUnit_MsSql_Strict
 58#elif MSSQL
 59    namespace Test_NUnit_MsSql
 60#elif FIREBIRD
 61    namespace Test_NUnit_Firebird
 62#endif
 63{
 64    [TestFixture]
 65    public class ReadTests_DateTimeFunctions : TestBase
 66    {
 67#if !DEBUG && SQLITE
 68        [Explicit]
 69#endif
 70        [Test]
 71        public void GetYear()
 72        {
 73            Northwind db = CreateDB();
 74
 75            var q = from o in db.Orders
 76                    where o.OrderDate.Value.Year == 1996
 77                    select o;
 78
 79            var list = q.ToList();
 80            Assert.IsTrue(list.Count > 0);
 81        }
 82
 83#if !DEBUG && SQLITE
 84        [Explicit]
 85#endif
 86        [Test]
 87        public void GetMonth()
 88        {
 89            Northwind db = CreateDB();
 90
 91            var q = from o in db.Orders
 92                    where o.OrderDate.Value.Month == 10
 93                    select o;
 94
 95            var list = q.ToList();
 96            Assert.IsTrue(list.Count > 0);
 97        }
 98
 99#if !DEBUG && SQLITE
100        [Explicit]
101#endif
102        [Test]
103        public void GetDay()
104        {
105            Northwind db = CreateDB();
106
107            var q = from o in db.Orders
108                    where o.OrderDate.Value.Day == 16
109                    select o;
110
111            var list = q.ToList();
112            Assert.IsTrue(list.Count > 0);
113        }
114
115#if !DEBUG && SQLITE
116        [Explicit]
117#endif
118        [Test]
119        public void GetHours()
120        {
121            Northwind db = CreateDB();
122
123            var q = (from o in db.Orders
124                     where o.OrderDate.Value.Hour == 0
125                     select o).ToList();
126
127
128        }
129
130#if !DEBUG && SQLITE
131        [Explicit]
132#endif
133        [Test]
134        public void GetMinutes()
135        {
136            Northwind db = CreateDB();
137
138            var q = (from o in db.Orders
139                     where o.OrderDate.Value.Minute == 0
140                     select o).ToList();
141
142
143        }
144
145#if !DEBUG && SQLITE
146        [Explicit]
147#endif
148        [Test]
149        public void GetSeconds()
150        {
151            Northwind db = CreateDB();
152
153            var q = (from o in db.Orders
154                     where o.OrderDate.Value.Second == 16
155                     select o).ToList();
156
157        }
158
159#if !DEBUG && SQLITE
160        [Explicit]
161#endif
162        [Test]
163        public void GetMilliSeconds()
164        {
165            Northwind db = CreateDB();
166
167            var q = (from o in db.Orders
168                     where o.OrderDate.Value.Millisecond == 0
169                     select o).ToList();
170
171        }
172
173#if !DEBUG && SQLITE
174        [Explicit]
175#endif
176        [Test]
177        public void GetCurrentDateTime()
178        {
179            Northwind db = CreateDB();
180            var query = from e in db.Employees
181                        where e.BirthDate.HasValue && e.BirthDate.Value == DateTime.Now
182                        select e;
183
184            var list = query.ToList();
185        }
186
187#if !DEBUG && SQLITE
188        [Explicit]
189#endif
190        [Test]
191        public void Parse01()
192        {
193            Northwind db = CreateDB();
194            var query = from e in db.Employees
195                        where e.BirthDate.Value == DateTime.Parse("1984/05/02")
196                        select e;
197
198            var list = query.ToList();
199        }
200
201        [Test]
202        [ExpectedException(typeof(NotSupportedException))]
203        public void Parse02()
204        {
205            Northwind db = CreateDB();
206            var query = from e in db.Employees
207                        where e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString())
208                        select e;
209
210            var list = query.ToList();
211        }
212
213        [Test]
214        public void Parse03()
215        {
216            Northwind db = CreateDB();
217            var query = from e in db.Employees
218                        where e.BirthDate.HasValue
219                        select e.BirthDate.Value == DateTime.Parse("1984/05/02");
220
221
222            var list = query.ToList();
223        }
224
225        [Test]
226        [ExpectedException(typeof(InvalidOperationException))]
227        public void Parse04()
228        {
229            Northwind db = CreateDB();
230            var query = from e in db.Employees
231                        select e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString());
232
233
234            var list = query.ToList();
235        }
236
237#if !DEBUG && SQLITE
238        [Explicit]
239#endif
240        [Test]
241        public void DateTimeDiffTotalHours()
242        {
243            Northwind db = CreateDB();
244            var query = from e in db.Employees
245                        where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalHours > 0
246                        select e;
247
248
249            var list = query.ToList();
250        }
251
252#if !DEBUG && SQLITE
253        [Explicit]
254#endif
255        [Test]
256        public void DateTimeDiffHours()
257        {
258            Northwind db = CreateDB();
259
260            DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddHours(2);
261
262            var query = from e in db.Employees
263                        where (e.BirthDate.Value - parameterDateTime).Hours > -2
264                        select e;
265
266
267            var list = query.ToList();
268            Assert.Greater(list.Count, 0);
269        }
270
271#if !DEBUG && SQLITE
272        [Explicit]
273#endif
274        [Test]
275        public void DateTimeDiffTotalMinutes()
276        {
277            Northwind db = CreateDB();
278            var query = from e in db.Employees
279                        where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
280                        select e;
281
282
283            var list = query.ToList();
284        }
285
286#if !DEBUG && SQLITE
287        [Explicit]
288#endif
289        [Test]
290        public void DateTimeDiffMinutes()
291        {
292            Northwind db = CreateDB();
293
294            DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMinutes(2);
295
296            var query = from e in db.Employees
297                        where (e.BirthDate.Value - parameterDateTime).Minutes == -2
298                        select e;
299
300
301            var list = query.ToList();
302            Assert.Greater(list.Count, 0);
303        }
304
305
306#if !DEBUG && SQLITE
307        [Explicit]
308#endif
309        [Test]
310        public void DateTimeDiffTotalSeconds()
311        {
312            Northwind db = CreateDB();
313            var query = from e in db.Employees
314                        where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalSeconds > 0
315                        select e;
316
317
318            var list = query.ToList();
319        }
320
321#if !DEBUG && SQLITE
322        [Explicit]
323#endif
324        [Test]
325        public void DateTimeDiffSeconds()
326        {
327            Northwind db = CreateDB();
328
329            DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddSeconds(2);
330
331            var query = from e in db.Employees
332                        where (e.BirthDate.Value - parameterDateTime).Seconds == -2
333                        select e;
334
335
336            var list = query.ToList();
337            Assert.Greater(list.Count, 0);
338        }
339
340#if !DEBUG && (SQLITE || MSSQL)
341        // L2SQL: SQL Server doesnt' seem to support millisecond precision.
342        [Explicit]
343#endif
344        [Test]
345        public void DateTimeDiffMilliseconds()
346        {
347            Northwind db = CreateDB();
348
349            DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMilliseconds(2);
350
351            var query = from e in db.Employees
352                        where (e.BirthDate.Value - parameterDateTime).Milliseconds == -2
353                        select e;
354            
355
356            var list = query.ToList();
357            Assert.Greater(list.Count, 0);
358        }
359
360#if !DEBUG && SQLITE
361        [Explicit]
362#endif
363        [Test]
364        public void DateTimeDiffTotalMilliseconds()
365        {
366            Northwind db = CreateDB();
367            var query = from e in db.Employees
368                        where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
369                        select e;
370
371
372            var list = query.ToList();
373        }
374
375#if !DEBUG && SQLITE
376        [Explicit]
377#endif
378        [Test]
379        public void DateTimeDiffDays()
380        {
381            Northwind db = CreateDB();
382
383            DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddDays(2);
384
385            var query = from e in db.Employees
386                        where (e.BirthDate.Value - parameterDateTime).Days == -2
387                        select e;
388
389
390            var list = query.ToList();
391            Assert.Greater(list.Count, 0);
392        }
393
394#if !DEBUG && SQLITE
395        [Explicit]
396#endif
397        [Test]
398        public void DateTimeDiffTotalDays()
399        {
400            Northwind db = CreateDB();
401            DateTime firstDate = db.Employees.First().BirthDate.Value;
402
403            DateTime parameterDate = firstDate.Date.AddDays(2);
404            parameterDate = parameterDate.Date.AddHours(12);
405
406
407            var query = from e in db.Employees
408                        where (e.BirthDate.Value -parameterDate).TotalDays == -2.5
409                        select e;
410
411            var list = query.ToList();
412
413            Assert.Greater(list.Count, 0);
414        }
415
416#if !DEBUG && POSTGRES
417        [Explicit]
418#endif
419        [Test]
420        [ExpectedException(typeof(InvalidOperationException))]
421        public void DateTimeDiffTotalDaysSelectWithNulls01()
422        {
423            
424            Northwind db = CreateDB();
425
426            var employee = new Employee
427            {
428                FirstName = "Test First",
429                LastName  = "Test Last",
430            };
431            db.Employees.InsertOnSubmit(employee);
432            db.SubmitChanges();
433
434            DateTime firstDate = db.Employees.First().BirthDate.Value;
435            firstDate.Date.AddDays(2);
436            DateTime parameterDate = firstDate.Date.AddHours(12);
437
438            try
439            {
440                //this test should throw an invalid operation exception since one BirthDate is null so select clausle should crash
441                var query = from e in db.Employees
442                            select (e.BirthDate.Value - parameterDate).TotalDays;
443
444                var list = query.ToList();
445
446                Assert.Greater(list.Count, 0);
447            }
448            finally
449            {
450                db.Employees.DeleteOnSubmit(employee);
451                db.SubmitChanges();
452            }
453        }
454
455#if !DEBUG && POSTGRES
456        [Explicit]
457#endif
458        [Test]
459        public void DateTimeDiffTotalDaysSelectWithNulls02()
460        {
461            Northwind db = CreateDB();
462
463            var employee = new Employee
464            {
465                FirstName = "Test First",
466                LastName = "Test Last",
467            };
468            db.Employees.InsertOnSubmit(employee);
469            db.SubmitChanges();
470
471            DateTime firstDate = db.Employees.First().BirthDate.Value;
472
473            DateTime parameterDate = firstDate.Date.AddDays(2);
474            parameterDate = parameterDate.Date.AddHours(12);
475
476            try
477            {
478                var query = from e in db.Employees
479                            where e.BirthDate.HasValue
480                            select (e.BirthDate.Value - parameterDate).TotalDays;
481
482                var list = query.ToList();
483
484                Assert.Greater(list.Count, 0);
485            }
486            finally
487            {
488                db.Employees.DeleteOnSubmit(employee);
489                db.SubmitChanges();
490            }
491        }
492
493
494#if !DEBUG && (SQLITE || (MSSQL && L2SQL))
495        // L2SQL: System.Data.SqlClient.SqlException : The datepart minute is not supported by date function datepart for data type date.
496        [Explicit]
497#endif
498        [Test]
499        public void DateGetDate()
500        {
501            Northwind db = CreateDB();
502            var query = from e in db.Employees
503                        where (e.BirthDate.Value.Date).Minute == 0
504                        select e;
505
506
507            var list = query.ToList();
508        }
509    }
510}