?
Solved

How to download BLOB data from SQL database

Posted on 2010-01-07
3
Medium Priority
?
312 Views
Last Modified: 2012-05-08
I have uploaded some blob data into a sql table. How I have this list displayed in a gridview and working off of the "select" option on the gridview, i want to download the blob data back to my local PC. I use VB.net so i would appreciate that type of code please.

here is my upload code:
        Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("IntranetConnectionString").ConnectionString)
            Dim fn As String
            fn = FileUpload2.FileName.ToString.Trim
            Dim SQL As String
            SQL = "INSERT INTO [Trouble_Ticket_Uploads] ([wo], [filename],[fileobject]) VALUES ('" & Request.QueryString("ID").Trim & "','" & FileUpload2.FileName.ToString.Trim & "', @Data)"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            Dim imageBytes(FileUpload2.PostedFile.InputStream.Length) As Byte
            FileUpload2.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
            myCommand.Parameters.AddWithValue("@Data", imageBytes)
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
            Label20.Text = "File uploaded successfully!"
            SqlDataSource7.SelectCommand = "select * from trouble_ticket_uploads where [wo] like '%" & Request.QueryString("ID").Trim & "%'"
            GridView7.DataBind()
            RESOLV.Focus()
        End Using



Now I'm looking for code to get it back out. Thanks in advance.
0
Comment
Question by:klt13
  • 2
3 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26273539
Sorry I do not have an ASP example - this is PHP, but hopefully the headers and the logic about MSIE will be of some use to you.  Best regards, ~ray
<?php // RAY_force_download.php
error_reporting(E_ALL);


// A FILE TO DOWNLOAD - THIS LINK COULD COME IN THE URL VIA $_GET
$url = "http://a0.twimg.com/a/1252097501/images/twitter_logo_header.png";

// USE CASE
force_download($url);


// FUNCTION TO FORCE A DOWNLOAD
function force_download($filename)
{
   $basename = basename($filename);
   $filedata = file_get_contents($filename);

   if ($filedata)
   {
   // THESE HEADERS ARE USED ON ALL BROWSERS
      header("Content-Type: application-x/force-download");
      header("Content-Disposition: attachment; filename=\"$basename\"");
      header("Content-length: ".(string)(strlen($filedata)));
      header("Expires: ".gmdate("D, d M Y H:i:s", mktime(date("H")+2, date("i"), date("s"), date("m"), date("d"), date("Y")))." GMT");
      header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");

   // THIS HEADER MUST BE OMITTED FOR IE 6+
      if (FALSE === strpos($_SERVER["HTTP_USER_AGENT"], 'MSIE '))
      {
         header("Cache-Control: no-cache, must-revalidate");
      }

   // THIS IS THE LAST HEADER
      header("Pragma: no-cache");

   // FLUSH THE HEADERS TO THE BROWSER
      flush();

   // CAPTURE THE FILE IN THE OUTPUT BUFFERS - WILL BE FLUSHED AT SCRIPT END
      ob_start();
      echo $filedata;
   }
}

Open in new window

0
 
LVL 1

Accepted Solution

by:
klt13 earned 0 total points
ID: 26308220
I finally figured out how to do it myself in VB.net.  Here is my code in case anyone else may need an example.

  Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        Dim fs As FileStream
        Dim bw As BinaryWriter
        Dim bufferSize As Integer = 300000
        Dim outbyte(300000 - 1) As Byte
        Dim retval As Long
        Dim startIndex As Long = 0
        Dim pub_id As String = ""
        Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
        ' Read first record
        reader.Read()


        fs = New FileStream(TextBox2.Text.Trim & "\" & filename, FileMode.OpenOrCreate, FileAccess.Write)


        bw = New BinaryWriter(fs)
        startIndex = 0
        retval = reader.GetBytes(0, 0, outbyte, 0, bufferSize)
        bw.Write(outbyte)
        bw.Flush()
        ' Close the output file.
        bw.Close()
        fs.Close()
        reader.Close()
        ' Clean up connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
            ' Dispose connection
            conn.Dispose()
        End If
0
 
LVL 1

Author Comment

by:klt13
ID: 26340273
WARNING!!!!!
Although I posted that I had found a way to download this data, sadly to say, this will download it to the server and NOT the client PC. I thought I was putting it in my C:\temp directory, but it never showed up. I found it in the temp directory located on the server.

Still looking.....
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Introduction HyperText Transfer Protocol (http://www.ietf.org/rfc/rfc2616.txt) or "HTTP" is the underpinning of internet communication.  As a teacher of web development I have heard many questions, mostly from my younger students who have come to t…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

864 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