Solved

Display SQL Word VARBINARY(MAX) Binary Document in Visual Studio Form - REVISITED

Posted on 2013-10-23
13
1,438 Views
Last Modified: 2014-01-13
I already asked this question in this post.  It was recommended to me not to continue with the original plan of importing Word documents into VARBINARY(MAX) fields:

Original EE Post

But after doing some research, I believe this is the better way to go.  According to a whitepaper I found from Microsoft, since the vast majority of our Resume Word documents are around 50KB (well under 1MB), it was recommended to import them into a VARBINARY(MAX) instead of using FILESTREAM.  We have about 77,000 Resume files which total about 4GB so I think we'll be fine once they are all loaded.

So, now I'm back to my original question.  In a Visual Studio Form using Visual Basic, how do I display the Word document in it's original format?  I have a table where I manually inserted 10 Resumes rows including the Word binary file into a VARBINARY(MAX) column.  I dropped all of the fields from the Data Source in Details mode so it looks like a record edit screen complete with the usual record options -- except the resume_binary field which is showing a red circle with a white horizontal line (indicating "no").
0
Comment
Question by:wchestnut
  • 7
  • 3
  • 3
13 Comments
 

Author Comment

by:wchestnut
ID: 39594904
BTW, I did try and change the field type to Rich TextBox, but only "Byte[] Array" is displayed:
Screen Capture
0
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 39596390
Do you already know how to read Word files and their different formats?

Basically what you have there is the Byte array representing the file.
You need to use a MemoryStream and a tool to actually read the doc or docx files.

What exactly do you have now?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39596474
Have you considered using a browser object and allow that to open your document

There are a couple of 3rd party tools as well... (though dwindling in number) a friend has suggested : http://www.gemboxsoftware.com/document/overview there is a free version but really limited to size of doc (20 paragraphs) but means you can test and play.

Might be worthwhile reading : http://support.microsoft.com/kb/257757
0
 

Author Comment

by:wchestnut
ID: 39597433
I have nothing now.  I'm just starting again with Visual Basic and SQL after being away from them for a number of years.

mark_willis, that is an AWESOME idea and I think it's exactly what we need.  I'm going to try it out and let you guys know.
0
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 39597482
I already used GemBox on a project some years ago and highly recommend it :)
0
 

Author Comment

by:wchestnut
ID: 39597626
Okay, bare with me guys... I installed SetupGemBoxDocument23.msi, used Add Reference and checked GemBox.Document 23 for .NET 3.0 - 4.5.  Then I found sample code to "Get Content" and modified it:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Imports GemBox.Document
        Imports System.Text

        ' If using Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY")

        Dim document As DocumentModel = DocumentModel.Load(File_linkLinkLabel.Text)

        Dim sb = New StringBuilder()

        ' Get content from each paragraph
        For Each paragraph As Paragraph In document.GetChildElements(True, ElementType.Paragraph)
            sb.AppendFormat("Paragraph: {0}", paragraph.Content.ToString())
            sb.AppendLine()
        Next

        ' Get content from each bold run
        For Each run As Run In document.GetChildElements(True, ElementType.Run)
            If (run.CharacterFormat.Bold) Then
                sb.AppendFormat("Bold run: {0}", run.Content.ToString())
                sb.AppendLine()
            End If
        Next

        '        Console.WriteLine(sb.ToString())

        WebBrowserResume.DocumentText = sb.ToString

Open in new window


I created a Button1 on the Form which I was going to use to populate the WebBrowserResume object.  But I received a bunch of errrors:

Errors
What did I do wrong or not do?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:wchestnut
ID: 39598605
I'm also having difficulty finding some sample code to try and use WebBrowser to load Word documents.  I've searched all over but didn't find anything current with the code.
0
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 39598784
But wait... isn't just transforming the byte array to a file and send it to the user enough?
Do all the users have Word installed or you have to provide a way to show the contents on the website?

If this is enough just use:
System.IO.File.WriteAllBytes(filepath, bitearray);

Open in new window

After this you know that filepath holds your doc, just send it to the user!
He will click save or open with Word and it's done... no? :)
0
 

Author Comment

by:wchestnut
ID: 39598895
No, not really.  Our goal is to create a system to import the 77,000 Resumes we have in Word documents (and all new ones moving forward) into an SQL database and be able to search and view them.  My first task as proof of concept is to create a user interface and provide basic keyword search abilities.  The end game would not use external files at all after they're imported and include some intelligence to narrow-down potential candidates faster and easier than the Recruiters do now.

I've already manually created and populated a 10 row sample in a table with file information as well as imported the Word document into a VARBINARY(MAX) column which would be used for Full Text searching as well as (hopefully) displaying the document on a form:
Screenshot
The large white box at the bottom is where the Resume contents would be displayed in it's original Word format -- either from the VARBINARY(MAX) column or the actual Word file itself.

I've been trying to get GemBox Document to work, but I'm not as advanced in .Net now as I want to be.  I created some Projects in Visual Studio but have to learn how to get the Modules to work from a Form.

I do have a backup plan to use VBA code in Word to Open, Select All, Copy the text in each of the 77,000 Word documents then insert new rows into SQL using a large Text field instead and provide a link on the Form to the external document.  But that's more of a last-resort option if I can't get this to work.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39598933
Can use something like : WebBrowser1.Navigate(@"C:\myDocment.doc");

but that assumes the doc can be loaded from disk...

as for gembox, it was a recommendation from a developer mate of mine. So, cant really help all that much other than following the examples. I can always ask him though...

also might be worth running the sample from their online documentation : http://www.gemboxsoftware.com/SampleExplorer/Document/CommonUses/Reading?tab=vb
0
 

Author Comment

by:wchestnut
ID: 39599002
Yeah, tried the Navigate method... it just opens the document externally in Word.

I've been up and down through their documentation.  I think I figured out that I needed to create a Module in my VS Project.  I just don't know how I can use it from my Form yet.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 39599186
77,000 word documents ??? Wow...

I would be looking at using FILESTREAM within SQL rather than varbinary(max) http://technet.microsoft.com/en-us/library/gg471497.aspx

Just as a side note... Interestingly (frustratingly) the samples I found seem to always load doc files from disk (or streaming data) such as : http://www.dotnetspider.com/projects/512-Store-Retrieve-pdf-txt-doc-Images-Sql-server-database.aspx

In fact, the more I look the less I get. The browser wont work for most recent docx variants.

So, might be inclined to write the "load resume" to file it away as a DOC / DOCX / PDF and store its location within the DB.
0
 

Author Comment

by:wchestnut
ID: 39601476
I read Microsoft's white paper on FILESTREAM... they recommend loading the binary into a VARBINARY(MAX) if the file sizes are less than 1MB -- which virtually all of them are.

I managed to figure out the GemBox to a point where I hit the 20-paragraph limit with the free version on my own resume (didn't think it really had 20 paragraphs).  Now I just have to figure out the error event handler and let it work with those first 20 paragraphs and display the results somewhere just for proof of concept.

I will also check our your streaming from disk link, too.  Thanks for the feedback so far!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL server is using more virtual memory. 5 69
Microsoft Azure SQL - create a read only user 2 17
Azure SQL Insert not working suddenly 11 23
sql query questions 2 25
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

23 Experts available now in Live!

Get 1:1 Help Now