public void ShouldThrowExceptionIfNoSetterSpecified()
{
IQuery query;
ICommandBuilder builder;
query = new Update(TestDB.PersonnTable);
builder = new SqlCommandBuilder();
Assert.ThrowsException <InvalidOperationException>(() => builder.BuildCommand(query));
}
public void ShouldBuildOrderedDESCSelect()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName).From(TestDB.PersonnTable).OrderBy(NetORMLib.OrderModes.DESC, PersonnTable.FirstName);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName] FROM [Personn] ORDER BY [Personn].[FirstName] DESC", command.CommandText);
}
public void ShouldBuildExplicitDelete()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Delete().From(TestDB.PersonnTable);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("DELETE FROM [Personn]", command.CommandText);
}
public void ShouldBuildExplicitSelectFirst()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName).Top(10).From(TestDB.PersonnTable);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT TOP(10) [Personn].[FirstName] FROM [Personn]", command.CommandText);
}
public void ShouldBuildExplicitSelectIdentity()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new SelectIdentity();
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT @@IDENTITY", command.CommandText);
}
public void ShouldBuildCreateTableWithPrimaryKeyAndIdentity()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateTable(TestDB.PersonnTable, PersonnTable.PersonnID, PersonnTable.FirstName, PersonnTable.LastName, PersonnTable.Job);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("CREATE TABLE [Personn] ([PersonnID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY, [FirstName] nvarchar(MAX) NOT NULL, [LastName] nvarchar(MAX) NOT NULL, [Job] nvarchar(MAX) NULL)", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildIsNotNullFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName, PersonnTable.LastName).From(TestDB.PersonnTable).Where(PersonnTable.FirstName.IsNotNull());
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Personn].[LastName] FROM [Personn] WHERE [Personn].[FirstName] IS NOT NULL", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateTableWithoutPrimaryKey()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateTable(TestDB.JobTable, JobTable.Description, JobTable.Company);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("CREATE TABLE [Job] ([Description] nvarchar(MAX) NOT NULL, [Company] nvarchar(MAX) NULL)", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateTableWithoutIdentity()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateTable(TestDB.JobTypeTable, JobTypeTable.JobTypeID, JobTypeTable.Description);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("CREATE TABLE [JobType] ([JobTypeID] int NOT NULL PRIMARY KEY, [Description] nvarchar(MAX) NOT NULL)", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateTableWithUniqueConstraint()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateTable(TestDB.EnumTable, EnumTable.EnumID, EnumTable.Description);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("CREATE TABLE [Enum] ([EnumID] int NOT NULL, [Description] nvarchar(MAX) NOT NULL UNIQUE)", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateColumnWithoutIdentity()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateColumn(TestDB.JobTypeTable, JobTypeTable.JobTypeID);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [JobType] ADD [JobTypeID] int NOT NULL PRIMARY KEY", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateColumnWithoutPrimaryKey()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateColumn(TestDB.JobTable, JobTable.Description);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [Job] ADD [Description] nvarchar(MAX) NOT NULL", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateColumnWithPrimaryKeyAndIdentity()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateColumn(TestDB.PersonnTable, PersonnTable.PersonnID);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [Personn] ADD [PersonnID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildImplicitInsert()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Insert().Into(TestDB.PersonnTable);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("INSERT INTO [Personn]", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateUniqueConstraint()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateConstraint(TestDB.PersonnTable, ColumnConstraints.Unique, PersonnTable.PersonnID, PersonnTable.FirstName);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [Personn] ADD CONSTRAINT [AK_Personn_PersonnID_FirstName] UNIQUE ([PersonnID], [FirstName])", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreatePrimaryKeyConstraint()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateConstraint(TestDB.PersonnTable, ColumnConstraints.PrimaryKey, PersonnTable.PersonnID);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [Personn] ADD CONSTRAINT [AK_Personn_PersonnID] PRIMARY KEY ([PersonnID])", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildCreateRelation()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new CreateRelation <int>(TestDB.JobTypeTable, PersonnTable.PersonnID, JobTypeTable.JobTypeID);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("ALTER TABLE [JobType] WITH CHECK ADD CONSTRAINT [FK_JobType_JobTypeID_Personn] FOREIGN KEY ([JobTypeID]) REFERENCES [Personn] ([PersonnID])", command.CommandText);
Assert.AreEqual(0, command.Parameters.Count);
}
public void ShouldBuildSimpleOrFilterWith2Members()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName, PersonnTable.LastName).From(TestDB.PersonnTable).Where(new OrFilter(PersonnTable.FirstName.IsNotNull(), PersonnTable.LastName.IsEqualTo("Doe")));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Personn].[LastName] FROM [Personn] WHERE ([Personn].[FirstName] IS NOT NULL OR [Personn].[LastName]=@LastName0)", command.CommandText);
Assert.AreEqual(1, command.Parameters.Count);
Assert.AreEqual("@LastName0", command.Parameters[0].ParameterName);
Assert.AreEqual("Doe", command.Parameters[0].Value);
}
public void ShouldBuildLowerFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Delete().From(TestDB.PersonnTable).Where(PersonnTable.FirstName.IsLowerThan("John"));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("DELETE FROM [Personn] WHERE [Personn].[FirstName]<@FirstName0", command.CommandText);
Assert.AreEqual(1, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
}
public void ShouldBuildEqualsFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName, PersonnTable.LastName).From(TestDB.PersonnTable).Where(PersonnTable.FirstName.IsEqualTo("John"));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Personn].[LastName] FROM [Personn] WHERE [Personn].[FirstName]=@FirstName0", command.CommandText);
Assert.AreEqual(1, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
}
public void ShouldBuildSimpleAndFilterWith3Members()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Delete().From(TestDB.PersonnTable).Where(new AndFilter(PersonnTable.FirstName.IsNotNull(), PersonnTable.LastName.IsEqualTo("Doe"), PersonnTable.LastName.IsEqualTo("Doe")));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("DELETE FROM [Personn] WHERE ([Personn].[FirstName] IS NOT NULL AND [Personn].[LastName]=@LastName0 AND [Personn].[LastName]=@LastName1)", command.CommandText);
Assert.AreEqual(2, command.Parameters.Count);
Assert.AreEqual("@LastName0", command.Parameters[0].ParameterName);
Assert.AreEqual("Doe", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
}
public void ShouldBuildSeveralEqualsFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Delete().From(TestDB.PersonnTable).Where(PersonnTable.FirstName.IsEqualTo("John"), PersonnTable.LastName.IsEqualTo("Doe"));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("DELETE FROM [Personn] WHERE [Personn].[FirstName]=@FirstName0 AND [Personn].[LastName]=@LastName1", command.CommandText);
Assert.AreEqual(2, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
}
public void ShouldBuildEqualsFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Update(TestDB.PersonnTable).Set(PersonnTable.FirstName, "John").Set(PersonnTable.LastName, "Doe").Where(PersonnTable.FirstName.IsEqualTo("Homer"));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("UPDATE [Personn] SET [Personn].[FirstName]=@FirstName0, [Personn].[LastName]=@LastName1 WHERE [Personn].[FirstName]=@FirstName2", command.CommandText);
Assert.AreEqual(3, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
Assert.AreEqual("@FirstName2", command.Parameters[2].ParameterName);
Assert.AreEqual("Homer", command.Parameters[2].Value);
}
public void ShouldBuildExplicitUpdateWithNullValue()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Update(TestDB.PersonnTable).Set(PersonnTable.FirstName, "John").Set(PersonnTable.LastName, "Doe").Set(PersonnTable.SecureCode, null);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("UPDATE [Personn] SET [Personn].[FirstName]=@FirstName0, [Personn].[LastName]=@LastName1, [Personn].[SecureCode]=@SecureCode2", command.CommandText);
Assert.AreEqual(3, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
Assert.AreEqual("@SecureCode2", command.Parameters[2].ParameterName);
Assert.AreEqual(DBNull.Value, command.Parameters[2].Value);
}
public void ShouldBuildExplicitInsertWithNullValue()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Insert().Into(TestDB.PersonnTable).Set(PersonnTable.FirstName, "John").Set(PersonnTable.LastName, "Doe").Set(PersonnTable.SecureCode, null);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("INSERT INTO [Personn] ([Personn].[FirstName], [Personn].[LastName], [Personn].[SecureCode]) VALUES (@FirstName0, @LastName1, @SecureCode2)", command.CommandText);
Assert.AreEqual(3, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
Assert.AreEqual("@SecureCode2", command.Parameters[2].ParameterName);
Assert.AreEqual(DBNull.Value, command.Parameters[2].Value);
}
public void ShouldBuildNestedAndFilter()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName, PersonnTable.LastName).From(TestDB.PersonnTable).Where(new AndFilter(PersonnTable.FirstName.IsEqualTo("John"), new AndFilter(PersonnTable.FirstName.IsNotNull(), PersonnTable.LastName.IsEqualTo("Doe")), PersonnTable.LastName.IsEqualTo("Doe")));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Personn].[LastName] FROM [Personn] WHERE ([Personn].[FirstName]=@FirstName0 AND ([Personn].[FirstName] IS NOT NULL AND [Personn].[LastName]=@LastName1) AND [Personn].[LastName]=@LastName2)", command.CommandText);
Assert.AreEqual(3, command.Parameters.Count);
Assert.AreEqual("@FirstName0", command.Parameters[0].ParameterName);
Assert.AreEqual("John", command.Parameters[0].Value);
Assert.AreEqual("@LastName1", command.Parameters[1].ParameterName);
Assert.AreEqual("Doe", command.Parameters[1].Value);
Assert.AreEqual("@LastName2", command.Parameters[2].ParameterName);
Assert.AreEqual("Doe", command.Parameters[2].Value);
}
public void ShouldBuildExplicitSelectWithJoinedTable()
{
IQuery query;
ICommandBuilder builder;
DbCommand command;
query = new Select(PersonnTable.FirstName, ChildTable.FirstName).From(TestDB.PersonnTable.Join(TestDB.ChildTable.On(PersonnTable.PersonnID, ChildTable.PersonnID)));
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Child].[FirstName] FROM [Personn] INNER JOIN [Child] ON [Personn].[PersonnID] = [Child].[PersonnID]", command.CommandText);
query = new Select(PersonnTable.FirstName, ChildTable.FirstName).From(
TestDB.PersonnTable.Join(TestDB.ChildTable.On(PersonnTable.PersonnID, ChildTable.PersonnID))
.Join(TestDB.JobTypeTable.On(ChildTable.FirstName, JobTypeTable.Description))
);
builder = new SqlCommandBuilder();
command = builder.BuildCommand(query);
Assert.AreEqual("SELECT [Personn].[FirstName], [Child].[FirstName] FROM [Personn] INNER JOIN [Child] ON [Personn].[PersonnID] = [Child].[PersonnID] INNER JOIN [JobType] ON [Child].[FirstName] = [JobType].[Description]", command.CommandText);
}
