My Master's Thesis Problems and solutions encountered…


Download files from a MS SQL Database – MVVM

This post will show to download a file that is located in a MS SQL Database. The user goes through the following steps:

1) The user clicks the download button
2) The user is informed that the download is in progress (progress bar)
3) The user has to confirm the download
4) The user is asked to define where he want the file to be saved
5) The user is of the success or failure of saving the file to his desktop

The most important thing to notice is that the user has to 'confirm' the download twice: When he first clicks the button, and again when it has been downloaded, and the user can chose to 'Save' or 'Cancel' to open the dialog box, where the user determines where on his desktop he want to save the file.

Step 1. Create the object that is returned from the database containing the file you want to download. The file object has two attributes: The name of the file, and the file itself, which is saved as a bite array, and is of type SqlBytes:

using System.Data.SqlTypes;

    public class FileDTO

        private string _fileType;
        private SqlBytes _data;

        public string FileType
            get { return _fileType;}
            set { _fileType = value; }

        public SqlBytes Data
            get  {return _data;}
            set  {_data = value; }

Step 2: While you're in the Model, create the method GetFile() that returns an instance of the FileDTO class, that contains the file from the database.

 public FileDTO GetFile(int ID)

            //New instance
            FileDTO fileDTO = new FileDTO();
            SqlConnection dbConn = connectToDB();
            //The query
            string _selectQuery = string.Format("Select * from attachments where a_ID = " + ID + "");
                using (SqlCommand cmd = new SqlCommand("Select * from attachments where a_ID = @ID", dbConn))
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
                    SqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                        fileDTO.FileType = rdr.GetString(2);
                        fileDTO.Data = rdr.GetSqlBytes(3);
            catch (Exception)
            return fileDTO;

Step 3: Create the "Download file" button in the View. Notice that it binds to the command DownloadFile

<Button x:Name="Get" Content="Download file" 
                Style="{StaticResource Knap}"  Width="80"  
                Command="{Binding DownloadFile}" 

Step 4: In the ViewModel, create the command that executes when the "Download File" button is clicked, and that calls the GetFile() method described in step 2.

You will have to notice several things: First, the executeDownloadFile calls the GetFile(), and when the call is completed, the WebService_GetFileCompleted method is called, and return value (the file) is saved in a newly created object of type FileDTO , i.e. the class you created in step 1.

//Property bound to the button
 private DelegateCommand downloadFile;
        public DelegateCommand DownloadFile
                if (downloadFile == null)
                    downloadFile = new DelegateCommand(executeDownloadFile, canExecuteDownloadFile);
                return downloadFile;

//Defines if the button is clickable
        private bool canExecuteDownloadFile(object parameter) //Definerer om knappen er klik-bar. 
                return true; //always clickable

        private void executeDownloadFile(object parameter)
           //Shows progress bar while downloading 
           ShowText = "Downloading file";
            BusyWindow = true;

            WebService = new Service1Client();
            //defines the method that is called when it returns with a value
            WebService.GetFileCompleted += new EventHandler<GetFileCompletedEventArgs>(WebService_GetFileCompleted);
             //Calls the method

         //Method is called when the Model has returned with a value
        void WebService_GetFileCompleted(object sender, GetFileCompletedEventArgs e)
            // Call the method CallBackFromService with the result (the file) as a parameter
        //Create new instance of FileDTO. 
        FileDTO fileDTO;
        public void CallBackFromService(FileDTO fileDTOCallBack)
            Set to the local version of the fileDTO, close progressbar
            fileDTO = fileDTOCallBack;
            BusyWindow = false;

            //Makes button from (3) visible: The user has to confirm that he wants to download the file. 
            AfterDownload = Visibility.Visible;

Step 6: After the CallBackFromService has been called, the user must confirm that he wants to download the file. The button in "Save File" in window (3) shown above is bound to the command SaveAttachment:

     //Property bound to the button
        private DelegateCommand saveAttachment;
        public DelegateCommand SaveAttachment
                if (saveAttachment == null)
                    saveAttachment = new DelegateCommand(executeSaveAttachment);
                return saveAttachment;
        private void executeSaveAttachment(object parameter)
            //Enables the apperance of a Dialog, where the user can specify where to save the file
            SaveFileDialog textDialog = new SaveFileDialog();

            //We found out what type of file it is (.doc, .pdf, etc). 
            //name of file, for example MyFile.doc
            string test = fileDTO.FileType;
            //Gets the number of characters before the "."
            int index = test.LastIndexOf(@".");
            //Find the type of the file by retrieving the characters after the index number
            string docType = test.Substring(index);
            //Doctype : .doc, .pdf, etc. 
           //Knowing the type, we can now define which default type-value has to be chosen in the Dialog, where the user saves the file. 

            if (docType == ".jpg")

                textDialog.DefaultExt = ".jpg";
                textDialog.Filter = "JPG|*.jpg";


            else if (docType == ".docx")
                textDialog.DefaultExt = ".docx";
                textDialog.Filter = "Microsoft Office 2008 .docx|*.docx";


            else if (docType == ".doc")
                textDialog.DefaultExt = ".doc";
                textDialog.Filter = "Microsoft Office 2003 .doc|*.doc";


//10 other formats have been added
             //save the file in a bite array
            byte[] fileBytes = fileDTO.Data;//your bytes here 
            //Open dialog where the user determines where to save the file. 
             bool? result = textDialog.ShowDialog();
            if (result == true)
                When the user clicks OK, the file is saved. 
                using (Stream fs = (Stream)textDialog.OpenFile())
                    fs.Write(fileBytes, 0, fileBytes.Length);

            //The user is informed of the download. 
            AfterRealDownload = Visibility.Visible;

And that's it!

Comments (0) Trackbacks (0)

No comments yet.

Leave a comment

nine − = 6

No trackbacks yet.