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