public void SqlServerInsertQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, true);
IQuery query = new InsertQuery(new SqlServerDialect(), columns, command, "dbo.People");
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("INSERT INTO [dbo].[People]"));
Assert.IsFalse(queryText.Contains("@ID"), "Should not contain ID column since it is marked as AutoIncrement");
Assert.IsTrue(queryText.Contains("[Name]"), "Should contain the name column");
}
public void Find_WithNoRows_PassingInObject_ShouldReturnObject()
{
// Arrange
StubResultSet rs = new StubResultSet("ID", "Name", "Age", "IsHappy", "BirthDate");
// Act
var db = CreateDB_ForQuery(rs);
Person person = new Person { ID = 5 };
db.Find<Person>("sql...", person);
// Assert
Assert.IsNotNull(person);
Assert.AreEqual(5, person.ID);
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, true);
int idValue = 7;
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob";
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new UpdateQuery(new SqlServerDialect(), columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE [dbo].[People]"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P4"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[Name] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.AreEqual(command.Parameters["@P4"].Value, 1);
Assert.AreEqual(command.Parameters["@P5"].Value, 7);
Assert.AreEqual(command.Parameters["@P6"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P7"].Value, "Bob");
}
private Person LoadPerson(DbDataReader reader)
{
Person p = new Person();
p.ID = reader.GetValue<int>("ID");
p.Name = reader.GetValue<string>("Name");
p.Age = reader.GetValue<int>("Age");
return p;
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
var orderBy = MockRepository.GenerateStub<ISortQueryBuilder>();
orderBy.Expect(o => o.BuildQuery()).Return("");
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List<Person> list = new List<Person>();
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.Name.Contains("John") && p.Age > 5;
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new SelectQuery(new SqlServerDialect(), tables, where.ToString(), orderBy, false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}
public void DataReader_Test()
{
// Arrange
StubResultSet rs = new StubResultSet("ID", "Name", "Age", "IsHappy", "BirthDate");
rs.AddRow(1, "Jordan", 33, true, new DateTime(1977, 1, 22));
rs.AddRow(2, "Amyme", 31, false, new DateTime(1979, 10, 19));
rs.AddRow(3, "Guy", 29, false, new DateTime(1981, 10, 3));
// Act
var db = CreateDB_ForQuery(rs);
List<Person> people = new List<Person>();
db.Query<Person>()
.QueryText("sql...")
.DataReader(r =>
{
while (r.Read())
{
Person p = new Person();
p.ID = r.GetInt32(r.GetOrdinal("ID"));
p.Name = r.GetString(r.GetOrdinal("Name"));
p.Age = r.GetInt32(r.GetOrdinal("Age"));
p.IsHappy = r.GetBoolean(r.GetOrdinal("IsHappy"));
p.BirthDate = r.GetDateTime(r.GetOrdinal("BirthDate"));
people.Add(p);
}
});
// Assert
Assert.IsTrue(people.Count == 3);
Person jordan = people[0];
Assert.AreEqual(1, jordan.ID);
Assert.AreEqual("Jordan", jordan.Name);
Assert.AreEqual(33, jordan.Age);
Assert.AreEqual(true, jordan.IsHappy);
Assert.AreEqual(new DateTime(1977, 1, 22), jordan.BirthDate);
Person amyme = people[1];
Assert.AreEqual(2, amyme.ID);
Assert.AreEqual("Amyme", amyme.Name);
Assert.AreEqual(31, amyme.Age);
Assert.AreEqual(false, amyme.IsHappy);
Assert.AreEqual(new DateTime(1979, 10, 19), amyme.BirthDate);
}
public void Insert_ShouldAddFiveParameters_And_ExecuteScalar_AndSetReturnValue()
{
// Arrange
Person person = new Person();
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
var db = CreateDB_ForInsert();
db.Command
.Expect(c => c.ExecuteScalar())
.Return(55);
db.Command.Parameters
.Expect(p => p.Add(null))
.IgnoreArguments()
.Repeat.Times(4)
.Return(0);
// Act
db.Insert<Person>(person, "sql...");
// Assert
db.Command.Parameters.VerifyAllExpectations();
db.Command.VerifyAllExpectations();
Assert.AreEqual(55, person.ID);
}
public void Update_ShouldAddFourParameters_And_ExecuteNonQuery()
{
// Arrange
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
var db = CreateDB_ForUpdate();
db.Command.Parameters
.Expect(p => p.Add(null))
.IgnoreArguments()
.Repeat.Times(4)
.Return(0);
// Act
db.Update<Person>(person, "sql...");
// Assert
db.Command.Parameters.VerifyAllExpectations();
db.Command.VerifyAllExpectations();
}
