public void NoRowsFoundInJoinReturnsEmptyList()
{
ICollection<ExamplePersonDetailed> people;
using (var conn = Connection())
{
conn.Open();
var cmd = new ZocUtil.Database.SqlCommandHelper()
{
CommandText = @"
Select * From FetchListExamplePerson
Select * From FetchListExampleDepartment
Select * From FetchListExampleProject WHERE ExampleProjectId < 0
",
CommandType = CommandType.Text,
Connection = conn
};
people = cmd.FetchRelatedLists<ExamplePersonDetailed, ExampleDepartmentDetailed, ExampleProject>();
}
Assert.AreEqual(8,
people.Count,
"FetchRelatedLists should return one list containing the results of the first query.");
foreach (var examplePersonDetailed in people)
{
Assert.AreNotEqual(null, examplePersonDetailed.Projects);
Assert.AreEqual(new List<ExampleProject>(), examplePersonDetailed.Projects);
}
}
public void FetchMultipleLists_SelectAllFromDynamicSql()
{
ICollection<FetchListExample.ExamplePerson> people;
ICollection<FetchListExample.ExampleDepartment> departments;
using (var conn = Connection())
{
conn.Open();
var cmd = new zd.SqlCommandHelper()
{
CommandText = "Select * From FetchListExamplePerson Select * From FetchListExampleDepartment",
CommandType = CommandType.Text,
Connection = conn
};
var results = cmd.FetchMultipleLists<FetchListExample.ExamplePerson, FetchListExample.ExampleDepartment>();
people = results.Item1;
departments = results.Item2;
}
Assert.AreEqual(8, people.Count, "FetchMultipleLists should return two lists. The first list should be for the results of the first query.");
Assert.AreEqual(14, departments.Count, "FetchMultipleLists should return two lists. The second list should be for the results of the second query.");
Assert.AreEqual(people.Count, people.Select(x => x.PersonId).Distinct().Count(), "FetchMultipleLists should populate the Id property from the primary key column. Since the PK is unique, each object should have its own unique id.");
Assert.AreEqual(departments.Count, departments.Select(x => x.Id).Distinct().Count(), "FetchMultipleLists should populate the Id property from the primary key column. Since the PK is unique, each object should have its own unique id.");
Assert.IsFalse(people.Any(x => x.Name == null), "FetchMultipleLists should populate the Name property from the Name column. This column is not nullable, so each object should have a not null value.");
Assert.IsFalse(departments.Any(x => x.DepartmentName == null), "FetchMultipleLists should populate the DepartmentName property from the DepartmentName column. This column is not nullable, so each object should have a not null value.");
}
public void BatchTempInsert_InsertObjectCollectionAndSelectItBack()
{
ICollection<ExampleCalendarEvent> events = new ExampleCalendarEvent[]
{
new ExampleCalendarEvent()
{
ExampleCalendarEventId = 1,
Title = "ZocTalk",
TimeUtc = new DateTime(2015, 1, 9, 22, 0, 0),
Address = "568 Broadway, New York, NY 10012",
Priority = EventPriority.Medium
},
new ExampleCalendarEvent()
{
ExampleCalendarEventId = 2,
Title = "Meet with financial data vendor",
TimeUtc = new DateTime(2015, 2, 12, 18, 0, 0),
Address = "55 Water St, New York, NY 10041",
Priority = EventPriority.Low,
CalendarEventSeriesId = 123
},
new ExampleCalendarEvent()
{
ExampleCalendarEventId = 3,
Title = "Birthday Party",
TimeUtc = new DateTime(2015, 12, 11, 23, 0, 0),
Address = "111 8th Ave, New York, NY 10011",
Description = "It's going to be a blast!",
Priority = EventPriority.High
}
};
ICollection<ExampleCalendarEvent> eventsFromDb;
using (var conn = Connection())
{
conn.Open();
var bulkCopy = new zd.SqlBulkCopyHelper(conn);
const string tempTableName = "#events";
bulkCopy.BulkTempInsert(events, tempTableName);
const string sqlSelect = "Select * From " + tempTableName;
var cmd = new zd.SqlCommandHelper(sqlSelect, conn);
eventsFromDb = cmd.FetchList<ExampleCalendarEvent>();
}
CollectionAssert.AreEquivalent(events, eventsFromDb, "BatchTempInsert should create a temp table and populate it with entities from the given list.");
}
public void FetchRelatedLists_SelectAllFromDynamicSql()
{
ICollection<ExamplePersonDetailed> people;
using (var conn = Connection())
{
conn.Open();
var cmd = new zd.SqlCommandHelper()
{
CommandText = @"
Select * From FetchListExamplePerson
Select * From FetchListExampleDepartment
Select * From FetchListExampleProject
",
CommandType = CommandType.Text,
Connection = conn
};
people = cmd.FetchRelatedLists<ExamplePersonDetailed, ExampleDepartmentDetailed, ExampleProject>();
}
Assert.AreEqual(8, people.Count, "FetchRelatedLists should return one list containing the results of the first query.");
Assert.IsFalse(people.Any(person => person.Departments.Any(d => person.Department != d.Id)), "FetchRelatedLists should always add list constituents who have the same identifier as their parent.");
Assert.IsFalse(people.Any(person => person.Projects.Any(project => person.PersonId != project.PersonId)), "FetchRelatedLists should always add list constituents who have the same identifier as their parent.");
}
public void BatchTempInsert_InsertPrimitiveCollectionAndSelectItBack()
{
int[] ids = new int[]
{
1,
1,
2,
3,
5,
8,
13
};
ICollection<int> idsFromDb;
using (var conn = Connection())
{
conn.Open();
var bulkCopy = new zd.SqlBulkCopyHelper(conn);
const string tempTableName = "#ids";
bulkCopy.BulkTempInsert(ids, tempTableName);
const string sqlSelect = "Select * From " + tempTableName;
var cmd = new zd.SqlCommandHelper(sqlSelect, conn);
idsFromDb = cmd.FetchList<int>();
}
CollectionAssert.AreEquivalent(ids, idsFromDb, "BatchTempInsert should create a temp table and populate it with the integers from the given list.");
}
