private static async Task SaveToDbAsync(string connectionString, FunctionFileInfo queueInfo)
{
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
var selectId = "select MAX(DocumentNode) from [Production].[Document] where DocumentNode.GetAncestor(1) = hierarchyid::GetRoot()";
Microsoft.SqlServer.Types.SqlHierarchyId maxId;
using (var cmd = new SqlCommand(selectId, connection, transaction))
{
maxId = (Microsoft.SqlServer.Types.SqlHierarchyId) await cmd.ExecuteScalarAsync();
}
var insertDoc =
"INSERT INTO [Production].[Document] (DocumentNode, [Owner], [Title], [FileName], [FileExtension], [Revision], [DocumentSummary], [Document], [Status])" +
"VALUES(hierarchyid::GetRoot().GetDescendant(CAST(@id as hierarchyid), null), 1, @title, @file, @extension, 1, null, @content, 1)";
using (var cmd = new SqlCommand(insertDoc, connection, transaction))
{
cmd.Parameters.Add("@id", System.Data.SqlDbType.NVarChar).Value = maxId.ToString();
cmd.Parameters.Add("@title", System.Data.SqlDbType.NVarChar).Value = Path.GetFileName(queueInfo.FileName);
cmd.Parameters.Add("@file", System.Data.SqlDbType.NVarChar).Value = queueInfo.FileName;
cmd.Parameters.Add("@extension", System.Data.SqlDbType.NVarChar).Value = Path.GetExtension(queueInfo.FileName);
cmd.Parameters.Add("@content", System.Data.SqlDbType.VarBinary, queueInfo.Data.Length).Value = queueInfo.Data;
await cmd.ExecuteNonQueryAsync();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
private static async Task <FunctionFileInfo> GetFromDbAsync(string connectionString, string data)
{
var result = new FunctionFileInfo();
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
var selectDoc = @"select FileName, Document from [Production].[Document] where FileName = @fileName";
using (var cmd = new SqlCommand(selectDoc, connection))
{
cmd.Parameters.Add("@fileName", System.Data.SqlDbType.NVarChar).Value = data;
Guid id;
if (Guid.TryParse(data, out id))
{
cmd.CommandText += " or rowguid = @id";
cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier).Value = id;
}
if (_hierarchyIdRegex.IsMatch(data))
{
cmd.CommandText += " or CAST([DocumentNode] AS nvarchar(100)) = @path";
cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar).Value = data;
}
using (var dataReader = await cmd.ExecuteReaderAsync())
{
if (dataReader.HasRows)
{
await dataReader.ReadAsync();
var content = dataReader["Document"];
result.Data = content != DBNull.Value ? (byte[])content : new byte[0];
result.FileName = (string)dataReader["FileName"];
}
}
}
}
return(result);
}