public void WhereExtensionTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
Assert.IsNotNull(select);
select.Where(a => a.Field("A").Contains("ABC") & a.Field("B").Equals(123));
var priObj = new PrivateObject(select);
var cond = priObj.GetProperty("WhereConditions") as ICondition;
Assert.IsNotNull(cond);
Assert.IsNotNull(cond is BinaryCondition);
Assert.IsNotNull(((BinaryCondition)cond).LeftCondition is FieldCondition);
Assert.IsNotNull(((BinaryCondition)cond).RightCondition is FieldCondition);
Assert.IsNotNull(((BinaryCondition)cond).Operation == BinaryOperation.And);
select.Where(a => a.Field("AA").Equals(123) | a.Field("BB").Equals(345));
cond = priObj.GetProperty("WhereConditions") as ICondition;
Assert.IsNotNull(cond);
Assert.IsNotNull(cond is BinaryCondition);
Assert.IsNotNull(((BinaryCondition)cond).LeftCondition is BinaryCondition);
Assert.IsNotNull(((BinaryCondition)cond).RightCondition is BinaryCondition);
Assert.IsNotNull(((BinaryCondition)cond).Operation == BinaryOperation.And);
}
}
public void SelectQueryTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
select.From(t => t.Field("A")
.LeftJoin("B").On(f => f.Field("A.ID").EqualsField("B.ID") | f.Field("A.ID").Equals(12))
.InnerJoin("C").On(f => f.Field("B.ID").EqualsField("C.ID") & f.Field("B.B").Equals(111))
)
.Where(c => c.Field("f1").Equals(123))
.GroupBy("f1")
.Having(c => c.Field("f2").Equals(123))
.OrderBy("f1")
.OrderByDescending("f2");
var priObj = new PrivateObject(sql);
var result = priObj.Invoke("Build", select) as QueryInfo;
Assert.IsNotNull(result);
Assert.IsNotNull(result.Query);
Assert.IsTrue(result.Query.Contains("SELECT"));
Assert.IsTrue(result.Query.Contains("*"));
Assert.IsTrue(result.Query.Contains("FROM"));
Assert.IsTrue(result.Query.Contains("LEFT JOIN"));
Assert.IsTrue(result.Query.Contains("ON"));
Assert.IsTrue(result.Query.Contains("INNER JOIN"));
Assert.IsTrue(result.Query.Contains("WHERE"));
Assert.IsTrue(result.Query.Contains("GROUP BY"));
Assert.IsTrue(result.Query.Contains("ORDER BY"));
Assert.IsTrue(result.Query.Contains("HAVING"));
Assert.IsTrue(result.Parameters.Count > 2);
Assert.IsTrue(sql.Build(select).Validate().Count == 0);
}
}
public void SelectMaxTest()
{
using (ShimsContext.Create())
{
//Verify the ExecuteNonQuery will call SqlCommand.ExecuteNonQuery
System.Data.SqlClient.Fakes.ShimSqlCommand.AllInstances.ExecuteDbDataReaderCommandBehavior =
(s, c) =>
{
if (!s.CommandText.StartsWithIgnoreCase("SELECT"))
{
return(null);
}
if (
!s.CommandText.ContainsIgnoreCase(
"MAX([O].[Freight]) [Max],MIN([O].[Freight]) [Min],SUM([O].[Freight]) [Sum],AVG([O].[Freight]) [Avg],[E].[EmployeeID],[O].[OrderID]"))
{
return(null);
}
if (s.Parameters.Count <= 1)
{
return(null);
}
return(new CsvAdapter("TestData\\Employees.csv").Read().CreateDataReader());
};
using (var sql = new SqlQueryBuilderContext(ConnectionName))
{
var select = sql.CreateSelectQuery();
select.Fields(f =>
new Field[]
{
f.Max("O.Freight").As("Max"),
f.Min("O.Freight").As("Min"),
f.Sum("O.Freight").As("Sum"),
f.Avg("O.Freight").As("Avg")
}
)
.Fields("E.EmployeeID", "O.OrderID")
.From(t => t.Field("Employees").As("E")
.InnerJoin("Orders")
.As("O")
.On(j => j.Field("E.EmployeeID").EqualsField("O.EmployeeID"))
.InnerJoin("Order Details")
.As("D")
.On(j => j.Field("D.OrderID").EqualsField("O.OrderID"))
)
.Where(f => f.Field("E.TitleOfCourtesy").In("Ms.", "Dr.")
& f.Field("D.OrderID").In(10258, 10270, 10275)
)
.GroupBy("E.EmployeeID", "O.OrderID")
.Having(f => f.Field("E.EmployeeID").LessThan(5))
.OrderBy("E.EmployeeID");
var data = sql.ExecuteTable(select);
Assert.IsNotNull(data);
Assert.IsTrue(data.Rows.Count >= 3);
}
}
}
public void ComplexSelectTest()
{
using (ShimsContext.Create())
{
//Verify the ExecuteNonQuery will call SqlCommand.ExecuteNonQuery
System.Data.SqlClient.Fakes.ShimSqlCommand.AllInstances.ExecuteDbDataReaderCommandBehavior =
(s, c) =>
{
if (!s.CommandText.StartsWithIgnoreCase("SELECT"))
{
return(null);
}
if (!s.CommandText.ContainsIgnoreCase("[E].[EmployeeID],[O].[OrderID],[O].[Freight]"))
{
return(null);
}
if (!s.CommandText.ContainsIgnoreCase("FROM [dbo].[Employees][E]"))
{
return(null);
}
if (
!s.CommandText.ContainsIgnoreCase(
"INNER JOIN [dbo].[Orders][O] ON ([E].[EmployeeID] = [O].[EmployeeID])"))
{
return(null);
}
if (s.Parameters.Count <= 1)
{
return(null);
}
return(new DataTable().CreateDataReader());
};
using (var sql = new SqlQueryBuilderContext(ConnectionName))
{
var select = sql.CreateSelectQuery();
select.From(t => t.Field("Employees").As("E")
.InnerJoin("Orders")
.As("O")
.On(j => j.Field("E.EmployeeID").EqualsField("O.EmployeeID"))
.InnerJoin("Order Details")
.As("D")
.On(j => j.Field("D.OrderID").EqualsField("O.OrderID"))
)
.Fields("E.EmployeeID", "O.OrderID", "O.Freight")
.Where(f => f.Field("E.TitleOfCourtesy").In("Ms.", "Dr.")
& f.Field("D.OrderID").In(10258, 10270, 10275)
)
.GroupBy("E.EmployeeID", "O.OrderID", "O.Freight")
.Having(f => f.Field("E.EmployeeID").LessThan(5))
.OrderBy("E.EmployeeID");
var data = sql.ExecuteTable(select);
Assert.IsNotNull(data);
}
}
}
public void Create_SqlBuilderContext_WithConnectionStringAndIBuilderProvider_Test()
{
var builder = new Mock <IBuilderProvider>().Object;
using (var sql = new SqlQueryBuilderContext(dummyConnectionString, builder))
{
var priObj = new PrivateObject(sql);
Assert.AreEqual(builder, priObj.GetFieldOrProperty("Provider"));
}
}
public void Create_SqlBuilderContext_WithIDbConnection_Test()
{
var iDbObj = new Mock <IDbConnection>().Object;
using (var sql = new SqlQueryBuilderContext(iDbObj))
{
var priObj = new PrivateObject(sql);
Assert.AreEqual(iDbObj, priObj.GetFieldOrProperty("Connection") as IDbConnection);
Assert.IsNotNull(priObj.GetFieldOrProperty("Provider"));
}
}
public void SelectTest()
{
using (ShimsContext.Create())
{
//Verify the ExecuteNonQuery will call SqlCommand.ExecuteNonQuery
System.Data.SqlClient.Fakes.ShimSqlCommand.AllInstances.ExecuteDbDataReaderCommandBehavior =
(s, c) =>
{
if (!s.CommandText.StartsWithIgnoreCase("SELECT"))
{
return(null);
}
if (!s.CommandText.ContainsIgnoreCase("*"))
{
return(null);
}
if (!s.CommandText.ContainsIgnoreCase("FROM [dbo].[Categories]"))
{
return(null);
}
if (!s.CommandText.ContainsIgnoreCase("WHERE ([CategoryID] >= @CategoryID)"))
{
return(null);
}
if (s.Parameters.Count != 1)
{
return(null);
}
return(new CsvAdapter("TestData\\Categories.csv").Read().CreateDataReader());
};
using (var sql = new SqlQueryBuilderContext(ConnectionName))
{
var select = sql.CreateSelectQuery();
select.From("Categories")
.Where(c => c.Field("CategoryID").GreaterThanOrEquals(1));
var data = sql.ExecuteTable(select);
Assert.IsNotNull(data);
Assert.IsTrue(data.Rows.Count > 0);
}
}
}
public void UpdateTest()
{
//Insert first
InsertTest();
using (ShimsContext.Create())
{
//Verify the ExecuteNonQuery will call SqlCommand.ExecuteNonQuery
System.Data.SqlClient.Fakes.ShimSqlCommand.AllInstances.ExecuteNonQuery =
c =>
{
if (!c.CommandText.StartsWithIgnoreCase("UPDATE [dbo].[Categories]"))
{
return(0);
}
if (!c.CommandText.ContainsIgnoreCase("SET [CategoryName] = @CategoryName"))
{
return(0);
}
if (!c.CommandText.ContainsIgnoreCase("WHERE ([CategoryName] LIKE @CategoryName1)"))
{
return(0);
}
if (c.Parameters.Count != 2)
{
return(0);
}
return(1);
};
using (var sql = new SqlQueryBuilderContext(ConnectionName))
{
var update = sql.CreateUpdateQuery();
update.Table("Categories")
.Set("CategoryName").By("Category U")
.Where(c => c.Field("CategoryName").Contains("Category"));
var v = sql.ExecuteNonQuery(update);
Assert.IsTrue(v > 0);
}
}
}
public void CreateDeleteQueryTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var delete = sql.CreateDeleteQuery();
delete.From("AA")
.Where(f => f.Field("ID").Equals(123));
var priObj = new PrivateObject(sql);
var result = priObj.Invoke("Build", delete) as QueryInfo;
Assert.IsNotNull(result);
Assert.IsTrue(result.Query.Contains("DELETE"));
Assert.IsTrue(result.Query.Contains("FROM"));
Assert.IsTrue(result.Query.Contains("WHERE"));
Assert.IsTrue(result.Parameters.Count == 1);
Assert.IsTrue(sql.Build(delete).Validate().Count == 0);
}
}
public void TableExtensionsTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
select.Fields("A", "B")
.From(t => t.Field("ABC").As("A")
.LeftJoin("123").As("B").On(f => f.Field("A.A").EqualsField("B.B"))
.RightJoin("C").On(f => f.Field("A.C").EqualsField("C.ID"))
);
var priObj = new PrivateObject(select);
var tbs = priObj.GetProperty("Tables") as IList <Table>;
Assert.IsNotNull(tbs);
Assert.IsTrue(tbs.Count == 1);
Assert.IsTrue(tbs[0].Joins.Count > 1);
Assert.IsTrue(sql.Build(select).Validate().Count == 0);
}
}
public void CreateInsertQueryTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var insert = sql.CreateInsertQuery();
insert.Into("AA")
.Values("F1").By(1)
.Values("F2").By(2)
.Values("F3").By(3);
var priObj = new PrivateObject(sql);
var result = priObj.Invoke("Build", insert) as QueryInfo;
Assert.IsNotNull(result);
Assert.IsTrue(result.Query.Contains("INSERT INTO"));
Assert.IsTrue(result.Query.Contains("VALUES"));
Assert.IsTrue(result.Parameters.Count == 3);
Assert.IsTrue(sql.Build(insert).Validate().Count == 0);
}
}
public void SelectQuery_Count_GroupByNewID_Test()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
select.Fields(f => f.Count()).From("A")
.GroupBy("NewId()");
var result = select.Build();
Assert.IsNotNull(result);
Assert.IsNotNull(result.Query);
Assert.IsTrue(result.Query.Contains("SELECT"));
Assert.IsTrue(result.Query.Contains("COUNT(*)"));
Assert.IsTrue(result.Query.Contains("GROUP BY"));
Assert.IsTrue(result.Query.Contains("NewId()"));
Assert.IsFalse(result.Query.Contains("[NewId()]"));
Assert.IsTrue(sql.Build(select).Validate().Count == 0);
}
}
public void FieldsExtensionTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
Assert.IsNotNull(select);
select.Fields("A".As("BA"), "B".As("BB"))
.Fields("C", "D", "E")
.From("TB1");
var priObj = new PrivateObject(select);
var fields = priObj.GetProperty("Fields") as IList <Field>;
Assert.IsNotNull(fields);
Assert.IsTrue(fields.Count == 5);
Assert.IsTrue(fields[0].Name == "A");
Assert.IsTrue(fields[0].Alias == "BA");
Assert.IsTrue(sql.Build(select).Validate().Count == 0);
}
}
public void InsertTest()
{
using (ShimsContext.Create())
{
//Verify the ExecuteNonQuery will call SqlCommand.ExecuteNonQuery
System.Data.SqlClient.Fakes.ShimSqlCommand.AllInstances.ExecuteNonQuery =
c =>
{
if (!c.CommandText.StartsWithIgnoreCase("INSERT INTO"))
{
return(0);
}
if (!c.CommandText.ContainsIgnoreCase("[dbo].[Categories]([CategoryName])"))
{
return(0);
}
if (!c.CommandText.ContainsIgnoreCase("VALUES (@CategoryName)"))
{
return(0);
}
if (c.Parameters.Count != 1)
{
return(0);
}
return(1);
};
using (var sql = new SqlQueryBuilderContext(ConnectionName))
{
var insert = sql.CreateInsertQuery();
insert.Into("Categories")
.Values("CategoryName").By("Category 1");
var v = sql.ExecuteNonQuery(insert);
Assert.IsTrue(v > 0);
}
}
}
public void SelectQueryCustomFunctionTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var select = sql.CreateSelectQuery();
select.Fields(f => f.Func("Function1", "ABC", 123))
.From("A")
.Where(c => c.Field("f1").Equals(123));
var priObj = new PrivateObject(sql);
var result = priObj.Invoke("Build", select) as QueryInfo;
Assert.IsNotNull(result);
Assert.IsNotNull(result.Query);
Assert.IsTrue(result.Query.Contains("SELECT"));
Assert.IsTrue(result.Query.Contains("FUNCTION1(N'ABC',123)"));
Assert.IsTrue(result.Query.Contains("FROM [dbo].[A]"));
Assert.IsTrue(result.Query.Contains("WHERE"));
Assert.IsTrue(result.Parameters.Count == 1);
Assert.IsTrue(sql.Build(select).Validate().Count == 0);
}
}
public void CreateUpdateQueryTest()
{
using (var sql = new SqlQueryBuilderContext(dummyConnectionString))
{
var update = sql.CreateUpdateQuery();
update.Table("AA")
.Set("A").By(1)
.Set("B").By(2)
.Set("C").By(3)
.Where(f => f.Field("ID").Equals(10));
var priObj = new PrivateObject(sql);
var result = priObj.Invoke("Build", update) as QueryInfo;
Assert.IsNotNull(result);
Assert.IsTrue(result.Query.Contains("UPDATE"));
Assert.IsTrue(result.Query.Contains("SET"));
Assert.IsTrue(result.Query.Contains("WHERE"));
Assert.IsTrue(result.Parameters.Count == 4);
Assert.IsTrue(sql.Build(update).Validate().Count == 0);
}
}
public DeleteQueryBuilder(SqlQueryBuilderContext context) : base(context)
{
}
public InsertQueryBuilder(SqlQueryBuilderContext context) : base(context)
{
}
public UpdateQueryBuilder(SqlQueryBuilderContext context) : base(context)
{
}
public SelectQueryBuilder(SqlQueryBuilderContext context) : base(context)
{
}
