private static void InsertFileContentAsFileStream(Guid fileContentId, MemoryStream fileContentStream, SqlTransaction transaction)
{
UploadHelper.InsertEmptyFileContent(fileContentId, transaction, false, false);
using var uploadStream = SqlFileStreamProvider.GetSqlFileStreamForUpload(fileContentId, transaction);
fileContentStream.CopyTo(uploadStream);
uploadStream.Close();
}
private bool TryDownloadFromFileStream(HttpContext context, FileMetadata file, SqlConnection sqlConnection)
{
SqlCommand checkFileStreamEnabled = new SqlCommand("SELECT TOP 1 1 FROM sys.columns c WHERE OBJECT_SCHEMA_NAME(C.object_id) = 'LightDMS' AND OBJECT_NAME(C.object_id) = 'FileContent' AND c.Name = 'Content' AND c.is_filestream = 1", sqlConnection);
if (checkFileStreamEnabled.ExecuteScalar() == null)
{
return(false);
}
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted); // Explicit transaction is required when working with SqlFileStream class.
try
{
using (var fileStream = SqlFileStreamProvider.GetSqlFileStreamForDownload(file.FileContentId, sqlTransaction))
{
byte[] buffer = new byte[BUFFER_SIZE];
int bytesRead;
PopulateHeader(context, file.FileName, file.Size);
int totalBytesWritten = 0;
while ((bytesRead = fileStream.Read(buffer, 0, BUFFER_SIZE)) > 0)
{
if (!context.Response.IsClientConnected)
{
break;
}
Action writeResponse = () => context.Response.OutputStream.Write(buffer, 0, bytesRead);
if (_detectResponseBlockingErrors)
{
// HACK: `Response.OutputStream.Write` sometimes blocks the process at System.Web.dll!System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush();
// Until the issue is solved, this hack allows 1. logging of the problem, and 2. closing the SQL transaction while the thread remains blocked.
// Tried removing PreSendRequestHeaders, setting aspnet:UseTaskFriendlySynchronizationContext and disabling anitivirus, but it did not help.
// Total performance overhead of Task.Run...Wait is around 0.01 sec when downloading 200MB file with BUFFER_SIZE 100kB.
var task = Task.Run(writeResponse);
if (!task.Wait(_detectResponseBlockingErrorsTimeoutMs))
{
throw new FrameworkException(ResponseBlockedMessage +
$" Process {Process.GetCurrentProcess().Id}, thread {Thread.CurrentThread.ManagedThreadId}," +
$" streamed {totalBytesWritten} bytes of {file.Size}, current batch {bytesRead} bytes.");
}
}
else
{
writeResponse();
}
totalBytesWritten += bytesRead;
context.Response.Flush();
}
}
}
finally
{
try { sqlTransaction.Rollback(); } catch { }
}
return(true);
}
private async Task DownloadFromFileStream(FileMetadata fileMetadata, SqlConnection sqlConnection, HttpContext context)
{
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted)) // Explicit transaction is required when working with SqlFileStream class.
{
using (var stream = SqlFileStreamProvider.GetSqlFileStreamForDownload(fileMetadata.FileContentId, sqlTransaction))
{
await Download(fileMetadata, context, stream);
}
}
}
private static async Task UploadStreamToDatabase(Stream inputStream, Guid id, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
int bufferSize = 100 * 1024; // 100 kB buffer
byte[] buffer = new byte[bufferSize];
long totalbytesRead = 0;
SqlCommand checkFileStreamEnabled = new SqlCommand("SELECT TOP 1 1 FROM sys.columns c WHERE OBJECT_SCHEMA_NAME(C.object_id) = 'LightDMS' AND OBJECT_NAME(C.object_id) = 'FileContent' AND c.Name = 'Content' AND c.is_filestream = 1", sqlConnection, sqlTransaction);
if (checkFileStreamEnabled.ExecuteScalar() == null)
{ //FileStream is not supported
SqlCommand fileUpdateCommand = new SqlCommand("update LightDMS.FileContent set Content.WRITE(@Data, @Offset, null) where ID = @ID", sqlConnection, sqlTransaction);
fileUpdateCommand.Parameters.Add("@Data", SqlDbType.Binary);
fileUpdateCommand.Parameters.AddWithValue("@ID", id);
fileUpdateCommand.Parameters.AddWithValue("@Offset", 0);
var fileStream = inputStream;
var bytesRead = await fileStream.ReadAsync(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
if (bytesRead < buffer.Length)
{
fileUpdateCommand.Parameters["@Data"].Value = buffer.Where((val, ix) => ix < bytesRead).ToArray();
}
else
{
fileUpdateCommand.Parameters["@Data"].Value = buffer;
}
fileUpdateCommand.Parameters["@Offset"].Value = totalbytesRead;
fileUpdateCommand.ExecuteNonQuery();
totalbytesRead += bytesRead;
bytesRead = await fileStream.ReadAsync(buffer, 0, buffer.Length);
}
fileUpdateCommand.Dispose();
fileStream.Close();
}
else
{
using (SqlFileStream sfs = SqlFileStreamProvider.GetSqlFileStreamForUpload(id, sqlTransaction))
{
while (totalbytesRead < inputStream.Length)
{
var readed = await inputStream.ReadAsync(buffer, 0, bufferSize);
sfs.Write(buffer, 0, readed);
totalbytesRead += readed;
}
sfs.Close();
}
}
}
public async Task UploadThenDownload_ShouldSucceed()
{
string uploadContent = "Test file content";
string downloadContent;
var fileId = Guid.NewGuid();
var fileContentStream = new MemoryStream(Encoding.UTF8.GetBytes(uploadContent));
using var scope = _factory.Server.Services.CreateScope();
var connectionString = scope.ServiceProvider.GetService <IRhetosComponent <ConnectionString> >().Value;
using var connection = new SqlConnection(connectionString);
connection.Open();
using var transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
// Upload
UploadHelper.InsertEmptyFileContent(fileId, transaction, false, false);
var uploadStream = SqlFileStreamProvider.GetSqlFileStreamForUpload(fileId, transaction);
await fileContentStream.CopyToAsync(uploadStream);
uploadStream.Close();
// Download
var downloadStream = SqlFileStreamProvider.GetSqlFileStreamForDownload(fileId, transaction);
var downloadBytes = new byte[downloadStream.Length];
await downloadStream.ReadAsync(downloadBytes);
downloadStream.Close();
transaction.Rollback();
connection.Close();
downloadContent = Encoding.UTF8.GetString(downloadBytes);
Assert.Equal(uploadContent, downloadContent);
}
public void ProcessRequest(HttpContext context)
{
if (context.Request.Files.Count != 1)
{
Respond.BadRequest(context, "Exactly one file has to be sent as request in Multipart format. There were " + context.Request.Files.Count + " files in upload request.");
return;
}
var id = Guid.NewGuid();
var sw = Stopwatch.StartNew();
int bufferSize = 100 * 1024; // 100 kB buffer
byte[] buffer = new byte[bufferSize];
long totalbytesRead = 0;
SqlConnection sqlConnection = new SqlConnection(SqlUtility.ConnectionString);
sqlConnection.Open();
SqlTransaction sqlTransaction = null;
try
{
sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
SqlCommand checkFileStreamEnabled = new SqlCommand("SELECT TOP 1 1 FROM sys.columns c WHERE OBJECT_SCHEMA_NAME(C.object_id) = 'LightDMS' AND OBJECT_NAME(C.object_id) = 'FileContent' AND c.Name = 'Content' AND c.is_filestream = 1", sqlConnection, sqlTransaction);
string createdDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
if (checkFileStreamEnabled.ExecuteScalar() == null)
{ //FileStream is not supported
SqlCommand createEmptyFileContent = new SqlCommand("INSERT INTO LightDMS.FileContent(ID, [Content], [CreatedDate]) VALUES('" + id + "', 0x0, '" + createdDate + "');", sqlConnection, sqlTransaction);
createEmptyFileContent.ExecuteNonQuery();
SqlCommand fileUpdateCommand = new SqlCommand("update LightDMS.FileContent set Content.WRITE(@Data, @Offset, null) where ID = @ID", sqlConnection, sqlTransaction);
fileUpdateCommand.Parameters.Add("@Data", SqlDbType.Binary);
fileUpdateCommand.Parameters.AddWithValue("@ID", id);
fileUpdateCommand.Parameters.AddWithValue("@Offset", 0);
var fileStream = context.Request.Files[0].InputStream;
var bytesRead = fileStream.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
if (bytesRead < buffer.Length)
{
fileUpdateCommand.Parameters["@Data"].Value = buffer.Where((val, ix) => ix < bytesRead).ToArray();
}
else
{
fileUpdateCommand.Parameters["@Data"].Value = buffer;
}
fileUpdateCommand.Parameters["@Offset"].Value = totalbytesRead;
fileUpdateCommand.ExecuteNonQuery();
totalbytesRead += bytesRead;
bytesRead = fileStream.Read(buffer, 0, buffer.Length);
}
fileUpdateCommand.Dispose();
fileStream.Close();
}
else
{
using (SqlFileStream sfs = SqlFileStreamProvider.GetSqlFileStreamForUpload(id, createdDate, sqlTransaction))
{
while (totalbytesRead < context.Request.Files[0].ContentLength)
{
var readed = context.Request.Files[0].InputStream.Read(buffer, 0, bufferSize);
sfs.Write(buffer, 0, readed);
totalbytesRead += readed;
}
sfs.Close();
}
}
sqlTransaction.Commit();
sqlConnection.Close();
_performanceLogger.Write(sw, "UploadFile (" + id + ") Executed.");
Respond.Ok(context, new { ID = id });
}
catch (Exception ex)
{
try
{
// Try to discard the database transaction (if still open and working).
if (sqlTransaction != null)
{
sqlTransaction.Rollback();
}
sqlConnection.Close();
}
catch
{
}
if (ex.Message == "Function PathName is only valid on columns with the FILESTREAM attribute.")
{
Respond.BadRequest(context, "FILESTREAM is not enabled on Database, or FileStream FileGroup is missing on database, or FILESTREAM attribute is missing from LightDMS.FileContent.Content column. Try with enabling FileStream on database, add FileGroup to database and transform Content column to VARBINARY(MAX) FILESTREAM type.");
}
else
{
Respond.InternalError(context, ex);
}
}
}
