Ejemplo n.º 1
0
        public void MultipleJoins()
        {
            var u = new UserTable();
            var p1 = new PostTable("p1");
            var p2 = new PostTable("p2");
            var tv = new CustomTableValuedFunction();

            var q = new SharpQuery();
            q.Select(u.AllColumns)
                .From(u)
                .InnerJoin(p1, p1.UserId.IsEqualTo(u.Id))
                .InnerJoin(p2, Predicate.TRUE)
                .CrossApply(tv, Predicate.TRUE)
                .LeftOuterJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .CrossApply(tv, Predicate.TRUE)
                .LeftOuterJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .InnerJoin(p2, Predicate.TRUE)
                .EndStatement();
        }
Ejemplo n.º 2
0
        public void InnerJoin()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();

            q.Select(p.AllColumns)
                .From(p)
                .InnerJoin(u, u.Id.IsEqualTo(p.UserId))
                .Where(u.FirstName.IsEqualTo("Mike"))
                .EndStatement();
            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
            SELECT
            [dbo].[Post].[Id]
              , [dbo].[Post].[UserId]
              , [dbo].[Post].[Title]
              , [dbo].[Post].[Text]FROM
            [dbo].[Post]
            INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
            [dbo].[User].[Name] =  @p0
            ");
        }
Ejemplo n.º 3
0
        public void BetweenMustThrowOnNullArgument3()
        {
            var q    = new SharpQuery();
            var expr = (Expression)null;

            Assert.Throws <ArgumentNullException>(() => q.Select(1).Where(expr.IsBetween(1, 2)));
        }
Ejemplo n.º 4
0
        public void MultipleJoins()
        {
            var u  = new UserTable();
            var p1 = new PostTable("p1");
            var p2 = new PostTable("p2");
            var tv = new CustomTableValuedFunction();

            var q = new SharpQuery();

            q.Select(u.AllColumns)
            .From(u)
            .InnerJoin(p1, p1.UserId.IsEqualTo(u.Id))
            .InnerJoin(p2, Predicate.TRUE)
            .CrossApply(tv, Predicate.TRUE)
            .LeftOuterJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .CrossApply(tv, Predicate.TRUE)
            .LeftOuterJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .InnerJoin(p2, Predicate.TRUE)
            .EndStatement();
        }
Ejemplo n.º 5
0
        public void CodeUsingSharpQL()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();
            var condition = Predicate.TRUE;
            if (string.IsNullOrWhiteSpace(this.Email)) {
                condition &= u.Email.IsEqualTo(this.Email);
            }

            if (this.Date.HasValue) {
                condition &= p.PostDate.IsGreaterThanOrEqualTo(this.Date);
            }

            q.Select(p.Id, p.Title, p.Text, p.PostDate, u.Email.As("UserMail"))
                .From(p)
                .InnerJoin(u, p.UserId.IsEqualTo(u.Id))
                .Where(condition)
                .EndStatement()
                .OrderByDesc(p.PostDate)
                .EndStatement();

            using (var connection = new SqlConnection("connectionString")) {
                var cmd = q.CreateCommand(connection);
                using (var reader = cmd.ExecuteReader()) {
                    // code to read data goes here
                }
            }
        }
Ejemplo n.º 6
0
        public void Having()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            int userId = 1;
            q.Select(u.Id, Sql.Count(1))
                .From(u)
                .InnerJoin(p, u.Id.IsEqualTo(p.UserId))
                .Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
                .GroupBy(u.Id)
                .Having(Sql.Count(1).IsGreaterThan(10))
                .EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT   [dbo].[User].[Id],
             COUNT(@p0)
            FROM     [dbo].[User]
             INNER JOIN
             [dbo].[Post]
             ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
            WHERE    ([dbo].[User].[Id] = @p0
              AND [dbo].[Post].[PostDate] >= @p1)
            GROUP BY [dbo].[User].[Id]
            HAVING   COUNT(@p0) > @p2;
            ");
        }
Ejemplo n.º 7
0
        public void InnerJoin()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q = new SharpQuery();

            q.Select(p.AllColumns)
            .From(p)
            .InnerJoin(u, u.Id.IsEqualTo(p.UserId))
            .Where(u.FirstName.IsEqualTo("Mike"))
            .EndStatement();
            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
