asked on

Retrieve Attachments - ASP.NET MVC 5 website with Microsoft SQL

I am using ASP.NET MVC 5 with entity framework. I have an existing SQL database that houses a table with a path and name for an attachment (Attachment) that links to another table (Meeting). I want the details view of the Meeting to show the name of the attachment and a link to download the attachment. The attachments are stored on a server on the same network. The attachments are not housed in SQL. I will also want to update the create view to create an attachment but I want to tackle one thing at a time.

Sidenote: This project was created using Entity Framework scaffolding so most of the code is default created from the scaffolding.

Meeting Details View

@model CRM.Models.Meeting

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div id="MeetingDetails">
    <h4>Meeting</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.InteractionDate)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.InteractionDate)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.Notes)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Notes)
        </dd>
        <p />

        <dt>
            @Html.DisplayNameFor(model => model.CustomerCallDecisionedBy)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.CustomerCallDecisionedBy)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.CustomerCallDecisionDate)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.CustomerCallDecisionDate)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.BankOfficer)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BankOfficer)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.TrustOfficer)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.TrustOfficer)
        </dd>
        <br />

        <dt>
            @Html.DisplayNameFor(model => model.ConfidenceRating)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.ConfidenceRating)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.IsReview)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.IsReview)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.Customer1.SystemOfRecordId)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Customer1.SystemOfRecordId)
        </dd>
        <br />
        <dt>
            @Html.DisplayNameFor(model => model.Employee1.FirstName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Employee1.FirstName)
        </dd>
        <br />
    </dl>
</div>

<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
    @Html.ActionLink("Back to List", "Index")
</p>

Open in new window

Meetings Controller - GET Details (if needed I can provide code for the rest of the controller)

 // GET: Meetings/Details
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Meeting meeting = db.Meetings.Find(id);
            if (meeting == null)
            {
                return HttpNotFound();
            }
            return View(meeting);
        }

Open in new window

This is a screenshot of part of the model diagram showing the relationship between the Attachment and the Meeting table. Each attachment is linked to a specific meeting. Each Meeting is linked to a specific customer.

Robb Hill

Well this seems like a broad question.

You will need to pull the data to your controller from the model that stores your information for path, file..etc.

You will use the .net file/io objects to build the proper code to open a file..etc.

This logic then need only be wired up to your view for how you expect your UI to interact with your db and the network.

If the DB's only purpose here is to pull database locations ti would seem that you would do the following.

UI
---Build UI control that will open the file....this is up to you.

Controller ---- this will be your logic that takes the information from your Model and builds the logic.

You may have something like this..just depends how you build your path and use this code.

Gets a string representing the directory's full path.

Sample usage:

string filename = @"C:\MyDirectory\MyFile.bat";
FileInfo fileInfo = new FileInfo(filename);
string directoryFullPath = fileInfo.DirectoryName; // contains "C:\MyDirectory"

Open in new window

Link to the MSDN documentation.

https://docs.microsoft.com/en-us/dotnet/api/system.io.fileinfo.directoryname?redirectedfrom=MSDN&view=netframework-4.8#System_IO_FileInfo_DirectoryName

the above example would be variables for path...from your model.

Hope this helps.

Deepak Vasudevan

I would have an handler (.ashx file) which takes an attachment id and using AJAX it retrieves the attachment and streams the same to an appropriate view in the browser.

Intelli-Seeker

ASKER

Do you happen to have a reference or example of how I would set up the ashx file?

Chinmay Patel

Hello Intelli-Seeker,

As per our discussion, there were couple of fixes needed for your code to start working. Once I complete my current work I will post my code for future reference.

Regards,
Chinmay.

Intelli-Seeker

ASKER

I was able to get the attachments path uploaded to the database as a GUID using the advice of Chimnay in this post - https://www.experts-exchange.com/questions/29149058/Upload-and-Download-files-to-server-as-GUID-in-MVC-5-with-Entity-Framework-6.html?anchorAnswerId=42894856#a42894856. The files are saved to the server in the uploads folder.

Now, I am working on the download and am having some issues. I created a table and am trying to add a link to download the attachment. With my current code, the link tries to navigate to the path in the database. It is not downloading the file that is set in the path. I am not sure how to get my controller set up to properly manage downloading the attachment. I provided the code for the table in the details view and a portion of the controller code.

