Solved

Retrieve File from Database

Posted on 2011-02-14
40
288 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:asp_net2
  • 20
  • 18
  • 2
40 Comments
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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");
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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).

0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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();
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
            }
       
    }
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
Use                        
rdr.GetBytes instead of Item property
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Hi JuanchoVzla,

That did not work either.
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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)
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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?
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Is there a way to use the same code you provided but use the Column Names instead of the file column number?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
>> 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.
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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?
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
        }
    }
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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)
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
Try replacing 'Response.Write(arrContent.ToString());' by 'Response.BinaryWrite(arrContent)'.
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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 :(
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
What is the mimetype in the DB for the docx?

It should be
Response.ContentType = "application/vnd.ms-word"
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
        }

    }
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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.
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
            }
        }
    }
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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");
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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"
.
.
.
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
Well if you where only going to allow .pdf's what method would you use?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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.
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
            }
        }
    }
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
This is the filename that the user uploaded
>> fuPricePackageUpload.PostedFile.FileName
Just take the extension part and send to the DB
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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();
0
 
LVL 3

Accepted Solution

by:
JuanchoVzla earned 500 total points
Comment Utility
This should be working, what error are you getting? and where?
        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;

                string filename = fuPricePackageUpload.PostedFile.FileName.Split(new char[] { '\\' }).Last();
                cmdInsertPricePackage.Parameters.AddWithValue("@pp_filetitle", 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

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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"?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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);

0
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
ok, is there anyway to hide the extension on the retrieval page?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
Comment Utility
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

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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