public void ItCommitsTheChangesToTheServer()
{
var insertFirstColumnValue = RandomTool.CreateAnInt32();
new Insert(SomeTable)
[
FirstColumn.WillBe(insertFirstColumnValue),
SecondColumn.WillBe(SecondValue)
]
.ConnectTo(Connection)
.Run();
var whereTheFirstColumIsEqualToTheRandomValue = new Where()
[
FirstColumn.IsEqualTo(insertFirstColumnValue)
];
var newFirstColumnValue = RandomTool.CreateAnInt32();
RetrieveCountOfRowsWithTheValue(newFirstColumnValue).Should().Be(0);
new Update(SomeTable)
[
FirstColumn.WillBe(newFirstColumnValue)
]
.Where(whereTheFirstColumIsEqualToTheRandomValue)
.ConnectTo(Connection)
.Run();
RetrieveCountOfRowsWithTheValue(newFirstColumnValue).Should().Be(1);
}
public static IList<string> RetrieveTheWhereItemUniqueNames(Where whereToCheck)
{
return
whereToCheck.GetType().GetProperty("WhereItems", BindingFlagsToSeeAll)
.GetValue(whereToCheck, BindingFlagsToSeeAll, null, null, null)
.As<IList<WhereItem>>()
.Select(x => x.UniqueKey)
.ToList();
}
public void TheConnectionStringisChangedButTheWhereContainerIsTheSame()
{
const string whereContainerName = "_whereContainer";
var whereClause = new Where()[FirstColumn.IsEqualTo(1)];
var deleteStatement = new Delete(SomeTable).Where(whereClause).ConnectTo("A");
var record = deleteStatement.ConnectTo("B");
RetrieveValueFromObject(deleteStatement, whereContainerName).Should().Be(RetrieveValueFromObject(record, whereContainerName));
}
public void TheDeleteCanCreateAValidWithAnAlsoThatContainsTheSameName()
{
var firstColumnIsEqualToFirstValue =
new Where()
[
FirstColumn.IsEqualTo(FirstValue),
Also.And(FirstColumn.IsEqualTo(FirstValue))
];
var deleteStatement =
new Delete(SomeTable)
.Where(firstColumnIsEqualToFirstValue);
deleteStatement.CreateSql().Should().NotBe("DELETE FROM " + SomeTable + " WHERE " + FirstColumn + " = @where_" + FirstColumn + " AND (" + FirstColumn + " = @where_" + FirstColumn + ")");
}
public void ItShouldAllowASingleEquals()
{
const string finalText = "WHERE {0} = @{0}{1}";
var whereClause =
new Where()
[
FirstColumn.IsEqualTo(1)
];
var whereItems = WhereDeconstruction.RetrieveTheWhereItemUniqueNames(whereClause).First();
whereClause
.CreateSql()
.Should()
.Be(string.Format(finalText, FirstColumn, whereItems));
}
public void ItShouldAllowAnOr()
{
const string finalText = "WHERE {0} = @{0}{2} OR ({1} = @{1}{3})";
var whereClause =
new Where()
[
FirstColumn.IsEqualTo(1),
Also.Or(SecondColumn.IsEqualTo(1))
];
var whereItems = WhereDeconstruction.RetrieveTheWhereItemUniqueNames(whereClause);
whereClause
.CreateSql()
.Should()
.Be(string.Format(finalText, FirstColumn, SecondColumn, whereItems[0], whereItems[1]));
}
public void TheItemIsDeletedFromTheDatabaseWithALessSimpelWhereClause()
{
var neededInformation = SetUpTheFakeData();
RetrieveCountOfRowsWithTheValue(neededInformation.Item1).Should().Be(2);
var theFirstColumnIsEqualToVakueAndTheSecondColumnIsEqualToNeededSecondValue =
new Where()
[
FirstColumn.IsEqualTo(neededInformation.Item1),
Also.And(SecondColumn.IsEqualTo(neededInformation.Item2))
];
new Delete(SomeTable)
.Where(theFirstColumnIsEqualToVakueAndTheSecondColumnIsEqualToNeededSecondValue)
.ConnectTo(Connection)
.Run();
RetrieveCountOfRowsWithTheValue(neededInformation.Item1).Should().Be(1);
}
public void TheDeleteCanCreateAValidStatementWithConditions()
{
var firstColumnIsEqualToFirstValue =
new Where()
[
FirstColumn.IsEqualTo(FirstValue)
];
var deleteStatement =
new Delete(SomeTable)
.Where(firstColumnIsEqualToFirstValue);
var whereItem =
firstColumnIsEqualToFirstValue.GetType().GetProperty("WhereItems", BindingFlagsToSeeAll)
.GetValue(firstColumnIsEqualToFirstValue, BindingFlagsToSeeAll, null, null, null)
.As<IList<WhereItem>>()
.First();
deleteStatement.CreateSql().Should().Be("DELETE FROM " + SomeTable + " WHERE " + FirstColumn + " = @" + whereItem.Name + whereItem.UniqueKey);
}
public void ItShouldAllowCompoundExpressions()
{
const string finalText = "WHERE {0} = @{0}{3} AND ({1} = @{1}{4}) OR ({2} = @{2}{5})";
ThirdColumn = "ThirdColumn";
var whereClause =
new Where()
[
FirstColumn.IsEqualTo(1),
Also.And(SecondColumn.IsEqualTo(1)),
Also.Or(ThirdColumn.IsEqualTo(1))
];
var whereItems = WhereDeconstruction.RetrieveTheWhereItemUniqueNames(whereClause);
whereClause
.CreateSql()
.Should()
.Be(string.Format(finalText, FirstColumn, SecondColumn, ThirdColumn, whereItems[0], whereItems[1], whereItems[2]));
}
public void ItShouldCreateAValidParameterList()
{
var parameters = new[] { new SqlParameter("@" + FirstColumn, FirstValue), new SqlParameter("@" + SecondColumn, SecondValue) };
var whereClause =
new Where()
[
FirstColumn.IsEqualTo(FirstValue),
Also.And(SecondColumn.IsEqualTo(SecondValue))
];
var whereItems = WhereDeconstruction.RetrieveTheWhereItemUniqueNames(whereClause);
whereClause
.CreateParameters().Should().Match(
(SqlParameter[] x)
=> x[0].Value.ToString() == parameters[0].Value.ToString() && x[0].ParameterName == parameters[0].ParameterName + whereItems[0]
&& x[1].Value.ToString() == parameters[1].Value.ToString() && x[1].ParameterName == parameters[1].ParameterName + whereItems[1]);
}
public void TheItemIsDeletedFromTheDatabaseWithASimpleWhereClause()
{
var value = RandomTool.CreateAnInt32();
InsertRow(value);
RetrieveCountOfRowsWithTheValue(value).Should().Be(1);
var firstColumnIsEqualToFirstValue =
new Where()
[
FirstColumn.IsEqualTo(value)
];
new Delete(SomeTable)
.ConnectTo(Connection)
.Where(firstColumnIsEqualToFirstValue)
.Run();
RetrieveCountOfRowsWithTheValue(FirstValue).Should().Be(0);
}
public void TheWhereContainerisChangedButTheConnectionStringIsTheSame()
{
var firstColumnIsEqualToFirstValue =
new Where()
[
FirstColumn.IsEqualTo(FirstValue)
];
var deleteStatement = new Delete(SomeTable).Where(firstColumnIsEqualToFirstValue).ConnectTo("a");
var record = deleteStatement.Where(new Where()[FirstColumn.IsEqualTo(1)]);
RetrieveValueFromObject(deleteStatement, ConnectionKeyword).Should().Be(RetrieveValueFromObject(record, ConnectionKeyword));
}
public void TheWhereContainerisChangedButANewDeleteIsCreated()
{
var firstColumnIsEqualToFirstValue =
new Where()
[
FirstColumn.IsEqualTo(FirstValue)
];
var deleteStatement = new Delete(SomeTable).Where(firstColumnIsEqualToFirstValue).ConnectTo("a");
var record = deleteStatement.Where(new Where()[FirstColumn.IsEqualTo(1)]);
deleteStatement.Should().NotBe(record);
}
public Delete Where(Where where)
{
return new Delete(_tableName) {_connection = _connection, _whereContainer = where};
}
public void ItCreatesACorrectQueryWithAWhereClause()
{
const string query = BasicQuery + " WHERE {1} = @{1}{3}";
var firstColumnIsEqualToFirstValue = new Where()
[
FirstColumn.IsEqualTo(FirstValue)
];
var whereItems = WhereDeconstruction.RetrieveTheWhereItemUniqueNames(firstColumnIsEqualToFirstValue).First();
var finalSql =
new Update(SomeTable)
[
FirstColumn.WillBe(FirstValue),
SecondColumn.WillBe(SecondValue)
]
.Where(firstColumnIsEqualToFirstValue)
.CreateSql();
finalSql.Should().Be(string.Format(query, SomeTable, FirstColumn, SecondColumn, whereItems));
}
public Update Where(Where where)
{
return new Update(_tableName) { _connection = _connection, Columns = _columns, WhereContainer = where};
}
