Link to home
Start Free TrialLog in
Avatar of Brian
BrianFlag for United States of America

asked on

Retrieve File from Database

Hello EE,

I have been having a big problem trying to find an anwser to this question. How can I retrieve my file(s) from my Database with the following code below? If I use the following process below then i generates a .zip file with .xml files and folders, but I need it to just give me an option to Open or Save just the file only and no other data. I could really use some help with this one. Very urgent to me. I'm using ASP.NET 4.0 C#.


CODE:

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

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
       
            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "PicksPhoto_RetrievePhotographySubCategoryPricePackages";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            try
            {
                conn.Open();

                SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.ExecuteReader();

                if (rdr.Read())
                {
                    int buffersize = 100;
                    byte[] bytes = new byte[buffersize];
                    long bytesRead;
                    long readFrom = 0;

                    //Read the field 100 bytes at a time
                    do
                    {
                        bytesRead = rdr.GetBytes(0, readFrom, bytes, 0, buffersize);
                        Response.BinaryWrite(bytes);
                        readFrom += buffersize;
                    } while (bytesRead == buffersize);
                }

                rdr.Close();
            }

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

            finally
            {
                conn.Close();
            }
       
    }



STORED PROCEDURE:

(
@pp_id int
)

AS

SELECT pp_file
FROM FileTable
WHERE pp_id = @pp_id
Avatar of JuanchoVzla
JuanchoVzla
Flag of Venezuela, Bolivarian Republic of image

You have to set the correct headers in order to the browser to interpret your response correctly. Try this, before any Response.BinaryWrite

            Response.Clear();
            Response.Buffer = true;
            Response.ContentType="application/x-zip-compressed";
            Response.AddHeader("content-disposition", "attachment;filename=POWithComments.xls");
It seems that the zip file is stored in the database on the pp_file column, that why you get the zip as a result.

So, to get a specific file you need to save the zip and retrieve the particular file you want (probably using the DotNetZip Library).

You should also add the length header
         Response.AddHeader ("Content-Length", data.Length.ToString());
And set the filename to .zip.

It would be something like this:
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType="application/x-zip-compressed";
            Response.AddHeader ("Content-Length", data.Length.ToString());
            Response.AddHeader("content-disposition", "attachment;filename=Something.zip");
            ......send the data with BinaryWrite.......
            Response.Flush();
Avatar of Brian

ASKER

Hi,

Ok, I changed my code to the following below. Reason being is that the code below did work in one of my VB sites but using C# now. I keep getting an error message under the word "Item" that says the following message below.

>> It seems that the zip file is stored in the database on the pp_file column, that why you get the zip as a result.
The file that was uploaded was a Word 2010 document.

Message:
'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Item' and no extension method 'Item' accepting a first argument of type 'System.Data.SqlClient.SqlDataReader' could be found (are you missing a using directive or an assembly reference?)


UPDATE CODE:

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

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
       
            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "RetrievePhotographySubCategoryPricePackages";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            try
            {
                byte[] arrContent = null;

                conn.Open();

                SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.ExecuteReader();

                if (rdr.Read())
                {
                    arrContent = Convert.ToByte(rdr.Item("pp_file"));
                    string conType = rdr.Item("pp_mime").ToString();
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.ContentType = conType;
                    Response.OutputStream.Write(arrContent, 0, rdr.Item("pp_size"));
                }

                rdr.Close();
            }

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

            finally
            {
                conn.Close();
            }
       
    }
Use                        
rdr.GetBytes instead of Item property
Avatar of Brian

ASKER

Hi JuanchoVzla,

That did not work either.
Avatar of Brian

ASKER

Also please see my Table fields below along with their field type in the DB.

pp_id  int  PK
pp_file  VarBinary(MAX)
pp_mime  Varchar(50)
pp_size Varchar(50)
I tried this, and there is no error.
        protected void Retrieve_PhotographySubCategoryPricePackagesFileByID()
        {
            int pp_id = Convert.ToInt32(Request.QueryString["pp_id"]);

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "RetrievePhotographySubCategoryPricePackages";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            try
            {
                byte[] arrContent = null;

                conn.Open();

                SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.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(1);
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.ContentType = conType;
                    
                    //You must set the filename
                    string filename = "Something.som";
                    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.Flush();
                    Response.End();
                }

                rdr.Close();
            }

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

            finally
            {
                conn.Close();
            }

        } 

Open in new window

Avatar of Brian

ASKER

Hi JuanchoVzla,

