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