public void QueryCachingTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
IDataReader mockReader = mocks.NewMock<IDataReader>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(mockCommand));
Expect.Once.On(mockCommand)
.SetProperty("CommandText").To("SELECT DISTINCT id FROM employees;");
Expect.Once.On(mockCommand)
.Method("ExecuteReader")
.Will(Return.Value(mockReader));
Expect.Exactly(1).On(mockReader)
.Method("Read")
.Will(Return.Value(true));
Expect.Once.On(mockReader)
.Get["employee_id"]
.Will(Return.Value("0"));
Expect.Once.On(mockReader)
.Get["employee_name"]
.Will(Return.Value("Alice"));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(false));
Expect.Once.On(mockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("Dispose");
SQLBuilder builder = new MySQLBuilder();
// NB: The select clause we're setting here isn't sufficient to instantiate the objects,
// which have two fields. This doesn't matter here since we are using mock objects
// anyway, and in practice it will be up to the author of the SQL statements to get details
// like this right.
builder.AddSelectClause("id");
builder.AddFromClause("employees");
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, builder, new Dictionary<string, object>());
Query<Employee> myQuery = new Query<Employee>(provider);
List<Employee> resultList = myQuery.ToList();
Assert.AreEqual(1, resultList.Count);
Assert.AreEqual(0, resultList[0].ID);
Assert.AreEqual("Alice", resultList[0].Name);
// This shouldn't cause any further DB queries, since we've already evaluated this
// expression once
resultList = myQuery.ToList();
mocks.VerifyAllExpectationsHaveBeenMet();
}
public void OrderByCastTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
IDataReader mockReader = mocks.NewMock<IDataReader>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(mockCommand));
Expect.Once.On(mockCommand)
.SetProperty("CommandText").To("SELECT DISTINCT id AS employee_id, name AS employee_name FROM employees ORDER BY employee_name;");
Expect.Once.On(mockCommand)
.Method("ExecuteReader")
.Will(Return.Value(mockReader));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(true));
Expect.Once.On(mockReader)
.Get["employee_id"]
.Will(Return.Value("0"));
Expect.Once.On(mockReader)
.Get["employee_name"]
.Will(Return.Value("Alice"));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(false));
Expect.Once.On(mockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("Dispose");
SQLBuilder sqlBuilder = new MySQLBuilder();
sqlBuilder.AddSelectClause("id AS employee_id");
sqlBuilder.AddSelectClause("name AS employee_name");
sqlBuilder.AddFromClause("employees");
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());
Query<Employee> query = new Query<Employee>(provider);
IQueryable<IPerson> people = query.Cast<IPerson>();
people = from person in people
orderby person.Name
select person;
List<IPerson> peopleList = people.ToList();
Assert.AreEqual(1, peopleList.Count);
Assert.IsInstanceOf<IPerson>(peopleList[0]);
}
public void CountExpressionTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
IDataReader mockReader = mocks.NewMock<IDataReader>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(mockCommand));
Expect.Once.On(mockCommand)
.SetProperty("CommandText").To("SELECT COUNT(*) AS numrows FROM employees WHERE name LIKE '%smith' ;");
Expect.Once.On(mockCommand)
.Method("ExecuteReader")
.Will(Return.Value(mockReader));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(true));
// Note that we only call the reader once, rather than iterating through until
// it returns false. This works fine when we know we have only one result,
// which will be the case until we implement GroupBy
/*Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(false));*/
Expect.Once.On(mockReader)
.Get["numrows"]
.Will(Return.Value(16));
Expect.Once.On(mockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("Dispose");
SQLBuilder builder = new MySQLBuilder();
builder.AddSelectClause("id");
builder.AddSelectClause("name");
builder.AddFromClause("employees");
builder.AddWhereClause("name LIKE '%smith'", ExpressionType.And);
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, builder, new Dictionary<string, object>());
Query<Employee> myQuery = new Query<Employee>(provider);
Assert.AreEqual(16, myQuery.Count());
mocks.VerifyAllExpectationsHaveBeenMet();
}
public void SkipTakeTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
IDataReader mockReader = mocks.NewMock<IDataReader>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(mockCommand));
Expect.Once.On(mockCommand)
.SetProperty("CommandText").To("SELECT DISTINCT id AS employee_id, name AS employee_name FROM employees LIMIT 10, 15;");
Expect.Once.On(mockCommand)
.Method("ExecuteReader")
.Will(Return.Value(mockReader));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(true));
Expect.Once.On(mockReader)
.Get["employee_id"]
.Will(Return.Value("0"));
Expect.Once.On(mockReader)
.Get["employee_name"]
.Will(Return.Value("Alice"));
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(false));
Expect.Once.On(mockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("Dispose");
SQLBuilder sqlBuilder = new MySQLBuilder();
sqlBuilder.AddSelectClause("id AS employee_id");
sqlBuilder.AddSelectClause("name AS employee_name");
sqlBuilder.AddFromClause("employees");
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());
Query<Employee> query = new Query<Employee>(provider);
List<Employee> employeeList = query.Skip(10).Take(15).ToList();
Assert.AreEqual(1, employeeList.Count); // Though we asked for 15 results, there was only one returned
Assert.AreEqual(0, employeeList[0].ID);
Assert.AreEqual("Alice", employeeList[0].Name);
}
public void SetParametersTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand mockCommand = mocks.NewMock<IDbCommand>();
IDataReader mockReader = mocks.NewMock<IDataReader>();
IDbDataParameter mockParameter = mocks.NewMock<IDbDataParameter>();
IDataParameterCollection mockParameterCollection = mocks.NewMock<IDataParameterCollection>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(mockCommand));
Expect.Once.On(mockCommand)
.SetProperty("CommandText").To("SELECT DISTINCT id, name FROM employees WHERE name=@name ;");
Expect.Once.On(mockCommand)
.Method("CreateParameter")
.Will(Return.Value(mockParameter));
Expect.Once.On(mockParameter)
.SetProperty("ParameterName").To("@name");
Expect.Once.On(mockParameter)
.SetProperty("Value").To("smith");
Expect.Once.On(mockCommand)
.GetProperty("Parameters")
.Will(Return.Value(mockParameterCollection));
Expect.Once.On(mockParameterCollection)
.Method("Add").With(mockParameter)
.Will(Return.Value(0));
Expect.Once.On(mockCommand)
.Method("ExecuteReader")
.Will(Return.Value(mockReader));
// We don't return any values, since we don't need to test what we do with
// the returned values. The important thing to test is that the parameter
// methods above get called.
Expect.Once.On(mockReader)
.Method("Read")
.Will(Return.Value(false));
Expect.Once.On(mockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("Dispose");
SQLBuilder sqlBuilder = new MySQLBuilder();
sqlBuilder.AddSelectClause("id");
sqlBuilder.AddSelectClause("name");
sqlBuilder.AddFromClause("employees");
sqlBuilder.AddWhereClause("name=@name", ExpressionType.And);
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters["@name"] = "smith";
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, parameters);
Query<Employee> query = new Query<Employee>(provider);
List<Employee> employees = query.ToList();
// The important testing is done in the mock code. Provided the right
// AddParameter stuff is called, we don't care what happens with the results
mocks.VerifyAllExpectationsHaveBeenMet();
}
public void QueryRepeatabilityTest()
{
Mockery mocks = new Mockery();
IDbConnection mockConnection = mocks.NewMock<IDbConnection>();
IDbCommand firstMockCommand = mocks.NewMock<IDbCommand>();
IDbCommand secondMockCommand = mocks.NewMock<IDbCommand>();
IDataReader firstMockReader = mocks.NewMock<IDataReader>();
IDataReader secondMockReader = mocks.NewMock<IDataReader>();
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(firstMockCommand));
Expect.Once.On(firstMockCommand)
.SetProperty("CommandText").To("SELECT COUNT(*) AS numrows FROM employees WHERE id=42 ;");
Expect.Once.On(firstMockCommand)
.Method("ExecuteReader")
.Will(Return.Value(firstMockReader));
Expect.Once.On(firstMockReader)
.Method("Read")
.Will(Return.Value(true));
Expect.Once.On(firstMockReader)
.Get["numrows"]
.Will(Return.Value(1));
Expect.Once.On(firstMockReader)
.Method("Dispose");
Expect.Once.On(mockConnection)
.Method("CreateCommand")
.Will(Return.Value(secondMockCommand));
Expect.Once.On(secondMockCommand)
.SetProperty("CommandText").To("SELECT DISTINCT id AS employee_id, name AS employee_name FROM employees WHERE id=42 ;");
Expect.Once.On(secondMockCommand)
.Method("ExecuteReader")
.Will(Return.Value(secondMockReader));
Expect.Once.On(secondMockReader)
.Method("Read")
.Will(Return.Value(true));
Expect.Once.On(secondMockReader)
.Get["employee_id"]
.Will(Return.Value(1));
Expect.Once.On(secondMockReader)
.Get["employee_name"]
.Will(Return.Value("Bob"));
Expect.Once.On(secondMockReader)
.Method("Read")
.Will(Return.Value(false));
Expect.Once.On(secondMockReader)
.Method("Dispose");
// NB: We're returning the same connection object twice, so we'll dispose it twice. This
// isn't really correct, but since these are mock objects nobody will know that they're
// the same object
Expect.Exactly(2).On(mockConnection)
.Method("Dispose");
SQLBuilder sqlBuilder = new MySQLBuilder();
sqlBuilder.AddSelectClause("id AS employee_id");
sqlBuilder.AddSelectClause("name AS employee_name");
sqlBuilder.AddFromClause("employees");
sqlBuilder.AddWhereClause("id=42", ExpressionType.And);
LazyDBQueryProvider<Employee> provider = new LazyDBQueryProvider<Employee>(() => mockConnection, sqlBuilder, new Dictionary<string, object>());
Query<Employee> query = new Query<Employee>(provider);
int numEmployees = query.Count();
Assert.AreEqual(1, numEmployees);
List<Employee> employees = query.ToList();
}