Spire.Doc is a professional Word .NET library specifically designed for developers to create, read, write, convert and print Word document files. Get free and professional technical support for Spire.Doc for .NET, Java, Android, C++, Python.

Mon Mar 26, 2018 2:46 pm

Is it possible to save a document displayed in Word directly to a varbinary(max) column in SQL Server. Later I wish to recall the doc back into Word.

How would I accomplish this?

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Tue Mar 27, 2018 7:57 am

Hello,

Thanks for you inquiry. Please refer to the below code snippet and give it a try.
Code: Select all
        private void button1_Click(object sender, EventArgs e)
        {
            Document doc = new Document();
            doc.LoadFromFile(@"test.docx");
            MemoryStream ms = new MemoryStream();
            doc.SaveToStream(ms, FileFormat.Docx);
            ms.ToArray();
            Byte[] byData = ms.ToArray();

            string constr = "server=.;User Id=root;password=123;Database=mytest";
            SqlConnection mycon = new SqlConnection(constr);
            mycon.Open();
            SqlCommand mycmd = new SqlCommand("insert into pro_table (pro_name,pro_file) values('testFile',@file)", mycon);
            mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length);
            mycmd.Parameters["@file"].Value = byData;
            mycmd.ExecuteNonQuery();
            mycon.Close();
        }


        //get
        private void button2_Click(object sender, EventArgs e)
        {
            string conn = "server=.;database=mytest;Uid=root;Pwd=123 ";
            string str = "select pro_file from pro_table where pro_name='testFile' ";
            SqlConnection myconn = new SqlConnection(conn);
            SqlDataAdapter sda = new SqlDataAdapter(str, conn);
           
            DataSet myds = new DataSet();
            myconn.Open();
            sda.Fill(myds);
            myconn.Close();
            Byte[] Files = (Byte[])myds.Tables[0].Rows[0]["pro_file"];

            Document odoc = new Document();
            MemoryStream oms = new MemoryStream(Files);
            odoc.LoadFromStream(oms, FileFormat.Docx);
            odoc.SaveToFile("get.docx", FileFormat.Docx);
        }


Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Fri Mar 30, 2018 8:05 am

Hello,

Greeting from E-iceblue.
Is the solution helpful to you?
Your feedback will be greatly appreciated.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Tue Apr 03, 2018 3:10 pm

Thank you for your follow up.

I can successfully save docx in SQL Server, but I am having difficulty getting the docx from SQL Server to Word. See code below.


objDocument.LoadFromStream(objMemoryStream1, FileFormat.Docx) produces an error:

"Zip exception. Can't locate end of central directory record. Possible wrong file format or archive is corrupt."


Jeff

---------------------
Code: Select all
 Public Sub GrabDocx()

        Dim objDataSet2 As New DataSet

        Dim strDoc_Id As String = txtDoc_Id.Text

        strSelect = "SELECT doc_id, doc_image FROM fpdocs " &
                    "WHERE doc_id = " & "'" & strDoc_Id & "'"

        Dim objDataAdapter2 As New SqlDataAdapter(strSelect, objConnection)

        'fill the dataSet object with data
        objDataAdapter2.Fill(objDataSet2, "cldocs")

        Dim objCommand As New SqlCommand(strSelect, objConnection)
        'open connection
        If objConnection.State = ConnectionState.Closed Then objConnection.Open()

        Dim objSQLDataReader = objCommand.ExecuteReader
        Dim objDataTable As New DataTable

        objDataTable.Load(objSQLDataReader)

        Dim row As DataRow = objDataTable.Rows(0)

        Dim objDocument As New Document

        If IsDBNull(row("doc_Image")) Then
            'do not try display doc
        Else
            Dim objMemoryStream1 As New IO.MemoryStream(CType(row("doc_image"), Byte()))

            Try
                objDocument.LoadFromStream(objMemoryStream1, FileFormat.Docx)
            Catch ExceptionErr As Exception
                MessageBox.Show(ExceptionErr.Message)
            End Try

            'dispose of object memorystream
            If Not IsNothing(objMemoryStream1) Then objMemoryStream1.Dispose()
        End If

        'close connection
        If objConnection.State = ConnectionState.Open Then objConnection.Close()
        'dispose of objects
        If Not IsNothing(objCommand) Then objCommand.Dispose()
        If Not IsNothing(objSQLDataReader) Then objSQLDataReader.Close()
        If Not IsNothing(objDataTable) Then objDataTable.Dispose()

    End Sub

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Wed Apr 04, 2018 9:31 am

Hello,

Thanks for your feedback. I tested your code which worked smoothly on my side. To help us investigate the issue, would you please share the input Word file and the code you are saving Word file to database? On the other hand, what's the data type of column["doc_Image"] in your database's table which is used for saving the Word file?

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Wed Apr 04, 2018 5:10 pm

Doc_Image is varbinary(max), null

Code to save docx in sql database:



