public void Setup()
{
//Create the test data.
DBObject db = new DBObject(Utility.connMy, "users", "external_id"); //Lie about the primary key so we can insert it.
DBRow dan = new DBRow();
dan["id"] = 2;
dan["first_name"] = "Dan";
dan["middle_initial"] = "A";
dan["last_name"] = "Rese";
dan["email"] = "*****@*****.**";
dan.Insert(db);
DBRow chris = new DBRow();
chris["id"] = 1313;
chris["first_name"] = "Chris";
chris["middle_initial"] = "A";
chris["last_name"] = "Richards";
chris["email"] = "*****@*****.**";
chris.Insert(db);
DBRow ross = new DBRow();
ross["id"] = 1315;
ross["first_name"] = "Ross";
ross["middle_initial"] = "A";
ross["last_name"] = "Weaver";
ross["email"] = "*****@*****.**";
ross.Insert(db);
DBRow rick = new DBRow();
rick["id"] = 1327;
rick["first_name"] = "Rick";
rick["middle_initial"] = "R";
rick["last_name"] = "Frazer";
rick["email"] = "*****@*****.**";
rick.Insert(db);
}
public void FindByTwoColumnsInRow()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
//Create a row with the propertys we want to find
DBRow where = new DBRow();
where["middle_initial"] = "A";
where["last_name"] = "Richards";
//Now find everyone with an A for a middle name.
db.Where(where);
//Should find 3 rows.
Assert.AreEqual(2, db.Rows.Count);
//Verify they are the three we expect
int found = 0;
foreach (DBRow row in db.Rows)
{
if (1313 == (int)row["id"])
{
Assert.AreEqual(row["first_name"], "Chris");
Assert.AreEqual(row["middle_initial"], "A");
Assert.AreEqual(row["last_name"], "Richards");
Assert.AreEqual(row["email"], "*****@*****.**");
found++;
}
else if (1315 == (int)row["id"])
{
Assert.AreEqual(row["first_name"], "Ross");
Assert.AreEqual(row["middle_initial"], "A");
Assert.AreEqual(row["last_name"], "Richards");
Assert.AreEqual(row["email"], "*****@*****.**");
found++;
}
}
Assert.AreEqual(2, found);
}
public void FindIndexWithColumnsReturnsTrueWithBadPrimaryKey()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
DBRow row = new DBRow();
//Set the columns we want to find
row["id"] = 0; //This bad primary key should be ignored.
row["first_name"] = "Bob"; //It will still work because we are not checking this column
row["last_name"] = "Richards";
//Check that it reports success in finding the index
Assert.IsTrue(row.FindIndex(db, new string[] { "last_name" }));
//Check that it really did get the index.
Assert.AreEqual(1313, row["id"]);
}
public void FindIndexTwoColumns()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
//Add just the data we want
DBRow row = new DBRow();
row["email"] = "*****@*****.**";
row["first_name"] = "Chris";
//Now find the id
row.FindIndex(db);
Assert.AreEqual(1313, row[db.PrimaryKey]);
}
public void FindIndexReturnsTrue()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
DBRow row = new DBRow();
//Set the columns we want to find
row["first_name"] = "Chris";
row["last_name"] = "Richards";
//Check that it reports success in finding the index
Assert.IsTrue(row.FindIndex(db));
//Check that it really did get the index.
Assert.AreEqual(1313, row["id"]);
}
public void FillFromIndexOverwritesColumns()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
//Add just the data we want
DBRow row = new DBRow();
row[db.PrimaryKey] = 1313;
row["email"] = "*****@*****.**"; //It should overwrite this value with the one from the database.
//Fill from the ID
row.FillFromIndex(db);
Assert.AreEqual("Chris", row["first_name"]);
Assert.AreEqual("Richards", row["last_name"]);
//Check that our email was replaced with the one in the database.
Assert.AreEqual("*****@*****.**", row["email"]);
}
public void FillFromIndexKeepsNonNullValues()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
//Add just the data we want
DBRow row = new DBRow();
row[db.PrimaryKey] = 1313;
row["email"] = "*****@*****.**";
//Fill from the ID
row.FillFromIndex(db, true);
Assert.AreEqual("Chris", row["first_name"]);
Assert.AreEqual("Richards", row["last_name"]);
//Check that it kept out value.
Assert.AreEqual("*****@*****.**", row["email"]);
}
public void FillFromIndex()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
//Add just the data we want
DBRow row = new DBRow();
row[db.PrimaryKey] = 1313;
//Fill from the ID
row.FillFromIndex(db);
Assert.AreEqual("*****@*****.**", row["email"]);
}
public void FailedFindIndexWithColumnsReturnsFalse()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
DBRow row = new DBRow();
//Set the columns we want to find
row["first_name"] = "Bob"; //It will still fail because we are checking this column
row["last_name"] = "Richards";
//Check that it reports failure in finding the index
Assert.IsFalse(row.FindIndex(db, new string[] { "first_name" }));
//Check that it really did not get the index.
Assert.IsNull(row["id"]);
}
public void FailedFindIndexReturnsFalse()
{
DBObject db = new DBObject(Utility.connMy, "users", "id");
DBRow row = new DBRow();
//Set the columns we want to find
row["first_name"] = "Bob";
row["last_name"] = "JoeJoe";
//Check that it reports failure in finding the index
Assert.IsFalse(row.FindIndex(db));
//Check that it really did not get the index.
Assert.IsNull(row["id"]);
}
public void InsertRecordShowInRow()
{
//Inserting a Record, the record should be added to the objects Rows property
//--
// These parrts are tested by InsertRecord()
//Verify our record doesn't already exist.
DBObject obj = new DBObject(Utility.connMy, "users", "id");
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(0, obj.Rows.Count);
//Create a new Row
DBRow ladygaga = new DBRow();
ladygaga["first_name"] = "Lady";
ladygaga["last_name"] = "Gaga";
ladygaga["email"] = "*****@*****.**";
//--
Assert.AreEqual(0, obj.Rows.Count);
//Insert the Row
ladygaga.Insert(obj);
//Test that it's in the DBObject
Assert.AreEqual(1, obj.Rows.Count);
//--
// These parrts are tested by InsertRecord()
//Try to get it now
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(1, obj.Rows.Count, "Checking that the record exists.");
//Now get rid of it.
obj.Rows[0].Delete(obj);
//Verify that it's gone
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(0, obj.Rows.Count);
//--
}
public void InsertRecord()
{
//Make sure the row doesn't exist already.
DBObject obj = new DBObject(Utility.connMy, "users", "id");
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(0, obj.Rows.Count);
//Create a new Row
DBRow ladygaga = new DBRow();
ladygaga["first_name"] = "Lady";
ladygaga["last_name"] = "Gaga";
ladygaga["email"] = "*****@*****.**";
//Insert the Row
ladygaga.Insert(obj);
//Try to get it now
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(1, obj.Rows.Count, "Checking that the record exists.");
//Now get rid of it.
obj.Rows[0].Delete(obj);
//Verify that it's gone
obj.Where("email=@0", "*****@*****.**");
Assert.AreEqual(0, obj.Rows.Count);
}
/// <summary>
/// Finds all the rows based on the non null columns in the row.
/// After this method, the Rows property will only contain Rows that match the where caluse.
/// </summary>
/// <param name="row">Example: row["active"] = true will return all records where active is true.</param>
public void Where(DBRow row)
{
//Build a where query from the non null columns.
List<Object> query_praramters = new List<object>();
System.Text.StringBuilder where = new System.Text.StringBuilder();
//Build the where clause
int index = 0;
foreach (string key in row.Keys)
{
if (null != row[key])
{
//key=@index
where.Append(key);
//Check the type, strings should use LIKE
if (row[key].GetType() == typeof(String)) {
where.Append(" LIKE @");
} else {
where.Append("=@");
}
where.Append(index);
where.Append(" AND ");
//Add the value
query_praramters.Add(row[key]);
index++;
}
}
//Remove the last AND
where.Remove(where.Length - 5, 5);
//Clear any rows
this.Rows.Clear();
//Now get all the new rows
this.FillFromSelect("SELECT * FROM " + this.TableName + " WHERE " + where.ToString(), query_praramters.ToArray());
}