public void PropertiesFromIsNull()
{
string statement = new Srt2.SqlQueries.Select
{
}.ToString(DbConnectionType);
Assert.AreEqual(MultipleExpected, statement);
}
public void ConstructorNotIn()
{
string statement = new Srt2.SqlQueries.Select("Customers")
.Where(new NotIn("Country", new Srt2.SqlQueries.Select("Suppliers", "Country")))
.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void ConstructorIsNotNull()
{
string statement = new Srt2.SqlQueries.Select("Customers")
.Where(new IsNotNull("Country"))
.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void FluentIsNotNull()
{
string statement = new Srt2.SqlQueries.Select()
.From("Customers")
.IsNotNull("Country")
.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Properties2GroupBy()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.GroupBy.Add(new Field("ContactName"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void ConstructorWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Any("Country", SqlOperator.Equal, new Srt2.SqlQueries.Select("Suppliers", "Country")));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Properties1Join()
{
Srt2.SqlQueries.Select select = SelectCustomerAs();
select.Joins.Add(new Join("Orders o", JoinType.Inner, "c.CustomerID", "o.CustomerID"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Or(new EqualToValue("City", "Berlin"), new Equal("CustomerName", "ContactName")));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void ConstructorWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Between("Country", "Luxemburg", "Nederland"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void FluentNotIn()
{
string statement = new Srt2.SqlQueries.Select()
.From("Customers")
.NotIn("Country", new Srt2.SqlQueries.Select("Suppliers", "Country"))
.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesMultiple()
{
string statement = new Srt2.SqlQueries.Select
{
From = { "TestDatabase.Dbo.Customers" }
}.From("TestDatabase.Dbo.Orders").ToString(DbConnectionType);
Assert.AreEqual(MultipleExpected, statement);
}
public void PropertiesWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Like("City", "Ber%"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public virtual void PropertiesTopPercentage()
{
string statement = new Srt2.SqlQueries.Select
{
From = { "TestDatabase.Dbo.Customers" },
Top = new Top(10, true)
}.ToString(DbConnectionType);
Assert.AreEqual(PercentageExpected, statement);
}
public void PropertiesTop()
{
string statement = new Srt2.SqlQueries.Select
{
From = { "TestDatabase.Dbo.Customers" },
Top = 10
}.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Properties3Having()
{
Srt2.SqlQueries.Select select = SelectCustomer().GroupBy("City, ContactName");
select.Having.Add(new HavingValue("City", "Daan"));
select.Having.Add(new HavingField("ContactName", "CustomerName"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesColumns()
{
Srt2.SqlQueries.Select select = SelectCustomerAs();
select.Columns.Add(new Maximum("TotalAmount"));
select.Columns.Add(new Maximum("c", "TotalAmount"));
select.Columns.Add(new Maximum("c", "Price", "p"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Constructor2GroupBy()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "[TestDatabase].[Dbo].[Customers]" },
GroupBy = { "ContactName" }
};
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void ConstructorWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
And and = new And();
and.Add(new EqualToValue("City", "Berlin"));
and.Add(new Equal("CustomerName", "ContactName"));
select.Where.Add(and);
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesColumnTableStar()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "[TestDatabase].[Dbo].[Customers] AS [c]" },
Columns = { "c.*" }
};
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(StarExpected, statement);
}
public void PropertiesDistinct()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "[TestDatabase].[Dbo].[Customers]" },
Columns = { "[City]" },
Distinct = true
};
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesNotIn()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "Customers" }
};
select.Where.Add(new NotIn {
Field = "Country", Select = new Srt2.SqlQueries.Select("Suppliers", "Country")
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Between
{
Field = "Country",
FromValue = "Luxemburg",
ToValue = "Nederland"
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesOrderBy()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.OrderBy.Add(new OrderByField("CustomerName"));
select.OrderBy.Add(new OrderByField
{
Field = "ContactName",
Descending = true
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Properties3Join()
{
Srt2.SqlQueries.Select select = SelectCustomerAs();
select.Joins.Add(new Join {
Table = "Orders o", JoinType = JoinType.Inner
});
select.Joins[0].On.Add(new Equal("c.CustomerID", "o.CustomerID"));
select.Joins[0].On.AndOr = SqlAndOr.Or;
select.Joins[0].On.Add(new Equal("c.CustomerID", "o.CustomerID"));
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(ExpectedAndOr, statement);
}
public void PropertiesWhere()
{
Srt2.SqlQueries.Select select = SelectCustomer();
select.Where.Add(new Any
{
Field = "Country",
Operator = SqlOperator.Equal,
Select = new Srt2.SqlQueries.Select("Suppliers", "Country")
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesExist()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "[TestDatabase].[Dbo].[Customers]" }
};
select.Where.Add(new Exists {
Select = new Srt2.SqlQueries.Select("Orders", "CustomerID")
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesIsNotNull()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "Customers" }
};
select.Where.Add(new IsNotNull {
Field = "Country"
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void Properties2Having()
{
Srt2.SqlQueries.Select select = SelectCustomer().GroupBy("City, ContactName");
select.Having.Add(new HavingValue()
{
Field = "City", Value = "Daan"
});
select.Having.Add(new HavingField {
Field = "ContactName", Operator = SqlOperator.Equal, ToField = "CustomerName"
});
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void PropertiesColumns()
{
Srt2.SqlQueries.Select select = new Srt2.SqlQueries.Select
{
From = { "TestDatabase.Dbo.Customers c" },
Columns = { "Price" }
};
select.Columns.Add(new Field("c", "Price"));
select.Columns.Add("[c].[Price] as [p]");
select.Columns.Add("[Price] as [p]");
string statement = select.ToString(DbConnectionType);
Assert.AreEqual(Expected, statement);
}
public void FluentSingle()
{
string statement = new Srt2.SqlQueries.Select().From("TestDatabase.Dbo.Customers").ToString(DbConnectionType);
Assert.AreEqual(SingleExpected, statement);
}
