public void AppendCriteria_WithNoWhere_AddWhere()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob";
var builder = new SqlStatementBuilder(_connection, startSql);
const string appendSql = "this = that";
const string expectedSql = startSql + " WHERE " + appendSql;
//---------------Execute Test ----------------------
builder.AppendCriteria(appendSql);
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AppendCriteria_Complex()
{
//---------------Set up test pack-------------------
const string startSql = "select [FAKE WHERE CLAUSE] from bob WHERE that = 'FAKE WHERE CLAUSE'";
var builder = new SqlStatementBuilder(_connection, startSql);
const string appendSql = "this = that";
const string expectedSql = startSql + " AND " + appendSql;
//---------------Execute Test ----------------------
builder.AppendCriteria(appendSql);
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AppendOrderBy()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
const string appendSql = "this";
const string expectedSql = startSql + " ORDER BY " + appendSql;
//---------------Execute Test ----------------------
builder.AppendOrderBy(appendSql);
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void TestAddSelectFields()
{
//-------------Setup Test Pack ------------------
const string startSql = "select [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE] from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
var fields = new List<string> {"myField1", "myField2"};
const string expectedSql = "select [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE], [myField1], [myField2] from bob WHERE that = this";
//-------------Execute test ---------------------
builder.AddSelectFields(fields);
var actual = builder.GetStatement().Statement.ToString();
//-------------Test Result ----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AddJoin_Complex()
{
//---------------Set up test pack-------------------
const string startSql = "select [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE] from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
const string expectedSql = "select DISTINCT [FALSE FROM CLAUSE], [FALSE WHERE CLAUSE] from bob LEFT JOIN [bobby] ON bobs = bobbys WHERE that = this AND this = that";
//---------------Execute Test ----------------------
builder.AddJoin("left join", "bobby", "bobs = bobbys");
builder.AppendCriteria("this = that");
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AddJoin_WithWhere_AlsoAddWhere_AddAnotherJoin()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob WHERE that = this";
var builder = new SqlStatementBuilder(_connection, startSql);
const string expectedSql = "select DISTINCT * from (bob LEFT JOIN [bobby] ON bobs = bobbys) "+
"LEFT JOIN [bobbin] ON bobbys = bobbins WHERE that = this AND this = that";
//---------------Execute Test ----------------------
builder.AddJoin("left join", "bobby", "bobs = bobbys");
builder.AppendCriteria("this = that");
builder.AddJoin("left join", "bobbin", "bobbys = bobbins");
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AddJoin_WithNoWhere_InnerJoin()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob";
var builder = new SqlStatementBuilder(_connection, startSql);
const string expectedSql = "select * from bob INNER JOIN [bobby] ON bobs = bobbys";
//---------------Execute Test ----------------------
builder.AddJoin("inner join", "bobby", "bobs = bobbys");
var actual = builder.GetStatement().Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedSql, actual);
}
public void AddJoin_WithEmptyStatement()
{
//---------------Set up test pack-------------------
const string startSql = "";
var builder = new SqlStatementBuilder(_connection, startSql);
//---------------Execute Test ----------------------
try
{
builder.AddJoin("left join", "bobby", "bobs = bobbys");
Assert.Fail("Expected to throw an SqlStatementException");
}
//---------------Test Result -----------------------
catch (SqlStatementException ex)
{
StringAssert.Contains("Cannot add a join clause to a SQL statement that does not contain a from clause", ex.Message);
}
}