public async Task <(List <ApplicationUser> users, int count)> GetUsersAsync(UserRequestParameters parameters)
{
int skipCount = (parameters.Page - 1) * parameters.PageSize;
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT AspNetUsers.Id) FROM AspNetUsers
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate(
$@"SELECT * FROM
(SELECT DISTINCT AspNetUsers.* FROM AspNetUsers
/**where**/ /**orderby**/) AS FilteredUsers");
sqlBuilder.Where($"AspNetUsers.UserName != '{AdminCredentials.UserName}'");
if (!string.IsNullOrWhiteSpace(parameters.KeyWord))
{
parameters.KeyWord = parameters.KeyWord.ToLower();
sqlBuilder.Where($@"LOWER(AspNetUsers.FirstName + ' ' + AspNetUsers.LastName + ' ' + AspNetUsers.UserName + ' ' + AspNetUsers.Email) LIKE '%' + @KeyWord + '%'");
}
int count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
sqlBuilder.OrderBy($"AspNetUsers.CreationDate DESC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
List <ApplicationUser> response = (await _connection.QueryAsync <ApplicationUser>(itemsExpression.RawSql, parameters)).ToList();
return(response, count);
}
private string GetGetQuery(ProductSearchFilter filter, out object parameters)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template sqlTemplate = sqlBuilder.AddTemplate(GetQuery);
if (!string.IsNullOrWhiteSpace(filter.Name))
{
sqlBuilder.Where("CDG.Name LIKE @Name", new { Name = $"%{filter.Name}%" });
}
if (filter.UpdatedOnStart.HasValue)
{
sqlBuilder.Where("CD.UpdatedOn >= @UpdatedOnStart", new { filter.UpdatedOnStart });
}
if (filter.UpdatedOnEnd.HasValue)
{
sqlBuilder.Where("CD.UpdatedOn <= @UpdatedOnEnd", new { filter.UpdatedOnEnd });
}
// Return all parameters to be reused (passed to Dapper exec/query method)
parameters = sqlTemplate.Parameters;
return(sqlTemplate.RawSql);
}
/// <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)));
}
/// <summary>
/// 获取数据记录数
/// </summary>
/// <param name="connectionString">数据库连接信息</param>
/// <param name="builder"></param>
/// <param name="template"></param>
/// <returns>the list of query result.</returns>
protected int GetCount(string connectionString, SqlBuilder builder, SqlBuilder.Template template)
{
var sql = template.RawSql;
var param = template.Parameters;
return(GetCount(connectionString, sql, param));
}
//Dapper.SQLBuilder Query using template
private static List <Drawing> QueryDatabase(SqlBuilder.Template template)
{
using (IDbConnection connection = new SQLiteConnection(LoadConnectionString()))
{
return(connection.Query <Drawing>(template.RawSql, template.Parameters).ToList());
}
}
private void Init()
{
Builder.Clear();
SimpleTemplate = Builder.AddTemplate(GetSimpleSql());
PaginateTemplate = Builder.AddTemplate(GetPaginateSql(), new { Criteria.Skip, Criteria.Take });
CountTemplate = Builder.AddTemplate(GetCountSql());
ExistsTemplate = Builder.AddTemplate(GetExistsSql());
}
public (string BuiltQuery, object BuiltParams) Build()
{
var retVal = (sqlTemplate.RawSql, sqlTemplate.Parameters);
sqlBuilder = null;
sqlTemplate = null;
return(retVal);
}
//Load all drawings from DB to list of Drawing objects
public static List <Drawing> LoadAllDrawings()
{
SqlBuilder builder = new Dapper.SqlBuilder();
SqlBuilder.Template select = builder.AddTemplate("SELECT * FROM Drawings");
return(QueryDatabase(select));
}
public virtual void Remove(SqlBuilder.Template template)
{
if (template == null)
{
return;
}
this.Db.Execute(template);
}
public async Task <(List <AuthorWithNestedObjects>, int)> GetWithParamsAsync(AuthorRequestParameters parameters)
{
int count = default(int);
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT Authors.Id) FROM Authors
LEFT JOIN AuthorInBooks ON Authors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate($@"SELECT * FROM
(SELECT DISTINCT Authors.* FROM Authors
LEFT JOIN AuthorInBooks ON Authors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
/**where**/ /**orderby**/) AS FilteredAuthors
LEFT JOIN AuthorInBooks ON FilteredAuthors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId");
if (!string.IsNullOrWhiteSpace(parameters.Name))
{
parameters.Name = parameters.Name.ToLower();
sqlBuilder.Where($@"LOWER(Authors.Name) LIKE '%' + @Name + '%'");
}
if (parameters.IgnoreAuthorsList.Any())
{
sqlBuilder.Where($"Authors.Id NOT IN @IgnoreAuthorsList");
}
count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
if (parameters.WithPagination)
{
sqlBuilder.OrderBy($"Authors.Name OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
Dictionary <int, AuthorWithNestedObjects> authorWithNestedObjectsDictionary = new Dictionary <int, AuthorWithNestedObjects>();
IEnumerable <AuthorWithNestedObjects> response = await _connection.QueryAsync <Author, AuthorInBook, PrintedEdition, AuthorWithNestedObjects>(itemsExpression.RawSql,
(author, authorInBook, printedEdition) =>
{
bool isExist = authorWithNestedObjectsDictionary.TryGetValue(author.Id, out AuthorWithNestedObjects authorWithNestedObjects);
if (!isExist)
{
authorWithNestedObjects = new AuthorWithNestedObjects();
authorWithNestedObjects.Author = author;
authorWithNestedObjectsDictionary.Add(author.Id, authorWithNestedObjects);
}
if (!(authorInBook is null))
{
authorInBook.PrintedEdition = printedEdition;
authorWithNestedObjects.AuthorInBooks.Add(authorInBook);
}
return(authorWithNestedObjects);
}, parameters);
public async Task <List <PrintedEdition> > GetRangeByIdAsync(List <int> printedEditionIds)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate($@"SELECT * FROM PrintedEditions
WHERE PrintedEditions.Id IN @Ids");
List <PrintedEdition> printedEditions = (await _connection.QueryAsync <PrintedEdition>(itemsExpression.RawSql, new { Ids = printedEditionIds })).ToList();
return(printedEditions);
}
public virtual bool IsExists(SqlBuilder.Template template)
{
var result = false;
var num = GetCount(template);
if (num >= 1)
{
result = true;
}
return(result);
}
private IEnumerable <T> Query <T>(SqlBuilder.Template template)
{
string sql = template.RawSql;
var parameters = template.Parameters;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
return(conn.Query <T>(sql, parameters).ToList());
}
}
/// <summary>
/// Builds a query wrapper to retrieve a paged result set and it's total row count
/// </summary>
/// <param name="sqlQuery">The get sql query to be paged</param>
/// <param name="orderByClauseMembers">The sql ORDER BY clause value. <example>Id DESC, Name ASC</example></param>
/// <param name="rowOffset">The rows to skip when retrieving the result set</param>
/// <param name="pageSize">The number of rows to retrive in the data set</param>
/// <returns>The specified page rows, ordered</returns>
public virtual string PagedQueryWithTotals(string sqlQuery, int rowOffset, int pageSize, string orderByClauseMembers = "Id")
{
SqlBuilder sqlBuilder = new SqlBuilder();
if (!string.IsNullOrWhiteSpace(orderByClauseMembers))
{
string[] orderColumns = orderByClauseMembers.Split(',', StringSplitOptions.RemoveEmptyEntries);
string orderBy = string.Join(",", orderColumns.Select(c => $"Main_Q.{c}"));
sqlBuilder.OrderBy(orderBy);
}
SqlBuilder.Template sqlTemplate = sqlBuilder.AddTemplate(string.Format(CtePagingAndTotalCountQueryTemplate, sqlQuery, orderByClauseMembers, rowOffset, pageSize));
return(sqlTemplate.RawSql);
}
/// <summary>
/// Fetches the roles that the supplied <see cref="User"/> is assigned to.
/// </summary>
/// <param name="userId">The user id to use when searching for roles.</param>
/// <returns></returns>
public ICollection <Role> GetUserRoles(int userId)
{
var builder = new SqlBuilder();
SqlBuilder.Template tmpl = builder.AddTemplate(@"
SELECT r.Id, r.Name
FROM UserRoles ur
/**join**/
/**where**/"
);
builder.Join("Roles AS r ON r.Id = ur.RoleId");
builder.Where("ur.UserId = @userId");
return(Current.DB.Query <Role>(tmpl.RawSql, new { userId }).ToList());
}
public List <T> QueryBy(Dictionary <string, object> parameters)
{
SqlBuilder builder = new SqlBuilder();
string query = $"SELECT * FROM {typeof(T).Name} /**where**/ ";
SqlBuilder.Template template = builder.AddTemplate(query);
DynamicParameters dynamicParams = new DynamicParameters();
foreach (var parameter in parameters)
{
builder.Where($"{parameter.Key} = @{parameter.Key}");
dynamicParams.Add(parameter.Key, parameter.Value);
}
string rawSql = template.RawSql;
return(_sqlConnection.Query <T>(rawSql, dynamicParams).AsList());
}
//Load from filter using Dapper.SQLBuilder to combine where clauses
public static List <Drawing> LoadDrawings(string filter, string starts, string group)
{
SqlBuilder builder = new Dapper.SqlBuilder();
SqlBuilder.Template select = builder.AddTemplate("SELECT * FROM Drawings /**where**/");
if (!string.IsNullOrEmpty(filter))
{
builder.Where($"\"File\" like '{starts}{filter}%'");
}
if (!string.IsNullOrEmpty(group))
{
builder.Where($"\"Group\" like '{group}'");
}
Log.Write.Info(select.RawSql.TrimEnd());
return(QueryDatabase(select));
}
public virtual string PagedQuery(string sqlQuery, ISearchFilter filter, ref object param)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template sqlTemplate = sqlBuilder.AddTemplate(string.Format(PagingQueryTemplate, sqlQuery, filter.Offset, filter.PageSize));
if (!string.IsNullOrWhiteSpace(filter.SqlOrderByExpression))
{
sqlBuilder.OrderBy(filter.SqlOrderByExpression);
}
if (!filter.IncludeMetadata)
{
return(sqlTemplate.RawSql);
}
string totalCountQuery = SqlSelectClauseRegex.Replace(sqlQuery, m => $"{m.Groups[1]} {SqlTotalCountSelectClauseValue} {m.Groups[3]}", 1);
return($"{sqlTemplate.RawSql}{Environment.NewLine};{totalCountQuery}");
}
public async Task <List <Order> > GetRangeByUserId(int id)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template sqlExpression = sqlBuilder.AddTemplate(
$@"SELECT * FROM
(SELECT DISTINCT Orders.* FROM Orders
LEFT JOIN AspNetUsers ON Orders.ApplicationUserId = AspNetUsers.Id
WHERE AspNetUsers.Id = @Id) AS FilteredOrders
LEFT JOIN AspNetUsers ON FilteredOrders.ApplicationUserId = AspNetUsers.Id");
List <Order> result = await _connection.QueryAsync <Order, ApplicationUser, Order>(sqlExpression.RawSql,
(order, applicationUser) =>
{
order.ApplicationUser = applicationUser;
return(order);
},
new { Id = id }) as List <Order>;
return(result);
}
/// <summary>
/// 获取查询sql模板
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dataBase"></param>
/// <returns></returns>
public SqlBuilder.Template Build <TEntity>(IDatabase dataBase)
{
if (sqlBuilder == null)
{
throw new ArgumentNullException("SearchSqlBuilder未初始化,获取查询模板失败：" + nameof(sqlBuilder));
}
SqlBuilder.Template _sqlTmpl = null;
if (!string.IsNullOrWhiteSpace(SqlTemplate))
{
_sqlTmpl = sqlBuilder.AddTemplate(SqlTemplate);
}
else
{
_sqlTmpl = sqlBuilder.DefaultTemplete <TEntity>(dataBase);
}
if (QueryConditionAct != null)
{
QueryConditionAct(sqlBuilder);
}
return(_sqlTmpl);
}
public async Task <(List <Order>, int)> GetByParmeters(OrderRequestParameters parameters)
{
int skipCount = (parameters.Page - 1) * parameters.PageSize;
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT Orders.Id) FROM Orders
LEFT JOIN AspNetUsers ON Orders.ApplicationUserId = AspNetUsers.Id
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate(
$@"SELECT * FROM
(SELECT DISTINCT Orders.* FROM Orders
LEFT JOIN AspNetUsers ON Orders.ApplicationUserId = AspNetUsers.Id
/**where**/ /**orderby**/) AS FilteredOrders
LEFT JOIN AspNetUsers ON FilteredOrders.ApplicationUserId = AspNetUsers.Id
WHERE FilteredOrders.ApplicationUserId NOT IN (SELECT AspNetUsers.Id FROM AspNetUsers
WHERE AspNetUsers.IsRemoved = 1)");
if (!string.IsNullOrWhiteSpace(parameters.KeyWord))
{
parameters.KeyWord = parameters.KeyWord.ToLower();
sqlBuilder.Where($@"LOWER(AspNetUsers.FirstName + ' ' + AspNetUsers.FirstName + ' ' + AspNetUsers.UserName ) LIKE '%' + @KeyWord + '%'");
}
int count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
sqlBuilder.OrderBy($"Orders.Id OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
List <Order> result = await _connection.QueryAsync <Order, ApplicationUser, Order>(itemsExpression.RawSql,
(order, applicationUser) =>
{
order.ApplicationUser = applicationUser;
return(order);
},
parameters) as List <Order>;
return(result, count);
}
public virtual string PagedQuery(string fullSqlQuery, ISearchFilter filter, ref object param)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template sqlTemplate = sqlBuilder.AddTemplate(string.Format(PagingQueryTemplate, fullSqlQuery, filter.Offset, filter.PageSize));
if (!string.IsNullOrWhiteSpace(filter.SqlOrderByExpression))
{
sqlBuilder.OrderBy(filter.SqlOrderByExpression);
}
if (!filter.IncludeMetadata)
{
return(sqlTemplate.RawSql);
}
string totalCountQuery = SqlSelectClauseRegex.Replace(fullSqlQuery, m => $"{m.Groups[1]} {SqlTotalCountSelectClauseValue} {m.Groups[3]}", 1);
string[] cursorsName = new string[] { "main_cursor", "counter_cursor" };
if (param == null)
{
param = new OracleDynamicParameters(cursorsName);
}
else
{
OracleDynamicParameters oracleParam = new OracleDynamicParameters((DynamicParameters)param);
oracleParam.AddRefCursorParameters(cursorsName);
param = oracleParam;
}
return($@"BEGIN
OPEN :main_cursor FOR {sqlTemplate.RawSql};
{Environment.NewLine}
OPEN :counter_cursor FOR {totalCountQuery};
END;");
}
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;
if (order_by == "popular")
{
builder.Where("qs.Views > @threshold", new { threshold = threshold });
builder.OrderBy("qs.Views DESC");
builder.OrderBy("qs.Votes DESC");
}
else
{
order_by = "favorite";
builder.Where("qs.Votes > @threshold", new { threshold = threshold });
builder.OrderBy("qs.Votes DESC");
builder.OrderBy("qs.Views DESC");
}
}
if (searchCriteria.IsValid)
{
builder.Where("qs.Title LIKE @search OR qs.[Description] LIKE @search", new { search = '%' + searchCriteria.SearchTerm + '%' });
}
}
else if (order_by == "everything")
{
pager = builder.AddTemplate(@"
SELECT
/**select**/
FROM (
SELECT r.*, ROW_NUMBER() OVER(/**orderby**/) AS RowNumber FROM Revisions r
) r
/**join**/
/**leftjoin**/
WHERE
RowNumber BETWEEN @start AND @finish
ORDER BY
RowNumber",
new { start = start, finish = finish }
);
counter = builder.AddTemplate("SELECT COUNT(*) FROM Revisions r");
builder.Select("r.Id AS RevisionId");
builder.Select("qs.Id as QuerySetId");
builder.Select("r.CreationDate AS LastRun");
builder.Join("QuerySetRevisions qr on qr.RevisionId = r.Id ");
builder.Join("QuerySets qs on qs.Id = qr.QuerySetId");
builder.Join("Queries q on q.Id = r.QueryId");
builder.LeftJoin("Users u ON r.OwnerId = u.Id");
builder.OrderBy("CreationDate DESC");
}
builder.Select("u.Id as CreatorId");
builder.Select("u.Login as CreatorLogin");
builder.Select("qs.Title AS Name");
builder.Select("qs.[Description] AS [Description]");
builder.Select("qs.Votes AS FavoriteCount");
builder.Select("qs.Views AS Views");
builder.Select("q.QueryBody AS [SQL]");
IEnumerable <QueryExecutionViewData> queries = Current.DB.Query <QueryExecutionViewData>(
pager.RawSql,
pager.Parameters
).Select(
(view) =>
{
view.SiteName = Site.TinyName.ToLower();
return(view);
}
);
int total = Current.DB.Query <int>(counter.RawSql, counter.Parameters).First();
sitename = Site.TinyName.ToLower();
var href = "/" + sitename + "/queries?order_by={0}";
var extra = "";
if (searchCriteria.IsValid)
{
extra += "&q=" + HtmlUtilities.UrlEncode(searchCriteria.RawInput);
}
// TODO: This is a hack to avoid misrepresenting the fact we aren't actually doing the search in this case
if (order_by == "everything")
{
searchCriteria = new QuerySearchCriteria(null);
}
ViewData["SearchCriteria"] = searchCriteria;
ViewData["Href"] = string.Format(href, order_by) + extra;
if (Current.User.IsAnonymous && pagesizeProvided)
{
extra += "&pagesize=" + pagesize.Value;
}
SetHeader(
"All Queries",
new SubHeaderViewData
{
Description = "featured",
Title = "Interesting queries selected by the administrators",
Href = string.Format(href, "featured") + extra,
Selected = (order_by == "featured")
},
new SubHeaderViewData
{
Description = "recent",
Title = "Recently saved queries",
Href = string.Format(href, "recent") + extra,
Selected = (order_by == "recent")
},
new SubHeaderViewData
{
Description = "favorite",
Title = "Favorite saved queries",
Href = string.Format(href, "favorite") + extra,
Selected = (order_by == "favorite")
},
new SubHeaderViewData
{
Description = "popular",
Title = "Saved queries with the most views",
Href = string.Format(href, "popular") + extra,
Selected = (order_by == "popular")
},
new SubHeaderViewData
{
Description = "everything",
Title = "All queries recently executed on the site",
Href = string.Format(href, "everything") + extra,
Selected = (order_by == "everything")
}
);
return(View(new PagedList <QueryExecutionViewData>(queries, page.Value, pagesize.Value, false, total)));
}
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);
}
}
private List <NatureArea> GetNatureAreaInfos(SqlConnection sqlConnection, SqlBuilder builder, SqlBuilder.Template template, int infoLevel)
{
var natureAreas = new List <NatureArea>();
builder.Select("na.id AS Id, na.localId AS LocalId, na.naturnivå_id AS NaturnivaId, na.kartlagt AS Kartlagt, na.institusjon AS Institusjon" + (infoLevel == 2 ? ", na.kartlegger_id AS KartleggerId" : string.Empty));
string sql = template.RawSql;
var infos = sqlConnection.Query <NatureAreaInfoDto>(sql, template.Parameters).ToList();
foreach (var info in infos)
{
var natureArea = new NatureAreaExport
{
Id = info.Id,
UniqueId = new Identification {
LocalId = info.LocalId
},
Nivå = (NatureLevel)info.NaturnivaId,
Surveyed = info.Kartlagt,
Institution = info.Institusjon
};
if (infoLevel == 2 && info.KartleggerId.HasValue)
{
natureArea.Surveyer = new Contact {
Id = info.KartleggerId.Value
};
}
natureAreas.Add(natureArea);
}
if (infoLevel == 1)
{
natureAreas = SqlServer.SetParameters(sql.Contains("WHERE"), natureAreas).ToList();
}
else if (infoLevel == 2)
{
SqlServer.SetMetadata(natureAreas);
}
return(natureAreas);
}
public WhereClauseBuilder(string originalQuery, CriteriaJoinStrategy joinStrategy = CriteriaJoinStrategy.And)
{
sqlBuilder = new SqlBuilder();
sqlTemplate = sqlBuilder.AddTemplate($"{originalQuery} {WhereClauseTemplate}");
this.joinStrategy = joinStrategy;
}
public static Sql ToSql(this SqlBuilder.Template template)
{
return(new Sql(true, template.RawSql, template.Parameters));
}
public virtual long GetCount(SqlBuilder.Template template)
{
return(this.Db.ExecuteScalar <long>(template));
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="connectionString">数据信息</param>
/// <param name="builder"></param>
/// <param name="template"></param>
/// <param name="orderColumn">排序列</param>
/// <param name="orderBy">排序类型</param>
/// <param name="rows">每页显示记录数</param>
/// <returns>the list of query result.</returns>
protected virtual IEnumerable <TResult> GetList <TResult>(string connectionString, SqlBuilder builder, SqlBuilder.Template template, string orderColumn, ListResultsOrder orderBy, int rows)
where TResult : class
{
if (!string.IsNullOrEmpty(orderColumn))
{
builder.OrderBy(orderBy == ListResultsOrder.Descending ? orderColumn + " DESC" : orderColumn + " ASC");
}
builder.Limit(null, rows);
var sql = template.RawSql;
var param = template.Parameters;
return(GetList <TResult>(connectionString, sql, param));
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="connectionString">数据信息</param>
/// <param name="builder"></param>
/// <param name="template"></param>
/// <param name="orderColumn">排序列</param>
/// <param name="orderBy">排序类型</param>
/// <param name="rows">每页显示记录数</param>
/// <returns>the list of query result.</returns>
protected virtual IEnumerable <TEntity> GetList(string connectionString, SqlBuilder builder, SqlBuilder.Template template, string orderColumn, ListResultsOrder orderBy, int rows)
{
return(GetList <TEntity>(connectionString, builder, template, orderColumn, orderBy, rows));
}
public async Task <(List <PrintedEditionWithNestedObjects>, int)> GetWithNestedObjectsAsync(PrintedEditionRequestParameters parameters)
{
int skipCount = (parameters.Page - 1) * parameters.PageSize;
int count = default(int);
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT PrintedEditions.Id) FROM PrintedEditions
LEFT JOIN AuthorInBooks ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate(
$@"SELECT * FROM (SELECT DISTINCT PrintedEditions.* FROM PrintedEditions
LEFT JOIN AuthorInBooks ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId
/**where**/ /**orderby**/ ) AS FilteredPrintedEditions
LEFT JOIN AuthorInBooks ON FilteredPrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId");
sqlBuilder.Where("PrintedEditions.Price >= @PriceFrom AND PrintedEditions.Price <= @PriceTo");
if (!string.IsNullOrWhiteSpace(parameters.KeyWord))
{
parameters.KeyWord = parameters.KeyWord.ToLower();
sqlBuilder.Where($@"LOWER(PrintedEditions.Name + ' ' + PrintedEditions.Description) LIKE '%' + @KeyWord + '%'");
}
if (parameters.PrintedEditionType != PrintedEditionType.None)
{
sqlBuilder.Where("PrintedEditions.Type = @PrintedEditionType");
}
count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
if (parameters.SortCriteria == SortCriteria.PriceAsc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price ASC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.PriceDesc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price DESC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.CurrencyAsc)
{
sqlBuilder.OrderBy($"PrintedEditions.Currency ASC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.CurrencyDesc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price DESC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.None)
{
sqlBuilder.OrderBy($"PrintedEditions.Id OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
Dictionary <int, PrintedEditionWithNestedObjects> printedEditionWithNestedObjectsDictionary = new Dictionary <int, PrintedEditionWithNestedObjects>();
IEnumerable <PrintedEditionWithNestedObjects> response = await _connection.QueryAsync <PrintedEdition, AuthorInBook, Author, PrintedEditionWithNestedObjects>(itemsExpression.RawSql,
(printedEdition, authorInBook, author) =>
{
bool isExsist = printedEditionWithNestedObjectsDictionary.TryGetValue(printedEdition.Id, out PrintedEditionWithNestedObjects printedEditionWithNestedObjects);
if (!isExsist)
{
printedEditionWithNestedObjects = new PrintedEditionWithNestedObjects();
printedEditionWithNestedObjects.PrintedEdition = printedEdition;
printedEditionWithNestedObjectsDictionary.Add(printedEdition.Id, printedEditionWithNestedObjects);
}
if (!(authorInBook is null))
{
authorInBook.Author = author;
printedEditionWithNestedObjects.AuthorInBooks.Add(authorInBook);
}
return(printedEditionWithNestedObjects);
},