SELECT
    [dbo].[Post].[Id]
  , [dbo].[Post].[UserId]
  , [dbo].[Post].[Title]
  , [dbo].[Post].[Text]FROM
[dbo].[Post]
INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
[dbo].[User].[Name] =  @p0 
");
        }
Ejemplo n.º 8
0
        private static void ImageFix()
        {
            var tests = new SpecialistTestDataContext().Tests.ToList();

            foreach (var test in tests)
            {
                var query = new SharpQuery("<div>" + test.Description + "</div>");
                var imgs  = query.Find("img");
                if (imgs.Length == 0)
                {
                    continue;
                }

                var scr = imgs.Attr("src");
                var url = scr;
                if (!url.Contains("http"))
                {
                    url = "http://test.specialist.ru" + url;
                }
                var ext   = Path.GetExtension(scr);
                var image = GetImage(url);
                if (image != null)
                {
                    image.Save(test.Id + ext);
                }
            }
        }
Ejemplo n.º 9
0
        public void NotEndedStatementsShouldNotBeAddedToScript1()
        {
            var q = new SharpQuery();

            q.Select(1);
            TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
        }
Ejemplo n.º 10
0
 public void EndedStatementsShouldBeAddedToScript()
 {
     var q = new SharpQuery();
     q.Select(1).EndStatement();
     var script = q.ToString();
     TSqlAssert.ScriptsAreEqual(script, "SELECT @p0");
 }
Ejemplo n.º 11
0
        public void Having()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            int userId = 1;

            q.Select(u.Id, Sql.Count(1))
            .From(u)
            .InnerJoin(p, u.Id.IsEqualTo(p.UserId))
            .Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
            .GroupBy(u.Id)
            .Having(Sql.Count(1).IsGreaterThan(10))
            .EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT   [dbo].[User].[Id],
         COUNT(@p0)
FROM     [dbo].[User]
         INNER JOIN
         [dbo].[Post]
         ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
WHERE    ([dbo].[User].[Id] = @p0
          AND [dbo].[Post].[PostDate] >= @p1)
GROUP BY [dbo].[User].[Id]
HAVING   COUNT(@p0) > @p2;
");
        }
Ejemplo n.º 12
0
        public static string GetSpecialDirectSites(string phrase)
        {
            var sq    = new SharpQuery(GetHtml(yandex.FormatWith(phrase)));
            var sites = GetSites(sq);

            return(sites.JoinWith(","));
        }
Ejemplo n.º 13
0
        // You know for sure that the previous code sample is very error prone.
        // It is hard to understand and hard to extend. If you need to change it you'll most likely break something and you'll find out in runtime instead of compile time.
        //
        // Here is the equivalent code written in SharpQL

        #endregion

        #region ns code

        public void CodeUsingSharpQL()
        {
            var u = new UserTable();
            var p = new PostTable();

            var q         = new SharpQuery();
            var condition = Predicate.TRUE;

            if (string.IsNullOrWhiteSpace(this.Email))
            {
                condition &= u.Email.IsEqualTo(this.Email);
            }

            if (this.Date.HasValue)
            {
                condition &= p.PostDate.IsGreaterThanOrEqualTo(this.Date);
            }

            q.Select(p.Id, p.Title, p.Text, p.PostDate, u.Email.As("UserMail"))
            .From(p)
            .InnerJoin(u, p.UserId.IsEqualTo(u.Id))
            .Where(condition)
            .EndStatement()
            .OrderByDesc(p.PostDate)
            .EndStatement();

            using (var connection = new SqlConnection("connectionString")) {
                var cmd = q.CreateCommand(connection);
                using (var reader = cmd.ExecuteReader()) {
                    // code to read data goes here
                }
            }
        }
Ejemplo n.º 14
0
        public void SelectSingleColumnCreatesSingleExpSelectStatement()
        {
            var u   = new UserTable();
            var q   = new SharpQuery();
            var stm = q.Select(u.Id).From(u).Statement;

            Assert.IsInstanceOf(typeof(SingleExprSelectStatement), stm);
        }
Ejemplo n.º 15
0
        public void SelectSingleColumnCreatesSingleExpSelectStatement()
        {
            var u = new UserTable();
            var q = new SharpQuery();
            var stm = q.Select(u.Id).From(u).Statement;

            Assert.IsInstanceOf(typeof(SingleExprSelectStatement), stm);
        }
