public void SqlServerInsertQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
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, false, true);
IQuery query = new SqlServerInsertQuery(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();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
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, false, true);
int idValue = 7;
var where = new WhereBuilder<Person>(command, p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob", false);
IQuery query = new UpdateQuery(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] = @P5"));
Assert.IsTrue(queryText.Contains("[ID] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.IsTrue(queryText.Contains("[Name] = @P8"));
Assert.AreEqual(command.Parameters["@P5"].Value, 1);
Assert.AreEqual(command.Parameters["@P6"].Value, 7);
Assert.AreEqual(command.Parameters["@P7"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P8"].Value, "Bob");
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
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>();
var where = new WhereBuilder<Person>(command, p => p.Name.Contains("John") && p.Age > 5, false);
IQuery query = new SelectQuery(columns, "dbo.People", where.ToString(), "", 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 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_ShouldAddFiveParameters_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(5)
.Return(0);
// Act
db.Update<Person>(person, "sql...");
// Assert
db.Command.Parameters.VerifyAllExpectations();
db.Command.VerifyAllExpectations();
}