Save and Retrieve word document in Sql Database

[Benniit]

Good day, gurus of this well-known site. I am using vb.net 2013 Ultimate and SQL 2012 and can someone show/link me how to save and retrieve a word document from SQL database? Thanks

[Reverend Jim]

I'm not up to SQL 2012 yet (I'm using 2008) but this works for me.

My table is

FileName     VARCHAR(255) (PK)
WordDoc      VARBINARY(MAX)

To get the data into the table you have to read the Word document into a Byte array as binary. When you want to recreate the document you must write it as binary. The two routines to read and write the files are

Private Function ReadWordDoc(filename As String) As Byte()

    Dim fs As New System.IO.FileStream(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(filename As String, 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

Note that you should add your own error checkinig as required. Although I named the routines to work with Word docs, they will work for any file type.

Here is the code to save a document to the database and to retrieve a previously saved document.

Private Sub btnStore_Click(sender As System.Object, e As System.EventArgs) Handles btnStore.Click

    Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
    Dim cmd As New SqlCommand("", con)

    Dim file As String = "D:\temp\test.docx"
    Dim doc() As Byte = ReadWordDoc(file)

    cmd.CommandText = "INSERT INTO WordDocs (FileName,WordDoc) VALUES(@FILE,@DOC)"
    cmd.Parameters.AddWithValue("@FILE", file)
    cmd.Parameters.AddWithValue("@DOC ", doc)

    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()

End Sub

Private Sub btnRetrieve_Click(sender As System.Object, e As System.EventArgs) Handles btnRetrieve.Click

    Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
    Dim cmd As New SqlCommand("", con)

    Dim file As String = "D:\temp\test.docx"

    cmd.CommandText = "SELECT * FROM WordDocs WHERE FileName = @FILE"
    cmd.Parameters.AddWithValue("@FILE", file)

    con.Open()

    Dim rdr As SqlDataReader = cmd.ExecuteReader

    If rdr.Read Then
        WriteWordDoc(file, rdr("WordDoc"))
    Else
        MsgBox(file & " not found")
    End If

    rdr.Close()
    con.Close()

End Sub

[rubberman]

The reverend's solution should work with MS-SQL. Basically, a Word file is a BLOB of unknown size. If you aren't using MS-SQL, then the definition and possibly access will differ. I don't think the solution will work for Oracle, for example.

[ricric.ferrancullo]

Sir, after retrieving the file from database can it be open again in ms word?

[Reverend Jim]

Not directly. First you must write the data to a Word file, then you can open it with Word.