//public IList<SuOrganization_SuOrganizationLang> FindByLanguageId(int languageId) //{ // StringBuilder strQuery = new StringBuilder(); // strQuery.AppendLine(" SELECT org.OrganizationID,orgLang.OrganizationName FROM SuOrganization org "); // strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang "); // strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID "); // strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID "); // strQuery.AppendLine(" ORDER BY orgLang.OrganizationName "); // return GetCurrentSession().CreateSQLQuery(strQuery.ToString()) // .AddEntity(typeof(SuOrganization_SuOrganizationLang)) // .SetInt32("LanguageID", languageId) // .List<SuOrganization_SuOrganizationLang>(); //} //public IList<SuOrganization_SuOrganizationLang> FindByLanguageId(int languageId) //{ // StringBuilder strQuery = new StringBuilder(); // strQuery.AppendLine(" SELECT org.*,orgLang.* FROM SuOrganization org "); // strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang "); // strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID "); // strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID "); // strQuery.AppendLine(" ORDER BY orgLang.OrganizationName "); // return GetCurrentSession().CreateSQLQuery(strQuery.ToString()) // .AddEntity(typeof(SuOrganization_SuOrganizationLang)) // .SetInt32("LanguageID", languageId) // .List<SuOrganization_SuOrganizationLang>(); //} #endregion #region ISuOrganizationLangQuery Members public IList <SuOrganizationWithLang> FindByLanguageId(short languageId) { //throw new NotImplementedException(); StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT org.OrganizationID, org.OrganizationName "); sqlBuilder.Append(" FROM SuOrganizationLang org where org.LanguageID = :LanguageId "); QueryParameterBuilder parameterBuilder = new QueryParameterBuilder(); parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); parameterBuilder.FillParameters(query); query.AddScalar("OrganizationID", NHibernateUtil.Int16) .AddScalar("OrganizationName", NHibernateUtil.String); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SuOrganizationWithLang))).List <SuOrganizationWithLang>()); }
public int GetBottlesAtCounterparty(IUnitOfWork UoW, Counterparty counterparty, DateTime?before = null) { BottlesMovementOperation operationAlias = null; BottlesBalanceQueryResult result = null; var queryResult = UoW.Session.QueryOver <BottlesMovementOperation>(() => operationAlias) .Where(() => operationAlias.Counterparty.Id == counterparty.Id); if (before.HasValue) { queryResult.Where(() => operationAlias.OperationTime < before); } var bottles = queryResult.SelectList(list => list .SelectSum(() => operationAlias.Delivered).WithAlias(() => result.Delivered) .SelectSum(() => operationAlias.Returned).WithAlias(() => result.Returned) ).TransformUsing(Transformers.AliasToBean <BottlesBalanceQueryResult>()).List <BottlesBalanceQueryResult>() .FirstOrDefault()?.BottlesDebt ?? 0; return(bottles); }
public IList <ModuleChange> GetByDate(int year, int month, int?moduleId) { using (var session = sessionFactory.OpenSession()) { var query = string.Format(@"SELECT * FROM ModuleChange Where YEAR(StartDate) = :param1 AND MONTH(StartDate) = :param2 "); if (moduleId != null) { query += string.Join(query, "AND ModuleId = :param3"); } var result = session.CreateSQLQuery(query). SetParameter("param1", year). SetParameter("param2", month); if (moduleId != null) { result.SetParameter("param3", moduleId); } return(result.SetResultTransformer(Transformers.AliasToBean <ModuleChange>()). List <ModuleChange>()); } }
public IList <OperationNode> GetCashBalanceForOrganizations(IUnitOfWork uow) { Organization organizationAlias = null; OrganisationCashMovementOperation operationAlias = null; OperationNode resultAlias = null; var query = uow.Session.QueryOver(() => organizationAlias) .JoinEntityAlias(() => operationAlias, () => organizationAlias.Id == operationAlias.Organisation.Id, JoinType.LeftOuterJoin ) .SelectList(list => list .SelectGroup(() => organizationAlias.Id) .Select(Projections.Sum(() => operationAlias.Amount)).WithAlias(() => resultAlias.Balance) .Select(() => organizationAlias.Name).WithAlias(() => resultAlias.Name)) .TransformUsing(Transformers.AliasToBean <OperationNode>()) .List <OperationNode>(); return(query); }
protected override IQueryOver <PECMailLog, PECMailLog> MappingProjection(IQueryOver <PECMailLog, PECMailLog> queryOver) { PECMail pec = null; PECMailLogHeader header = null; queryOver .JoinAlias(o => o.Mail, () => pec) .SelectList(list => list .Select(x => x.Id).WithAlias(() => header.Id) .Select(x => x.Description).WithAlias(() => header.Description) .Select(x => x.Type).WithAlias(() => header.Type) .Select(x => x.Date).WithAlias(() => header.Date) .Select(x => x.SystemComputer).WithAlias(() => header.SystemComputer) .Select(x => x.SystemUser).WithAlias(() => header.SystemUser) .Select(x => pec.Id).WithAlias(() => header.MailId) .Select(x => pec.MailSubject).WithAlias(() => header.MailSubject) ) .TransformUsing(Transformers.AliasToBean <PECMailLogHeader>()); return(queryOver); }
public IList <ProgramRole> FindSuProgramRoleByProgramCode(string programCode) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.AppendLine(" SELECT SuProgramRole.RoleId "); sqlBuilder.AppendLine(" FROM SuProgramRole INNER JOIN SuProgram ON SuProgram.ProgramID = SuProgramRole.ProgramID "); sqlBuilder.AppendLine(" WHERE SuProgram.ProgramCode = :ProgramCode "); sqlBuilder.AppendLine(" AND SuProgramRole.Active = 1 "); QueryParameterBuilder parameterBuilder = new QueryParameterBuilder(); parameterBuilder.AddParameterData("ProgramCode", typeof(string), programCode); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); parameterBuilder.FillParameters(query); query.AddScalar("RoleId", NHibernateUtil.Int16); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.ProgramRole))).List <ProgramRole>()); }
public IList <Language> FindByDbLanguageCriteria(DbLanguage language) { StringBuilder sql = new StringBuilder(); sql.Append("Select l.LanguageName "); sql.Append("from DbLanguage as l "); sql.Append("where l.LanguageName = :LanguageName "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString()); QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder(); queryParameterBuilder.AddParameterData("LanguageName", typeof(String), language.LanguageName); queryParameterBuilder.FillParameters(query); query.AddScalar("LanguageName", NHibernateUtil.String);; IList <SS.DB.DTO.ValueObject.Language> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.ValueObject.Language))) .List <SS.DB.DTO.ValueObject.Language>(); return(list); }
public IList <PlaylistDto> GetCreatedPlaylists(int userId, PageData pageData) { PlaylistDto dto = null; Playlist playlist = null; User creator = null; return(Session.QueryOver(() => playlist) .Where(p => p.Creator.Id == userId) .JoinAlias(() => playlist.Creator, () => creator) .OrderBy(p => p.DateCreated).Desc .SelectList(l => l .Select(() => creator.UserName).WithAlias(() => dto.CreatorUserName) .Select(() => creator.Alias).WithAlias(() => dto.CreatorAlias) .Select(() => playlist.Name).WithAlias(() => dto.Name) .Select(() => playlist.Id).WithAlias(() => dto.PlaylistId) .Select(() => playlist.UrlId).WithAlias(() => dto.UrlId)) .TransformUsing(Transformers.AliasToBean <PlaylistDto>()) .Paginate(pageData) .List <PlaylistDto>()); }
public async Task OrderBySpecifiedPropertyWithQueryOverAsync() { using (var session = OpenSession()) using (session.BeginTransaction()) { PersonDto dto = null; var people = await(session.QueryOver <Person>() .SelectList(b => b.Select(p => p.Id).WithAlias(() => dto.Id) .Select(p => p.Name).WithAlias(() => dto.Name) .Select(p => p.Address).WithAlias(() => dto.Address) .Select(p => p.Age).WithAlias(() => dto.Age)) .OrderBy(p => p.Age) .Desc .TransformUsing(Transformers.AliasToBean <PersonDto>()) .ListAsync <PersonDto>()); Assert.That(people.Count, Is.EqualTo(2)); Assert.That(people, Is.Ordered.By("Age").Descending); } }
public IList <TranslatedListItem> GetTranslatedList(short languageID) { StringBuilder strQuery = new StringBuilder(); strQuery.AppendLine(" SELECT div.DivisionID as Id, divLang.DivisionName as Text FROM SuDivision div "); strQuery.AppendLine(" INNER JOIN SuDivisionLang divLang "); strQuery.AppendLine(" ON div.DivisionID = divLang.DivisionID "); strQuery.AppendLine(" WHERE divLang.LanguageID = :LanguageID "); strQuery.AppendLine(" ORDER BY divLang.DivisionName "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString()); query.SetInt16("LanguageID", languageID == short.Parse("0") ? short.Parse("1") : languageID); query.AddScalar("Id", NHibernateUtil.Int16); query.AddScalar("Text", NHibernateUtil.String); IList <TranslatedListItem> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>(); return(list); }
public IList <ClosedComplaintResultNode> GetComplaintsResultsOfEmployees(IUnitOfWork uow, DateTime?start = null, DateTime?end = null) { ComplaintResultOfEmployees resultOfEmployeesAlias = null; ClosedComplaintResultNode resultAlias = null; var query = uow.Session.QueryOver <Complaint>() .Left.JoinAlias(c => c.ComplaintResultOfEmployees, () => resultOfEmployeesAlias) .Where(c => c.Status == ComplaintStatuses.Closed); AddDateRestriction(start, end, query); return(query.SelectList(list => list .SelectGroup(() => resultOfEmployeesAlias.Name).WithAlias(() => resultAlias.Name) .Select(Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Int32, "COUNT(IFNULL(?1, 0))"), NHibernateUtil.Int32, Projections.Property(() => resultOfEmployeesAlias.Id))).WithAlias(() => resultAlias.Count)) .TransformUsing(Transformers.AliasToBean <ClosedComplaintResultNode>()) .List <ClosedComplaintResultNode>()); }
protected override Dictionary <Product, IEnumerable <string> > GetValues(List <Product> objs) { ProductVariant productVariantAlias = null; OptionValueData data = null; IList <OptionValueData> values = _session.QueryOver <ProductOptionValue>() .JoinAlias(value => value.ProductVariant, () => productVariantAlias) .SelectList(builder => builder .Select(value => value.Value).WithAlias(() => data.Value) .Select(value => value.ProductOption.Id).WithAlias(() => data.OptionId) .Select(() => productVariantAlias.Product.Id).WithAlias(() => data.ProductId) ).TransformUsing(Transformers.AliasToBean <OptionValueData>()) .List <OptionValueData>(); Dictionary <int, IEnumerable <string> > dictionary = values.GroupBy(valueData => valueData.ProductId) .ToDictionary(datas => datas.Key, datas => datas.Select(valueData => valueData.TermValue)); return(objs.ToDictionary(product => product, product => dictionary.ContainsKey(product.Id) ? dictionary[product.Id] : Enumerable.Empty <string>())); }
public List <SelectListItem> GetOptions() { BrandInfo info = null; return(_session.QueryOver <Brand>() .OrderBy(x => x.Name).Asc .SelectList(builder => { builder.Select(x => x.Id).WithAlias(() => info.Id); builder.Select(x => x.Name).WithAlias(() => info.Name); return builder; }).TransformUsing(Transformers.AliasToBean <BrandInfo>()) .Cacheable() .List <BrandInfo>() .BuildSelectItemList(item => item.Name, item => item.Id.ToString(), null, new SelectListItem { Text = "Please select...", Value = "0" })); }
public IList <DbPBCurrency> FindPBLocalCurrencyByPBID(long pbID, string currencyType) { string sql = @"select PBCurrency.ID,PBCurrency.PBID,Currency.CurrencyID,Currency.Symbol,PBCurrency.UpdBy,PBCurrency.UpdDate,PBCurrency.CreBy,PBCurrency.CreDate,PBCurrency.UpdPgm from DbPBCurrency as PBCurrency inner join DbCurrency as Currency on PBCurrency.CurrencyID = Currency.CurrencyID where PBCurrency.PBID = :pbID order by PBCurrency.ID"; ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql); query.SetInt64("pbID", pbID); query.AddScalar("ID", NHibernateUtil.Int64); query.AddScalar("PBID", NHibernateUtil.Int64); query.AddScalar("CurrencyID", NHibernateUtil.Int16); query.AddScalar("Symbol", NHibernateUtil.String); query.AddScalar("UpdBy", NHibernateUtil.Int64); query.AddScalar("UpdDate", NHibernateUtil.DateTime); query.AddScalar("CreBy", NHibernateUtil.Int64); query.AddScalar("CreDate", NHibernateUtil.DateTime); query.AddScalar("UpdPgm", NHibernateUtil.String); query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPBCurrency))); return(query.List <DbPBCurrency>());; }
public IList <DbPaymentMethod> FindPaymentMethodActive(short ComID) { StringBuilder sqlBuilder = new StringBuilder(); ISQLQuery query = null; sqlBuilder.Append(" SELECT B.PaymentMethodID , '[' + B.PaymentMethodCode + '] ' + B.PaymentMethodName AS PaymentMethodName "); sqlBuilder.Append(" FROM DbCompanyPaymentMethod AS A , DbPaymentMethod AS B "); sqlBuilder.Append(" WHERE A.PaymentMethodID = B.PaymentMethodID AND "); sqlBuilder.Append(" A.CompanyID = :CompanyID "); sqlBuilder.Append(" ORDER BY B.PaymentMethodCode "); query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); query.SetParameter("CompanyID", ComID); query.AddScalar("PaymentMethodID", NHibernateUtil.Int64); query.AddScalar("PaymentMethodName", NHibernateUtil.String); IList <DbPaymentMethod> dbPyamentMethodList = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPaymentMethod))).List <DbPaymentMethod>(); return(dbPyamentMethodList); }
public override void UpdateNodes() { CommentsTemplatesVMNode resultAlias = null; CommentsTemplates commentsTemplatesAlias = null; var query = UoW.Session.QueryOver <CommentsTemplates>(() => commentsTemplatesAlias); //if(Filter.RestrictionFineDateStart.HasValue) { // query.Where(() => fineAlias.Date >= Filter.RestrictionFineDateStart.Value); //} var result = query.SelectList(list => list .Select(() => commentsTemplatesAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => commentsTemplatesAlias.CommentTemplate).WithAlias(() => resultAlias.CommentTmp) ).TransformUsing(Transformers.AliasToBean <CommentsTemplatesVMNode>()) .List <CommentsTemplatesVMNode>(); SetItemsSource(result); }
public static CounterpartyDebtQueryResult GetCounterpartyBalanceResult(IUnitOfWork UoW, Counterparty counterparty, DateTime?before = null) { MoneyMovementOperation operationAlias = null; CounterpartyDebtQueryResult result = null; var queryResult = UoW.Session.QueryOver <MoneyMovementOperation>(() => operationAlias) .Where(() => operationAlias.Counterparty.Id == counterparty.Id); if (before.HasValue) { queryResult.Where(() => operationAlias.OperationTime < before); } var debt = queryResult .SelectList(list => list .SelectSum(() => operationAlias.Debt).WithAlias(() => result.Charged) .SelectSum(() => operationAlias.Money).WithAlias(() => result.Payed) .SelectSum(() => operationAlias.Deposit).WithAlias(() => result.Deposit) ).TransformUsing(Transformers.AliasToBean <CounterpartyDebtQueryResult>()).List <CounterpartyDebtQueryResult>(); return(debt.FirstOrDefault()); }
public List <KodIsim> StokKods(string stokKodIsim, Sube sube) { //ICriteria criteria = Session.CreateCriteria(typeof(Stok)).SetProjection(Projections.Property("Id")) // .Add(Expression.Like("Id", stokKod, MatchMode.Start)).SetMaxResults(GetMaxResult); //ICriteria subeCriteria = criteria.CreateCriteria("Sube"); //subeCriteria.Add(NHibernate.Criterion.Expression.Eq("Id", sube.Id)); //return criteria.List<string>() as List<string>; //IQuery query = Session.CreateQuery("select st.Id from Stok st where st.Sube=:sube and st.Id like :st").SetString("st", stokKod + "%").SetEntity("sube", sube).SetMaxResults(10); //return (List<string>)query.List<string>(); KodIsim kodIsim = null; return(Session.QueryOver <Stok>().Where(x => (x.Sube == sube || x.SubelerdeOrtak == true) && (x.Id.IsLike(stokKodIsim, MatchMode.Anywhere) || x.StokAdi.IsLike(stokKodIsim, MatchMode.Anywhere))) .SelectList(liste => liste.SelectGroup(c => c.Id).WithAlias(() => kodIsim.Kod) .SelectGroup(c => c.StokAdi).WithAlias(() => kodIsim.Isim) ).TransformUsing(Transformers.AliasToBean <KodIsim>()) .Take(GetMaxResult).List <KodIsim>() as List <KodIsim>); }
public ActionResult Conclave() { // Get data for existing Types and pass to View using (ISession _S = MvcApplication.SF.GetCurrentSession()) { Conclave clv = null; var conclaveList = _S.QueryOver(() => clv) .SelectList(l => l .Select(x => x.Typepk).WithAlias(() => clv.Typepk) .Select(x => x.Typeid).WithAlias(() => clv.Typeid) .Select(x => x.Name).WithAlias(() => clv.Name) .Select(x => x.Customflg).WithAlias(() => clv.Customflg) ) .TransformUsing(Transformers.AliasToBean <Conclave>()) .List <Conclave>(); return(View(conclaveList)); } }
private void DeleteTransformers() { var tasks = Transformers.Select(transformerDefinition => DatabaseCommands.DeleteTransformerAsync(transformerDefinition.Name)) .ToList(); TaskEx.WhenAll(tasks) .ContinueOnUIThread(t => { if (t.IsFaulted) { ApplicationModel.Current.AddErrorNotification(t.Exception, "not all transformers could be deleted"); } else { ApplicationModel.Current.AddInfoNotification("all transformers were successfully deleted"); UrlUtil.Navigate("/transformers"); } }); }
public IList <DbTax> GetTaxCodeActiveByCompany(long companyID) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append(" select DbTax.TaxCode,DbTax.TaxID "); sqlBuilder.Append(" from DbTax "); sqlBuilder.Append(" left join DbCompanyTax on DbTax.TaxID = DbCompanyTax.TaxID and DbCompanyTax.CompanyID = :companyID"); sqlBuilder.Append(" Where DbTax.Active = 1 "); sqlBuilder.Append(" and (ISNULL(DbCompanyTax.Disable,0) <> 1)"); sqlBuilder.Append(" or (DbTax.ApplyAllCompany = 1 and DbTax.Active = 1)"); sqlBuilder.Append(" order by DbTax.TaxCode "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder(); queryParameterBuilder.AddParameterData("companyID", typeof(string), companyID); queryParameterBuilder.FillParameters(query); query.AddScalar("TaxCode", NHibernateUtil.String) .AddScalar("TaxID", NHibernateUtil.Int64); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbTax))).List <DbTax>()); }
public IList <SupportTeamDto> GetSupportTeamSummaries() { var criteria = Session.CreateCriteria(typeof(SupportTeam)); criteria .CreateAlias("Supervisor", "Supervisor", JoinType.LeftOuterJoin) .SetProjection(Projections.ProjectionList() .Add(Projections.Property("Id"), "Id") .Add(Projections.Property("TeamName"), "TeamName") .Add(Projections.Property("PhoneNumber"), "PhoneNumber") .Add(Projections.Property("Supervisor.FirstName"), "SupervisorFirstName") .Add(Projections.Property("Supervisor.LastName"), "SupervisorLastName") //.Add(Projections.Property("SupervisorId"), "SupervisorId"); ); criteria .SetResultTransformer(Transformers.AliasToBean <SupportTeamDto>()); return(criteria.List <SupportTeamDto>()); }
public void CountWithConditionalDoesNotThrowAsync() { using (var session = OpenSession()) using (session.BeginTransaction()) { MappingEntity mappingEntity = null; Assert.DoesNotThrowAsync( () => session.QueryOver <Entity>().SelectList( builder => builder.Select( Projections.Count( Projections.Conditional( Restrictions.Eq(Projections.Property <Entity>(x => x.Name), "FOO"), Projections.Constant("", NHibernateUtil.String), Projections.Constant(null, NHibernateUtil.String))).WithAlias(() => mappingEntity.Count)) ).TransformUsing(Transformers.AliasToBean <MappingEntity>()).ListAsync <MappingEntity>() ); } }
public IEnumerable <CategoryContactDTO> GetAllByCategoriesIds(List <int> categoriesIds) { Contact contact = null; Category category = null; CategoryContactDTO dto = null; var query = new DefaultQueryOver <Contact, int>().GetQueryOver(() => contact) .JoinQueryOver(() => contact.Categories, () => category) .WhereRestrictionOn(() => category.Id) .IsIn(categoriesIds) .SelectList( l => l.Select(() => contact.Id) .WithAlias(() => dto.contactId) .Select(() => category.Id) .WithAlias(() => dto.categoryId)) .TransformUsing(Transformers.AliasToBean <CategoryContactDTO>()); return(this.Repository.FindAll <CategoryContactDTO>(query)); }
public IList <TrackThumbnailDto> GetUploadedTracks(int userId, PageData pageData) { User uploader = null; TrackThumbnailDto dto = null; return(Session.QueryOver <Track>() .Where(t => t.Uploader.Id == userId) .OrderBy(t => t.DateUploaded).Desc .JoinAlias(t => t.Uploader, () => uploader) .SelectList(l => l .Select(t => t.Id).WithAlias(() => dto.Id) .Select(t => t.Title).WithAlias(() => dto.Title) .Select(t => t.UrlId).WithAlias(() => dto.UrlId) .Select(t => t.Artwork).WithAlias(() => dto.Artwork) .Select(() => uploader.Alias).WithAlias(() => dto.UserAlias) .Select(() => uploader.UserName).WithAlias(() => dto.UserName)) .TransformUsing(Transformers.AliasToBean <TrackThumbnailDto>()) .Paginate(pageData) .List <TrackThumbnailDto>()); }
public void ForceRefreshComponentsDictionary() { using (var session = Hibernate.SessionFactory.OpenSession()) { Component c = null; Mount m = null; ComponentWithCountsDTO dto = null; components = session .QueryOver(() => c) .Left.JoinAlias(() => c.Mounts, () => m) .SelectList(l => l .SelectGroup(() => c.Id).WithAlias(() => dto.Id) .SelectGroup(() => c.Name).WithAlias(() => dto.Name) .SelectCount(() => m.Id).WithAlias(() => dto.MountsCount)) .OrderBy(() => c.Name).Asc .TransformUsing(Transformers.AliasToBean <ComponentWithCountsDTO>()) .List <ComponentWithCountsDTO>(); } }
public IList <FnExpenseDocument> GetTotalExpense(long remittanceID) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append(" select FnExpenseDocument.TotalExpense as TotalExpense ,FnExpenseDocument.PaymentType"); sqlBuilder.Append(" from FnRemittance inner join "); sqlBuilder.Append(" FnExpenseDocument on FnRemittance.documentID = FnExpenseDocument.DocumentID "); sqlBuilder.Append(" where FnRemittance.RemittanceID = :remittanceID "); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString()); QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder(); queryParameterBuilder.AddParameterData("remittanceID", typeof(long), remittanceID); queryParameterBuilder.FillParameters(query); query.AddScalar("TotalExpense", NHibernateUtil.Double); query.AddScalar("PaymentType", NHibernateUtil.String); return(query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseDocument))).List <FnExpenseDocument>()); }
private int GetNumberBought(IList <ProductVariant> variants) { if (!variants.Any()) { return(0); } List <int> values = variants.Select(variant => variant.Id).ToList(); var numberBoughtCount = new NumberBoughtCount(); var singleOrDefault = _session.QueryOver <OrderLine>() .Where(line => line.ProductVariant.Id.IsIn(values)) .SelectList( builder => builder.SelectSum(line => line.Quantity) .WithAlias(() => numberBoughtCount.Count)) .TransformUsing(Transformers.AliasToBean <NumberBoughtCount>()) .Cacheable() .SingleOrDefault <NumberBoughtCount>(); return(singleOrDefault != null ? singleOrDefault.Count : 0); }
public void ProjectionsShouldWorkWithHqlAndFutures() { using (var session = OpenSession()) using (session.BeginTransaction()) { var query1 = session.CreateQuery("select e.Id as EntityId, e.Name as EntityName from TestEntity e").SetResultTransformer( Transformers.AliasToBean(typeof(TestEntityDto))) .List <TestEntityDto>(); Assert.AreEqual(2, query1.Count()); var query2 = session.CreateQuery("select e.Id as EntityId, e.Name as EntityName from TestEntity e").SetResultTransformer( Transformers.AliasToBean(typeof(TestEntityDto))) .Future <TestEntityDto>(); Assert.AreEqual(2, query2.Count()); } }
/// <summary> /// SQL分页 /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="utilityPager">分页类</param> /// <param name="querysql">SQL语句</param> /// <returns></returns> public IList <T> PagerList <T>(UtilityPager utilityPager, string querysql, List <NHParameter> parameters, string orderBy = "", string ascOrDesc = "", bool isDTO = false) where T : class { if (utilityPager == null) { utilityPager = new UtilityPager(); } string _querysql = querysql; if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(ascOrDesc)) { _querysql = querysql + " order by " + orderBy + " " + ascOrDesc; } var queryList = ICreateSQLQuery(_querysql); if (parameters != null && parameters.Count > 0) { foreach (NHParameter para in parameters) { queryList.SetParameter(para.ParameterName, para.ParameterValue, para.DbType); } } if (!isDTO) { queryList.AddEntity(typeof(T)); } else { queryList.SetResultTransformer(Transformers.AliasToBean(typeof(T))); } if (!utilityPager.IsGetCount) { return(queryList.SetFirstResult(GetPageIndex(utilityPager)) .SetMaxResults(utilityPager.PageSize) .List <T>()); } int count = Convert.ToInt32(GetCustomSQLQueryUniqueResult <object>("select count(*) from (" + querysql + ") as countTable", parameters)); return(queryList.SetFirstResult(GetPageIndex(utilityPager, count)) .SetMaxResults(utilityPager.PageSize) .List <T>()); }