public void CascadesOnDelete()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<TypeWithOnDeleteCascade>(true);
db.Save(new ReferencedType { Id = 1 });
db.Save(new TypeWithOnDeleteCascade { RefId = 1 });
Assert.AreEqual(1, db.Select<ReferencedType>().Count);
Assert.AreEqual(1, db.Select<TypeWithOnDeleteCascade>().Count);
db.Delete<ReferencedType>(r => r.Id == 1);
Assert.AreEqual(0, db.Select<ReferencedType>().Count);
Assert.AreEqual(0, db.Select<TypeWithOnDeleteCascade>().Count);
}
}
public void Can_Delete_from_ModelWithFieldsOfDifferentTypes_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
var rowIds = new List<int>(new[] {1, 2, 3});
rowIds.ForEach(x => db.Insert(ModelWithFieldsOfDifferentTypes.Create(x)));
var rows = db.Select<ModelWithFieldsOfDifferentTypes>();
var row2 = rows.First(x => x.Id == 2);
db.Delete(row2);
rows = db.GetByIds<ModelWithFieldsOfDifferentTypes>(rowIds);
var dbRowIds = rows.ConvertAll(x => x.Id);
Assert.That(dbRowIds, Is.EquivalentTo(new[] {1, 3}));
}
}
public void Can_Delete_from_ModelWithFieldsOfDifferentTypes_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithFieldsOfDifferentTypes>(true);
var rowIds = new List <int>(new[] { 1, 2, 3 });
rowIds.ForEach(x => db.Insert(ModelWithFieldsOfDifferentTypes.Create(x)));
var rows = db.Select <ModelWithFieldsOfDifferentTypes>();
var row2 = rows.First(x => x.Id == 2);
db.Delete(row2);
rows = db.GetByIds <ModelWithFieldsOfDifferentTypes>(rowIds);
var dbRowIds = rows.ConvertAll(x => x.Id);
Assert.That(dbRowIds, Is.EquivalentTo(new[] { 1, 3 }));
}
}
public void CascadesOnDelete()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <TypeWithOnDeleteCascade>(true);
db.Save(new ReferencedType {
Id = 1
});
db.Save(new TypeWithOnDeleteCascade {
RefId = 1
});
Assert.AreEqual(1, db.Select <ReferencedType>().Count);
Assert.AreEqual(1, db.Select <TypeWithOnDeleteCascade>().Count);
db.Delete <ReferencedType>(r => r.Id == 1);
Assert.AreEqual(0, db.Select <ReferencedType>().Count);
Assert.AreEqual(0, db.Select <TypeWithOnDeleteCascade>().Count);
}
}
public void Shippers_UseCase()
{
using (var db = new OrmLiteConnectionFactory("User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;", FirebirdDialect.Provider).Open())
{
const bool overwrite = false;
db.DropTable <Shipper>();
db.DropTable <ShipperType>();
db.CreateTables(overwrite, typeof(ShipperType), typeof(Shipper)); // ShipperType must be created first!
int trainsTypeId, planesTypeId;
//Playing with transactions
using (IDbTransaction dbTrans = db.BeginTransaction())
{
db.Insert(new ShipperType {
Name = "Trains"
});
trainsTypeId = (int)db.LastInsertId();
db.Insert(new ShipperType {
Name = "Planes"
});
planesTypeId = (int)db.LastInsertId();
dbTrans.Commit();
}
using (IDbTransaction dbTrans = db.BeginTransaction(IsolationLevel.ReadCommitted))
{
db.Insert(new ShipperType {
Name = "Automobiles"
});
Assert.That(db.Select <ShipperType>(), Has.Count.EqualTo(3));
dbTrans.Rollback();
}
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 = trainsTypeId
});
db.Insert(new Shipper {
CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesTypeId
});
db.Insert(new Shipper {
CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesTypeId
});
var trainsAreUs = db.Single <Shipper>("\"Type\" = @id", new { id = trainsTypeId });
Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
Assert.That(db.Select <Shipper>("CompanyName = @company OR Phone = @phone",
new { company = "Trains R Us", phone = "555-UNICORNS" }), Has.Count.EqualTo(2));
Assert.That(db.Select <Shipper>("\"Type\" = @id", new { id = planesTypeId }), 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>(typeof(Shipper), "\"Type\" = @id", new { id = planesTypeId });
Assert.That(partialColumns, Has.Count.EqualTo(2));
//Select into another POCO class that matches sql
var rows = db.Select <ShipperTypeCount>(
"SELECT \"Type\" as ShipperTypeId, COUNT(*) AS Total FROM ShippersT GROUP BY \"Type\" ORDER BY COUNT(*)");
Assert.That(rows, Has.Count.EqualTo(2));
Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsTypeId));
Assert.That(rows[0].Total, Is.EqualTo(1));
Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesTypeId));
Assert.That(rows[1].Total, Is.EqualTo(2));
//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));
}
}