Question How to store and retrieve files......

[Jeraldin]

I'm a MCA student..
I'm doing my final year project....
Please clarify my doubt...
How to store word,pdf,excel file in sql server 2008 and also retrieve that files......

 

[jmcilhinney]

Generally speaking, if you're going to store arbitrary file data in SQL Server then you would use a `varbinary` column and store Byte arrays in it. You can get a Byte array from a file or save a Byte array to a file using the File.ReadAllBytes or .WriteAllBytes method.

While you should already be using parameters to insert values into SQL code anyway, you have no real option when using Byte arrays, because they have no textual representation. If you don't already know how to do that, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

 

[Jeraldin]

Please explain with coding...

 

[jmcilhinney]

Please explain with coding...

That wouldn't be explaining, but rather writing your code for you. I have already explained and now I expect you to make an effort to implement what I have explained. If you have trouble then I have no issue helping further but if you just want to sit on your hands and let others do your thinking for you then I'm afraid that I'm not your man. I am willing to help if you can't do it but you don't even know that you can't do it if you haven't tried. If you have tried, show us what you tried, explain what you expected and also what actually happened.

 

[Herman]

A simpler approach that will also probably improve database performance would be to store a path to a network location in the database. You can fetch the path and just retrieve the file.

 

[jmcilhinney]

A simpler approach that will also probably improve database performance would be to store a path to a network location in the database. You can fetch the path and just retrieve the file.

There's not necessarily an advantage to that when using SQL Server. If you enable the FILESTREAM functionality of your SQL Server instance then the actual file data is stored externally to the database anyway. You can also enable file system access to the data, in which case SQL Server creates a network share for the data storage location.

 

[Jeraldin]

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
 
Public Class file
    Dim con As New SqlConnection("Data Source=ELCOT-PC\S;Initial Catalog=project;Integrated Security=True")
    Dim cmd As New SqlCommand
    Dim str, c1 As String
    Dim dr As SqlDataReader
    Dim b As New Integer
    Private Sub browse_button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles browse_button.Click
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            file_rtb.LoadFile(OpenFileDialog1.FileName, RichTextBoxStreamType.PlainText)
        End If
 
    End Sub
    Private Function ReadWordDoc(ByVal filename As String) As Byte()
 
        Dim fs As New System.IO.FileStream(OpenFileDialog1.FileName, IO.FileMode.Open)
        Dim br As New System.IO.BinaryReader(fs)
 
        Dim data() As Byte = br.ReadBytes(fs.Length)
 
        br.Close()
        fs.Close()
 
        Return (data)
 
    End Function
 
    Private Sub WriteWordDoc(ByVal filename As String, ByVal data As Byte())
 
        Dim fs As New System.IO.FileStream(filename, IO.FileMode.Create)
        Dim bw As New System.IO.BinaryWriter(fs)
 
        bw.Write(b)
 
        bw.Close()
        fs.Close()
 
    End Sub
 
    Private Sub save_button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save_button.Click
        con.Open()
        Dim cmd As New SqlCommand("", con)
        Dim file As String = OpenFileDialog1.FileName
        Dim doc() As Byte = ReadWordDoc(file)
        cmd.CommandText = "INSERT INTO WordDocs VALUES('" & name_txt.Text & "','" & type_combo.Text & "',@DOC)"
        cmd.Parameters.AddWithValue("@DOC ", doc)
        cmd.ExecuteNonQuery()
        MsgBox("File has been saved", MsgBoxStyle.Information, "SAVE")
        con.Close()
 
    End Sub
 
 
 
 
    Private Sub retrieve_button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles retrieve_button.Click
       
       
 
        type_combo.Visible = False
        ComboBox1.Visible = True
        name_txt.Visible = False
        type_txt.Visible = True
        con.Open()
        str = "Select * from WordDocs where FileName='" & ComboBox1.SelectedItem & "'"
        cmd = New SqlCommand(str, con)
        dr = cmd.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            ' name_txt.Text = dr("FileName").ToString()
            Dim data As Byte() = DirectCast(dr("WordDoc"), Byte())
            Dim ms As New MemoryStream(data)
            file_rtb.Text = dr("WordDoc").ToString
            type_txt.Text = dr("FileType").ToString
        End If
        con.Close()
        'con.Close()
 
 
    End Sub
 
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ComboBox1.Visible = False
        type_txt.Visible = False
        con.Open()
        Dim se As String = "select FileName from WordDocs"
        Dim cmd As New SqlCommand(se, con)
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            ComboBox1.Items.Add(dr("FileName"))
        End While
        type_combo.Items.Add(".txt")
        type_combo.Items.Add(".doc")
        type_combo.Items.Add(".pdf")
        type_combo.Items.Add(".exl")
        'display()
        con.Close()
    End Sub
  
End Class

By use this code, I was load a text file and a document file in a Rich Textbox and then save it finally retrieve the files. The text file content is loaded and saved in SQL database, but it will not retrieve the text file content. Same as when I load a document file, the content is displayed as an encrypted file the original file content is not shown in the rich textbox.

 

[jmcilhinney]

Have you considered doing what I suggested?