Details View

    <div id = "Attachments" class="hidden-print">
        <h3>
            Attachments
        </h3>
        <br />
        <table class="table-striped table-bordered">
            <thead>
                <tr>
                    <th scope="col">Id</th>
                    <th scope="col">Path</th>
                    <th scope="col"> Name</th>
                    <th scope="col">Meeting</th>
                    <th scope="col">Interaction Date</th>
                    <th scope="col"> Customer Name</th>
                </tr>
            </thead>
            <tbody>
                @foreach(var attachment in Model.Attachments)
                {
                <tr>
                    <td> @attachment.Id</td>
                    <td><a href = @attachment.Path> Download Attachment </a></td>
                    <td> @attachment.Name </td>
                    <td> @attachment.Meeting </td>
                    <td> @attachment.Meeting1.InteractionDate </td>
                    <td> @attachment.Meeting1.Customer1.Name </td>
                 </tr>
                }
            </tbody>
        </table>
    </div>

Open in new window

Controller Code

// GET: Meetings/Details
public ActionResult Details(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    Meeting meeting = db.Meetings.Find(id);
    if (meeting == null)
    {
        return HttpNotFound();
    }
    foreach (string upload in Request.Files)
    {
        if (Request.Files[upload].FileName != "")
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + "/uploads/";
            string filename = Path.GetFileName(Request.Files[upload].FileName);
            Request.Files[upload].SaveAs(Path.Combine(path, filename));
        }
    }
    return View(meeting);
}

Open in new window

Intelli-Seeker

ASKER

I also tried this code for the table and it still did not work for me. I'm sure there is something I am missing in the controller.

        <table class="table-striped table-bordered">
            <thead>
                <tr>
                    <th scope="col">Id</th>
                    <th scope="col">Name</th>
                    <th scope="col">Meeting</th>
                    <th scope="col">Interaction Date</th>
                    <th scope="col">Customer Name</th>
                    <th scope="col">Download Attachment</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var attachment in Model.Attachments)
                {

                    <tr>
                        <td>@attachment.Id</td>
                        <td>@attachment.Name</td>
                        <td>@attachment.Meeting</td>
                        <td>@attachment.Meeting1.InteractionDate</td>
                        <td>@attachment.Meeting1.Customer1.Name</td>
                        <td>@Html.ActionLink("Download", "Details", new { download = Model.Attachments.ToString()})</td>
                    </tr>
                }
            </tbody>
        </table>

Open in new window

ASKER CERTIFIED SOLUTION

Chinmay Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Intelli-Seeker

ASKER

That is helpful. However, the name and extension will be different for each file as I am creating a GUID for each file. The files are saved to the uploads folder on the server. Here is the portion of my Controller that handles creating the meetings.

 [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "Id,InteractionDate,Notes,Customer,Employee,Purpose,FollowUpItems")] Meeting meeting, HttpPostedFileBase upload)

        {
            if (ModelState.IsValid)
            //Add upload capability to the create meeting view
            {
                List<Attachment> attachments = new List<Attachment>();
                for (int i = 0; i <Request.Files.Count; i++)
                {
                    var file = Request.Files[i];
                    if(file != null && file.ContentLength > 0)
                    {
                        var fileName = Path.GetFileName(file.FileName);
                        var id = Guid.NewGuid().ToString();
                        Attachment attachment = new Attachment()
                        {
                            Name = fileName,
                            Path = id + Path.GetExtension(upload.FileName),
                        };
                        attachments.Add(attachment);
                        var path = Path.Combine(Server.MapPath("~/uploads"), attachment.Path);
                        file.SaveAs(path);
                    }
                }
            //Add Account List to create view
                foreach (var account in db.Accounts.Where(x => x.Customer == meeting.Customer))
                {
                    var snapshot = new Snapshot
                    {
                        AccountNumber = account.AccountNumber,
                        System = account.System,
                        Category = account.Category,
                        Balance = account.Balance,
                    };

                    meeting.Snapshots.Add(snapshot);
                }
                meeting.Attachments = attachments;
                db.Meetings.Add(meeting);
                db.SaveChanges();
                return RedirectToAction("Details", new { Id = meeting.Id });
            }
                return View(meeting);
        }

Open in new window

Intelli-Seeker

ASKER

I love Experts Exchange. There are so many talented people who are willing to assist those who are still getting their feet wet with coding. Chinmay was especially helpful. He took the time out of his schedule to assist via a Skype chat. For that I am eternally grateful.

I will post my code below so that anyone else with the same problem can use this solution.

Details View:

<div id="Attachments" class="hidden-print">
        <h3>
            Attachments
        </h3>
        <br />
        <table class="table-striped table-bordered">
            <thead>
                <tr>
                    <th scope="col">Id</th>
                    <th scope="col">Path</th>
                    <th scope="col">Name</th>
                    <th scope="col">Meeting</th>
                    <th scope="col">Interaction Date</th>
                    <th scope="col">Customer Name</th>
                    <th scope="col">Download Attachment</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var attachment in Model.Attachments)
                {

                <tr>
                    <td>@attachment.Id</td>
                    <td>@attachment.Path</td>
                    <td>@attachment.Name</td>
                    <td>@attachment.Meeting</td>
                    <td>@attachment.Meeting1.InteractionDate</td>
                    <td>@attachment.Meeting1.Customer1.Name</td>
                    <td>@Html.ActionLink("Download", "Download", new { id = attachment.Id }) </td>
                </tr>
                }
            </tbody>
        </table>
    </div>

Open in new window

Meetings Controller Upload Attachments POST Meetings Create:

public ActionResult Create([Bind(Include = "Id,InteractionDate,Notes,Customer,Employee,Purpose,FollowUpItems")] Meeting meeting, HttpPostedFileBase upload)

        {
            if (ModelState.IsValid)
            //Add upload capability to the create meeting view
            {
                List<Attachment> attachments = new List<Attachment>();
                for (int i = 0; i <Request.Files.Count; i++)
                {
                    var file = Request.Files[i];
                    if(file != null && file.ContentLength > 0)
                    {
                        var fileName = Path.GetFileName(file.FileName);
                        var id = Guid.NewGuid().ToString();
                        Attachment attachment = new Attachment()
                        {
                            Name = fileName,
                            Path = id + Path.GetExtension(upload.FileName),
                        };
                        attachments.Add(attachment);
                        var path = Path.Combine(Server.MapPath("~/uploads"), attachment.Path);
                        file.SaveAs(path);
                    }
                }

            //Add Account List to create view
                foreach (var account in db.Accounts.Where(x => x.Customer == meeting.Customer))
                {
                    var snapshot = new Snapshot
                    {
                        AccountNumber = account.AccountNumber,
                        System = account.System,
                        Category = account.Category,
                        Balance = account.Balance,
                    };

                    meeting.Snapshots.Add(snapshot);
                }
                meeting.Attachments = attachments;
                db.Meetings.Add(meeting);
                db.SaveChanges();
                return RedirectToAction("Details", new { Id = meeting.Id });
            }
                return View(meeting);
        }

Open in new window

Get Meetings Create Meetings Controller:

 public ActionResult Create(int customerId)

        {
            var snapshots = db.Accounts.Where(x => x.Customer == customerId)
                .Select(x => new SnapshotModel
                {
                    AccountNumber = x.AccountNumber,
                    System = x.System,
                    Category = x.Category,
                    Balance = x.Balance,
                }).ToList();
            var model = new MeetingModel
            {
                Customer = customerId,
                Snapshots = snapshots,
            };

            //Add upload capability to Create Meeting view
            foreach (string upload in Request.Files)
            {
                if (Request.Files[upload].FileName != "")
                {
                    string path = AppDomain.CurrentDomain.BaseDirectory + "~/uploads/";
                    string filename = Path.GetFileName(Request.Files[upload].FileName);
                    Request.Files[upload].SaveAs(Path.Combine(path, filename));
                }
            }

            ViewBag.Customer = new SelectList(db.Meetings, "Id", "Customer1.Name");
            ViewBag.Employee = new SelectList(db.Employees, "Id", "FullName");
            return View(model);
        }

Open in new window

Get Meeting Details - Meetings Controller:

 
public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Meeting meeting = db.Meetings.Find(id);
            if (meeting == null)
            {
                return HttpNotFound();
            }
            foreach (string download in Request.Files)
            {
                if (Request.Files[download].FileName != "")
                {
                    string path = AppDomain.CurrentDomain.BaseDirectory + "/uploads/";
                    string filename = Path.GetFileName(Request.Files[download].FileName);
                    Request.Files[download].SaveAs(Path.Combine(path, filename));
                }
            }
            return View(meeting);
        }
        [HttpGet]
        public FileResult Download(int id)
        {
            Attachment attachment = db.Attachments.Find(id);
            string path = AppDomain.CurrentDomain.BaseDirectory + "/uploads/";
            string filename = Path.GetFileName(attachment.Path);
            return File
                (path+filename, System.Net.Mime.MediaTypeNames.Application.Octet, attachment.Name );
        }

Open in new window