public void BuilderSelectClause() { using (var connection = GetOpenConnection()) { var rand = new Random(8675309); var data = new List<User>(); for (int i = 0; i < 100; i++) { var nU = new User { Age = rand.Next(70), Id = i, Name = Guid.NewGuid().ToString() }; data.Add(nU); nU.Id = (int)connection.Insert<User>(nU); } var builder = new SqlBuilder(); var justId = builder.AddTemplate("SELECT /**select**/ FROM Users"); var all = builder.AddTemplate("SELECT Name, /**select**/, Age FROM Users"); builder.Select("Id"); var ids = connection.Query<int>(justId.RawSql, justId.Parameters); var users = connection.Query<User>(all.RawSql, all.Parameters); foreach (var u in data) { if (!ids.Any(i => u.Id == i)) throw new Exception("Missing ids in select"); if (!users.Any(a => a.Id == u.Id && a.Name == u.Name && a.Age == u.Age)) throw new Exception("Missing users in select"); } } }
public void BuilderSelectClause() { var rand = new Random(8675309); var data = new List<User>(); for (var i = 0; i < 100; i++) { var nU = new User { Age = rand.Next(70), Id = i, Name = Guid.NewGuid().ToString() }; data.Add(nU); nU.Id = (int) db.Insert(nU, selectIdentity: true); } var builder = new SqlBuilder(); var justId = builder.AddTemplate("SELECT /**select**/ FROM Users"); var all = builder.AddTemplate("SELECT /**select**/, Name, Age FROM Users"); builder.Select("Id"); var ids = db.Column<int>(justId.RawSql, justId.Parameters); var users = db.Select<User>(all.RawSql, all.Parameters); foreach (var u in data) { Assert.That(ids.Any(i => u.Id == i), "Missing ids in select"); Assert.That(users.Any(a => a.Id == u.Id && a.Name == u.Name && a.Age == u.Age), "Missing users in select"); } }
public void BuilderTemplateWOComposition() { var builder = new SqlBuilder(); var template = builder.AddTemplate("SELECT COUNT(*) FROM Users WHERE Age = @age", new { age = 5 }); if (template.RawSql == null) throw new Exception("RawSql null"); if (template.Parameters == null) throw new Exception("Parameters null"); db.Insert(new User { Age = 5, Name = "Testy McTestington" }); Assert.That(db.Scalar<int>(template.RawSql, template.Parameters), Is.EqualTo(1)); }
public void BuilderTemplateWOComposition() { SuppressIfOracle("Oracle provider is not smart enough to replace '@' parameter delimiter with ':'"); var builder = new SqlBuilder(); var template = builder.AddTemplate("SELECT COUNT(*) FROM Users WHERE Age = @age", new { age = 5 }); if (template.RawSql == null) throw new Exception("RawSql null"); if (template.Parameters == null) throw new Exception("Parameters null"); db.Insert(new User { Age = 5, Name = "Testy McTestington" }); Assert.That(db.Scalar<int>(template.RawSql, template.Parameters), Is.EqualTo(1)); }
public void BuilderTemplateWithoutComposition() { var builder = new SqlBuilder(); var template = builder.AddTemplate("SELECT COUNT(*) FROM Users WHERE Age = @age", new { age = 5 }); if (template.RawSql == null) throw new Exception("RawSql null"); if (template.Parameters == null) throw new Exception("Parameters null"); using (var connection = GetOpenConnection()) { connection.DeleteAll<User>(); connection.Insert(new User { Age = 5, Name = "Testy McTestington" }); if (connection.Query<int>(template.RawSql, template.Parameters).Single() != 1) throw new Exception("Query failed"); } }
public async Task BuilderTemplateWOCompositionAsync() { var builder = new SqlBuilder(); var template = builder.AddTemplate("SELECT COUNT(*) FROM Users WHERE Age = @age", new { age = 5 }); if (template.RawSql == null) throw new Exception("RawSql null"); if (template.Parameters == null) throw new Exception("Parameters null"); using (var connection = GetOpenConnection()) { await connection.DeleteAllAsync<User>(); await connection.InsertAsync(new User { Age = 5, Name = "Testy McTestington" }); if ((await connection.QueryAsync<int>(template.RawSql, template.Parameters)).Single() != 1) throw new Exception("Query failed"); } }
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; }
public static QueriedIssuesViewModel QueryIssues(Guid userGuid, Query query) { SqlBuilder sql = new SqlBuilder(); Dictionary<string, object> parameters = new Dictionary<string, object>(); if (query.Filters != null) { foreach (var filter in query.Filters) { ProcessFilter(userGuid, filter, sql, parameters); } } else { ProcessFilter(userGuid, new QueryFilterColumn("status:Open"), sql, parameters); } if (query.Sorts != null) { foreach (var sort in query.Sorts) { ProcessSort(sort, sql, parameters); } } else { ProcessSort(new QuerySortColumn("id:desc"), sql, parameters); } if (query.Searches != null) { ProcessSearch(String.Join(" ", query.Searches), sql, parameters); } // Prepare the SQL statements. var countTemplate = sql.AddTemplate(IssueCountSqlQueryTemplate); var issuesTemplate = sql.AddTemplate(IssueSqlQueryTemplate); string rawSql = issuesTemplate.RawSql + " LIMIT " + query.Count; if (query.Page > 1) { rawSql += " OFFSET " + (query.Page - 1) * query.Count; } using (var db = DataService.Connect()) { int total = db.SqlScalar<int>(countTemplate.RawSql, parameters); return new QueriedIssuesViewModel() { Issues = db.Query<IssueViewModel>(rawSql, parameters), Total = total, }; } }
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); }