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");
            }
        }
Пример #2
0
        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");
                }
            }
        }
Пример #3
0
        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);
            }
        }