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