Ejemplo n.º 16
0
        public void Test1()
        {
            var q = new SharpQuery();

            q.Select(1).EndStatement();
            var query = q.ToString();
            Assert.That(q.ScriptBuilder.Parameters.Count, Is.EqualTo(1));
        }
Ejemplo n.º 17
0
        public void EndedStatementsShouldBeAddedToScript()
        {
            var q = new SharpQuery();

            q.Select(1).EndStatement();
            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, "SELECT @p0");
        }
Ejemplo n.º 18
0
        public void Test1()
        {
            var q = new SharpQuery();

            q.Select(1).EndStatement();
            var query = q.ToString();

            Assert.That(q.ScriptBuilder.Parameters.Count, Is.EqualTo(1));
        }
Ejemplo n.º 19
0
 public void Between()
 {
     var q = new SharpQuery();
     q.Select(1).Where(ConstantExpression.GetConstant(4).IsBetween(3, 5)).EndStatement();
     TSqlAssert.ScriptsAreEqual(q.ToString(), @"
     SELECT @p0
     WHERE  (@p1 BETWEEN @p2 AND @p3)
     ");
 }
Ejemplo n.º 20
0
        public void In()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.GetConstant(1).IsIn(1, 2, 3, 4, 5)).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  @p0 IN (@p0, @p1, @p2, @p3, @p4)
");
        }
Ejemplo n.º 21
0
        public void LessThan()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.GetConstant(1).IsLessThan(2)).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  @p0 < @p1
");
        }
Ejemplo n.º 22
0
        public void GreaterThanOrEqualTo()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.GetConstant(1).IsGreaterThanOrEqualTo(1)).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  @p0 >= @p0
");
        }
Ejemplo n.º 23
0
        public void NotIn()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.GetConstant(1).IsNotIn(2, 3, 4, 5, 6, 7)).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  @p0 NOT IN (@p1, @p2, @p3, @p4, @p5,@p6)
");
        }
Ejemplo n.º 24
0
        public void Between()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.GetConstant(4).IsBetween(3, 5)).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  (@p1 BETWEEN @p2 AND @p3)
");
        }
Ejemplo n.º 25
0
        public void IsNull()
        {
            var q = new SharpQuery();

            q.Select(1).Where(ConstantExpression.NULL.IsNull()).EndStatement();
            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  NULL IS NULL
");
        }
Ejemplo n.º 26
0
        public void Should_use_sharpquery_find_when_indexer_invoked()
        {
            var query = new SharpQuery(@"<html><head></head><body><div id='testId' class='myClass'>Test</div></body></html>");
            var queryResult = query.Find("#testId").FirstOrDefault();
            QueryWrapper wrapper = query;

            var result = (HtmlNode)wrapper["#testId"].FirstOrDefault();

            Assert.NotNull(result);
            Assert.Same(queryResult, result);
        }
Ejemplo n.º 27
0
 public void Contains()
 {
     var q = new SharpQuery();
     var a = ConstantExpression.GetConstant("code insiders");
     q.Select(1).Where(a.Contains("code")).EndStatement();
     var actual = q.ToString();
     TSqlAssert.ScriptsAreEqual(actual, @"
     SELECT @p0
     WHERE  @p1 LIKE ((@p2 + @p3) + @p2)
     ");
 }
Ejemplo n.º 28
0
        public void Should_use_sharpquery_find_when_indexer_invoked()
        {
            var          query       = new SharpQuery(@"<html><head></head><body><div id='testId' class='myClass'>Test</div></body></html>");
            var          queryResult = query.Find("#testId").FirstOrDefault();
            QueryWrapper wrapper     = query;

            var result = (HtmlNode)wrapper["#testId"].FirstOrDefault();

            Assert.NotNull(result);
            Assert.Same(queryResult, result);
        }
Ejemplo n.º 29
0
        public void Minus()
        {
            var q = new SharpQuery();

            var a = Expression.GetConstant(1);
            var b = Expression.GetConstant(2);

            q.Select(a - b).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 - @p1)");
        }
Ejemplo n.º 30
0
        public void Divide()
        {
            var q = new SharpQuery();
            var a = ConstantExpression.GetConstant(5);
            var b = ConstantExpression.GetConstant(2);

            q.Select(a / b).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT (@p0 / @p1);
            ");
        }
