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