Ok, I added your code above and when I click the file to open or save nothing happens. The browser just keeps loading the page but no erorrs and no file download :(

Also, not sure what you mean by //Get the buffer size, Assuming the file is in the column Zero. How can I determine what the files column number is?
Avatar of Brian

ASKER

Is there a way to use the same code you provided but use the Column Names instead of the file column number?
>> Ok, I added your code above and when I click the file to open or save nothing happens. The
>> browser just keeps loading the page but no erorrs and no file download :(
Did you change the things, column numbers, querys, filename, etc?

>>Also, not sure what you mean by //Get the buffer size, Assuming the file is in the column Zero.
>>How can I determine what the files column number is?
The column number is the position of the fileds in the select that retrieves the data, in your case the stor proc
If you use this select:
SELECT pp_file, pp_mime, pp_size.......
The file would be the Zero, mime the 1 and size the 2.

>> Is there a way to use the same code you provided but use the Column Names instead of the file
>> column number?
I don't think so, but honestly i'm not sure.
Use this
        protected void Retrieve_PhotographySubCategoryPricePackagesFileByID()
        {
            int pp_id = Convert.ToInt32(Request.QueryString["pp_id"]);

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "RetrievePhotographySubCategoryPricePackages";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            try
            {
                byte[] arrContent = null;

                conn.Open();

                SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.ExecuteReader();

                if (rdr.Read())
                {
                    //Get the file size
                    int filesize = rdr.GetInt32(2);
                    arrContent = new byte[filesize];

                    //Get the data
                    long bytesread = rdr.GetBytes(0, 0, arrContent, 0, filesize);
                    
                    //Get the mime type
                    string conType = rdr.GetString(1);
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.ContentType = conType;

                    //You must set the filename according to the mimetype
                    string filename = "?????.???";
                    Response.AddHeader("content-disposition", "attachment;filename=" + filename);

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

                    //Write the file content
                    Response.Write(arrContent.ToString());
                    Response.Flush();
                    Response.End();
                }

                rdr.Close();
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            finally
            {
                conn.Close();
            }
        } 

Open in new window


And this for the stored procedure
STORED PROCEDURE:

(
@pp_id int
)

AS

SELECT pp_file, pp_mime, pp_size
FROM FileTable
WHERE pp_id = @pp_id 

Open in new window

Avatar of Brian

ASKER

Updated Stored Procedure because if I only had pp_file as the Select field which when I click on the file to open/save it just went to a blank page and nothing happened.

Could you show me where I need to place the Column values at. My SP matches what you mentioned. pp_file = 0 pp_mime = 1 pp_size = 2. Just not where those values need to be plugged in at.

UPDATE STORED PROCEDURE:

(
@pp_id int
)

AS

SELECT pp_file, pp_mime, pp_size
FROM [PicksPhoto_PricePackages]
WHERE pp_id = @pp_id
Avatar of Brian

ASKER

Hi JuanchoVzla,

Ok, I copied your code and made sure the SP matches yours and now when I click on the file to open/save nothing happens, the actually page acts as if it's trying to download something but after 4 minutes nothing comes up or pops up.
The column indexes are used ins each rdr.Get??? operation.
But the last code i gave you had already the correct indexes, did you try debugging to see where does it hangs?
Avatar of Brian

ASKER

Still no luck JuanchoVzla, are you sure theres nothing else being missed?

Where should I debug at?

This is the code that I'm using below which is what you provided. The only thing that I added was the document type like you mentioned.

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

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

        SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
        cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "PicksPhoto_RetrievePhotographySubCategoryPricePackagesFileByID";
        cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
        cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

        cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

        try
        {
            byte[] arrContent = null;

            conn.Open();

            SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.ExecuteReader();

            if (rdr.Read())
            {
                //Get the file size
                int filesize = rdr.GetInt32(2);
                arrContent = new byte[filesize];

                //Get the data
                long bytesread = rdr.GetBytes(0, 0, arrContent, 0, filesize);

                //Get the mime type
                string conType = rdr.GetString(1);
                Response.ClearContent();
                Response.ClearHeaders();
                Response.ContentType = conType;

                //You must set the filename according to the mimetype
                string filename = "PricePackage.docx";
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);

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

                //Write the file content
                Response.Write(arrContent.ToString());
                Response.Flush();
                Response.End();
            }

            rdr.Close();
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }
        finally
        {
            conn.Close();
        }
    }
Avatar of Brian

ASKER

Also my field types in the Table are listed below. Not sure if this matters or not.

pp_file  VarBinary(MAX)
pp_mime  Varchar(50)
pp_size  Varchar(50)
Try replacing 'Response.Write(arrContent.ToString());' by 'Response.BinaryWrite(arrContent)'.
Avatar of Brian

ASKER

Hi wdosanjos,

That did not do anything either. When I click on the link to try and open/save the file it just goes to a blank page now :(
Umh..... debug the entire sub to see if the flow stops at some particular point.

I'm ussing this in my site to download excel reports, and it works just fine:
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=POWithComments.xls");
            Response.Charset = "";
            this.EnableViewState = false;

            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            gvReport.RenderControl(htw);
            
            Response.Write(sw.ToString());
            Response.End();

Open in new window

Maybe you could try it simpler
                if (rdr.Read())
                {
                    //Get the file size
                    int filesize = rdr.GetInt32(2);
                    arrContent = new byte[filesize];

                    //Get the data
                    long bytesread = rdr.GetBytes(0, 0, arrContent, 0, filesize);

                    //Get the mime type
                    string conType = rdr.GetString(1);
                    Response.Clear();
                    Response.ContentType = conType;

                    //You must set the filename according to the mimetype
                    string filename = "PricePackage.docx";
                    Response.AddHeader("content-disposition", "attachment;filename=" + filename);

                    //Write the file content
                    Response.Write(arrContent.ToString());
                    Response.End();
                }

Open in new window

What is the mimetype in the DB for the docx?

It should be
Response.ContentType = "application/vnd.ms-word"
Avatar of Brian

ASKER

Below is the actual file and it's attributes that I'm having trouble downloading.

pp_file = Binary Data
pp_mime = application/vnd.openxmlformats-officedocument.word
pp_size = 9882
Avatar of Brian

ASKER

All,

I was able to get this to work. However, as for JuanchoVzla's code I can only use a Word DOC because of specifying the ContentType and manually specifying the Filename. What I would like to be able to do with the following code below is to add the Document type after the file type so that the user has the filename title that is associated to the file.

If you look at this line below you will see what I changed and what else I need to add so that I can associate the filename with it's file type.

>>  string filename = rdr.GetString(3);

STORED PROCEDURE:

(
@pp_id int
)

AS

SELECT pp_file, pp_mime, pp_size, pp_filetitle
FROM [PicksPhoto_PricePackages]
WHERE pp_id = @pp_id




UPDATED CODE:

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

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PicksPhoto"].ConnectionString);

        SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
        cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "PicksPhoto_RetrievePhotographySubCategoryPricePackagesFileByID";
        cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
        cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

        cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

        try
        {
            byte[] arrContent = null;

            conn.Open();

            SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.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(1);
                Response.ClearContent();
                Response.ClearHeaders();
                Response.ContentType = conType;

                //You must set the filename
                //string filename = "PricePackage.doc";
                string filename = rdr.GetString(3);
                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();
            }

            rdr.Close();
        }

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

        finally
        {
            conn.Close();
        }

    }