Ejemplo n.º 31
0
        public void Mod()
        {
            var q = new SharpQuery();
            var a = ConstantExpression.GetConstant(5);
            var b = ConstantExpression.GetConstant(2);

            q.Select(a % b).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 % @p1)
");
        }
Ejemplo n.º 32
0
        private static List <string> AdWordsText(SharpQuery sq)
        {
            var directText1 = sq.Find("#tads h3 a")
                              .Select(x => RepaceHref(x.OuterHtml));
            var directText2 = sq.Find("#tads span.ac").Select(x => x.InnerHtml);
            var directText3 = sq.Find("#tads cite").Select(x => x.InnerHtml);
            var adWords     =
                directText1.Zip(directText2.Zip(directText3, (x, y) => x + "<br/>" + y), (x, y) => x + "<br/>" + y)
                .ToList();

            return(adWords);
        }
Ejemplo n.º 33
0
    public void HudIsReturned()
    {
      var browser = new Browser(new TestBootstrapper());
      var response = browser.Get("/_cassette", with => with.HttpRequest());
      Console.Write(response.Body.AsString());

      var query = new SharpQuery(response.Body.AsString());
      var title = query.Find("head title").First();

      Assert.That(title, Is.Not.Null);

    }
Ejemplo n.º 34
0
        public void Minus()
        {
            var q = new SharpQuery();

            var a = Expression.GetConstant(1);
            var b = Expression.GetConstant(2);

            q.Select(a - b).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT (@p0 - @p1)");
        }
Ejemplo n.º 35
0
        public void Multiply()
        {
            var q = new SharpQuery();
            var a = ConstantExpression.GetConstant(1.1);
            var b = ConstantExpression.GetConstant(2.2);

            q.Select(a * b).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT (@p0 * @p1)
            ");
        }
        public void HudIsReturned()
        {
            var browser  = new Browser(new NonOptimizingBootstrapper());
            var response = browser.Get("/_cassette", with => with.HttpRequest());

            Console.Write(response.Body.AsString());

            var query = new SharpQuery(response.Body.AsString());
            var title = query.Find("head title").First();

            Assert.That(title, Is.Not.Null);
        }
Ejemplo n.º 37
0
        public void NotInSelect()
        {
            var q = new SharpQuery();
            var singleExprSelectStatement = q.Select(1).Statement;

            q.Select(1).Where(ConstantExpression.GetConstant(2).IsNotIn(singleExprSelectStatement)).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE  @p1 NOT IN (SELECT @p0)
");
        }
Ejemplo n.º 38
0
        public void EndsWith()
        {
            var q = new SharpQuery();
            var a = ConstantExpression.GetConstant("code insiders");

            q.Select(1).Where(a.EndsWith("insiders")).EndStatement();
            var actual = q.ToString();

            TSqlAssert.ScriptsAreEqual(actual, @"
SELECT @p0
WHERE  @p1 LIKE (@p2 + @p3)
");
        }
Ejemplo n.º 39
0
        public void QueryAlwaysGeneratesTheSameScript()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();

            var script1 = q.ToString();
            var script2 = q.ToString();

            TSqlAssert.ScriptsAreEqual(script1, script2);
        }
Ejemplo n.º 40
0
        public void QueryAlwaysGeneratesTheSameScript()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();

            var script1 = q.ToString();
            var script2 = q.ToString();

            TSqlAssert.ScriptsAreEqual(script1, script2);
        }
Ejemplo n.º 41
0
        public void InsertWithMultipleValues()
        {
            var u = new UserTable();

            var q = new SharpQuery();
            q.InsertInto(u, u.Id, u.FirstName, u.Email)
                .Values(1, "a", "b")
                .Values(2, "a", "b")
                .Values(3, "a", "b")
                .EndStatement();

            var str = q.ToString();
        }
