My Master's Thesis Problems and solutions encountered…

29Oct/100

Upload Files to MS SQL DB – MVVM

In my application, I needed the users to be able to upload a file in order to document their answer to questions about their working and environmental conditions.

First, I am just going to show the sequence of windows the user has to go through in order to upload a specific file.

1) The users clicks the "Upload file" button
2) The user chooses which file to upload
3) The user is informed about the upload (progress bar)
4) The user is informed of whether the upload succeeded or failed

Step 1: Create the "Upload file" button.


        <Button x:Name="Upload"
            Content="Upload file"
            Command="{Binding UploadFile}"
            HorizontalAlignment="Center"/>

Step 2: Create the UploadFile command that the button is bound. If you have not already created the DelegateCommand.cs needed to handle your commands from buttons, add the following class to your ViewModel:

using System;
using System.Windows.Input;

namespace ViewModel
{
    public class DelegateCommand : ICommand //
    {
        private Predicate<object> _canExecute;
        private Action<object> _method;
        public event EventHandler CanExecuteChanged;

        public DelegateCommand(Action<object> method)
            : this(method, null)
        {
        }

        public DelegateCommand(Action<object> method, Predicate<object> canExecute)
        {
            _method = method;
            _canExecute = canExecute;
        }

        public bool CanExecute(object parameter)
        {
            if (_canExecute == null)
            {
                return true;
            }

            return _canExecute(parameter);
        }

        public void Execute(object parameter)
        {
            _method.Invoke(parameter);
        }

        protected virtual void OnCanExecuteChanged(EventArgs e)
        {
            var canExecuteChanged = CanExecuteChanged;

            if (canExecuteChanged != null)
                canExecuteChanged(this, e);
        }

        public void RaiseCanExecuteChanged()
        {
            OnCanExecuteChanged(EventArgs.Empty);
        }
    }
}

Otherwise, just create UploadFile command, that defines what needs to be done when the user clicks the button.


        private DelegateCommand uploadFile;
        public DelegateCommand UploadFile // property bound to the button
        {
            get
            {
                if (uploadFile == null)
                    uploadFile = new DelegateCommand(executeuploadFile, canExecuteUploadFile);
                return uploadFile;
            }
        }
 //Defines if the button is clickable.
        private bool canExecuteUploadFile(object parameter)
        {
                return true; //always clickable... for now.
          }

      //Execute
        private void executeuploadFile(object parameter)
        {

            //Provides the dialog box that allows the user to chose files from their computer
            OpenFileDialog openDialog = new OpenFileDialog();
          //New instance of WebService
            WebService = new Service1Client();

           //Open dialog box, where user choses file
           if (openDialog.ShowDialog() == true)
            {
                try
                {
                    using (Stream stream = openDialog.File.OpenRead())
                    {
                        // Don't allow really big files (more than 5 MB).
                        if (stream.Length < 5120000)
                        {
                            byte[] data = new byte[stream.Length];
                            stream.Read(data, 0, (int)stream.Length);

                             //The method that is called after the upload has completed:
                            WebService.UploadFileCompleted += new EventHandler<AsyncCompletedEventArgs>(WebService_UploadFileCompleted);
                            // The method that uploads to the database: An ID, the name of the file, and the file in a bite array.
                            WebService.UploadFileAsync(AID, openDialog.File.Name, data);
                           //Open progress bar that lets the user know the file is being uploaded (window 2).
                             ShowText = "Uploading file";
                            BusyWindow = true;

                        }
                        stream.Close();
                    }

                }
                catch (FileNotFoundException ex)
                {

                }

            }
        }

//The method that is called when the file has been uploaded
  void WebService_UploadFileCompleted(object sender, AsyncCompletedEventArgs e)
        {
            //Close busyWindow
            BusyWindow = false;

//Show verification childwindow. 

        }

Remember that you have to make changes to the .config files on both the client and the server, in order to allow larger files to be sent.

Step 4: Be sure to have a table in your database, where you can upload the files: You'll need a table with the following 3 columns: An Int attribute to store the unique value, a varchar(50) for the name of the file, and a varbinary(MAX) for containing the bite array that constitutes the file.

Step 5: I am not going show how to add the [ServiceContract] etc. in the Model, as it has been showed many times before, so I am just showing the UploadFile() that is defined in the Model:


 public class FileDTO_DAC : DBConnection
    {

        public void UploadFile(int a_ID, string FileType, byte[] Data)
        {
            //Open SQL connection
            SqlConnection dbConn = connectToDB();

         try
            {
                dbConn.Open();
                using (SqlCommand cmd = new SqlCommand("INSERT INTO attachments (a_ID, fileName, fileBin) VALUES (@a_ID, @Type, @BinaryData)", dbConn))
                {
                    cmd.Parameters.Add("@a_ID", SqlDbType.Int).Value = a_ID;
                    cmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = FileType;
                    cmd.Parameters.Add("@BinaryData", SqlDbType.VarBinary).Value = Data;
                    cmd.ExecuteNonQuery();
                }
             }
            catch (Exception)
            {
                throw;
            }
            dbConn.Close();

Enjoy!

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment



× 5 = five

No trackbacks yet.