private async Task InsertData(FileStreamFile fsf, SqlConnection conn)
{
string sqlCommand = @"INSERT INTO [Model].[DocumentMeta]
([DocumentId]
,[DocumentType]
,[Container]
,[Name]
,[FileType]
,[ContentType]
,[AzureBlobPath])
VALUES
(@DocumentId
,@DocumentType
,@Container
,@Name
,@FileType
,@ContentType
,@AzureBlobPath)";
var cmd = new SqlCommand(sqlCommand, conn);
cmd.Parameters.Add(new SqlParameter("@DocumentId", fsf.DocumentId));
cmd.Parameters.Add(new SqlParameter("@DocumentType", fsf.DocumentType));
cmd.Parameters.Add(new SqlParameter("@Container", fsf.Container));
cmd.Parameters.Add(new SqlParameter("@Name", fsf.Name));
cmd.Parameters.Add(new SqlParameter("@FileType", fsf.FileType));
cmd.Parameters.Add(new SqlParameter("@ContentType", fsf.ContentType));
cmd.Parameters.Add(new SqlParameter("@AzureBlobPath", fsf.BlobLocation));
await cmd.ExecuteNonQueryAsync();
}
public async Task CopyDataAsync()
{
var fileStreamFiles = new List <FileStreamFile>();
using (var conn = new SqlConnection(_sourceSqlServerDatabaseConnectionString))
{
// List all files to be downloaded
SqlCommand cmd = new SqlCommand(_selectFileDocumentsCommand, conn);
await conn.OpenAsync();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// You'll need to adjust the nr. of fields here
var fsf = new FileStreamFile(
reader.GetValue(0).ToString(),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
reader.GetValue(4).ToString(),
reader.GetValue(5).ToString(),
reader.GetValue(6).ToString());
fileStreamFiles.Add(fsf);
}
}
// Foreach file, download content and copy to azure storage
cmd.CommandText = _selectDocumentsCommand;
cmd.Parameters.Add(new SqlParameter("@Id", string.Empty));
foreach (var fsf in fileStreamFiles)
{
cmd.Parameters[0].Value = fsf.StreamId;
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
var fileBytes = (byte[])reader[0];
await CopyFileStreamFile(fsf, fileBytes);
}
}
ExportToCsvFile(fileStreamFiles);
}
}
/// <summary>
/// Copy file bytes to Azure Blob
/// </summary>
/// <param name="fsf"></param>
/// <param name="fileBytes"></param>
/// <returns></returns>
private async Task CopyFileStreamFile(FileStreamFile fsf, byte[] fileBytes)
{
try
{
var stopwatch = Stopwatch.StartNew();
var cloudStorageAccount = CloudStorageAccount.Parse(_destinationAzureBlobStorageConnectionString);
var blobClient = cloudStorageAccount.CreateCloudBlobClient();
// Ensure container exits
var container = blobClient.GetContainerReference(fsf.Container);
await container.CreateIfNotExistsAsync();
// open stream writer to file
var blobFileName = Path.Combine(fsf.Folder, fsf.Name);
var blob = container.GetBlockBlobReference(blobFileName);
if (await blob.ExistsAsync())
{
return;
}
await blob.UploadFromByteArrayAsync(fileBytes, 0, fileBytes.Length);
fsf.BlobLocation = blob.StorageUri.PrimaryUri.AbsolutePath;
stopwatch.Stop();
Console.WriteLine($"File {fsf.Name} saved to container {fsf.Folder}, {fileBytes.Length} bytes, in {stopwatch.ElapsedMilliseconds}ms");
}
catch (Exception ex)
{
Console.WriteLine($"Error with {fsf.Name}: {ex}");
using (var writer = new StreamWriter($"{fsf.DocumentId}.csv"))
{
var csv = new CsvWriter(writer);
csv.WriteRecord(fsf);
}
}
}