本文整理匯總了C#中SqlBuilder.OrderBy方法的典型用法代碼示例。如果您正苦於以下問題:C# SqlBuilder.OrderBy方法的具體用法?C# SqlBuilder.OrderBy怎麽用?C# SqlBuilder.OrderBy使用的例子?那麽, 這裏精選的方法代碼示例或許可以為您提供幫助。您也可以進一步了解該方法所在類SqlBuilder
的用法示例。
在下文中一共展示了SqlBuilder.OrderBy方法的9個代碼示例,這些例子默認根據受歡迎程度排序。您可以為喜歡或者感覺有用的代碼點讚,您的評價將有助於係統推薦出更棒的C#代碼示例。
示例1: List
/// <summary>
/// The list of results.
/// </summary>
/// <param name="maxResults">The max results.</param>
/// <param name="start">The start</param>
/// <param name="finish">The finish</param>
/// <param name="orderBy">The order by.</param>
/// <returns>The result set</returns>
public override IEnumerable<Guid> List(
int maxResults,
DateTime? start = null,
DateTime? finish = null,
ListResultsOrder orderBy = ListResultsOrder.Descending)
{
var builder = new SqlBuilder();
var t = builder.AddTemplate("select Id from MiniProfilers /**where**/ /**orderby**/ LIMIT(" + maxResults + ")");
if (start != null)
{
builder.Where("Started > @start", new { start });
}
if (finish != null)
{
builder.Where("Started < @finish", new { finish });
}
builder.OrderBy(orderBy == ListResultsOrder.Descending ? "Started desc" : "Started asc");
using (var conn = GetOpenConnection())
{
return conn.Query<Guid>(t.RawSql, t.Parameters).ToList();
}
}
示例2: Index
/// <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));
}
示例3: Index
//.........這裏部分代碼省略.........
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")
{
if (order_by == "featured")
{
builder.Where("metadata.Featured = 1");
builder.Where("metadata.First = 1");
builder.OrderBy("metadata.Votes DESC");
}
builder.OrderBy("metadata.LastActivity DESC");
}
else
{
int threshold = 0;
if (order_by == "popular")
{
builder.Where("metadata.Views > @threshold", new { threshold = threshold });
builder.OrderBy("metadata.Views DESC");
builder.OrderBy("metadata.Votes DESC");
}
else
{
builder.Where("metadata.Votes > @threshold", new { threshold = threshold });
builder.OrderBy("metadata.Votes DESC");
builder.OrderBy("metadata.Views DESC");
}
}
}
else if (order_by == "everything")
{
pager = builder.AddTemplate(@"
SELECT
/**select**/
FROM
(
SELECT
Revisions.*, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber
FROM
示例4: Show
//.........這裏部分代碼省略.........
/**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
metadata.OwnerId IS NULL AND
Revisions.OwnerId IS NULL
)"
);
builder.OrderBy("execution.LastRun DESC");
message = user.Id == CurrentUser.Id ?
"You have never ran any queries" : "No queries ran recently";
}
else
{
builder.Select("metadata.RevisionId AS Id");
builder.Select("metadata.LastActivity AS LastRun");
builder.Join("Metadata metadata on metadata.LastQueryId = query.Id");
if (order_by == "favorite")
{
builder.Join(@"
Votes ON
Votes.RootId = metadata.RevisionId AND
(
Votes.OwnerId = metadata.OwnerId OR
(Votes.OwnerId IS NULL AND metadata.OwnerID IS NULL)
) AND
Votes.UserId = @user AND
Votes.VoteTypeId = @vote",
new { user = id, vote = (int)VoteType.Favorite }
);
builder.OrderBy("metadata.Votes DESC");
useLatest = true;
message = user.Id == CurrentUser.Id ?
"You have no favorite queries, click the star icon on a query to favorite it" : "No favorites";
} else {
builder.Where("metadata.OwnerId = @user", new { user = id });
builder.Where("metadata.Hidden = 0");
builder.OrderBy("metadata.LastActivity DESC");
示例5: Index
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;
//.........這裏部分代碼省略.........
示例6: FindDinners
private PagedList<Dinner> FindDinners(string where, object parameters, string orderBy = "DinnerID", int page = 1, int pageSize = 20)
{
using (var connection = MvcApplication.GetOpenConnection())
{
var builder = new SqlBuilder();
var start = (page - 1) * pageSize + 1;
var finish = page * pageSize;
var selectTemplate = builder.AddTemplate(pagedQuery, new { start, finish });
var countTemplate = builder.AddTemplate(totalQuery);
builder.Where(where, parameters);
builder.OrderBy(orderBy);
var results = connection.Query<Dinner>(selectTemplate.RawSql, selectTemplate.Parameters);
var count = connection.Query<int>(countTemplate.RawSql, countTemplate.Parameters).First();
return new PagedList<Dinner>(results, page, count, pageSize);
}
}
示例7: GetUserList
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;
}
示例8: Show
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")
{
//.........這裏部分代碼省略.........
示例9: CanUseWithBuilder
public void CanUseWithBuilder()
{
var sorts = SortExpression.Parse("This, That descending, [The Other]");
var b = new SqlBuilder();
b.OrderBy(sorts);
var t = b.AddTemplate("{{ORDERBY}}");
Assert.AreEqual("ORDER BY This ASC , That DESC , [The Other] ASC\r\n", t.RawSql);
}