/// <summary>
/// Gets the requested object out of the database.
/// </summary>
/// <param name="rep"></param>
/// <param name="key">The key to </param>
/// <param name="flds">List of fields to get out of the database. Leave empty to retrieve all. Names are case sensitive.</param>
/// <returns></returns>
public static async Task <IEnumerable <T> > GetAsync <T>(this IBaRepository rep, object key, params string[] flds) where T : BaTableObject, new()
{
var obj = new T();
var selCmd = new SelectCmdBuilder(obj.TableName);
// Add specified fields to the select command.
foreach (var fldName in flds)
{
var fld = obj.Fields[fldName];
if (fld != null)
{
selCmd.Fields.Add(fld.Name);
}
}
var propBag = key.ToPropertyBag();
foreach (var prop in propBag)
{
selCmd.Criteria.Add($"{prop.Key} = @{prop.Key}");
selCmd.Parameters.AddWithValue(prop.Key, prop.Value);
}
var cmd = selCmd.Build();
return(await rep.DB.GetObjectsAsync <T>(cmd).ConfigureAwait(false));
}
public void SimpleSelectTest()
{
var bldr = new SelectCmdBuilder("Person p");
var cmd = bldr.Build();
AreEqual("SELECT *\n\tFROM Person p\n", cmd.CommandText);
}
public void SingleFieldTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Fields.Add("p.Name");
var cmd = bldr.Build();
AreEqual("SELECT p.Name\n\tFROM Person p\n", cmd.CommandText);
}
public void MultipleFieldTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Fields.Add("p.Name");
bldr.Fields.Add("p.PersonTypeID");
var cmd = bldr.Build();
var expected =
@"SELECT p.Name, p.PersonTypeID
FROM Person p
";
AreEqual(expected, cmd.CommandText);
}
public void SingleOrderByTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.OrderBy.Add("p.Name");
var cmd = bldr.Build();
var expected =
@"SELECT *
FROM Person p
ORDER BY p.Name
";
AreEqual(expected, cmd.CommandText);
}
public void SingleCriteriaTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Criteria.Add("p.Name = 'Jill'");
var cmd = bldr.Build();
var expected =
@"SELECT *
FROM Person p
WHERE p.Name = 'Jill'
";
AreEqual(expected, cmd.CommandText);
}
public void SingleJoinTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Joins.Add("JOIN Address a ON (p.AddressID = a.AddressID)");
bldr.Fields.Add("*");
var cmd = bldr.Build();
var expected =
@"SELECT *
FROM Person p
JOIN Address a ON (p.AddressID = a.AddressID)
";
AreEqual(expected, cmd.CommandText);
}
public void SingleParameterTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Criteria.Add("p.Name = @Name");
var param = bldr.Parameters.AddWithValue("Name", "Jill");
var cmd = bldr.Build();
var expected =
@"SELECT *
FROM Person p
WHERE p.Name = @Name
";
AreEqual(expected, cmd.CommandText);
Assert.AreEqual(1, cmd.Parameters.Count);
Assert.AreSame(param, cmd.Parameters[0]);
}
public void ComplexSelectTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Fields.Add("p.ID");
bldr.Fields.Add("p.Name");
bldr.Fields.Add("pt.PersonTypeID");
bldr.Fields.Add("pt.PersonTypeName");
bldr.Fields.Add("a.AddressID");
bldr.Fields.Add("a.City");
bldr.Joins.Add("JOIN Address a ON (p.AddressID = a.AddressID)");
bldr.Joins.Add("JOIN PersonType pt ON (p.PersonTypeID = pt.PersonTypeID)");
bldr.Criteria.Add("p.Name = @Name");
bldr.Criteria.Add("p.PersonTypeID = @PersonTypeID");
var param = bldr.Parameters.AddValues(new { Name = "Jill", PersonTypeID = 1 });
bldr.OrderBy.Add("p.Name");
bldr.OrderBy.Add("a.City");
var cmd = bldr.Build();
var expected =
@"SELECT p.ID,
p.Name,
pt.PersonTypeID,
pt.PersonTypeName,
a.AddressID,
a.City
FROM Person p
JOIN Address a ON (p.AddressID = a.AddressID)
JOIN PersonType pt ON (p.PersonTypeID = pt.PersonTypeID)
WHERE p.Name = @Name
AND p.PersonTypeID = @PersonTypeID
ORDER BY p.Name, a.City
";
AreEqual(expected, cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
}
public void MultipleParameterTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.Criteria.Add("p.Name = @Name");
bldr.Criteria.Add("p.PersonTypeID = @PersonTypeID");
var param = bldr.Parameters.AddValues(new { Name = "Jill", PersonTypeID = 1 });
var cmd = bldr.Build();
var expected =
@"SELECT *
FROM Person p
WHERE p.Name = @Name
AND p.PersonTypeID = @PersonTypeID
";
AreEqual(expected, cmd.CommandText);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreEqual("Name", cmd.Parameters[0].ParameterName);
Assert.AreEqual("Jill", cmd.Parameters[0].Value);
Assert.AreEqual("PersonTypeID", cmd.Parameters[1].ParameterName);
Assert.AreEqual(1, cmd.Parameters[1].Value);
}
public void PagedTest()
{
var bldr = new SelectCmdBuilder("Person p");
bldr.OrderBy.Add("p.Name");
var cmd = bldr.Build(0, 10);
var expected =
@"WITH qry AS
(
SELECT ROW_NUMBER() OVER(ORDER BY p.Name) AS RowNbr,
COUNT(*) OVER() AS TotalRows,
*
FROM Person p
)
SELECT *
FROM qry
WHERE RowNbr BETWEEN 0 AND 10
ORDER BY RowNbr
";
AreEqual(expected, cmd.CommandText);
}