Ejemplo n.º 42
0
        private static List <string> DirectText(SharpQuery sq)
        {
            var directText1 = sq.Find("div.b-spec-adv a.b-serp-item__title-link")
                              .Select(x => x.OuterHtml);
            var directText2 = sq.Find("div.b-spec-adv div.b-serp-item__text")
                              .Select(x => x.InnerHtml);
            var directText3 = GetSites(sq);
            var directText  =
                directText1.Zip(directText2.Zip(directText3, (x, y) => x + "<br/>" + y), (x, y) => x + "<br/>" + y)
                .ToList();

            return(directText);
        }
    public void JsFileIsReturned()
    {
      var browser = new Browser(new NonOptimizingBootstrapper());
      var response = browser.Get("/RazorHome", with => with.HttpRequest());
      Console.Write(response.Body.AsString());

      var query = new SharpQuery(response.Body.AsString());
      var url = query.Find("body script[src^='/_cassette/asset/Scripts/lib/underscore.js?']").Attr("src");
      
      response = browser.Get(url, with => with.HttpRequest());
      Assert.AreEqual(HttpStatusCode.OK, response.StatusCode);
      Console.Write(response.Body.AsString());
    }
    public void SassFileIsReturned()
    {
      var browser = new Browser(new NonOptimizingBootstrapper());
      var response = browser.Get("/RazorHome", with => with.HttpRequest());
      Console.Write(response.Body.AsString());

      var query = new SharpQuery(response.Body.AsString());
      var url = query.Find("head link[href^='/_cassette/asset/Styles/Main.sass?']").Attr("href");

      response = browser.Get(url, with => with.HttpRequest());
      Assert.AreEqual(HttpStatusCode.OK, response.StatusCode);
      Console.Write(response.Body.AsString());
    } 
Ejemplo n.º 45
0
        public void FalsePredicate()
        {
            var q = new SharpQuery();
            var u = new UserTable();

            q.Select(1).From(u).Where(Predicate.FALSE).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
FROM   [dbo].[User]
WHERE  1 <> 1;
");
        }
    public void BundleIsReturned(string location, string element, string attribute, string urlFragmet)
    {
      var browser = new Browser(new OptimizingBootstrapper());
      var response = browser.Get("/RazorHome", with => with.HttpRequest());
      Console.Write(response.Body.AsString());
      
      var query = new SharpQuery(response.Body.AsString());

      var url = query.Find(string.Format("{0} {1}[{2}^='{3}']", location, element, attribute, urlFragmet)).Attr(attribute);
      
      response = browser.Get(url, with => with.HttpRequest());
      Assert.AreEqual(HttpStatusCode.OK, response.StatusCode);
      Console.Write(response.Body.AsString());
    }
Ejemplo n.º 47
0
        public void InsertWithMultipleValues()
        {
            var u = new UserTable();

            var q = new SharpQuery();

            q.InsertInto(u, u.Id, u.FirstName, u.Email)
            .Values(1, "a", "b")
            .Values(2, "a", "b")
            .Values(3, "a", "b")
            .EndStatement();

            var str = q.ToString();
        }
Ejemplo n.º 48
0
        public void SelectAliasedColumnFromTable()
        {
            var u = new UserTable();
            var q = new SharpQuery();

            q.Select(u.FirstName.As("Username")).From(u).EndStatement();

            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
SELECT [dbo].[User].[Name] AS [Username] 
FROM [dbo].[User]
");
        }