I'm not sure i understood your las post. But,
What i normally do when managing files is to store the whole filename (not just the title) & mimetype when uploading, since both of these infos come in the upload request. That way, the file remains as original as it can, and you dont have to worry about the filename nor the mimetype.
Avatar of Brian

ASKER

ok, if you look at the code below, I was able to get that resolved as well to. But is there a way to get what I have working without specifying the ".doc" filename type and just pull that filename extension based on the file uploaded to the DB?



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

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
        {
            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "RetrievePhotographySubCategoryPricePackagesFileByID";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            byte[] arrContent = null;

            conn.Open();

            SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.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(1);
                Response.ClearContent();
                Response.ClearHeaders();
                Response.ContentType = conType;

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

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

                Response.BinaryWrite(arrContent);
                Response.Flush();
                Response.End();
            }
        }
    }
Avatar of Brian

ASKER

Below is what I had to modify to make it work, my question is how can I pull the ".doc" filename extension based on the uploaded file in the DB without manually entering it in the code?

>> Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".doc");
You either include the extension in the database column "pp_filetitle", so you can remove the ".doc" part and leave just the
>> Response.AddHeader("content-disposition", "attachment;filename=" + filename);

Or you create a new column called "pp_fileext", and use
>> Response.AddHeader("content-disposition", "attachment;filename=" + filename + rdr.GetString(4));

