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