forked from ServiceStack/ServiceStack.OrmLite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Main.cs
248 lines (201 loc) · 11.3 KB
/
Main.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.Data;
using ServiceStack.Common.Utils;
using ServiceStack.DataAnnotations;
using ServiceStack.Common.Extensions;
using System.Reflection;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Oracle;
namespace TestExpressions
{
public class Author{
public Author(){}
[AutoIncrement]
[Alias("AuthorID")]
public Int32 Id { get; set;}
[Index(Unique = true)]
[StringLength(40)]
public string Name { get; set;}
public DateTime Birthday { get; set;}
public DateTime ? LastActivity { get; set;}
public Decimal? Earnings { get; set;}
public bool Active { get; set; }
[StringLength(80)]
[Alias("JobCity")]
public string City { get; set;}
[StringLength(80)]
[Alias("Comment")]
public string Comments { get; set;}
public Int16 Rate{ get; set;}
}
class MainClass
{
public static void Main (string[] args)
{
Console.WriteLine ("Hello World!");
OrmLiteConfig.DialectProvider = new OracleOrmLiteDialectProvider();
SqlExpressionVisitor<Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Author>();
using (IDbConnection db =
"Data Source=x;User Id=x;Password=x;".OpenDbConnection())
using ( IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.DropTable<Author>();
dbCmd.CreateTable<Author>();
dbCmd.DeleteAll<Author>();
List<Author> authors = new List<Author>();
authors.Add(new Author(){Name="Demis Bellot",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 99.9m,Comments="CSharp books", Rate=10, City="London"});
authors.Add(new Author(){Name="Angel Colmenares",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 50.0m,Comments="CSharp books", Rate=5, City="Bogota"});
authors.Add(new Author(){Name="Adam Witco",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 80.0m,Comments="Math Books", Rate=9, City="London"});
authors.Add(new Author(){Name="Claudia Espinel",Birthday= DateTime.Today.AddYears(-23),Active=true,Earnings= 60.0m,Comments="Cooking books", Rate=10, City="Bogota"});
authors.Add(new Author(){Name="Libardo Pajaro",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 80.0m,Comments="CSharp books", Rate=9, City="Bogota"});
authors.Add(new Author(){Name="Jorge Garzon",Birthday= DateTime.Today.AddYears(-28),Active=true,Earnings= 70.0m,Comments="CSharp books", Rate=9, City="Bogota"});
authors.Add(new Author(){Name="Alejandro Isaza",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 70.0m,Comments="Java books", Rate=0, City="Bogota"});
authors.Add(new Author(){Name="Wilmer Agamez",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 30.0m,Comments="Java books", Rate=0, City="Cartagena"});
authors.Add(new Author(){Name="Rodger Contreras",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 90.0m,Comments="CSharp books", Rate=8, City="Cartagena"});
authors.Add(new Author(){Name="Chuck Benedict",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.5m,Comments="CSharp books", Rate=8, City="London"});
authors.Add(new Author(){Name="James Benedict II",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.5m,Comments="Java books", Rate=5, City="Berlin"});
authors.Add(new Author(){Name="Ethan Brown",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 45.0m,Comments="CSharp books", Rate=5, City="Madrid"});
authors.Add(new Author(){Name="Xavi Garzon",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 75.0m,Comments="CSharp books", Rate=9, City="Madrid"});
authors.Add(new Author(){Name="Luis garzon",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.0m,Comments="CSharp books", Rate=10, City="Mexico"});
dbCmd.InsertAll(authors);
// lets start !
// select authors born 20 year ago
int year = DateTime.Today.AddYears(-20).Year;
int expected=5;
ev.Where(rn=> rn.Birthday>=new DateTime(year, 1,1) && rn.Birthday<=new DateTime(year, 12,31));
List<Author> result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
result = dbCmd.Select<Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)));
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count);
result = dbCmd.Select<Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31));
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count);
// select authors from London, Berlin and Madrid : 6
expected=6;
//Sql.In can take params object[]
ev.Where(rn=> Sql.In( rn.City, new object[]{"London", "Madrid", "Berlin"}) );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors from Bogota and Cartagena : 7
expected=7;
//... or Sql.In can take IList<Object>
List<Object> cities= new List<Object>();
cities.Add("Bogota");
cities.Add("Cartagena");
ev.Where(rn => Sql.In(rn.City, cities ));
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors which name starts with A
expected=3;
ev.Where(rn=> rn.Name.StartsWith("A") );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors which name ends with Garzon o GARZON o garzon ( no case sensitive )
expected=3;
ev.Where(rn=> rn.Name.ToUpper().EndsWith("GARZON") );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors which name ends with garzon ( no case sensitive )
expected=3;
ev.Where(rn=> rn.Name.EndsWith("garzon") );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors which name contains Benedict
expected=2;
ev.Where(rn=> rn.Name.Contains("Benedict") );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors with Earnings <= 50
expected=3;
ev.Where(rn=> rn.Earnings<=50 );
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// select authors with Rate = 10 and city=Mexio
expected=1;
ev.Where(rn=> rn.Rate==10 && rn.City=="Mexico");
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// enough selecting, lets update;
// set Active=false where rate =0
expected=2;
ev.Where(rn=> rn.Rate==0 ).Update(rn=> rn.Active);
var rows = dbCmd.UpdateOnly( new Author(){ Active=false }, ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
// insert values only in Id, Name, Birthday, Rate and Active fields
expected=4;
ev.Insert(rn =>new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate} );
dbCmd.InsertOnly( new Author(){Active=false, Rate=0, Name="Victor Grozny", Birthday=DateTime.Today.AddYears(-18) }, ev);
dbCmd.InsertOnly( new Author(){Active=false, Rate=0, Name="Ivan Chorny", Birthday=DateTime.Today.AddYears(-19) }, ev);
ev.Where(rn=> !rn.Active);
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
//update comment for City == null
expected=2;
ev.Where( rn => rn.City==null ).Update(rn=> rn.Comments);
rows=dbCmd.UpdateOnly(new Author(){Comments="No comments"}, ev);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
// delete where City is null
expected=2;
rows = dbCmd.Delete( ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
// lets select all records ordered by Rate Descending and Name Ascending
expected=14;
ev.Where().OrderBy(rn=> new{ at=Sql.Desc(rn.Rate), rn.Name }); // clear where condition
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
Console.WriteLine(ev.OrderByExpression);
var author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel", author.Name, "Claudia Espinel"==author.Name);
// select only first 5 rows ....
expected=5;
ev.Limit(5); // note: order is the same as in the last sentence
result=dbCmd.Select(ev);
Console.WriteLine(ev.WhereExpression);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
// lets select only Name and City (name will be "UPPERCASED" )
ev.Select(rn=> new { at= Sql.As( rn.Name.ToUpper(), "Name" ), rn.City} );
Console.WriteLine(ev.SelectExpression);
result=dbCmd.Select(ev);
author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper()==author.Name);
//paging :
ev.Limit(0,4);// first page, page size=4;
result=dbCmd.Select(ev);
author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper()==author.Name);
ev.Limit(4,4);// second page
result=dbCmd.Select(ev);
author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper()==author.Name);
ev.Limit(8,4);// third page
result=dbCmd.Select(ev);
author = result.FirstOrDefault();
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper()==author.Name);
// select distinct..
ev.Limit(); // clear limit
ev.SelectDistinct(r=>r.City).OrderBy();
expected=6;
result=dbCmd.Select(ev);
Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
Console.ReadLine();
Console.WriteLine("Press Enter to continue");
}
Console.WriteLine ("This is The End my friend!");
}
}
}