Or (so not recomended) if you know exactly what type of files are they uploading you could write an incredibly big switch with all the options.
switch(conType)
  case "application/vnd.openxmlformats-officedocument.word":
    sExt = ".docx"
  case "application/vnd.openxmlformats-officedocument.excel":
    sExt = ".xlsx"
.
.
.
Avatar of Brian

ASKER

Well if you where only going to allow .pdf's what method would you use?
IMHO scalability is always a better approach on the long term. So i would go with option 1 if i were you:
>> You either include the extension in the database column "pp_filetitle", so you can remove
>> the ".doc" part and leave just the
>> Response.AddHeader("content-disposition", "attachment;filename=" + filename);

But in the end that really is up to you :), if you are only going to use pdf's and you're absolutely sure that that's never going to change. You could just wire in the pdf's attributes for the mimetype and the file extension.
Avatar of Brian

ASKER

Could you show me how and what to do to have the files extension added to my DB. This way if the client decides to use .doc or .xls i'm not stuck. This is all i need for this post. I understand that I need to create a file extension field in my DB, for that I'll call it pp_fileext. How do I modify my Upload and Retireve code:

UPLOAD FILE CODE:

    protected void imbtn_SubmitPricePackage_Click(object sender, ImageClickEventArgs e)
    {
        // Make sure a file has been successfully uploaded
        if (fuPricePackageUpload.PostedFile == null || string.IsNullOrEmpty(fuPricePackageUpload.PostedFile.FileName) || fuPricePackageUpload.PostedFile.InputStream == null)
        {
        }

        string MIMEType = fuPricePackageUpload.PostedFile.ContentType;
        int fileSize = fuPricePackageUpload.PostedFile.ContentLength;

        // Allow only files less than 1,048,576 bytes (approximately 1 MB) to be uploaded.
        if ((fileSize < 1048576))
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

            SqlCommand cmdInsertPricePackage = new SqlCommand();
            cmdInsertPricePackage.CommandText = "InsertPricePackage";
            cmdInsertPricePackage.CommandType = CommandType.StoredProcedure;
            cmdInsertPricePackage.Connection = conn;

            // Load FileUpload's InputStream into Byte array
            byte[] imageBytes = new byte[fuPricePackageUpload.PostedFile.InputStream.Length + 1];
            fuPricePackageUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);

            cmdInsertPricePackage.Parameters.AddWithValue("@mnav_id", SqlDbType.Int).Value = ddlCategory.SelectedValue;
            cmdInsertPricePackage.Parameters.AddWithValue("@cnav_id", SqlDbType.Int).Value = ddlSubCategory.SelectedValue;
            cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = txtPriceTitle.Text;
            cmdInsertPricePackage.Parameters.AddWithValue("@pp_file", SqlDbType.Image).Value = imageBytes;
            cmdInsertPricePackage.Parameters.AddWithValue("@pp_mime", SqlDbType.VarChar).Value = MIMEType;
            cmdInsertPricePackage.Parameters.AddWithValue("@pp_size", SqlDbType.VarChar).Value = fileSize;

            try
            {
                conn.Open();

                cmdInsertPricePackage.ExecuteNonQuery();

                Response.Redirect("success_file-upload.aspx");
            }

            catch (Exception ex)
            {
                lblFileUploadError.Text = ("Error on insert: " + ex.Message.ToString());
            }

            finally
            {
                conn.Close();
            }
        }

        else
        {
            lblFileSize.Text = "Your File has to be 1MB or smaller";
        }
    }



RETRIEVE FILE CODE:

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

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
        {
            SqlCommand cmdRetrievePhotographySubCategoryPricePackagesByID = new SqlCommand();
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandText = "RetrievePhotographySubCategoryPricePackagesFileByID";
            cmdRetrievePhotographySubCategoryPricePackagesByID.CommandType = CommandType.StoredProcedure;
            cmdRetrievePhotographySubCategoryPricePackagesByID.Connection = conn;

            cmdRetrievePhotographySubCategoryPricePackagesByID.Parameters.AddWithValue("@pp_id", SqlDbType.Int).Value = pp_id;

            byte[] arrContent = null;

            conn.Open();

            SqlDataReader rdr = cmdRetrievePhotographySubCategoryPricePackagesByID.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(1);
                Response.ClearContent();
                Response.ClearHeaders();
                Response.ContentType = conType;

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

                //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();
            }
        }
    }
This is the filename that the user uploaded
>> fuPricePackageUpload.PostedFile.FileName
Just take the extension part and send to the DB
In fact, i recommend you to save the full file name, because the other field could have invalid chars for a file name, since it's user filled
String filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[]{'\\'}).Last();

