1

My winforms application is receiving a file from SQL Server, and I would like to display how much of the file has been downloaded in the form of a progress bar.

To retrieve the file, I am calling a stored procedure and saving the result into a byte array with:

Dim file() as Byte = SQLCommand.ExecuteScalar()

This works fine and for smaller files, I don't really need a progress bar as they complete so quickly. However, some of the files may get quite large, and some of the connections might not be very good, so I really think I will need some sort of progress indication.

I understand that I will probably need to use a background worker thread, and I understand how to do that. But how can periodically check how much of the file has been received, as doing it the way I am, it seems to perform the action in one big chunk?

Can it be done this way? Or do I need to review how I am receiving the file entirely?

My application is VB.Net but C# answers would perfectly acceptable.

Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • Since your coding language is VB.NET, do you think this question and its' answers can assist you with this problem? I do not think the question is duplicate, I just don't know enough about the process to post an answer to it. https://stackoverflow.com/questions/16688990/how-to-display-progress-bar-while-executing-big-sqlcommand-vb-net – Kanstantsin Arlouski Aug 01 '17 at 16:38
  • 1
    Check out https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sqlclient-streaming-support and https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getstream?view=netframework-4.7 – David Browne - Microsoft Aug 01 '17 at 16:40
  • 1
    @KanstantsinArlouski Thanks for your suggestion. I actually just read that a moment before you commented. Unfortunately, it doesn't help much, as in that question, they are dealing with a large number of rows, rather than a single, large BLOB of data. – Gravitate Aug 01 '17 at 16:44

2 Answers2

3

See SqlClient Streaming Support. You need to do several steps:

The linked articles show more details on how to accomplish the streaming part, adding a progress bar is trivial after that.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks. Apparently, I had a LOT of reading up to do on async/await. I am more familiar with BackgroundWorker, so it is a bit new to me. I still don't fully understand it but I think it is enough for me to get something working. – Gravitate Aug 03 '17 at 11:50
  • @Gravitate you shouldn't need to add async/await to the equation. Sync API should work as well. But if you're getting up to speed in async/await in the process and use that instead, by all means, go for it, is much better. – Remus Rusanu Aug 03 '17 at 12:51
2

You can use the streaming feature in .NET 4.5 in conjunction with the async programming model. 

private static async Task CopyBinaryValueToFile() {  
         string filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "binarydata.bin");  

         using (SqlConnection connection = new SqlConnection(connectionString)) {  
            await connection.OpenAsync();  
            using (SqlCommand command = new SqlCommand("SELECT [bindata] FROM [Streams] WHERE [id]=@id", connection)) {  
               command.Parameters.AddWithValue("id", 1);  

               // The reader needs to be executed with the SequentialAccess behavior to enable network streaming  
               // Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions  
               using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess)) {  
                  if (await reader.ReadAsync()) {  
                     if (!(await reader.IsDBNullAsync(0))) {  
                        using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write)) {  
                           using (Stream data = reader.GetStream(0)) {  

                              // Asynchronously copy the stream from the server to the file we just created  
                              await data.CopyToAsync(file);  
                           }  
                        }  
                     }  
                  }  
               }  
            }  
         }  
      }

see this: sqlclient-streaming-support

Alex
  • 2,247
  • 1
  • 27
  • 37