C# SqlBuilder.Select方法代码示例

        public void BuilderSelectClause()
        {
            using (var dbConn = dbFactory.OpenDbConnection())
            using (var dbCmd = dbConn.CreateCommand())
            {
                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);
                    dbCmd.Insert(nU);
                    nU.Id = (int) dbCmd.GetLastInsertId();
                }

                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 = dbCmd.Query<int>(justId.RawSql, justId.Parameters);
                var users = dbCmd.Query<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");
                }
            }
        }

        /// <summary>
        /// GET: /search
        /// GET: /search/index
        /// </summary>
        /// <returns></returns>
        public ActionResult Index(string input, int? page, int? pageSize)
        {
            ViewBag.Input = input;

            page = Math.Max(page ?? 1, 1); // Adjust.
            pageSize = Math.Max(Math.Min(pageSize ?? 10, 100), 10); // Adjust.

            int start = ((page.Value - 1) * pageSize.Value) + 1;
            int finish = page.Value * pageSize.Value;
            var builder = new SqlBuilder();
            SqlBuilder.Template pager = null, counter = null;

            pager = builder.AddTemplate("SELECT * FROM (SELECT /**select**/, ROW_NUMBER() OVER (/**orderby**/) AS RowNumber FROM Questions Q /**where**/) AS Results WHERE RowNumber BETWEEN @start AND @finish ORDER BY RowNumber",
                new { start = start, finish = finish });

            counter = builder.AddTemplate("SELECT COUNT(*) FROM Questions Q /**where**/");

            builder.Select("Q.*");
            builder.OrderBy("Q.Time DESC");
            builder.Where("Q.Title LIKE @searchInput OR Q.Answer LIKE @searchInput", new { searchInput = "%" + input + "%" });

            var results = Current.DB.QueryMultiple(pager.RawSql, pager.Parameters).Read<Question>();
            var totals = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First();

            ViewData["Href"] = "/search?";

            return View(new PagedList<Question>(results, page.Value, pageSize.Value, false, totals));
        }

 public void TestSqlBuilder()
 {
     TestModel model = new TestModel();
     SqlBuilder builder = new SqlBuilder();
     string expected = "SELECT OrderType, OrderNbr FROM SOOrder";
     string result = builder.Select(model).ToString();
     Assert.IsTrue(expected.ToLower().Trim() == result.ToLower().Trim());
 }

        public static void Main()
        {
            using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
            {
                sqlConnection.Open();
                var builder = new SqlBuilder();

                // /**select**/  -- has to be low case
                var selectSupplierIdBuilder = builder.AddTemplate("Select /**select**/ from Suppliers /**where**/ ");
                builder.Select("Id");
                builder.Where("City = @City", new { City = "Tokyo"}); // pass an anonymous object

                var supplierIds = sqlConnection.Query<int>(selectSupplierIdBuilder.RawSql,
                                                           selectSupplierIdBuilder.Parameters);

                ObjectDumper.Write(supplierIds);

                sqlConnection.Close();

            }
        }

        private string BuildGetSchemaQuery()
        {
            if (getSchemaSqlQuery != null) {
                return getSchemaSqlQuery;
            }

            // it's null, so let's build it
            SqlBuilder builder = new SqlBuilder(832);
            builder
                .Select()
                    .Identifiers("Tbls.TABLE_SCHEMA", "Tbls.TABLE_NAME", "Cols.COLUMN_NAME", "cols.DATA_TYPE", "cols.IS_NULLABLE")
                    .Comma()
                    .NestedExpression(e => e
                        .Select().Top(1).Value(1)
                        .From()
                            .Identifier("INFORMATION_SCHEMA.TABLE_CONSTRAINTS").Alias("tc")
                        .LeftJoin()
                            .Identifier("INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE").Alias("ccu").On()
                                .Identifier("tc.CONSTRAINT_NAME").Equals().Identifier("ccu.CONSTRAINT_NAME")
                        .Where()
                            .Identifier("tc.CONSTRAINT_TYPE").Equals().Value("PRIMARY KEY")
                            .And().Identifier("tc.TABLE_SCHEMA").Equals().Identifier("Tbls.TABLE_SCHEMA")
                            .And().Identifier("tc.TABLE_NAME").Equals().Identifier("Tbls.TABLE_NAME")
                            .And().Identifier("ccu.COLUMN_NAME").Equals().Identifier("Cols.COLUMN_NAME")
                    ).Alias("IsPrimaryKey")
                .From()
                    .Identifier("INFORMATION_SCHEMA.COLUMNS").Alias("Cols")
                .LeftJoin()
                    .Identifier("INFORMATION_SCHEMA.TABLES").Alias("Tbls").On()
                        .Identifier("Tbls.TABLE_SCHEMA").Equals().Identifier("Cols.TABLE_SCHEMA")
                        .And().Identifier("Tbls.TABLE_NAME").Equals().Identifier("Cols.TABLE_NAME")
                .Where()
                    .Identifier("Tbls.TABLE_TYPE").Equals().Value("BASE TABLE")
                    .And().Identifier("Tbls.TABLE_NAME").Not().Like().Value("[_][_]%")
                .OrderBy()
                    .Identifier("Tbls.TABLE_NAME")
            ;

            getSchemaSqlQuery = builder.SqlText;

            return getSchemaSqlQuery;
        }

        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(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 ActionResult Index(string sitename, string order_by, string q, int? page, int? pagesize)
        {
            Site = GetSite(sitename);

            if (Site == null)
            {
                return PageNotFound();
            }

            QuerySearchCriteria searchCriteria = new QuerySearchCriteria(q);

            if (string.IsNullOrEmpty(order_by))
            {
                if (searchCriteria.IsValid)
                    order_by = searchCriteria.IsFeatured ? "featured" : "recent";
                else
                    order_by = "featured";
            }

            ViewData["Site"] = Site;
            SelectMenuItem("Queries");
            SetHeader(
                "All Queries",
                new SubHeaderViewData
                {
                    Description = "featured",
                    Title = "Interesting queries selected by the administrators",
                    Href = "/" + sitename + "/queries?order_by=featured",
                    Selected = (order_by == "featured")
                },
                new SubHeaderViewData
                {
                    Description = "recent",
                    Title = "Recently saved queries",
                    Href = "/" + sitename + "/queries?order_by=recent",
                    Selected = (order_by == "recent")
                },
                new SubHeaderViewData
                {
                    Description = "favorite",
                    Title = "Favorite saved queries",
                    Href = "/" + sitename + "/queries?order_by=favorite",
                    Selected = (order_by == "favorite")
                },
                new SubHeaderViewData
                {
                    Description = "popular",
                    Title = "Saved queries with the most views",
                    Href = "/" + sitename + "/queries?order_by=popular",
                    Selected = (order_by == "popular")
                },
                new SubHeaderViewData
                {
                    Description = "everything",
                    Title = "All queries recently executed on the site",
                    Href = "/" + sitename + "/queries?order_by=everything",
                    Selected = (order_by == "everything")
                }
            );

            pagesize = Math.Max(Math.Min(pagesize ?? 50, 100), 10);
            page = Math.Max(page ?? 1, 1);

            int start = ((page.Value - 1) * pagesize.Value) + 1;
            int finish = page.Value * pagesize.Value;
            bool useLatest = true;
            var builder = new SqlBuilder();
            SqlBuilder.Template pager = null, counter = null;

            if (order_by != "everything")
            {
                pager = builder.AddTemplate(@"
                    SELECT
                        *
                    FROM
                        (
                            SELECT
                                /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
                            FROM
                                Metadata metadata
                                /**join**/
                                /**leftjoin**/
                                /**where**/
                        ) AS results
                    WHERE
                        RowNumber BETWEEN @start AND @finish
                    ORDER BY
                        RowNumber",
                    new { start = start, finish = finish }
                );
                counter = builder.AddTemplate("SELECT COUNT(*) FROM Metadata metadata /**join**/ /**leftjoin**/ /**where**/");

                builder.Select("metadata.RevisionId AS Id");
                builder.Select("metadata.LastActivity AS LastRun");
                builder.Join("Queries query ON query.Id = metadata.LastQueryId");
                builder.LeftJoin("Users [user] ON metadata.OwnerId = [user].Id");
                builder.Where("metadata.Hidden = 0");

                if (order_by == "featured" || order_by == "recent")
                {
//.........这里部分代码省略.........

        public ActionResult Show(int id, string name, string order_by, int? page)
        {
            User user = Current.DB.Users.FirstOrDefault(row => row.Id == id);
            if (user == null)
            {
                return PageNotFound();
            }
            // if this user has a display name, and the title is missing or does not match, permanently redirect to it
            if (user.UrlTitle.HasValue() && (string.IsNullOrEmpty(name) || name != user.UrlTitle))
            {
                return PageMovedPermanentlyTo(string.Format("/users/{0}/{1}",user.Id, HtmlUtilities.URLFriendly(user.Login)) + Request.Url.Query);
            }

            DBContext db = Current.DB;

            SetHeader(user.Login);
            SelectMenuItem("Users");

            order_by = order_by ?? "edited";

            ViewData["UserQueryHeaders"] = new SubHeader
            {
                Items = new List<SubHeaderViewData>
                {
                    new SubHeaderViewData
                    {
                        Description = "edited",
                        Title = "Recently edited queries",
                        Href =
                            "/users/" + user.Id + "?order_by=edited",
                        Selected = (order_by == "edited")
                    },
                    new SubHeaderViewData
                    {
                        Description = "favorite",
                        Title = "Favorite queries",
                        Href =
                            "/users/" + user.Id +
                            "?order_by=favorite",
                        Selected = (order_by == "favorite")
                    },
                    new SubHeaderViewData
                    {
                        Description = "recent",
                        Title = "Recently executed queries",
                        Href =
                            "/users/" + user.Id + "?order_by=recent",
                        Selected = (order_by == "recent")
                    }
                }
            };

            page = Math.Max(page ?? 1, 1);
            int? pagesize = 15; // In case we decide to make this a query param

            int start = ((page.Value - 1) * pagesize.Value) + 1;
            int finish = page.Value * pagesize.Value;
            bool useLatest = false;
            string message;
            var builder = new SqlBuilder();
            var pager = builder.AddTemplate(@"
                SELECT
                    *
                FROM
                    (
                        SELECT
                            /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
                        FROM
                            Queries query
                            /**join**/
                            /**leftjoin**/
                            /**where**/
                    ) AS results
                WHERE
                    RowNumber BETWEEN @start AND @finish
                ORDER BY
                    RowNumber",
                new { start = start, finish = finish }
            );
            var counter = builder.AddTemplate("SELECT COUNT(*) FROM Queries query /**join**/ /**leftjoin**/ /**where**/");

            if (order_by == "recent")
            {
                builder.Select("execution.RevisionId AS Id");
                builder.Select("execution.LastRun");
                builder.Select("site.Name AS SiteName");
                builder.Join("QueryExecutions execution ON execution.QueryId = query.Id");
                builder.Join("Sites site ON site.Id = execution.SiteId");
                builder.Join("Revisions ON Revisions.Id = execution.RevisionId AND execution.UserId = @user", new { user = id });
                builder.Join(@"
                    Metadata metadata ON
                    (
                        metadata.RevisionId = Revisions.RootId AND
                        metadata.OwnerId = Revisions.OwnerId
                    ) OR (
                        metadata.RevisionId = Revisions.Id AND
                        metadata.OwnerId = Revisions.OwnerId AND
                        Revisions.RootId IS NULL
                    ) OR (
                        metadata.RevisionId = Revisions.Id AND
//.........这里部分代码省略.........

        public ActionResult Index(string sitename, string order_by, string q, int? page, int? pagesize)
        {
            Site site;

            if (!TryGetSite(sitename, out site))
            {
                return site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/queries",
                    site.TinyName.ToLower()
                ));
            }

            Site = site;
            QuerySearchCriteria searchCriteria = new QuerySearchCriteria(q);

            if (string.IsNullOrEmpty(order_by))
            {
                if (searchCriteria.IsValid)
                {
                    order_by = searchCriteria.IsFeatured ? "featured" : "recent";
                }
                else
                {
                    order_by = CurrentUser.DefaultQuerySort ?? "featured";
                }
            }

            if (!searchCriteria.IsValid)
            {
                CurrentUser.DefaultQuerySort = order_by;
            }

            ViewData["Site"] = Site;
            SelectMenuItem("Queries");

            var pagesizeProvided = pagesize.HasValue;

            if (!Current.User.IsAnonymous && !pagesizeProvided)
            {
                pagesize = Current.User.DefaultQueryPageSize;
            }

            pagesize = Math.Max(Math.Min(pagesize ?? 50, 100), 10);
            page = Math.Max(page ?? 1, 1);

            if (!Current.User.IsAnonymous)
            {
                Current.User.DefaultQueryPageSize = pagesize;
            }

            int start = ((page.Value - 1) * pagesize.Value) + 1;
            int finish = page.Value * pagesize.Value;
            var builder = new SqlBuilder();
            SqlBuilder.Template pager = null, counter = null;

            if (order_by != "everything")
            {
                pager = builder.AddTemplate(@"
                    SELECT
                        *
                    FROM    
                        (
                            SELECT
                                /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
                            FROM
                                QuerySets qs
                                /**join**/
                                /**leftjoin**/
                                /**where**/
                        ) AS results
                    WHERE
                        RowNumber BETWEEN @start AND @finish
                    ORDER BY
                        RowNumber",
                    new { start = start, finish = finish }
                );
                counter = builder.AddTemplate("SELECT COUNT(*) FROM QuerySets qs /**join**/ /**leftjoin**/ /**where**/");

                builder.Select("qs.Id as QuerySetId");
                builder.Select("qs.LastActivity AS LastRun");
                builder.Join("Revisions r ON r.Id = qs.CurrentRevisionId");
                builder.Join("Queries q ON q.Id = r.QueryId");
                builder.LeftJoin("Users u ON qs.OwnerId = u.Id");
                builder.Where("qs.Hidden = 0");
                builder.Where("qs.Title is not null");
                builder.Where("qs.Title <> ''");

                if (order_by == "featured" || order_by == "recent")
                {
                    if (order_by == "featured")
                    {
                        builder.Where("qs.Featured = 1");
                        builder.OrderBy("qs.Votes DESC");
                    }

                    builder.OrderBy("qs.LastActivity DESC");
                }
                else
                {
                    int threshold = 0;

//.........这里部分代码省略.........

        private PagedList<User> GetUserList(string selected, int? page, string search)
        {
            int perPage = 36;
            int currentPage = Math.Max(page ?? 1, 1);
            var builder = new SqlBuilder();
            var pager = builder.AddTemplate(@"
                SELECT
                    *,
                    (SELECT COUNT(*) FROM QuerySets WHERE OwnerId = Y.Id) SavedQueriesCount,
                    (SELECT COUNT(*) FROM RevisionExecutions WHERE UserId = Y.Id) QueryExecutionsCount
                FROM
                (
                    SELECT * FROM
                    (
                        SELECT
                            ROW_NUMBER() OVER (/**orderby**/) AS Row, Users.Id, Users.Login, Users.Email,
                            Users.IPAddress, Users.IsAdmin, Users.CreationDate /**select**/
                        FROM Users /**join**/ /**where**/
                    ) AS X
                    WHERE Row > @start AND Row <= @finish
                ) AS Y
                ORDER BY Row ASC",
                new { start = (currentPage - 1) * perPage, finish = currentPage * perPage }
            );
            var counter = builder.AddTemplate("SELECT COUNT(*) FROM Users /**join**/ /**where**/");

            if (selected == "all")
            {
                builder.OrderBy("Login ASC");
            }
            else
            {
                var activePeriod = 30; // Last 30 days

                // We should probably just be...actually recording the user's LastActivityDate,
                // instead of performing this join all the time
                builder.Select(", LastRun AS LastActivityDate");
                builder.Join(@"(
                    SELECT UserId, MAX(LastRun) AS LastRun FROM RevisionExecutions GROUP BY UserId
                ) AS LastExecutions ON LastExecutions.UserId = Users.Id");
                builder.OrderBy("LastRun DESC");
                builder.Where("LastRun >= @since", new { since = DateTime.UtcNow.AddDays(-activePeriod).Date });
            }

            var url = "/users?order_by=" + selected;
            ViewData["SearchHref"] = "/users/search?order_by=" + selected;

            if (search.HasValue() && search.Length > 2)
            {
                url += "&search=" + HtmlUtilities.UrlEncode(search);
                ViewData["UserSearch"] = search;

                builder.Where("Login LIKE @search", new { search = '%' + search + '%' });
            }

            ViewData["Href"] = url;

            var rows = Current.DB.Query<User>(pager.RawSql, pager.Parameters);
            var total = Current.DB.Query<int>(counter.RawSql, counter.Parameters).First();
            var users = new PagedList<User>(rows, currentPage, perPage, false, total);

            return users;
        }

        public ActionResult Show(int id, string name, string order_by, int? page)
        {
            User user = !Current.User.IsAnonymous && Current.User.Id == id ? Current.User : Current.DB.Users.Get(id);

            if (user == null)
            {
                return PageNotFound();
            }

            // if this user has a display name, and the title is missing or does not match, permanently redirect to it
            if (user.UrlTitle.HasValue() && (string.IsNullOrEmpty(name) || name != user.UrlTitle))
            {
                return PageMovedPermanentlyTo(string.Format("/users/{0}/{1}",user.Id, HtmlUtilities.URLFriendly(user.Login)) + Request.Url.Query);
            }

            DataExplorerDatabase db = Current.DB;

            SetHeader(user.Login);
            SelectMenuItem("Users");

            var profileTabs = new SubHeader
            {
                Selected = order_by,
                Items = new List<SubHeaderViewData>
                {
                    new SubHeaderViewData
                    {
                        Description = "edited",
                        Title = "Recently edited queries",
                        Href = "/users/" + user.Id + "?order_by=edited",
                        Default = true,
                    },
                    new SubHeaderViewData
                    {
                        Description = "favorite",
                        Title = "Favorite queries",
                        Href = "/users/" + user.Id + "?order_by=favorite"
                    },
                    new SubHeaderViewData
                    {
                        Description = "recent",
                        Title = "Recently executed queries",
                        Href = "/users/" + user.Id + "?order_by=recent"
                    }
                }
            };
            ViewData["UserQueryHeaders"] = profileTabs;

            page = Math.Max(page ?? 1, 1);
            int? pagesize = 15; // In case we decide to make this a query param

            int start = ((page.Value - 1) * pagesize.Value) + 1;
            int finish = page.Value * pagesize.Value;
            string message;
            var builder = new SqlBuilder();
            var pager = builder.AddTemplate(@"
                SELECT
                    *
                FROM
                    (
                        SELECT
                            /**select**/, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
                        FROM
                            Queries q
                            /**join**/
                            /**leftjoin**/
                            /**where**/
                    ) AS results
                WHERE
                    RowNumber BETWEEN @start AND @finish
                ORDER BY
                    RowNumber",
                new { start = start, finish = finish }
            );
            var counter = builder.AddTemplate("SELECT COUNT(*) FROM Queries q /**join**/ /**leftjoin**/ /**where**/");

            if (order_by == "recent")
            {
                builder.Select("re.RevisionId AS Id");
                builder.Select("re.LastRun");
                builder.Select("s.Name AS SiteName");
                builder.Where("re.UserId = @user", new { user = id });
                builder.Join("Revisions r ON r.QueryId = q.Id");
                builder.Join("RevisionExecutions re ON re.RevisionId = r.Id");
                builder.Join("Sites s ON s.Id = re.SiteId");
                builder.Join(@"QuerySets qs ON qs.Id = r.OriginalQuerySetId");
                builder.OrderBy("re.LastRun DESC");

                message = user.Id == CurrentUser.Id ?
                    "You have never ran any queries" : "No queries ran recently";
            }
            else
            {
                builder.Select("qs.CurrentRevisionId AS Id");
                builder.Select("qs.LastActivity AS LastRun");
                builder.Join("Revisions r on r.QueryId = q.Id");
                builder.Join("QuerySets qs on qs.CurrentRevisionId = r.Id");

                if (order_by == "favorite")
                {
//.........这里部分代码省略.........