public void GetRecordVersion()
{
var db = new PostulateDb();
db.DeleteOneWhere <TableB>("[Description]='Yadda Yadda'", null);
int[] orgIds;
using (var cn = db.GetConnection())
{
cn.Open();
orgIds = cn.Query <int>("SELECT [Id] FROM [Organization]").ToArray();
}
var itemB = new TableB()
{
Description = "Yadda Yadda", OrganizationId = orgIds[0]
};
db.Save(itemB);
itemB.OrganizationId = orgIds[1];
db.Save(itemB);
int version;
db.Find <TableB>(itemB.Id, out version);
Assert.IsTrue(version == 2);
}
private static void InitTestRecords(out PostulateDb db, out TableB[] tableBItems)
{
db = new PostulateDb();
db.GetConnection().Execute("DELETE [TableB] WHERE [Description] IN ('whatever', 'this thing', 'circumscribed', 'least confident')");
tableBItems = new TableB[]
{
new TableB()
{
Description = "whatever", OrganizationId = 1, CreatedBy = "adamo"
},
new TableB()
{
Description = "this thing", OrganizationId = 1, CreatedBy = "adamo"
},
new TableB()
{
Description = "circumscribed", OrganizationId = 1, CreatedBy = "adamo"
},
new TableB()
{
Description = "least confident", OrganizationId = 1, CreatedBy = "adamo"
}
};
}
public void CopyOrg()
{
var db = new PostulateDb();
var srcOrg = FirstRandomOrg(db);
var newOrg = db.Copy <Organization>(srcOrg.Id, new { name = $"Org Copy {DateTime.Now.ToString()}", description = "copied record" });
Assert.IsTrue(newOrg.Description.Equals("copied record"));
}
private int MaxOrgId()
{
int result = 0;
using (IDbConnection cn = new PostulateDb().GetConnection())
{
result = cn.QuerySingleOrDefault <int?>("SELECT MAX([Id]) FROM [Organization]") ?? 0;
}
return(result);
}
public void DropTestTables()
{
var db = new PostulateDb();
using (IDbConnection cn = db.GetConnection())
{
cn.Open();
DropTablesIfExists(cn, "TableA", "TableB", "TableC");
}
}
public void TrackDeletion()
{
PostulateDb db = new PostulateDb();
TableB b = new TableB()
{
OrganizationId = 1, Description = "whatever"
};
db.Save(b);
db.DeleteOne(b);
}
public void ValidateMissingPKValue()
{
var a = new TableA();
a.FirstName = "whatever";
using (var cn = new PostulateDb().GetConnection())
{
cn.Open();
var errs = a.GetValidationErrors(cn, SaveAction.Insert);
Assert.IsTrue(errs.Any(item => item.Equals("Primary key field LastName requires a value.")));
}
}
public async Task CreateOrgAsync()
{
var org = new Organization()
{
Name = "Async Org", BillingRate = 1
};
PostulateDb db = new PostulateDb();
await db.SaveAsync(org);
Assert.IsTrue(org.Id != 0);
db.DeleteOne(org);
}
public void CreateDeleteOrgFromRecordNoConnection()
{
Organization org = new Organization();
org.Name = DefaultOrgName();
org.BillingRate = 10;
PostulateDb db = new PostulateDb();
db.Save(org);
db.DeleteOne(org);
}
public void CopyOrgOmitColumns()
{
var db = new PostulateDb();
Organization srcOrg = FirstRandomOrg(db);
Random rnd = new Random();
var newOrg = db.Copy <Organization>(srcOrg.Id,
new { createdBy = "/system", dateCreated = DateTime.Now, name = $"Org Copy {rnd.Next(1000)}", description = "copied record" },
"ModifiedBy", "DateModified");
Assert.IsTrue(newOrg.Description.Equals("copied record"));
}
public void AttemptRestore()
{
PostulateDb db = new PostulateDb();
TableB b = new TableB()
{
OrganizationId = 1, Description = "whatever1"
};
db.Save(b);
db.DeleteOne(b);
var restoredId = db.RestoreOne <TableB>(b.Id);
Assert.IsTrue(restoredId != 0);
db.DeleteOne <TableB>(restoredId);
}
public void CreateDeleteOrg()
{
Organization org = new Organization();
org.Name = DefaultOrgName();
org.BillingRate = 10;
PostulateDb db = new PostulateDb();
db.TraceCallback = (cn, qt) => { QueryUtil.SaveTrace(cn, qt, db); };
using (IDbConnection cn = db.GetConnection())
{
cn.Open();
db.Save(cn, org);
db.DeleteOne(cn, org);
}
}
public void TrackTableBChanges()
{
var db = new PostulateDb();
int[] orgIds = null;
using (var cn = db.GetConnection())
{
cn.Open();
if (!db.Syntax.TableExists(cn, typeof(TableB)))
{
CreateTable ct = new CreateTable(db.Syntax, db.Syntax.GetTableInfoFromType(typeof(TableB)));
foreach (var cmd in ct.SqlCommands(cn))
{
cn.Execute(cmd);
}
}
orgIds = cn.Query <int>("SELECT TOP (2) [Id] FROM [Organization]").ToArray();
}
db.DeleteOneWhere <TableB>("[Description]='Whatever'", null);
string oldName = db.Find <Organization>(orgIds[0]).Name;
string newName = db.Find <Organization>(orgIds[1]).Name;
db.TraceCallback = (cn, trace) => { Debug.WriteLine(trace.Sql); };
TableB b = new TableB()
{
OrganizationId = orgIds[0], Description = "Whatever"
};
db.Save(b);
b.OrganizationId = orgIds[1];
db.Save(b);
var history = db.QueryChangeHistory <TableB>(b.Id);
var changes = history.First().Properties.ToDictionary(item => item.PropertyName);
Assert.IsTrue(changes["OrganizationId"].OldValue.Equals(oldName) && changes["OrganizationId"].NewValue.Equals(newName));
}
public void Update()
{
Organization org = new Organization();
org.Name = DefaultOrgName();
org.BillingRate = 10;
var db = new PostulateDb();
db.Save(org);
int orgId = org.Id;
org.BillingRate = 11;
db.Update(org, r => r.BillingRate);
org = db.Find <Organization>(orgId);
db.DeleteOne <Organization>(orgId);
Assert.IsTrue(org.BillingRate == 11);
}
public void FindAndUpdateOrg()
{
Organization org = new Organization();
org.Name = DefaultOrgName();
org.BillingRate = 10;
PostulateDb db = new PostulateDb();
using (IDbConnection cn = db.GetConnection())
{
cn.Open();
db.Save(cn, org);
org = db.Find <Organization>(cn, org.Id);
org.Name = $"Another Sample {DateTime.Now}";
org.BillingRate = 11;
db.Save(cn, org);
}
}
private static Organization FirstRandomOrg(PostulateDb db)
{
Organization srcOrg = null;
var qry = new Query <Organization>("SELECT TOP (1) * FROM [dbo].[Organization]");
while (srcOrg == null)
{
srcOrg = qry.ExecuteSingle(db);
if (srcOrg != null)
{
break;
}
srcOrg = new Organization()
{
Name = "Sample", BillingRate = 10
};
db.Save(srcOrg);
}
return(srcOrg);
}
public void TrackTableAChanges()
{
var db = new PostulateDb();
using (var cn = db.GetConnection())
{
cn.Open();
if (!db.Syntax.TableExists(cn, typeof(TableA)))
{
CreateTable ct = new CreateTable(db.Syntax, db.Syntax.GetTableInfoFromType(typeof(TableA)));
foreach (var cmd in ct.SqlCommands(cn))
{
cn.Execute(cmd);
}
}
}
TableA a = new TableA()
{
FirstName = "Julio", LastName = "Arragato"
};
db.DeleteOneWhere <TableA>("[FirstName]=@FirstName AND [LastName]=@LastName", a);
db.DeleteOneWhere <TableA>("[FirstName]=@FirstName AND [LastName]=@LastName", new { FirstName = "Geoffrey", LastName = "Arragato" });
db.Save(a);
a.FirstName = "Geoffrey";
db.Save(a);
var history = db.QueryChangeHistory <TableA>(a.Id);
var changes = history.First().Properties.ToDictionary(item => item.PropertyName);
Assert.IsTrue(changes["FirstName"].OldValue.Equals("Julio") && changes["FirstName"].NewValue.Equals("Geoffrey"));
}
public void TableDInsertStatement_ShouldIncludeIdentity()
{
var tableD = new TableD()
{
FieldOne = "whatever", Id = 0
};
var db = new PostulateDb();
using (var cn = db.GetConnection())
{
if (!db.Syntax.TableExists(cn, typeof(TableD)))
{
var ct = new CreateTable(db.Syntax, new TableInfo("TableD", "dbo", typeof(TableD)));
foreach (var cmd in ct.SqlCommands(cn))
{
cn.Execute(cmd);
}
}
db.TraceCallback = (cn1, qt) => { Assert.IsTrue(qt.Sql.Equals("hello")); };
db.Save(tableD);
}
}
public void DeleteWhere()
{
var db = new PostulateDb();
db.DeleteAllWhere <Organization>("[Name] LIKE @name + '%'", new { name = "Org Copy" });
}
public void ExistsWhere()
{
var db = new PostulateDb();
bool result = db.ExistsWhere <Organization>("[Id]>@id", new { id = 0 });
}
