Solved

Retrieve File from Database

Posted on 2011-02-14
40
291 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
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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 500 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

Is Your Active Directory as Secure as You Think?

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

Question has a verified solution.

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

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

15 Experts available now in Live!

Get 1:1 Help Now