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