Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 496 lines (369 sloc) 20.018 kb
a469858 @mythz
mythz authored
1 [Join the new google group](http://groups.google.com/group/servicestack) or
2 follow [@demisbellot](http://twitter.com/demisbellot) and [@ServiceStack](http://twitter.com/servicestack)
3 for twitter updates.
4
5 ServiceStack.OrmLite is a convention-based, configuration-free lightweight ORM that uses standard POCO classes and Data Annotation attributes to infer its table schema.
6 # Introduction
7
8 OrmLite is a set of light-weight C# extension methods around System.Data.`*` interfaces which is designed to persist POCO classes with a minimal amount of intrusion and configuration.
9 Another Orm with similar goals is [sqlite-net](http://code.google.com/p/sqlite-net/).
10
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
11 OrmLite was designed with a focus on the core objectives:
0579d15 @mythz Edited README.md via GitHub
mythz authored
12
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
13 * Map a POCO class 1:1 to an RDBMS table, cleanly by conventions, without any attributes required.
14 * Create/Drop DB Table schemas using nothing but POCO class definitions (IOTW a true code-first ORM)
15 * Simplicity - typed, wrist friendly API for common data access patterns.
a469858 @mythz
mythz authored
16 * High performance - with support for indexes, text blobs, etc.
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
17 * Amongst the [fastest Micro ORMs](http://servicestack.net/benchmarks/) for .NET (just behind [Dapper](http://code.google.com/p/dapper-dot-net/)).
a469858 @mythz
mythz authored
18 * Expressive power and flexibility - with access to IDbCommand and raw SQL
19 * Cross platform - supports multiple dbs (currently: Sqlite and Sql Server) running on both .NET and Mono platforms.
20
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
21 In OrmLite: **1 Class = 1 Table**. There's no hidden behaviour behind the scenes auto-magically managing hidden references to other tables.
22 Any non-scalar properties (i.e. complex types) are text blobbed in a schema-less text field using [.NET's fastest Text Serializer](http://www.servicestack.net/mythz_blog/?p=176).
23 Effectively this allows you to create a table from any POCO type and it should persist as expected in a DB Table with columns for each of the classes 1st level public properties.
24
25 ### Other notable Micro ORMs for .NET
26 Many performance problems can be mitigated and a lot of use-cases can be simplified without the use of a heavyweight ORM, and their config, mappings and infrastructure.
27 As [performance is the most important feature](https://github.com/mythz/ScalingDotNET) we can recommend the following list, each with their own unique special blend of features.
28
29 * [Dapper](http://code.google.com/p/dapper-dot-net/) - by [@samsaffron](http://twitter.com/samsaffron) and [@marcgravell](http://twitter.com/marcgravell)
30 - The current performance king, supports both POCO and dynamic access, fits in a single class. Put in production to solve [StackOverflow's DB Perf issues](http://samsaffron.com/archive/2011/03/30/How+I+learned+to+stop+worrying+and+write+my+own+ORM). Requires .NET 4.
31 * [PetaPoco](http://www.toptensoftware.com/petapoco/) - by [@toptensoftware](http://twitter.com/toptensoftware)
32 - Fast, supports dynamics, expandos and typed POCOs, fits in a single class, runs on .NET 3.5 and Mono. Includes optional T4 templates for POCO table generation.
33 * [Massive](https://github.com/robconery/massive) - by [@robconery](http://twitter.com/robconery)
34 - Fast, supports dynamics and expandos, smart use of optional params to provide a wrist-friendly api, fits in a single class. Multiple RDBMS support. Requires .NET 4.
35 * [Simple.Data](https://github.com/markrendle/Simple.Data) - by [@markrendle](http://twitter.com/markrendle)
36 - A little slower than above ORMS, most wrist-friendly courtesy of a dynamic API, multiple RDBMS support inc. Mongo DB. Requires .NET 4.
37
a469858 @mythz
mythz authored
38 # Download
39 OrmLite is included with [ServiceStack.zip](https://github.com/downloads/mythz/ServiceStack/ServiceStack.zip) or available to download separately in a standalone
40 [ ServiceStack.OrmLite.zip](https://github.com/downloads/mythz/ServiceStack.OrmLite/ServiceStack.OrmLite.zip).
41
42 The full source code for OrmLite is also [available online](https://github.com/ServiceStack/ServiceStack.OrmLite).
43
234d0f4 @mythz Update README.md
mythz authored
44 # Code-first Customer & Order example with complex types on POCO as text blobs
fd1b70b @mythz Update README.md
mythz authored
45
d3b5ba1 @mythz Update README.md
mythz authored
46 Below is a complete stand-alone example. No other config or classes is required for it to run. It's also available as a
47 [stand-alone unit test](https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/tests/ServiceStack.OrmLite.Tests/UseCase/CustomerOrdersUseCase.cs).
48
6742c6d @mythz Update README.md
mythz authored
49 public enum PhoneType {
50 Home,
51 Work,
52 Mobile,
53 }
54
55 public enum AddressType {
56 Home,
57 Work,
58 Other,
59 }
60
61 public class Address {
62 public string Line1 { get; set; }
63 public string Line2 { get; set; }
64 public string ZipCode { get; set; }
65 public string State { get; set; }
66 public string City { get; set; }
67 public string Country { get; set; }
68 }
69
70 public class Customer {
71 public Customer() {
72 this.PhoneNumbers = new Dictionary<PhoneType, string>();
73 this.Addresses = new Dictionary<AddressType, Address>();
74 }
75
76 [AutoIncrement] // Creates Auto primary key
77 public int Id { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
78
6742c6d @mythz Update README.md
mythz authored
79 public string FirstName { get; set; }
80 public string LastName { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
81
6742c6d @mythz Update README.md
mythz authored
82 [Index(Unique = true)] // Creates Unique Index
83 public string Email { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
84
3ee5fdf @mythz Update README.md
mythz authored
85 public Dictionary<PhoneType, string> PhoneNumbers { get; private set; } //Blobbed
86 public Dictionary<AddressType, Address> Addresses { get; private set; } //Blobbed
6742c6d @mythz Update README.md
mythz authored
87 public DateTime CreatedAt { get; set; }
88 }
89
90 public class Order {
0be7ca1 @mythz give special properties some space
mythz authored
91
6742c6d @mythz Update README.md
mythz authored
92 [AutoIncrement]
93 public int Id { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
94
3ee5fdf @mythz Update README.md
mythz authored
95 [References(typeof(Customer))] //Creates Foreign Key
6742c6d @mythz Update README.md
mythz authored
96 public int CustomerId { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
97
3ee5fdf @mythz Update README.md
mythz authored
98 [References(typeof(Employee))] //Creates Foreign Key
6742c6d @mythz Update README.md
mythz authored
99 public int EmployeeId { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
100
6742c6d @mythz Update README.md
mythz authored
101 public Address ShippingAddress { get; set; } //Blobbed (no Address table)
0be7ca1 @mythz give special properties some space
mythz authored
102
6742c6d @mythz Update README.md
mythz authored
103 public DateTime? OrderDate { get; set; }
104 public DateTime? RequiredDate { get; set; }
105 public DateTime? ShippedDate { get; set; }
106 public int? ShipVia { get; set; }
107 public decimal Freight { get; set; }
108 public decimal Total { get; set; }
109 }
110
111 public class OrderDetail {
0be7ca1 @mythz give special properties some space
mythz authored
112
6742c6d @mythz Update README.md
mythz authored
113 [AutoIncrement]
114 public int Id { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
115
6742c6d @mythz Update README.md
mythz authored
116 [References(typeof(Order))] //Creates Foreign Key
117 public int OrderId { get; set; }
0be7ca1 @mythz give special properties some space
mythz authored
118
6742c6d @mythz Update README.md
mythz authored
119 public int ProductId { get; set; }
120 public decimal UnitPrice { get; set; }
121 public short Quantity { get; set; }
122 public decimal Discount { get; set; }
123 }
124
125 public class Employee {
126 public int Id { get; set; }
127 public string Name { get; set; }
128 }
129
130 public class Product {
131 public int Id { get; set; }
132 public string Name { get; set; }
133 public decimal UnitPrice { get; set; }
134 }
135
fd1b70b @mythz Update README.md
mythz authored
136 //Setup SQL Server Connection Factory
d3b5ba1 @mythz Update README.md
mythz authored
137 var dbFactory = new OrmLiteConnectionFactory(
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
138 @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
139 SqlServerOrmLiteDialectProvider.Instance);
fd1b70b @mythz Update README.md
mythz authored
140
d3b5ba1 @mythz Update README.md
mythz authored
141 //Use in-memory Sqlite DB instead
142 //var dbFactory = new OrmLiteConnectionFactory(
143 // ":memory:", false, SqliteOrmLiteDialectProvider.Instance);
fd1b70b @mythz Update README.md
mythz authored
144
145 //Non-intrusive: All extension methods hang off System.Data.* interfaces
146 IDbConnection dbConn = dbFactory.OpenDbConnection();
147 IDbCommand dbCmd = dbConn.CreateCommand();
148
149 //Re-Create all table schemas:
150 dbCmd.DropTable<OrderDetail>();
151 dbCmd.DropTable<Order>();
152 dbCmd.DropTable<Customer>();
153 dbCmd.DropTable<Product>();
154 dbCmd.DropTable<Employee>();
155
156 dbCmd.CreateTable<Employee>();
157 dbCmd.CreateTable<Product>();
158 dbCmd.CreateTable<Customer>();
159 dbCmd.CreateTable<Order>();
160 dbCmd.CreateTable<OrderDetail>();
161
162 dbCmd.Insert(new Employee { Id = 1, Name = "Employee 1" });
163 dbCmd.Insert(new Employee { Id = 2, Name = "Employee 2" });
164 var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 };
165 var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 };
166 dbCmd.Save(product1, product2);
167
168 var customer = new Customer
169 {
170 FirstName = "Orm",
171 LastName = "Lite",
172 Email = "ormlite@servicestack.net",
173 PhoneNumbers =
174 {
175 { PhoneType.Home, "555-1234" },
176 { PhoneType.Work, "1-800-1234" },
177 { PhoneType.Mobile, "818-123-4567" },
178 },
179 Addresses =
180 {
181 { AddressType.Work, new Address { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" } },
182 },
183 CreatedAt = DateTime.UtcNow,
184 };
185 dbCmd.Insert(customer);
186
187 var customerId = dbCmd.GetLastInsertId(); //Get Auto Inserted Id
188 customer = dbCmd.QuerySingle<Customer>(new { customer.Email }); //Query
189 Assert.That(customer.Id, Is.EqualTo(customerId));
190
191 //Direct access to System.Data.Transactions:
192 using (var trans = dbCmd.BeginTransaction(IsolationLevel.ReadCommitted))
193 {
194 var order = new Order
195 {
196 CustomerId = customer.Id,
197 EmployeeId = 1,
198 OrderDate = DateTime.UtcNow,
199 Freight = 10.50m,
200 ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
201 };
202 dbCmd.Save(order); //Inserts 1st time
203
204 order.Id = (int)dbCmd.GetLastInsertId(); //Get Auto Inserted Id
205
206 var orderDetails = new[] {
207 new OrderDetail
208 {
209 OrderId = order.Id,
210 ProductId = product1.Id,
211 Quantity = 2,
212 UnitPrice = product1.UnitPrice,
213 },
214 new OrderDetail
215 {
216 OrderId = order.Id,
217 ProductId = product2.Id,
218 Quantity = 2,
219 UnitPrice = product2.UnitPrice,
220 Discount = .15m,
221 }
222 };
223
224 dbCmd.Insert(orderDetails);
225
226 order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
227
228 dbCmd.Save(order); //Updates 2nd Time
229
230 trans.Commit();
231 }
232
ff8dd0f @mythz Update README.md
mythz authored
233 Running this against a SQL Server database will yield the results below:
a469858 @mythz
mythz authored
234
d3b5ba1 @mythz Update README.md
mythz authored
235 [![SQL Server Management Studio results](http://www.servicestack.net/files/ormlite-example.png)](http://www.servicestack.net/files/ormlite-example.png)
ff8dd0f @mythz Update README.md
mythz authored
236
237 Notice the POCO types are stored in the [very fast](http://www.servicestack.net/mythz_blog/?p=176)
238 and [Versatile](http://www.servicestack.net/mythz_blog/?p=314)
d3b5ba1 @mythz Update README.md
mythz authored
239 [JSV Format](https://github.com/ServiceStack/ServiceStack.Text/wiki/JSV-Format) which although hard to do -
240 is actually more compact, human and parser-friendly than JSON :)
ff8dd0f @mythz Update README.md
mythz authored
241
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
242 # API Overview
243
244 The API is minimal, providing basic shortcuts for the primitive SQL statements:
245
eeb5118 @mythz fixed img link
mythz authored
246 [![OrmLite API](http://www.servicestack.net/files/ormlite-api.png)](http://www.servicestack.net/files/ormlite-api.png)
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
247
248 Nearly all extension methods hang off the implementation agnostic `IDbCommand`.
249
250 `CreateTable<T>` and `DropTable<T>` create and drop tables based on a classes type definition (only public properties used).
251
252 For a one-time use of a connection, you can query straight of the `IDbFactory` with:
253
254 var customers = dbFactory.Exec(dbCmd => dbCmd.Where<Customer>(new { Age = 30 }));
255
256 The **Select** methods allow you to construct Sql using C# `string.Format()` syntax.
257 If you're SQL doesn't start with a **SELECT** statement, it is assumed a WHERE clause is being provided, e.g:
258
259 var tracks = dbCmd.Select<Track>("Artist = {0} AND Album = {1}", "Nirvana", "Heart Shaped Box");
260
261 The same results could also be fetched with:
262
263 var tracks = dbCmd.Select<Track>("select * from track WHERE Artist = {0} AND Album = {1}", "Nirvana", "Heart Shaped Box");
264
265 **Select** returns multiple records
266
267 List<Track> tracks = dbCmd.Select<Track>()
268
269 **Single** returns a single record
270
271 Track track = dbCmd.Single<Track>("RefId = {0}", refId)
272
273 **GetDictionary** returns a Dictionary made from the first to columns
274
275 Dictionary<int,string> trackIdNamesMap = dbCmd.GetDictionary<int, string>("select Id, Name from Track")
276
277 **GetLookup** returns an `Dictionary<K, List<V>>` made from the first to columns
278
279 var albumTrackNames = dbCmd.GetLookup<int, string>("select AlbumId, Name from Track")
280
281 **GetFirstColumn** returns a List of first column values
282
283 List<string> trackNames = dbCmd.GetFirstColumn<string>("select Name from Track")
284
285 **GetScalar** returns a single scalar value
286
287 var trackCount = dbCmd.GetScalar<int>("select count(*) from Track")
288
0e34841 @mythz README formatting
mythz authored
289 All **Insert**, **Update**, and **Delete** methods take multiple params, while `InsertAll`, `UpdateAll` and `DeleteAll` take IEnumerables.
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
290 **GetLastInsertId** returns the last inserted records auto incremented primary key.
291
0e34841 @mythz README formatting
mythz authored
292 `Save` and `SaveAll` will Insert if no record with **Id** exists, otherwise it Updates.
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
293 Both take multiple items, optimized to perform a single read to check for existing records and are executed within a sinlge transaction.
294
295 Methods containing the word **Each** return an IEnumerable<T> and are lazily loaded (i.e. non-buffered).
296
297 Selection methods containing the word **Query** or **Where** use parameterized SQL (other selection methods do not).
298 Anonymous types passed into **Where** are treated like an **AND** filter.
299
300 var track3 = dbCmd.Where<Track>(new { AlbumName = "Throwing Copper", TrackNo = 3 })
301
302 **Query** statements take in parameterized SQL using properties from the supplied anonymous type (if any)
303
0e34841 @mythz README formatting
mythz authored
304 var track3 = dbCmd.Query<Track>("select * from Track Where AlbumName = @album and TrackNo = @trackNo",
305 new { album = "Throwing Copper", trackNo = 3 })
9acfc11 @mythz Added documentation to OrmLite, incr to v3.06
mythz authored
306
307 GetById(s), QueryById(s), etc provide strong-typed convenience methods to fetch by a Table's **Id** primary key field.
308
309 var track = dbCmd.QueryById<Track>(1);
310
311
312 # Limitations
313
314 For simplicity, and to be able to have the same POCO class persisted in db4o, memcached, redis or on the filesystem (i.e. providers included in ServiceStack), each model must have an '`Id`' property which is its primary key.
315
316
ff8dd0f @mythz Update README.md
mythz authored
317 # More Examples
a469858 @mythz
mythz authored
318
319 In its simplest useage, OrmLite can persist any POCO type without any attributes required:
320
321 public class SimpleExample
322 {
323 public int Id { get; set; }
324 public string Name { get; set; }
325 }
326
327 //Set once before use (i.e. in a static constructor).
328 OrmLiteConfig.DialectProvider = new SqliteOrmLiteDialectProvider();
329
330 using (IDbConnection db = "/path/to/db.sqlite".OpenDbConnection())
331 using (IDbCommand dbConn = db.CreateCommand())
332 {
333 dbConn.CreateTable<SimpleExample>(true);
334 dbConn.Insert(new SimpleExample { Id=1, Name="Hello, World!"});
335 var rows = dbConn.Select<SimpleExample>();
336
337 Assert.That(rows, Has.Count(1));
338 Assert.That(rows[0].Id, Is.EqualTo(1));
339 }
340
341 To get a better idea of the features of OrmLite lets walk through a complete example using sample tables from the Northwind database.
342 _ (Full source code for this example is [available here](https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/tests/ServiceStack.OrmLite.Tests/ShippersExample.cs).) _
343
344 So with no other configuration using only the classes below:
345
346 [Alias("Shippers")]
347 public class Shipper
348 : IHasId<int>
349 {
350 [AutoIncrement]
351 [Alias("ShipperID")]
352 public int Id { get; set; }
353
354 [Required]
355 [Index(Unique = true)]
356 [StringLength(40)]
357 public string CompanyName { get; set; }
358
359 [StringLength(24)]
360 public string Phone { get; set; }
361
362 [References(typeof(ShipperType))]
363 public int ShipperTypeId { get; set; }
364 }
365
366 [Alias("ShipperTypes")]
367 public class ShipperType
368 : IHasId<int>
369 {
370 [AutoIncrement]
371 [Alias("ShipperTypeID")]
372 public int Id { get; set; }
373
374 [Required]
375 [Index(Unique = true)]
376 [StringLength(40)]
377 public string Name { get; set; }
378 }
379
380 public class SubsetOfShipper
381 {
382 public int ShipperId { get; set; }
383 public string CompanyName { get; set; }
384 }
385
386 public class ShipperTypeCount
387 {
388 public int ShipperTypeId { get; set; }
389 public int Total { get; set; }
390 }
391
392
393 ### Creating tables
394 Creating tables is a simple 1-liner:
395
396 using (IDbConnection dbConn = ":memory:".OpenDbConnection())
397 using (IDbCommand dbCmd = dbConn.CreateCommand())
398 {
399 const bool overwrite = false;
400 dbCmd.CreateTables(overwrite, typeof(Shipper), typeof(ShipperType));
401 }
402
403 /* In debug mode the line above prints:
404 DEBUG: CREATE TABLE "Shippers"
405 (
406 "ShipperID" INTEGER PRIMARY KEY AUTOINCREMENT,
407 "CompanyName" VARCHAR(40) NOT NULL,
408 "Phone" VARCHAR(24) NULL,
409 "ShipperTypeId" INTEGER NOT NULL,
410
411 CONSTRAINT "FK_Shippers_ShipperTypes" FOREIGN KEY ("ShipperTypeId") REFERENCES "ShipperTypes" ("ShipperID")
412 );
413 DEBUG: CREATE UNIQUE INDEX uidx_shippers_companyname ON "Shippers" ("CompanyName" ASC);
414 DEBUG: CREATE TABLE "ShipperTypes"
415 (
416 "ShipperTypeID" INTEGER PRIMARY KEY AUTOINCREMENT,
417 "Name" VARCHAR(40) NOT NULL
418 );
419 DEBUG: CREATE UNIQUE INDEX uidx_shippertypes_name ON "ShipperTypes" ("Name" ASC);
420 */
421
422
423 ### Transaction Support
424 As we have direct access to IDbCommand and friends - playing with transactions is easy:
425
426 int trainsTypeId, planesTypeId;
427 using (IDbTransaction dbTrans = dbCmd.BeginTransaction())
428 {
429 dbCmd.Insert(new ShipperType { Name = "Trains" });
430 trainsTypeId = (int) dbCmd.GetLastInsertId();
431
432 dbCmd.Insert(new ShipperType { Name = "Planes" });
433 planesTypeId = (int) dbCmd.GetLastInsertId();
434
435 dbTrans.Commit();
436 }
437 using (IDbTransaction dbTrans = dbCmd.BeginTransaction(IsolationLevel.ReadCommitted))
438 {
439 dbCmd.Insert(new ShipperType { Name = "Automobiles" });
440 Assert.That(dbCmd.Select<ShipperType>(), Has.Count(3));
441
442 dbTrans.Rollback();
443 }
444 Assert.That(dbCmd.Select<ShipperType>(), Has.Count(2));
445
446
447 ### CRUD Operations
448 No ORM is complete without the standard crud operations:
449
450 //Performing standard Insert's and Selects
451 dbCmd.Insert(new Shipper { CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsTypeId });
452 dbCmd.Insert(new Shipper { CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesTypeId });
453 dbCmd.Insert(new Shipper { CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesTypeId });
454
455 var trainsAreUs = dbCmd.First<Shipper>("ShipperTypeId = {0}", trainsTypeId);
456 Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
457 Assert.That(dbCmd.Select<Shipper>("CompanyName = {0} OR Phone = {1}", "Trains R Us", "555-UNICORNS"), Has.Count(2));
458 Assert.That(dbCmd.Select<Shipper>("ShipperTypeId = {0}", planesTypeId), Has.Count(2));
459
460 //Lets update a record
461 trainsAreUs.Phone = "666-TRAINS";
462 dbCmd.Update(trainsAreUs);
463 Assert.That(dbCmd.GetById<Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS"));
464
465 //Then make it disappear
466 dbCmd.Delete(trainsAreUs);
467 Assert.That(dbCmd.GetByIdOrDefault<Shipper>(trainsAreUs.Id), Is.Null);
468
469 //And bring it back again
470 dbCmd.Insert(trainsAreUs);
471
472
473 ### Performing custom queries
474 And with access to raw sql when you need it - the database is your oyster :)
475
476 //Select only a subset from the table
477 var partialColumns = dbCmd.Select<SubsetOfShipper>(typeof (Shipper), "ShipperTypeId = {0}", planesTypeId);
478 Assert.That(partialColumns, Has.Count(2));
479
480 //Select into another POCO class that matches the sql results
481 var rows = dbCmd.Select<ShipperTypeCount>(
482 "SELECT ShipperTypeId, COUNT(*) AS Total FROM Shippers GROUP BY ShipperTypeId ORDER BY COUNT(*)");
483
484 Assert.That(rows, Has.Count(2));
485 Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsTypeId));
486 Assert.That(rows[0].Total, Is.EqualTo(1));
487 Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesTypeId));
488 Assert.That(rows[1].Total, Is.EqualTo(2));
489
490
491 //And finally lets quickly clean up the mess we've made:
492 dbCmd.DeleteAll<Shipper>();
493 dbCmd.DeleteAll<ShipperType>();
494
495 Assert.That(dbCmd.Select<Shipper>(), Has.Count(0));
8fa296a @mythz added README.md
mythz authored
496 Assert.That(dbCmd.Select<ShipperType>(), Has.Count(0));
Something went wrong with that request. Please try again.