Ejemplo n.º 49
0
        public void Exists()
        {
            var q        = new SharpQuery();
            var subQuery = q.Select(1).Statement;

            q.Select(1).Where(Predicate.Exists(subQuery)).EndStatement();
            var actual = q.ToString();

            TSqlAssert.ScriptsAreEqual(actual, @"
SELECT
     @p0 
WHERE EXISTS( SELECT @p0 ) 
");
        }
Ejemplo n.º 50
0
        public void And()
        {
            var q = new SharpQuery();
            var a = ConstantExpression.GetConstant(1);
            var b = ConstantExpression.GetConstant(2);

            q.Select(1).Where(a.IsEqualTo(1) & b.IsEqualTo(3)).EndStatement();

            var actual = q.ToString();
            TSqlAssert.ScriptsAreEqual(actual, @"
            SELECT @p0
            WHERE  (@p0 = @p0
            AND @p1 = @p2)
            ");
        }
Ejemplo n.º 51
0
        public void InsertIntoTable()
        {
            var u = new UserTable();
            var q = new SharpQuery();

            q.InsertInto(u, u.Email, u.FirstName).Values("*****@*****.**", "username").EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            INSERT INTO [dbo].[User](    [dbo].[User].[Email]
              , [dbo].[User].[Name])
            VALUES(
             @p0
              ,  @p1  )
            ");
        }
Ejemplo n.º 52
0
        public void SingleExprSingleColumnSelect()
        {
            var q = new SharpQuery();

            var selectStm = q.SelectOne(2).Statement;
            var exp = (Expression)selectStm;

            q.Select(exp).EndStatement();

            var sqlString = q.ToString();
            TSqlAssert.ScriptsAreEqual(sqlString, @"
            SELECT
            ( SELECT TOP (  @p0  )      @p1 )
            ");
        }
Ejemplo n.º 53
0
        public void SimpleCase()
        {
            var q = new SharpQuery();
            SimpleCase c = new SimpleCase(1);

            c.When(1, 2).When(3, 4).Else(5);
            q.Select(c).EndStatement();

            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
            SELECT
            ( CASE  @p0
             WHEN  @p0  THEN  @p1
             WHEN  @p2  THEN  @p3
             ELSE  @p4
             END )
            ");
        }
Ejemplo n.º 54
0
        public void OrderBy()
        {
            var q = new SharpQuery();
            var u = new UserTable();

            q.Select(u.AllColumns).From(u).EndStatement();
            q.OrderByAsc(u.Id, u.FirstName).OrderByDesc(u.Email).EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT
            [dbo].[User].[Id]
              , [dbo].[User].[Name]
              , [dbo].[User].[Email]
             FROM [dbo].[User]
            ORDER BY
            [dbo].[User].[Id] ASC
              , [dbo].[User].[Name] ASC
              ,     [dbo].[User].[Email] DESC
            ");
        }
Ejemplo n.º 55
0
        public void SearchedCase2()
        {
            var q = new SharpQuery();
            var v = ConstantExpression.GetConstant(1);
            var p = ConstantExpression.GetConstant(2);
            q.Select(q
                .CaseWhen(v.IsBetween(2, 3), 3)
                .CaseWhen(v.IsEqualTo(5), 4)
                .CaseWhen(p.IsEqualTo(v), 10)
                .Else(p))
                .EndStatement();

            TSqlAssert.ScriptsAreEqual(q.ToString(), @"
            SELECT
             (CASE  WHEN ( @p0  BETWEEN  @p1  AND  @p2 ) THEN  @p2
             WHEN  @p0  =  @p3  THEN  @p4
             WHEN  @p1  =  @p0  THEN  @p5
             ELSE  @p1
             END)
            ");
        }
Ejemplo n.º 56
0
        public void Test1()
        {
            var q = new SharpQuery();
            var u = new UserTable();
            var p = new PostTable();

            q.Select(u.Id, Sql.Count(1))
                .From(u)
                .InnerJoin(p, p.UserId.IsEqualTo(u.Id))
                .GroupBy(u.Id)
                .EndStatement();
            var query = q.ToString();

            TSqlAssert.ScriptsAreEqual(query, @"
            SELECT
            [dbo].[User].[Id]
              , COUNT( @p0  )
            FROM [dbo].[User]
            INNER JOIN [dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
            GROUP BY
            [dbo].[User].[Id]");
        }
Ejemplo n.º 57
0
        public void SearchedCase()
        {
            var q = new SharpQuery();

            var sc = new SearchedCase();
            sc.CaseWhen(((Expression)1).IsEqualTo(1), 1)
                .CaseWhen(((Expression)2).IsEqualTo(3), 4)
                .Else(5);

            q.Select(sc).EndStatement();

            var script = q.ToString();

            TSqlAssert.ScriptsAreEqual(script, @"
            SELECT
             (CASE  WHEN  @p0  =  @p0  THEN  @p0
             WHEN  @p1  =  @p2  THEN  @p3
             ELSE  @p4
             END)

            ");
        }
Ejemplo n.º 58
0
 public void NotEndedStatementsShouldNotBeAddedToScript1()
 {
     var q = new SharpQuery();
     q.Select(1);
     TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
 }
Ejemplo n.º 59
0
 public void EmptyQueryReturnsEmptyScript()
 {
     var q = new SharpQuery();
     TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
 }
Ejemplo n.º 60
0
 public void SimpleCase2()
 {
     var q = new SharpQuery();
     q.Select(q.Case(1).When(1, 2).When(3, 4).When(5, 6).Else(7)).EndStatement();
     TSqlAssert.ScriptsAreEqual(q.ToString(), @"
     SELECT
     ( CASE  @p0
      WHEN  @p0  THEN  @p1
      WHEN  @p2  THEN  @p3
      WHEN  @p4  THEN  @p5
      ELSE  @p6
      END )
     ");
 }