how to read/access file saved in sql table field from asp.net

Hi all,
from aspx page with attached code behind I insert file in sql server 2000 table with image field. I now want to display data from table in gridview and in document column I want to have document name with link to this document (on click it should give Open and save options like when downloading file from internet). After I bind table and gridview column with document is not displaying at all (there is column name in bound fields but there is no column in gridview). How to enable access to documents saved in sql table from gridview?

Thanks for help
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    
    Protected Sub Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upload.Click
        Dim fileUpload1 As FileUpload = CType(Me.FindControl("fileUpload1"), FileUpload)
        'Make sure a file has been successfully uploaded
        If fileUpload1.PostedFile Is Nothing OrElse String.IsNullOrEmpty(fileUpload1.PostedFile.FileName) OrElse fileUpload1.PostedFile.InputStream Is Nothing Then
            Label1.Text = "Dodajte attachment"
            Exit Sub
        End If

        Dim extension As String = System.IO.Path.GetExtension(fileUpload1.PostedFile.FileName).ToLower()
        Dim MIMEType As String = Nothing
        Select Case extension
            Case ".txt"
                MIMEType = "text"
            Case ".rtf"
                MIMEType = "rich text"
            Case ".doc"
                MIMEType = "MS Word"
            Case ".xls"
                MIMEType = "MS Excel"
            Case ".zip"
                MIMEType = "WinZip"
            Case ".rar"
                MIMEType = "WinRAR"
            Case ".pdf"
                MIMEType = "Adobe"
            Case ".jpg", ".jpeg", ".jpe"
                MIMEType = "jpeg"
            Case ".png"
                MIMEType = "png"
            Case ".gif"
                MIMEType = "gif"
            Case Else
                'Invalid file type uploaded
                Label1.Text = "Ne mozete poslati ovu vrstu fajla"
                Exit Sub
        End Select
        If fileUpload1.PostedFile.ContentLength > 0 And fileUpload1.PostedFile.ContentLength < 307200 Then
            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CString").ConnectionString)
                Const SQL As String = "INSERT INTO [Attachment] ([Title], [MIMEType], [Image],[DateAdded]) VALUES (@Title, @MIMEType, @ImageData,@DateAdded)"
                Dim myCommand As New SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@Title", Server.HtmlEncode(fileUpload1.FileName))
                myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
                myCommand.Parameters.AddWithValue("@DateAdded", Now())
                'Load FileUpload's InputStream into Byte array
                Dim imageBytes(fileUpload1.PostedFile.InputStream.Length) As Byte
                fileUpload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
                myCommand.Parameters.AddWithValue("@ImageData", imageBytes)
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()
            End Using
        Else : Label1.Text = "Provjerite velicinu fajla"
        End If
        Label2.Text = fileUpload1.PostedFile.ContentLength
    End Sub
End Class

Open in new window

LVL 1
kahvedzicAsked:
Who is Participating?
 
masterpassCommented:
What you can do is the Bind the ID of the file to the link inside the grid. When the user clicks on it, call the ProcessRequest function and I think you can get what you wanted ...

Reference : http://blogs.msdn.com/jdixon/articles/495408.aspx
public static Stream GetFile(int FileID)
{ 
   using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString)) 
   {
      using (SqlCommand command = new SqlCommand("select [file] FROM [sampletable] WHERE ([id]=@id)", connection))
      { 
         command.Parameters.Add(new SqlParameter("@id", FileID));
         connection.Open();
         object result = command.ExecuteScalar();
         try
         {
            return new MemoryStream((byte[])result);
         }
         catch
         {
            return null;
         }
      }
   }
}
public void ProcessRequest (HttpContext context)// pass the Http.Current.Context as the argument to this function
{

   // Setup the FileID Parameter
   Int32 id = -1;
   Stream stream = null;
   id = Convert.ToInt32(context.Request.QueryString["FileID"]);
   stream = GetFile(id); 
   const int buffersize = 1024 * 16;
   byte[] buffer2 = new byte[buffersize];
   int count = stream.Read(buffer2, 0, buffersize);
   while (count > 0) {
      context.Response.OutputStream.Write(buffer2, 0, count);
      count = stream.Read(buffer2, 0, buffersize);
   }
}

Open in new window

0
 
daveamourCommented:
You may also need to set some headers to say what the file type is too
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.