Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
181 lines (140 sloc) 6.434 kB
using System;
using System.Collections.Generic;
using System.Data;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using ServiceStack.Text;
namespace ServiceStack.OrmLite.Tests
{
[TestFixture]
public class ShippersExample : OrmLiteTestBase
{
[Alias("Shippers")]
public class Shipper
: IHasId<int>
{
[AutoIncrement]
[Alias("ShipperID")]
public int Id { get; set; }
[Required]
[Index(Unique = true)]
[StringLength(40)]
public string CompanyName { get; set; }
[StringLength(24)]
public string Phone { get; set; }
[References(typeof(ShipperType))]
public int ShipperTypeId { get; set; }
}
[Alias("ShipperTypes")]
public class ShipperType
: IHasId<int>
{
[AutoIncrement]
[Alias("ShipperTypeID")]
public int Id { get; set; }
[Required]
[Index(Unique = true)]
[StringLength(40)]
public string Name { get; set; }
}
public class SubsetOfShipper
{
public int ShipperID { get; set; }
public string CompanyName { get; set; }
}
public class ShipperTypeCount
{
public int ShipperTypeId { get; set; }
public int Total { get; set; }
}
[Test]
public void Shippers_UseCase()
{
using (IDbConnection db = OpenDbConnection())
{
db.DropTable<Shipper>();
db.DropTable<ShipperType>();
db.CreateTable<ShipperType>();
db.CreateTable<Shipper>();
var trainsType = new ShipperType { Name = "Trains" };
var planesType = new ShipperType { Name = "Planes" };
//Playing with transactions
using (IDbTransaction dbTrans = db.OpenTransaction())
{
db.Save(trainsType);
db.Save(planesType);
dbTrans.Commit();
}
using (IDbTransaction dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
db.Insert(new ShipperType { Name = "Automobiles" });
Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(3));
}
Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(2));
//Performing standard Insert's and Selects
db.Insert(new Shipper { CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsType.Id });
db.Insert(new Shipper { CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesType.Id });
db.Insert(new Shipper { CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesType.Id });
var trainsAreUs = db.Single<Shipper>(q => q.ShipperTypeId == trainsType.Id);
Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
trainsAreUs = db.SingleFmt<Shipper>("ShipperTypeId".SqlColumn() + " = {0}", trainsType.Id);
Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
Assert.That(db.Select<Shipper>(q => q.CompanyName == "Trains R Us" || q.Phone == "555-UNICORNS"), Has.Count.EqualTo(2));
Assert.That(db.SelectFmt<Shipper>("CompanyName".SqlColumn() + " = {0} OR Phone = {1}", "Trains R Us", "555-UNICORNS"), Has.Count.EqualTo(2));
Assert.That(db.Select<Shipper>(q => q.ShipperTypeId == planesType.Id), Has.Count.EqualTo(2));
Assert.That(db.SelectFmt<Shipper>("ShipperTypeId".SqlColumn() + " = {0}", planesType.Id), Has.Count.EqualTo(2));
//Lets update a record
trainsAreUs.Phone = "666-TRAINS";
db.Update(trainsAreUs);
Assert.That(db.SingleById<Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS"));
//Then make it dissappear
db.Delete(trainsAreUs);
Assert.That(db.SingleById<Shipper>(trainsAreUs.Id), Is.Null);
//And bring it back again
db.Insert(trainsAreUs);
//Performing custom queries
//Select only a subset from the table
var partialColumns = db.Select<SubsetOfShipper>(db.From<Shipper>().Where(q => q.ShipperTypeId == planesType.Id));
Assert.That(partialColumns, Has.Count.EqualTo(2));
partialColumns = db.SelectFmt<SubsetOfShipper>(typeof(Shipper), "ShipperTypeId".SqlColumn() + " = {0}", planesType.Id);
Assert.That(partialColumns, Has.Count.EqualTo(2));
//Select into another POCO class that matches sql
var rows = db.SqlList<ShipperTypeCount>(
"SELECT {0}, COUNT(*) AS Total FROM Shippers GROUP BY {0} ORDER BY Total".Fmt("ShipperTypeId".SqlColumn()));
Assert.That(rows, Has.Count.EqualTo(2));
Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsType.Id));
Assert.That(rows[0].Total, Is.EqualTo(1));
Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesType.Id));
Assert.That(rows[1].Total, Is.EqualTo(2));
TestCustomGroupBy(db, trainsType, planesType);
//And finally lets quickly clean up the mess we've made:
db.DeleteAll<Shipper>();
db.DeleteAll<ShipperType>();
Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(0));
Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(0));
}
}
private static void TestCustomGroupBy(IDbConnection db, ShipperType trainsType, ShipperType planesType)
{
var rows = db.SqlList<ShipperTypeCount>(
db.From<Shipper>()
.GroupBy(x => x.ShipperTypeId)
.OrderBy("Total")
.Select(x => new {x.ShipperTypeId, Total = Sql.As(Sql.Count("*"), "Total")}));
Assert.That(rows, Has.Count.EqualTo(2));
Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsType.Id));
Assert.That(rows[0].Total, Is.EqualTo(1));
Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesType.Id));
Assert.That(rows[1].Total, Is.EqualTo(2));
var qGroup = db.From<Shipper>()
.GroupBy(x => x.ShipperTypeId)
.OrderBy("2")
.Select(x => new {x.ShipperTypeId, Total = Sql.Count("*")});
var rowsGroup = db.Dictionary<int, int>(qGroup);
Assert.That(rowsGroup, Has.Count.EqualTo(2));
Assert.That(rowsGroup[trainsType.Id], Is.EqualTo(1));
Assert.That(rowsGroup[planesType.Id], Is.EqualTo(2));
}
}
}
Jump to Line
Something went wrong with that request. Please try again.