Open in new window

Avatar of Brian

ASKER

Hi JuanchoVzla,

Not sure where this needs to go. Could use some help as to how I pass the FileUploads File Name to the pp_filetitle parameter to be added to the DB.

cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = string filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[]{'\\'}).Last();
Avatar of Brian

ASKER

Hi JuanchoVzla,

Ok, I tried different approaches to what you supplied above and each one of the methods I tried below tell me that the file is corrupt if I try to open or save the file.

string filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[] { '\\' }).Last();

cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = filename;

cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = fuPricePackageUpload.PostedFile.FileName.Split(new char[]{'\\'}).Last();
ASKER CERTIFIED SOLUTION
Avatar of JuanchoVzla
JuanchoVzla
Flag of Venezuela, Bolivarian Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brian

ASKER

Hi JuanchoVzla,

Ok, that worked great. However, the "pp_filetitle" field has the extension added to the filename, is this normal?

Is there a way to not store the file extension in this field "pp_filetitle"?
Hi!
Yes, that's the idea, store the whole filename (extension included) that way in the retrieval code you can use this without worrying about the file extension
                //You must set the filename.
                string filename = rdr.GetString(3);
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);

Avatar of Brian

ASKER

ok, is there anyway to hide the extension on the retrieval page?
Why dont you try this:
1.- Leave the pp_filetitle as it was, saving there what the user inputs in the txtPriceTitle
cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = txtPriceTitle.Text;

Open in new window

2.- Create a new column in your table "pp_filename", and store there the filename
string filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[] { '\\' }).Last();
cmdInsertPricePackage.Parameters.AddWithValue("@pp_filename", SqlDbType.VarChar).Value = filename;

Open in new window

3.- Change your retrieval store proc to return the new column "pp_filename" instead of the filetite

So it would be this:
        protected void imbtn_SubmitPricePackage_Click(object sender, ImageClickEventArgs e)
        {
            // Make sure a file has been successfully uploaded
            if (fuPricePackageUpload.PostedFile == null || string.IsNullOrEmpty(fuPricePackageUpload.PostedFile.FileName) || fuPricePackageUpload.PostedFile.InputStream == null)
                return;

            string MIMEType = fuPricePackageUpload.PostedFile.ContentType;
            int fileSize = fuPricePackageUpload.PostedFile.ContentLength;

            // Allow only files less than 1,048,576 bytes (approximately 1 MB) to be uploaded.
            if ((fileSize < 1048576))
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

                SqlCommand cmdInsertPricePackage = new SqlCommand();
                cmdInsertPricePackage.CommandText = "InsertPricePackage";
                cmdInsertPricePackage.CommandType = CommandType.StoredProcedure;
                cmdInsertPricePackage.Connection = conn;

                // Load FileUpload's InputStream into Byte array
                byte[] imageBytes = new byte[fuPricePackageUpload.PostedFile.InputStream.Length + 1];
                fuPricePackageUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);

                cmdInsertPricePackage.Parameters.AddWithValue("@mnav_id", SqlDbType.Int).Value = ddlCategory.SelectedValue;
                cmdInsertPricePackage.Parameters.AddWithValue("@cnav_id", SqlDbType.Int).Value = ddlSubCategory.SelectedValue;

                cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", SqlDbType.VarChar).Value = txtPriceTitle.Text

                string filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[] { '\\' }).Last();
                cmdInsertPricePackage.Parameters.AddWithValue("@pp_filename", SqlDbType.VarChar).Value = filename;
                
                cmdInsertPricePackage.Parameters.AddWithValue("@pp_file", SqlDbType.Image).Value = imageBytes;
                cmdInsertPricePackage.Parameters.AddWithValue("@pp_mime", SqlDbType.VarChar).Value = MIMEType;
                cmdInsertPricePackage.Parameters.AddWithValue("@pp_size", SqlDbType.VarChar).Value = fileSize;

                try
                {
                    conn.Open();

                    cmdInsertPricePackage.ExecuteNonQuery();

                    Response.Redirect("success_file-upload.aspx");
                }

                catch (Exception ex)
                {
                    lblFileUploadError.Text = ("Error on insert: " + ex.Message.ToString());
                }

                finally
                {
                    conn.Close();
                }
            }

            else
            {
                lblFileSize.Text = "Your File has to be 1MB or smaller";
            }
        }

Open in new window