Upload/download pdf files in SQL Server using mvc 2010

Question

I've been learning on my own how to program in c# with mvc 2010 Express, as I've been asked to create an application for the company I work for. The last thing I have to do is allow the users to upload and download a pdf file and I can't find a solution that helps me.

I use SQL Server 2005. The table I want to use is called Clients. It has an Id as the primary key. Should I create an attribute to store the pdf as a binary file? Or how?

To upload the pdf I have a link in my view that redirects the user to another view (named Upload) with the id of the client. The controller name is Home, so the user will see something like:

Home.aspx/Upload/2

There, I want the user to be able to select the pdf he wants to upload and then click the button to upload it. So the controller will handle it with the [HttpPost].

To edit a client it was pretty straight forward, as I created Model Views in the folder ViewModels and then passed them to the controller. But how can I pass to the controller both the id and the pdf file? I need the id to know what user is that. And how can I store the pdf in my table Clients in SQL Server? And then how can I download the pdf?

Answer 1

This is what i'm using...you should do some changes to adapt it to your requirements.

how can I pass to the controller both the id and the pdf file?

View:

  @using (Html.BeginForm("Add", "Archivos",
                     FormMethod.Post, new { id = "attachment", enctype = "multipart/form-data", encoding = "multipart/form-data" }))
    { 


        @Html.HiddenFor(x => Model.UserID)
        <input type="file" name="uploadFile" id="uploadFile" />

       <input type="submit" value="Guardar"/>

    }

Controller:

   [HttpPost]
    public ActionResult Add(HttpPostedFileBase uploadFile, int UserID)
    {
        if (uploadFile != null && uploadFile.ContentLength > 0)
        {

            //instance the user.. i.e "User1"

            byte[] tempFile = new byte[uploadFile.ContentLength];
            uploadFile.InputStream.Read(tempFile, 0, uploadFile.ContentLength);

            User1.file.Content = tempFile;
            User1.file.Save();

        }

        return RedirectToAction("Index");
    }

And then how can I download the pdf?

Controller:

public ActionResult Get(int UserID)
    { 

        var User1 = new User {UserID = UserID };
        User1.LoadFile();

   //If file exists....

        MemoryStream ms = new MemoryStream(User1.File.Content, 0, 0, true, true);
        Response.ContentType = User1.File.Type;
        Response.AddHeader("content-disposition", "attachment;filename=" + User1.File.Name);
        Response.Buffer = true;
        Response.Clear();
        Response.OutputStream.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
        Response.OutputStream.Flush();
        Response.End();
        return new FileStreamResult(Response.OutputStream, User1.File.Type);

    }

View:

    @Html.ActionLink("PDF", "Get", "Files", new { UserID = Model.UserID }, new { @class = "pdf-icon-l", title="Open PDF document" })

And how can I store the pdf in my table Clients in SQL Server? 

Database Table:

 CREATE TABLE [dbo].[FileTableName] (
 [UserID] int NOT NULL,
 [Name] varchar(256) NOT NULL,
 [Type] varchar(256) NOT NULL,
 [Length] int NOT NULL,
 [Content] varchar(max) NOT NULL) // option: varbinary(max)

To store the files I'm using a Stored Procedure

Model:

 public class File
 {

    public string Name { get; set; }
    public string Type { get; set; }
    public long Length { get; set; }
    public byte[] Content { get; set; }

}

public class User

{
    public int UserID {get; set;}
    public string name {get; set;}
    /**/

    public file file {get; set;}


    /**/

    public void SaveFile()
   {
    SqlDataReader _dataReader;
    using (new MyConnectionManager())
    {
        using (_sqlCommand = new SqlCommand("SavePDFFile", MyConnectionManager.Connection)) 
       {
        _sqlCommand.CommandType = CommandType.StoredProcedure;
        _sqlCommand.Parameters.Add(new SqlParameter("@UserID", this.UserID));
        _sqlCommand.Parameters.Add(new SqlParameter("@Name", this.Name));
        _sqlCommand.Parameters.Add(new SqlParameter("@Type", this.Type));
        _sqlCommand.Parameters.Add(new SqlParameter("@Length", this.Length));
        _sqlCommand.Parameters.Add(new SqlParameter("@Content", this.Content));
        _dataReader = _sqlCommand.ExecuteReader();

        _dataReader.Close();
    }
  }
}

  public void LoadFile()
    { 
        SqlDataReader _dataReader;
        using (new MyConnectionManager())
        {
             using (_sqlCommand = new SqlCommand("GetPDFFIle", MyConnectionManager.Connection))
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Parameters.Add(new SqlParameter("@UserID", this.IDEnsayo));
            _dataReader = _sqlCommand.ExecuteReader();
            if (_dataReader.HasRows)
            {
                _dataReader.Read();
                this.File.Name = (string)_dataReader["Name"];
                this.File.Type = (string)_dataReader["Type"];
                this.File.Length = (int)_dataReader["Length"];
                this.File.Content = (byte[])_dataReader["Content"];

            }
            _dataReader.Close();
        }
    }
  }

 }