Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
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);
                }
            }
        }
Ejemplo n.º 4
0
        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);
                }
            }
        }
Ejemplo n.º 5
0
        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"));
            }
        }
Ejemplo n.º 6
0
        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"));
            }
        }
Ejemplo n.º 7
0
        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);
                }
            }
        }
Ejemplo n.º 8
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);
                }
            }
        }
Ejemplo n.º 9
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);
            }
        }
Ejemplo n.º 10
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);
            }
        }
Ejemplo n.º 11
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);
            }
        }
Ejemplo n.º 12
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);
            }
        }
Ejemplo n.º 13
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);
            }
        }
Ejemplo n.º 14
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);
                }
            }
        }
Ejemplo n.º 15
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);
            }
        }
Ejemplo n.º 16
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);
            }
        }
Ejemplo n.º 17
0
 public DeleteQueryBuilder(SqlQueryBuilderContext context) : base(context)
 {
 }
Ejemplo n.º 18
0
 public InsertQueryBuilder(SqlQueryBuilderContext context) : base(context)
 {
 }
Ejemplo n.º 19
0
 public UpdateQueryBuilder(SqlQueryBuilderContext context) : base(context)
 {
 }
Ejemplo n.º 20
0
 public SelectQueryBuilder(SqlQueryBuilderContext context) : base(context)
 {
 }