Solved

Retrieve file from database

Posted on 2013-01-07
11
243 Views
Last Modified: 2013-01-09
Hello Experts,

I have an application that stores .doc, .pdf, .xls, .png, and .jpg files in my database. This all works with no errors. I can also retrieve the files to a GridView control with no problems.

The problem that I need help with is how to retrieve the file to a HyperLink control by binding all the data to a DataTable. Please see my CodeBehind below. I simple need to retrieve the file to a control such as a HyperLink control and not sure how to do so.

CodeBehind:
protected void RetrieveTicketsToModifyByID()
    {
        int TicketID = Convert.ToInt32(Request.QueryString["tkt_id"].ToString());

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "RetrieveTicketsToModifyByID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            cmd.Parameters.AddWithValue("@tkt_id", SqlDbType.Int).Value = TicketID;

            DataTable dtTicketsToModify = new DataTable();

            SqlDataAdapter adp = new SqlDataAdapter();

            try
            {
                conn.Open();

                adp.SelectCommand = cmd;
                adp.Fill(dtTicketsToModify);

                if ((dtTicketsToModify != null))
                {
                    DataRow data = dtTicketsToModify.Rows[0];

                    lblCreationDateValue.Text = Convert.ToDateTime(data["tkt_cre_dt"]).ToShortDateString();
                    txtUserRequestChange.Text = data["tkt_usr_req_chg"].ToString();
                    ddlCompany.SelectedValue = data["com_id"].ToString();
                    ddlRequestType.SelectedValue = data["req_typ_id"].ToString();
                    ddlStatusType.SelectedValue = data["sts_id"].ToString();
                    ddlTicketOwner.SelectedValue = data["ownr_id"].ToString();
                    txtShortDesc.Text = data["tkt_short_desc"].ToString();
                    txtTicketDesc.Text = data["tkt_desc"].ToString();
                    txtTicketResolution.Text = data["tkt_resolution"].ToString();

                    //txtCompletionDate.Text = string.Empty;
                    //if (data["tkt_cmpl_dt"] != DBNull.Value && !string.IsNullOrEmpty(Convert.ToString(data["tkt_cmpl_dt"])))
                    //    txtCompletionDate.Text = Convert.ToDateTime(data["tkt_cmpl_dt"]).ToShortDateString();

                    string tkt_filename = null;
                    if (data["tkt_filename"] != DBNull.Value)
                    {
                        tkt_filename = data["tkt_filename"].ToString();
                    }

                    if (!string.IsNullOrEmpty(tkt_filename))
                    {
                        lblVerificationFormFileName.Visible = true;
                        lblVerificationFormFileName.Text = "You have a file named " + "'" + tkt_filename + "'" + " in our system.";
                    }
                }
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
    }

Open in new window


Thanks in advance!
0
Comment
Question by:asp_net2
11 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38750848
do u create the control dynamically?
cause you can set the NavigateUrl property of the control to the file path.
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 38750961
To bind the file to Hyperlink you can simply add like this

HyperLink1.NavigateUrl=String.IsNullOrEmpty(retrievedFile);
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 38750979
I hope in the grid you would have Unique ID column. If not, have it.

Create a hyper link column and pass the ID as an argument to launch another page. In the page, parse the query string and get the content of the file from database for that ID.

Write the content in the webpage (Create an empty ASPX page for it)



To launch new page in hyperlink:

http://datawebcontrols.com/faqs/Hyperlinks/HyperlinkThatOpensInANewWindow.shtml
http://yasserzaid.wordpress.com/2008/12/20/open-popup-window-with-hyperlink-inside-gridview/
http://nice-tutorials.blogspot.in/2009/03/opening-pop-up-window-from-datagrid.html

To show file content in a webpage:

http://www.codeproject.com/Articles/15649/Create-and-download-text-file-from-a-web-page

HTH
0
 
LVL 19

Accepted Solution

by:
Manoj Patil earned 500 total points
ID: 38750992
If you are binding in aspx page :
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl= '<%# Bind("filename") %>' Text= '<%# Bind("filename") %>' ></asp:HyperLink>
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38751041
@techChallenger1,

I tried adding HyperLink1.NavigateUrl=String.IsNullOrEmpty(retrievedFile); but I'm getting red lines.

Also, i'm not binding to GridView. I'm trying to bind the file to a HyperLink control on my page. In order for this to work when binding to the GridView control I had to point to another page that processed the code based on the ID of the record that I need the image for. That page was called retrieve_file.aspx. Please see the codebehind for that below.

This time I need to retrieve the file to the HyperLink control to a page and NOT a GridView control. So i'm not sure if I still need the HyperLink control to point to the same page or not.

Retrieve File CodeBehind:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Security.Cryptography;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Security.Cryptography;
using System.Net.Mail;
using System.Net.NetworkInformation;
using System.Text;

public partial class tickets_retrieve_file : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        RetrieveTicketsFile();
    }

    protected void RetrieveTicketsFile()
    {
        int tkt_id = Convert.ToInt32(Request.QueryString["tkt_id"]);

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "RetrieveTicketsFile";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            cmd.Parameters.AddWithValue("@tkt_id", SqlDbType.Int).Value = tkt_id;

            byte[] arrContent = null;

            conn.Open();

            SqlDataReader rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                //Get the buffer size, Assuming the file is in the column Zero
                long buffsize = rdr.GetBytes(0, 0, arrContent, 0, 0);
                arrContent = new byte[buffsize];

                //Get the data, Assuming the file is in the column Zero
                long bytesread = rdr.GetBytes(0, 0, arrContent, 0, (int)buffsize);
                // bytesread  should be equal buffsize

                //Assuming the mimetype is in the column One
                string conType = rdr.GetString(2);
                Response.ClearContent();
                Response.ClearHeaders();
                Response.ContentType = conType;

                //You must set the filename.
                //string filename = "PricePackage.doc";
                string filename = rdr.GetString(1);
                //Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                //Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".pdf");
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);

                //And the size
                Response.AddHeader("Content-Length", buffsize.ToString());

                //Assuming "pp_size" is in the column Two
                //Response.Write(arrContent.ToString());
                Response.BinaryWrite(arrContent);
                Response.Flush();
                Response.End();
            }
        }
    } 
}

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 19

Expert Comment

by:Manoj Patil
ID: 38751067
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38751074
@techChallenger1,

The link you sent me is retrieving the file to a GridView control. I need to retrieve the image to a HyperLink control that is NOT inside of any GridView control.
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 38751098
What you can do is,
add an <a> tag in your aspx page
<a id="downloadFile" runat="server></a>
And when you retrieve the file from database, after the statement add following code.

string FileName=rdr.GetString(1);
downloadFile.Href="http://xyz.com/" + FileName +".extension";

This will also do the same function like download.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38756536
@techChallenger1,

Thank you for your help. I was able to figure out the issue on my own. Please see what I had to add to my CodeBehind and Markup:

CodeBehind:
string fileExists = data["tkt_size"].ToString();
                        if (fileExists == "0")
                        {
                            hlFile.Visible = false;
                        }

                        else
                        {
                            hlFile.NavigateUrl = String.Format("retrieve_file.aspx?tkt_id={0}", data["tkt_id"].ToString());
                        }


Markup:
<asp:HyperLink ID="hlFile" runat="server">Open/Save File</asp:HyperLink>
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 38756730
That is what I have already posted in my previous comment !!
HyperLink1.NavigateUrl
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38757256
Sorry, I must have missed that. My bad.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now