public override int GetFileSize(Item fileItem)
{
int?size = Items.
Where(item => item.Id == fileItem.Id).
Join(BinaryContentSet, item => item.Content.Id, content => content.Id, (item, content) => content).
Select(content => SqlFunctions.DataLength(content.Data)).
FirstOrDefault();
return(size.GetValueOrDefault());
}
public static IEnumerable <DbDocument> DocumentsFrom(IQueryable <PluginSessionDocument> docs)
{
return(docs
.Select(d => new { d.ID, d.Name, d.MimeType, d.IsViewable, Size = SqlFunctions.DataLength(d.Body) })
.AsEnumerable()
.Select(d => new DbDocument(docs, d.ID)
{
Name = d.Name, BodySize = d.Size ?? 0, Viewable = d.IsViewable, MimeType = d.MimeType
}));
}
public void DataLengthTest()//返回用于表示任意表达式的字节数。
{
using (var edm = new NorthwindEntities())
{
var cust1 = from e in edm.Order_Details
select SqlFunctions.DataLength("zxm");
// CAST(DATALENGTH(N'zxm') AS int) AS [C1]
cust1.TraceSql();
Console.WriteLine(cust1.First().Value);//6
}
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
/// <exception cref="System.NotImplementedException"></exception>
public void Execute(IJobExecutionContext context)
{
JobDataMap dataMap = context.JobDetail.JobDataMap;
int howMany = dataMap.GetString("HowMany").AsIntegerOrNull() ?? 300000;
var commandTimeout = dataMap.GetString("CommandTimeout").AsIntegerOrNull() ?? 3600;
CommunicationSchemaUpdates();
bool anyRemaining = UpdateCommunicationRecords(true, howMany, commandTimeout);
if (!anyRemaining)
{
// Verify that there are not any communication records with medium data.
using (var rockContext = new RockContext())
{
rockContext.Database.CommandTimeout = commandTimeout;
// if there is any v6 MediumDataJson data, it would be have a datalength of 2 or more (blank would be null, '', or '{}')
if (!new CommunicationService(rockContext)
.Queryable()
.Where(c => SqlFunctions.DataLength(c.MediumDataJson) > 2)
.Any())
{
// delete job if there are no PageView or CommunicationRecipientActivity rows left
var jobId = context.GetJobId();
var jobService = new ServiceJobService(rockContext);
var job = jobService.Get(jobId);
if (job != null)
{
jobService.Delete(job);
rockContext.SaveChanges();
return;
}
}
}
}
}
/// <summary>
/// Migrates communication data from the MediumDataJson field to the individual fields.
/// </summary>
/// <param name="updateTemplates">if set to <c>true</c> [update templates].</param>
/// <param name="howManyToConvert">The how many to convert.</param>
/// <param name="commandTimeout">The command timeout (seconds).</param>
/// <returns></returns>
public static bool UpdateCommunicationRecords(bool updateTemplates, int howManyToConvert, int?commandTimeout)
{
bool anyRemaining = true;
if (updateTemplates)
{
using (var rockContext = new RockContext())
{
if (commandTimeout.HasValue)
{
rockContext.Database.CommandTimeout = commandTimeout;
}
var binaryFileService = new BinaryFileService(rockContext);
// if there is any pre-v7 MediumDataJson data, it would be have a datalength of 2 or more (blank would be null, '', or '{}')
foreach (var comm in new CommunicationTemplateService(rockContext).Queryable()
.Where(c => SqlFunctions.DataLength(c.MediumDataJson) > 2))
{
var attachmentBinaryFileIds = new List <int>();
SetPropertiesFromMediumDataJson(comm, comm.MediumDataJson, attachmentBinaryFileIds);
foreach (int binaryFileId in attachmentBinaryFileIds)
{
var binaryFile = binaryFileService.Get(binaryFileId);
if (binaryFile != null)
{
var attachment = new CommunicationTemplateAttachment();
attachment.BinaryFile = binaryFile;
attachment.CommunicationType = CommunicationType.Email;
comm.AddAttachment(attachment, CommunicationType.Email);
}
}
comm.MediumDataJson = string.Empty;
}
rockContext.SaveChanges();
}
}
int howManyLeft = howManyToConvert;
while (howManyLeft > 0)
{
using (var rockContext = new RockContext())
{
int take = howManyLeft < 100 ? howManyLeft : 100;
// if there is any pre-v7 MediumDataJson data, it would be have a datalength of 2 or more (blank would be null, '', or '{}')
var communications = new CommunicationService(rockContext).Queryable()
.Where(c => SqlFunctions.DataLength(c.MediumDataJson) > 2)
.OrderByDescending(c => c.Id)
.Take(take)
.ToList();
anyRemaining = communications.Count >= take;
howManyLeft = anyRemaining ? howManyLeft - take : 0;
var binaryFileService = new BinaryFileService(rockContext);
foreach (var comm in communications)
{
var attachmentBinaryFileIds = new List <int>();
SetPropertiesFromMediumDataJson(comm, comm.MediumDataJson, attachmentBinaryFileIds);
foreach (int binaryFileId in attachmentBinaryFileIds)
{
var binaryFile = binaryFileService.Get(binaryFileId);
if (binaryFile != null)
{
var attachment = new CommunicationAttachment();
attachment.BinaryFile = binaryFile;
attachment.CommunicationType = CommunicationType.Email;
comm.AddAttachment(attachment, CommunicationType.Email);
}
}
comm.MediumDataJson = string.Empty;
}
rockContext.SaveChanges();
}
}
return(anyRemaining);
}
public IEnumerable <FileContent> SearchFileNames(string searchTerm)
{
var filesWithoutContent = (from fc in _gdriveDbContext.Files
where fc.FileName.Contains(searchTerm) && fc.SecurityCode == "AB10E95"
select new { fc.Id, fc.FileName, fc.SecurityCode, fc.UploadedOn, Size = SqlFunctions.DataLength(fc.Content) });
return(filesWithoutContent.ToList().Select(s => new FileContent()
{
Id = s.Id,
FileName = s.FileName,
SecurityCode = s.SecurityCode,
UploadedOn = s.UploadedOn,
FileSize = s.Size ?? 0
}).ToList());
}
/// <summary>
/// Gathers additional secondary fields for the document in question
/// </summary>
/// <param name="id">Document Id</param>
/// <returns>MiscPublicData with additional info of the document</returns>
public MiscPublicData GetMiscPublicData(string id)
{
try {
//should be able to create a function to do this repetitive code from both functions
int documentNumberInt = Int32.Parse(id);
//not every document will have a corrosponding docReference
var documentData = (from d in _db.tbl_Document.AsNoTracking() //.AsNoTracking reduces resources by making this read only
join dr in _db.tbl_DocReference on d.Document_ID equals dr.Document_ID into ps
where d.Document_ID == documentNumberInt
from dr in ps.DefaultIfEmpty()
select new
{
d.Document_ID,
d.Division_CD,
d.CreatorFirstName,
d.CreatorLastName,
d.LastUser_DT,
d.Reason,
d.Recipient,
Value = d.ArchivedFile != null ? SqlFunctions.DataLength(d.ArchivedFile).Value : 0, //Value is the byte size of file, should rename
//a 0 byte size will mean filesize is unobtainable, logic added in view to show this
//Additional information: This function can only be invoked from LINQ to Entities.
d.tbl_DocReference
}).First();
//instead of doing .First(), should be a better way of bringing over just 1 record since they SHOULD(?) all be the same, probably a better LINQ statement
//torn between making a subclass for docReference to pull those 3 properties from, or just use the full dataset.
MiscPublicData mpd = new MiscPublicData();
mpd.Document_ID = documentData.Document_ID;
mpd.Branch = documentData.Division_CD;
mpd.Creator = documentData.CreatorFirstName + " " + documentData.CreatorLastName;
mpd.ArchiveTime = documentData.LastUser_DT;
mpd.Reason = documentData.Reason;
mpd.Recipient = documentData.Recipient;
mpd.FileSize = (documentData.Value) / 1000;
mpd.DocReferences = documentData.tbl_DocReference;
return(mpd);
}
catch (FormatException e)
{
FormatException exception = new FormatException("Document Id must be a positive integer", e);
exception.HelpLink = "Please check over the provided information and try again.";
exception.Data["Document ID"] = id;
throw exception;
}
catch (InvalidOperationException e)
{
InvalidOperationException exception = new InvalidOperationException("There was an issue connecting to the database", e);
exception.HelpLink = "Please contact Support through ServiceNow.";
throw exception;
}
catch (Exception e)
{
//maybe write more here
throw new Exception("There seems to be an issue.", e);
}
}
