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);
}