public void TestSqlBuilder() { DataService.ConnectionString = "test.sqlite"; DataService.Initialize(true); using (var db = DataService.Connect()) { string releaseValue = "v3.8"; SqlBuilder sb = new SqlBuilder(); sb.Select("*"); sb.Select("u1.Email AS AssignedToUserEmail, u1.UserName AS AssignedToUserName"); sb.Select("u2.Email AS CreatedByUserEmail, u2.UserName AS CreatedByUserName"); sb.Join(" User AS u1 ON Issue.AssignedToUserId=u1.Id"); sb.Join(" User AS u2 ON Issue.CreatedByUserId=u2.Id"); sb.Where("Release=@a", new { a = releaseValue }); sb.Where("Votes>@b", new { b = 3}); sb.OrderBy("Issue.Id"); var t = new SqlBuilder.Template(sb, @"SELECT /**select**/ FROM Issue /**join**/ /**leftjoin**/ /**where**/ /**orderby**/", null); var iss = db.Query<IssueViewModel>(t.RawSql, t.Parameters); string sql = db.GetLastSql(); Assert.NotEmpty(iss); Assert.Equal("*****@*****.**", iss[0].AssignedToUserEmail); Assert.Equal("*****@*****.**", iss[0].CreatedByUserEmail); Assert.Equal("This is the title.", iss[0].Title); //var jsb = new JoinSqlBuilder<CompleteIssue, Issue>(); //jsb = jsb.Join<Issue, User>(i => i.AssignedToUserId, u => u.Id) // .Join<Issue, User>(i => i.CreatedByUserId, x => x.Id) // .Where<Issue>(i => i.Release == releaseValue); //string foo = jsb.ToSql(); //var issues = db.Query<CompleteIssue>(foo); //string sql2 = db.GetLastSql(); //Assert.NotEmpty(issues); } }
private static void ProcessSearch(string search, SqlBuilder sql, Dictionary<string, object> parameters) { sql.Join(" FullTextSearchIssue ON Issue.Id=FullTextSearchIssue.docid"); sql.Where("FullTextSearchIssue MATCH @search"); parameters.Add("search", search); }
public static bool TryGetIssueWithCommentsUsingDb(long issueId, IDbConnection db, out IssueViewModel issue) { SqlBuilder sql = new SqlBuilder(); sql.Where("Issue.Id=@id", new { id = issueId }); var issueTemplate = sql.AddTemplate(IssueSqlQueryTemplate); issue = db.Query<IssueViewModel>(issueTemplate.RawSql, issueTemplate.Parameters).SingleOrDefault(); if (issue != null) { issue.Comments = CommentsForIssueUsingDb(issue.Id, db); QueryService.AssignAreasReleasesAndTypesToIssue(issue); } return issue != null; }
private static void ProcessFilter(Guid userGuid, QueryFilterColumn filter, SqlBuilder sql, Dictionary<string, object> parameters) { string column; if (filter.Column.Equals("user", StringComparison.OrdinalIgnoreCase)) { User user; if (TryGetUser(userGuid, filter.Value, out user)) { string parameterName = "p" + parameters.Keys.Count; sql.Where(String.Format("(u1.Guid=@{0} OR u2.Guid=@{0})", parameterName)); parameters.Add(parameterName, user.Guid.ToString("N")); } } else if (AllowedColumns.TryGetValue(filter.Column, out column)) { string parameterName = "p" + parameters.Keys.Count; sql.Where(column + "=@" + parameterName); parameters.Add(parameterName, filter.Value); } }
public static List<IssueCommentViewModel> CommentsForIssueUsingDb(int issueId, IDbConnection db) { SqlBuilder sql = new SqlBuilder(); sql.Where("IssueId=@issueId", new { issueId = issueId }); var commentTemplate = sql.AddTemplate(CommentSqlQueryTemplate); return db.Query<IssueCommentViewModel>(commentTemplate.RawSql, commentTemplate.Parameters); }