public Page <Product> GetPaged(int page, int pageSize) { var countSql = new Sql().Select("count(*)") .From <Product>(ctx.SqlSyntax); var totalCount = db.ExecuteScalar <long>(countSql); var sql = new Sql().Select("Products.*, Categories.CategoryName") .From <Product>(ctx.SqlSyntax) .InnerJoin <Category>(ctx.SqlSyntax) .On <Product, Category>(ctx.SqlSyntax, p => p.CategoryId, c => c.Id) .OrderBy <Product>(p => p.Id, ctx.SqlSyntax); var items = db.Fetch <Product, Category, Product>((p, c) => { p.Category = c; return(p); }, sql); return(new Page <Product>() { CurrentPage = page, Items = items, ItemsPerPage = pageSize, TotalItems = totalCount, TotalPages = (long)Math.Ceiling(totalCount / (double)pageSize) }); }
private HealthCheckStatus CheckMembers() { var total = _services.MemberService.Count(); var memberObjectType = Guid.Parse(Constants.ObjectTypes.Member); var subQuery = new Sql() .Select("Count(*)") .From <ContentXmlDto>(_sqlSyntax) .InnerJoin <NodeDto>(_sqlSyntax) .On <ContentXmlDto, NodeDto>(_sqlSyntax, left => left.NodeId, right => right.NodeId) .Where <NodeDto>(dto => dto.NodeObjectType == memberObjectType); var totalXml = _database.ExecuteScalar <int>(subQuery); var actions = new List <HealthCheckAction>(); if (totalXml != total) { actions.Add(new HealthCheckAction(CheckMembersXmlTableAction, Id)); } return(new HealthCheckStatus(_textService.Localize("healthcheck/xmlDataIntegrityCheckMembers", new[] { totalXml.ToString(), total.ToString(), (total - totalXml).ToString() })) { ResultType = totalXml == total ? StatusResultType.Success : StatusResultType.Error, Actions = actions }); }
public List <CustomMember> GetMembers(int pageIndex, int pageSize, out int totalRecords) { totalRecords = _db.ExecuteScalar <int>("SELECT Count(*) FROM CustomMembers"); var members = _db.SkipTake <CustomMember>(pageIndex * pageSize, pageSize, "SELECT * FROM CustomMembers"); return(members); }
/// <summary> /// The perform exists. /// </summary> /// <param name="key"> /// The key. /// </param> /// <returns> /// A value indicating whether or not an entity with the key exists. /// </returns> protected bool PerformExists(Guid Key) { var sql = GetBaseQuery(true); sql.Where(GetBaseWhereClause(), new { Key = Key }); var count = _database.ExecuteScalar <int>(sql); return(count == 1); }
public CMSNode(Guid uniqueID, bool noSetup) { if (!noSetup) { SetupNodeFromDto("WHERE uniqueID = @uniqueID", new { uniqueID }); } else { _id = Database.ExecuteScalar <int>("SELECT id FROM umbracoNode WHERE uniqueId = @uniqueID", new { uniqueID }); } }
public Page <OrderItem> GetOrderItems(int orderId, int page, int pageSize) { var countSql = new Sql().Select("*") .From <OrderItem>(ctx.SqlSyntax) .Where <OrderItem>(oi => oi.OrderId == orderId, ctx.SqlSyntax); var totalCount = db.ExecuteScalar <long>(countSql); var sql = new Sql().Select("OrderItems.*, Products.Name") .From <OrderItem>(ctx.SqlSyntax) .InnerJoin <Product>(ctx.SqlSyntax) .On <OrderItem, Product>(ctx.SqlSyntax, oi => oi.ProductId, p => p.Id) .Where <OrderItem>(oi => oi.OrderId == orderId, ctx.SqlSyntax) .OrderBy <OrderItem>(oi => oi.ProductId, ctx.SqlSyntax); var items = db.Fetch <OrderItem, Product, OrderItem>((oi, p) => { oi.Product = p; return(oi); }, sql); return(new Page <OrderItem>() { CurrentPage = page, Items = items, ItemsPerPage = pageSize, TotalItems = totalCount, TotalPages = (long)Math.Ceiling(totalCount / (double)pageSize) }); }
public int GetReportsCount() { UmbracoDatabase db = ApplicationContext.DatabaseContext.Database; //return db.Fetch<ComplaintReport>(new Sql().Select("*").From("ComplaintReport")).Count(); return(db.ExecuteScalar <int>(new Sql().Select("COUNT(Id)").From("ComplaintReport"))); }
public void Can_Bulk_Insert_Native_Sql_Server_Bulk_Inserts() { //create the db var dbSqlServer = new UmbracoDatabase( "server=.\\SQLExpress;database=YOURDB;user id=YOURUSER;password=YOURPASSWORD", Constants.DatabaseProviders.SqlServer, new DebugDiagnosticsLogger()); //drop the table dbSqlServer.Execute("DROP TABLE [umbracoServer]"); //re-create it dbSqlServer.Execute(@"CREATE TABLE [umbracoServer]( [id] [int] IDENTITY(1,1) NOT NULL, [address] [nvarchar](500) NOT NULL, [computerName] [nvarchar](255) NOT NULL, [registeredDate] [datetime] NOT NULL CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()), [lastNotifiedDate] [datetime] NOT NULL, [isActive] [bit] NOT NULL, [isMaster] [bit] NOT NULL, CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )"); var data = new List <ServerRegistrationDto>(); for (var i = 0; i < 1000; i++) { data.Add(new ServerRegistrationDto { ServerAddress = "address" + i, ServerIdentity = "computer" + i, DateRegistered = DateTime.Now, IsActive = true, DateAccessed = DateTime.Now }); } var sqlServerSyntax = new SqlServerSyntaxProvider(); using (var tr = dbSqlServer.GetTransaction()) { dbSqlServer.BulkInsertRecords(data, tr, sqlServerSyntax, useNativeSqlPlatformBulkInsert: true); tr.Complete(); } // Assert Assert.That(dbSqlServer.ExecuteScalar <int>("SELECT COUNT(*) FROM umbracoServer"), Is.EqualTo(1000)); }
public long GetTotalSavedBytes() { var query = new Sql("select sum(originsize) - sum(optimizedsize) from [TinifierResponseHistory]"); return(_database.ExecuteScalar <long>(query)); }
public int GetDealersCount() { UmbracoDatabase db = ApplicationContext.DatabaseContext.Database; return(db.ExecuteScalar <int>(new Sql().Select("COUNT(Id)").From("Dealer"))); }