Code: Select all
 Public Sub ImportDoc()

        Dim Img As Image
        Dim ImageList As New ImageList '  provide for example
        Dim FileDiag As New OpenFileDialog
        Dim strImagePath As String = ""
        Dim blnHasRows As Boolean = False
        Dim strSelect As String = ""

        ' Make sure a document format is selected
        If Trim(cboDoc_Format.Text) = "" Then
            MessageBox.Show("A doc format type must be selected.")
            Exit Sub

        ElseIf Trim(cboDoc_Format.Text) = "Docx" Then
            'set provide image filter for filediag
            FileDiag.Filter = "Image Files(*.docx;|*.docx;|All files (*.*)|*.*"
            Dim intResult As DialogResult = FileDiag.ShowDialog

            Dim strFileName As String = FileDiag.FileName


            If intResult = Windows.Forms.DialogResult.OK Then

                Dim objDocx As Byte() = File.ReadAllBytes(strFileName)

                Dim objCommand As SqlCommand = New SqlCommand
                Dim objMemoryStream As New IO.MemoryStream

                'convert the stream to the byte array (Blob for SQL)
                Dim b As Byte() = objMemoryStream.ToArray()

                objCommand.CommandText = "UPDATE cldocs " &
                                "SET doc_image=@doc_image " &
                                "WHERE doc_id = " & "'" & strDoc_Id & "'"

                'set the SQLCommand object properties
                objCommand.Connection = objConnection
                objCommand.CommandType = CommandType.Text

                'paramters for columns
                objCommand.Parameters.AddWithValue("@doc_image", b)


                'open Connection
                If objConnection.State = ConnectionState.Closed Then objConnection.Open()

                Try
                    objCommand.ExecuteNonQuery()
                Catch SqlExceptionErr As SqlException
                    MessageBox.Show(SqlExceptionErr.Message)
                End Try

                'close connection
                If objConnection.State = ConnectionState.Open Then objConnection.Close()
                'reset save document flag
                blnSaveDoc = True
                'dispose of objects
                If Not IsNothing(objCommand) Then objCommand.Dispose()
                If Not IsNothing(objCommand) Then objMemoryStream.Dispose()

            End If
     End If

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Wed Apr 04, 2018 9:19 pm

Question: When i get a docx from SQL Server, do I need to save it to a file before I can open it in Word?

Thanks.

Jeff

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Thu Apr 05, 2018 6:20 am

Hello,

Thanks for sharing your code.
You don't need to save the docx to a file before open it, Spire.Doc can open the file stream directly.
With looking into your code for saving data, I found that you just saved the byte array form an empty stream. That was why you got the error when loading it. You just need to add the file bytes when using objCommand.Parameters.AddWithValue. Please see below.
Code: Select all
            byte[] objDocx = File.ReadAllBytes(strFileName);
                objCommand.Parameters.AddWithValue("@doc_image", objDocx);


Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Sun Apr 08, 2018 11:33 pm

Adding the line of code produced the same error. Will be sending you code and sample database.

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Mon Apr 09, 2018 3:54 am

Hello,

Please refer to my code snippet and try again. You could also save the bytes array from your database to a file and open it with MS Word to check if the data is correct. If the issue still bothers you, please share us with your source Word file.
Code: Select all
'save
      Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
         Dim doc As New Document()
         doc.LoadFromFile("test.docx")
         Dim ms As New MemoryStream()
         doc.SaveToStream(ms, FileFormat.Docx)
         ms.ToArray()
         Dim byData() As Byte = ms.ToArray()

         Dim constr As String = "server=.;User Id=root;password=123;Database=mytest"
         Dim mycon As New SqlConnection(constr)
         mycon.Open()
         Dim mycmd As New SqlCommand("insert into pro_table (pro_name,pro_file) values('testFile',@file)", mycon)
         mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length)
         mycmd.Parameters("@file").Value = byData
         mycmd.ExecuteNonQuery()
         mycon.Close()
      End Sub


      'get
      Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button2.Click
         Dim conn As String = "server=.;database=mytest;Uid=root;Pwd=123 "
         Dim str As String = "select pro_file from pro_table where pro_name='testFile' "
         Dim myconn As New SqlConnection(conn)
         Dim sda As New SqlDataAdapter(str, conn)

         Dim myds As New DataSet()
         myconn.Open()
         sda.Fill(myds)
         myconn.Close()
         Dim Files() As Byte = CType(myds.Tables(0).Rows(0)("pro_file"), Byte())
            'save to file
         'BinaryWriter bw = new BinaryWriter(File.Open("D:\\result.docx", FileMode.OpenOrCreate));
         'bw.Write(Files);
         'bw.Close();
         Dim odoc As New Document()
         Dim oms As New MemoryStream(Files)
         odoc.LoadFromStream(oms, FileFormat.Docx)
         odoc.SaveToFile("get.docx", FileFormat.Docx)
      End Sub


Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Tue Apr 10, 2018 12:36 am

Simon is a genius! Your code worked! I think these two lines of code allowed the docx to be properly saved in SQLServer.

Code: Select all
 mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length)
 mycmd.Parameters("@file").Value = byData


Have a couple more issues/questions which will be transmitted to you tomorrow.

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Tue Apr 10, 2018 1:21 am

Hello,

Thanks for your prompt feedback.
Glad to hear that it worked. I am looking forward to hearing from you. Good day.

Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Mon Apr 16, 2018 3:44 pm

Here is my next challenge. Steps are:

1. Retrieve document from SQL Server and load it into Word.

2. Make changes to the document.

Challenge: how do I save the modified document back into SQL Server without saving my changes to a file and then import the changed files to SQL server? Is there a way?

jstiegler
 
Posts: 18
Joined: Fri Mar 23, 2018 5:35 pm

Tue Apr 17, 2018 1:32 am

Hello,

Thanks for your inquiry. You could save the Word file to stream and then import it back to SQL Server like before.
Code: Select all
        '1. Retrieve document from SQL Server and load it into Word.
        Dim doc As New Document()
        doc.LoadFromStream(MS, FileFormat.Docx)
        '2. Make changes to the document.
        Dim resultMs As New IO.MemoryStream()
        'save to stream
        doc.SaveToStream(resultMs, FileFormat.Docx2010)
        Dim resultData() As Byte = resultMs.ToArray()
        'save bytes back to SQL Server


Best regards,
Simon
E-iceblue support team
User avatar

Simon.yang
 
Posts: 620
Joined: Wed Jan 11, 2017 2:03 am

Return to Spire.Doc