public void TestFindLessThanOrEqualWithInt32()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.foo.Users.Find(database.foo.Users.Id <= 1);
Assert.AreEqual("select " + usersColumns + " from [foo].[Users] where [foo].[Users].[Id] <= @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
public void TestFindEqualWithInt32()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.Find(database.Users.Id == 1);
Assert.AreEqual("select [users].* from [users] where [users].[id] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
public void TestFindNotEqualWithInt32()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.foo.Users.Find(database.foo.Users.Id != 1);
Assert.AreEqual("select [foo].[Users].* from [foo].[Users] where [foo].[Users].[Id] != @p1".ToLowerInvariant().ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
public void TestFindLessThanWithInt32()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.Find(database.Users.Id < 1);
Assert.AreEqual("select [dbo].[Users].* from [dbo].[Users] where [dbo].[Users].[Id] < @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
public void TestFindByWithDateRange()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.FindAllByJoinDate("2011-01-01".to("2011-01-31")).ToList();
Assert.AreEqual("select [dbo].[users].[id],[dbo].[users].[name],[dbo].[users].[password],[dbo].[users].[age],[dbo].[users].[joindate] from [dbo].[users] where [dbo].[users].[joindate] between @p1_start and @p1_end".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(new DateTime(2011,1,1), mockDatabase.Parameters[0]);
Assert.AreEqual(new DateTime(2011,1,31), mockDatabase.Parameters[1]);
}
public void TestFindByWithDateRange()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.FindAllByJoinDate("01/01/2011".to("31/01/2011")).ToList();
Assert.AreEqual("select [dbo].[users].* from [dbo].[users] where [dbo].[users].[joindate] between @p1 and @p2".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(new DateTime(2011,1,1), mockDatabase.Parameters[0]);
Assert.AreEqual(new DateTime(2011,1,31), mockDatabase.Parameters[1]);
}
static dynamic CreateDatabase(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
mockSchemaProvider.SetProcedures(new[] { "dbo", "ProcedureWithParameters" },
new[] { "dbo", "ProcedureWithoutParameters"});
mockSchemaProvider.SetParameters(new[] { "dbo", "ProcedureWithParameters", "@One" },
new[] { "dbo", "ProcedureWithParameters", "@Two" });
return new Database(new AdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider)));
}
public void TestFindByWithIntRange()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.FindAllById(1.to(10)).ToList();
Assert.AreEqual("select [dbo].[users].* from [dbo].[users] where [dbo].[users].[id] between @p1 and @p2".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
Assert.AreEqual(10, mockDatabase.Parameters[1]);
}
public void CallingMethodOnDatabase_Should_CallProcedure()
{
var mockDatabase = new MockDatabase();
var db = CreateDatabase(mockDatabase);
db.ProcedureWithoutParameters();
Assert.IsNotNull(mockDatabase.Sql);
Assert.AreEqual("[ProcedureWithoutParameters]".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(0, mockDatabase.Parameters.Count());
}
public void CallingMethodOnObjectInDatabase_Should_CallProcedure()
{
var mockDatabase = new MockDatabase();
var db = CreateDatabase(mockDatabase);
db.foo.ProcedureInAnotherSchema();
Assert.IsNotNull(mockDatabase.Sql);
Assert.AreEqual("[foo].[ProcedureInAnotherSchema]".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(0, mockDatabase.Parameters.Count());
}
public void TestFindByWithIntArray()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.FindAllById(new[] { 1, 2, 3 }).ToList();
Assert.AreEqual("select [dbo].[users].* from [dbo].[users] where [dbo].[users].[id] in (@p1,@p2,@p3)".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
Assert.AreEqual(2, mockDatabase.Parameters[1]);
Assert.AreEqual(3, mockDatabase.Parameters[2]);
}
public void TestQueryWithNotEqualIntArray()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.Users.Query().Where(database.Users.Id != new[] { 1, 2, 3 }).ToList();
Assert.AreEqual("select [dbo].[users].[id],[dbo].[users].[name],[dbo].[users].[password],[dbo].[users].[age],[dbo].[users].[joindate] from [dbo].[users] where [dbo].[users].[id] not in (@p1_0,@p1_1,@p1_2)".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
Assert.AreEqual(2, mockDatabase.Parameters[1]);
Assert.AreEqual(3, mockDatabase.Parameters[2]);
}
public void TestFindByDynamicSingleColumn()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
using (var transaction = database.BeginTransaction())
{
transaction.Users.FindByName("Foo");
}
Assert.AreEqual("select [dbo].[Users].* from [dbo].[Users] where [dbo].[Users].[name] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual("Foo", mockDatabase.Parameters[0]);
}
static Database CreateDatabase(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
mockSchemaProvider.SetTables(new[] { "foo", "Users", "BASE TABLE" });
mockSchemaProvider.SetColumns(new[] { "foo", "Users", "Id" },
new[] { "foo", "Users", "Name" },
new[] { "foo", "Users", "Password" },
new[] { "foo", "Users", "Age" });
mockSchemaProvider.SetPrimaryKeys(new object[] { "foo", "Users", "Id", 0 });
return new Database(new AdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider)));
}
Database CreateDatabase(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
SetSchema(mockSchemaProvider);
var adapter = MockHelper.CreateMockAdoAdapter(
new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider));
MockHelper.UseMockAdapter(adapter);
return Database.Open();
}
public void TestFindEqualWithInt32()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
using (var transaction = database.BeginTransaction())
{
transaction.Users.Find(database.Users.Id == 1);
}
Assert.AreEqual("select [dbo].[users].* from [dbo].[users] where [dbo].[users].[id] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
static Database CreateDatabase(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
mockSchemaProvider.SetTables(new[] {"dbo", "Customer", "BASE TABLE"},
new[] {"dbo", "Orders", "BASE TABLE"});
mockSchemaProvider.SetColumns(new[] {"dbo", "Customer", "CustomerId"},
new[] { "dbo", "Orders", "OrderId" },
new[] { "dbo", "Orders", "CustomerId" },
new[] {"dbo", "Orders", "OrderDate"});
mockSchemaProvider.SetPrimaryKeys(new object[] {"dbo", "Customer", "CustomerId", 0});
mockSchemaProvider.SetForeignKeys(new object[] {"FK_Orders_Customer", "dbo", "Orders", "CustomerId", "dbo", "Customer", "CustomerId", 0});
return new Database(new AdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider)));
}
public void CallingMethodOnDatabase_WithPositionalParameters_Should_CallProcedure()
{
var mockDatabase = new MockDatabase();
var db = CreateDatabase(mockDatabase);
db.ProcedureWithParameters(1, 2);
Assert.AreEqual(1, mockDatabase.CommandTexts.Count);
Assert.AreEqual("[ProcedureWithParameters]".ToLowerInvariant(), mockDatabase.CommandTexts[0].ToLowerInvariant());
Assert.AreEqual(2, mockDatabase.CommandParameters[0].Count);
Assert.IsTrue(mockDatabase.CommandParameters[0].ContainsKey("@One"));
Assert.AreEqual(1, mockDatabase.CommandParameters[0]["@One"]);
Assert.IsTrue(mockDatabase.CommandParameters[0].ContainsKey("@Two"));
Assert.AreEqual(2, mockDatabase.CommandParameters[0]["@Two"]);
}
Database CreateDatabaseWithShoutyNames(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
mockSchemaProvider.SetTables(new[] { "dbo", "CUSTOMER", "BASE TABLE" },
new[] { "dbo", "ORDER", "BASE TABLE" });
mockSchemaProvider.SetColumns(new[] { "dbo", "CUSTOMER", "CUSTOMER_ID" },
new[] { "dbo", "ORDER", "ORDER_ID" },
new[] { "dbo", "ORDER", "CUSTOMER_ID" },
new[] { "dbo", "ORDER", "ORDER_DATE" });
mockSchemaProvider.SetPrimaryKeys(new object[] { "dbo", "CUSTOMER", "CUSTOMER_ID", 0 });
mockSchemaProvider.SetForeignKeys(new object[] { "FK_ORDER_CUSTOMER", "dbo", "ORDER", "CUSTOMER_ID", "dbo", "CUSTOMER", "CUSTOMER_ID", 0 });
return new Database(new AdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider)));
}
public void IndexerMethodWorksWithShoutyFromSingular()
{
// Arrange
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabaseWithShoutyNames(mockDatabase);
string expectedSql =
"select * from [dbo].[CUSTOMER]".ToLowerInvariant();
// Act
database["Customer"].All();
// Assert
Assert.AreEqual(expectedSql, mockDatabase.Sql.ToLowerInvariant());
}
public void TestInsertWithNamedArguments()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
using (var transaction = database.BeginTransaction())
{
transaction.Users.Insert(Name: "Steve", Age: 50);
transaction.Commit();
}
Assert.AreEqual("insert into [dbo].[Users] ([Name],[Age]) values (@p0,@p1)".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual("Steve", mockDatabase.Parameters[0]);
Assert.AreEqual(50, mockDatabase.Parameters[1]);
Assert.IsTrue(MockDbTransaction.CommitCalled);
}
public void TestUpdateWithNamedArguments()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
using (var transaction = database.BeginTransaction())
{
transaction.Users.UpdateById(Id: 1, Name: "Steve", Age: 50);
transaction.Commit();
}
Assert.AreEqual("update [dbo].[Users] set [Name] = @p1, [Age] = @p2 where [dbo].[Users].[Id] = @p3".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual("Steve", mockDatabase.Parameters[0]);
Assert.AreEqual(50, mockDatabase.Parameters[1]);
Assert.AreEqual(1, mockDatabase.Parameters[2]);
Assert.IsTrue(MockDbTransaction.CommitCalled);
}
static Database CreateDatabase(MockDatabase mockDatabase)
{
var mockSchemaProvider = new MockSchemaProvider();
mockSchemaProvider.SetTables(new[] { "dbo", "Users", "BASE TABLE" });
mockSchemaProvider.SetColumns(new object[] { "dbo", "Users", "Id", true },
new[] { "dbo", "Users", "Name" },
new[] { "dbo", "Users", "Password" },
new[] { "dbo", "Users", "Age" });
mockSchemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 });
var adapter =
MockHelper.CreateMockAdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase),
mockSchemaProvider));
MockHelper.UseMockAdapter(adapter);
return Database.Open();
// return new Database(new AdoAdapter(new MockConnectionProvider(new MockDbConnection(mockDatabase), mockSchemaProvider)));
}
public void NaturalJoinCreatesCorrectCommand()
{
// Arrange
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
var orderDate = new DateTime(2010, 1, 1);
const string expectedSql =
"select [dbo].[Customer].* from [dbo].[Customer] join [dbo].[Orders] on ([dbo].[Customer].[CustomerId] = [dbo].[Orders].[CustomerId]) where [dbo].[Orders].[OrderDate] = @p1";
// Act
database.Customer.Find(database.Customer.Orders.OrderDate == orderDate);
var actualSql = Regex.Replace(mockDatabase.Sql, @"\s+", " ").ToLowerInvariant();
// Assert
Assert.AreEqual(expectedSql.ToLowerInvariant(), actualSql);
Assert.AreEqual(orderDate, mockDatabase.Parameters[0]);
}
public void NaturalJoinWithShoutyCaseCreatesCorrectCommand()
{
// Arrange
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabaseWithShoutyNames(mockDatabase);
var orderDate = new DateTime(2010, 1, 1);
string expectedSql = "select [dbo].[CUSTOMER].* from [dbo].[CUSTOMER] join [dbo].[ORDER] on ([dbo].[CUSTOMER].[CUSTOMER_ID] = [dbo].[ORDER].[CUSTOMER_ID])"
+ " where [dbo].[ORDER].[ORDER_DATE] = @p1";
// Act
database.Customer.Find(database.Customers.Orders.OrderDate == orderDate);
// Assert
Assert.AreEqual(expectedSql.ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(orderDate, mockDatabase.Parameters[0]);
}
public void NaturalJoinCreatesCorrectCommand()
{
// Arrange
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabaseWithSingularNames(mockDatabase);
var orderDate = new DateTime(2010, 1, 1);
string expectedSql =
"select [Customer].* from [Customer] join [Orders] on ([Customer].[CustomerId] = [Orders].[CustomerId]) where [Orders].[OrderDate] = @p1".ToLowerInvariant();
// Act
database.Customer.Find(database.Customers.Orders.OrderDate == orderDate);
// Assert
Assert.AreEqual(expectedSql, mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(orderDate, mockDatabase.Parameters[0]);
}
public void TestFindByDynamicSingleColumn()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.foo.Users.FindByName("Foo");
Assert.AreEqual("select " + usersColumns + " from [foo].[Users] where [foo].[Users].[name] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual("Foo", mockDatabase.Parameters[0]);
}
public MockDbConnection(MockDatabase mockDatabase)
{
_mockDatabase = mockDatabase;
}
public void TestInsertOnTable()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
dynamic person = new ExpandoObject();
person.Name = "Phil";
person.Age = 42;
database.foo.Users.Insert(person);
Assert.AreEqual("insert into [foo].[Users] ([Name],[Age]) values (@p0,@p1)".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual("Phil", mockDatabase.Parameters[0]);
Assert.AreEqual(42, mockDatabase.Parameters[1]);
}
public void TestFindWithDotInSchemaName()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.foobar.Test.Find(database.foobar.Test.Id == 1);
Assert.AreEqual("select [foo.bar].[Test].[Id], [foo.bar].[Test].[Value] from [foo.bar].[Test] where [foo.bar].[Test].[id] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}
public void TestDeleteBy()
{
var mockDatabase = new MockDatabase();
dynamic database = CreateDatabase(mockDatabase);
database.foo.Users.DeleteById(1);
Assert.AreEqual("delete from [foo].[Users] where [foo].[Users].[Id] = @p1".ToLowerInvariant(), mockDatabase.Sql.ToLowerInvariant());
Assert.AreEqual(1, mockDatabase.Parameters[0]);
}