[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Retrieve File from Database

Posted on 2011-02-14
40
Medium Priority
?
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 18
  • 2
40 Comments
 
LVL 3

Expert Comment

by:JuanchoVzla
ID: 34888208
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
ID: 34888209
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
ID: 34888230
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:asp_net2
ID: 34888280
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
ID: 34888327
Use                        
rdr.GetBytes instead of Item property
0
 
LVL 4

Author Comment

by:asp_net2
ID: 34888403
Hi JuanchoVzla,

That did not work either.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 34888483
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
ID: 34888531
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
ID: 34888611
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
ID: 34888624
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
ID: 34888696
>> 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
ID: 34888751
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
ID: 34888809
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
ID: 34888858
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
ID: 34888883
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
ID: 34888959
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
ID: 34888977
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
ID: 34889073
Try replacing 'Response.Write(arrContent.ToString());' by 'Response.BinaryWrite(arrContent)'.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 34889117
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
ID: 34889121
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
 
LVL 3

Expert Comment

by:JuanchoVzla
ID: 34889151
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
ID: 34889163
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
ID: 34889213
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
ID: 34890372
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
ID: 34890527
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
ID: 34890671
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
ID: 34890681
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
ID: 34890781
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
ID: 34890875
Well if you where only going to allow .pdf's what method would you use?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
ID: 34891091
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
ID: 34891245
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
ID: 34892449
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
ID: 34892471
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
ID: 34893110
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
ID: 34893210
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 2000 total points
ID: 34897039
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
ID: 34898313
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
ID: 34900185
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
ID: 34900756
ok, is there anyway to hide the extension on the retrieval page?
0
 
LVL 3

Expert Comment

by:JuanchoVzla
ID: 34902081
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

656 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