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(); }
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 "); }
public void BetweenMustThrowOnNullArgument3() { var q = new SharpQuery(); var expr = (Expression)null; Assert.Throws <ArgumentNullException>(() => q.Select(1).Where(expr.IsBetween(1, 2))); }
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 } } }
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; "); }
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); } } }
public void NotEndedStatementsShouldNotBeAddedToScript1() { var q = new SharpQuery(); q.Select(1); TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString()); }
public void EndedStatementsShouldBeAddedToScript() { var q = new SharpQuery(); q.Select(1).EndStatement(); var script = q.ToString(); TSqlAssert.ScriptsAreEqual(script, "SELECT @p0"); }
public static string GetSpecialDirectSites(string phrase) { var sq = new SharpQuery(GetHtml(yandex.FormatWith(phrase))); var sites = GetSites(sq); return(sites.JoinWith(",")); }
// 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 } } }
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); }
public void Test1() { var q = new SharpQuery(); q.Select(1).EndStatement(); var query = q.ToString(); Assert.That(q.ScriptBuilder.Parameters.Count, Is.EqualTo(1)); }
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) "); }
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) "); }
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 "); }
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 "); }
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) "); }
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 "); }
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); }
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) "); }
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)"); }
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); "); }
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) "); }
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); }
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); }
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); }
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) "); }
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) "); }
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); }
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(); }
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()); }
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()); }
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] "); }
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 ) "); }
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) "); }
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 ) "); }
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 ) "); }
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 ) "); }
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 "); }
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) "); }
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]"); }
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) "); }
public void EmptyQueryReturnsEmptyScript() { var q = new SharpQuery(); TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString()); }
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 ) "); }