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();
        }