How to Read Image Data (storing word document) from SQL Server Table and save it to local folder [duplicate]

Question

I would like to extract word documents stored as image type fields in SQL and save each individual document in a local folder. The doc files stored in the image type data in sql, are special encoded OLEObjects. I am trying with one first but I need to do the same for each record in the table. This is what I have done so far:

Byte[] bytData = null;
string constring = @"mystirngconnection";

 SqlCommand command = new SqlCommand(@"SELECT LongDescription FROM SuUserReport 
                                WHERE ProductId = 53 AND UserReportId = 31525");

 command.CommandType = CommandType.Text;

 SqlConnection myconn = new SqlConnection(constring);

 command.Connection = myconn;

 myconn.Open();

 using (SqlDataReader dr = command.ExecuteReader())
  {
    while (dr.Read())
     {
       bytData = (byte[])dr["LongDescription"];
     }
  }

if (bytData != null)
 {
      FileStream fs = new FileStream("C:\\Temp\\Test1.doc", 
                                     FileMode.OpenOrCreate, FileAccess.Write);

                    BinaryWriter br = new BinaryWriter(fs);
                    br.Write(bytData, 0, bytData.Length);
                    fs.Dispose();
 }

I basically have two problems:

Reading this type of data it is very slow
Using the code above works for normal word documents however the information in my table has OLEObject encoded information, the code saves a word document but the information is only encoded characters

Previously, these images (word documents) where opened straightaway from SQL when the form was opened using an Access bound object frame linked to the longDescription image type field. The user could edit the document by double clicking on this special frame. The following code opens the document and makes it available to save changes:

    With oleLongDescription
        .Verb = acOLEVerbOpen 'In a separate window
        .Action = acOLEActivate
    End With

Could anybody be so kind as to help me out with this, please? I do not mind to use c# or vb as long as it works =).

Have you tried with a different file just to be sure the file is not corrupted? — Alex
– Alex, Commented Jul 11, 2017 at 11:03
The problem with OLEObjects is that they are NOT proper Word files but rather Word files with extra headers and footers. This article explains why your exported "word files" cannot be opened and gives some hints regarding where to look: social.msdn.microsoft.com/Forums/vstudio/en-US/… — Alex
– Alex, Commented Jul 11, 2017 at 15:50
Thanks Alex yes I discovered they are OLEobjects stored in an image column in sql and not normal word documents, that is why others can read their files straightaway in c# or vba but I cannot I need to use this horrible ole objects to somehow get the info. Thanks a lot for your link it looks very similar to what I was trying to achieve =). — Kabethy
– Kabethy, Commented Jul 12, 2017 at 8:56

Haim Katz · Accepted Answer · 2017-07-11 11:49:49Z

0

Here is code that I use to open my blobs. the byte[] myarray is the blob field from the database, the fileext is the extension of the file you are creating and the filename is a name you give it. It deletes any previous file of this name.

 public static string Openfilefrombyte(byte[] myarray, string fileext, string filename)
    {
        Computer myComputer = new Computer();

        string filenamef = myComputer.FileSystem.SpecialDirectories.Temp + @"\" + filename + "." + fileext;
        if (File.Exists(filenamef))
        {
            File.Delete(filenamef);
        }

        //save to file and open
        FileStream myfs = new FileStream(filenamef, FileMode.CreateNew);

        myfs.Write(myarray, 0, myarray.Length);
        myfs.Flush();
        myfs.Close();

        myfs = null;
        Process.Start(filenamef);

        return "OK";

    }

answered Jul 11, 2017 at 11:49

Haim Katz

4613 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kabethy Over a year ago

Thanks Haim, I do not have a problem saving the file the problem is that the bytes or data are kind of encoded. I am also using a console application so I do not have FileSystem.SpecialDirectories.Temp =(

Haim Katz Over a year ago

You can obviously use any directory you want instead of the temp directory, but your main problem is converting all your oleobjects to word objects. I suggest you add these tag words, maybe you can get some help.

Aladein · Accepted Answer · 2017-07-11 11:51:00Z

0

i basically have two problems:

Reading this type of data it is very slow

Answer : Yes Its Slowly But if You save The Image in sql database as VarBinary it will be Fast .

i am Using OpenFileDialog1 To Bring Image Or what You Want (Rar , Pdf .World ...)

  If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            Try
                T4.Text = OpenFileDialog1.FileName
                T5.Text = Path.GetExtension(OpenFileDialog1.FileName)
                T7.Text = Path.GetFileNameWithoutExtension(OpenFileDialog1.FileName)
            Catch ex As Exception
            End Try
        End If

Here My Code I used it To Save Data In My Sql database :

 Dim SQLCON As New SqlConnection(MyDataBaseCon)
 Dim CMD As SqlCommand
 Try
        CMD = New SqlCommand("Insert Into TBLAtach (ID,AtachName,AtachMain,AtachFile,AtachNM,AtachDT,AtachAdres) Values (@ID,@AtachName,@AtachMain,@AtachFile,@AtachNM,@AtachDT,@AtachAdres)", SQLCON)
        SQLCON.Open()
        CMD.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int)).Value = Val(T1.Text)
        CMD.Parameters.Add(New SqlParameter("@AtachName", SqlDbType.Int)).Value = Val(T2.Text)
        CMD.Parameters.Add(New SqlParameter("@AtachMain", SqlDbType.Int)).Value = Val(T3.Text)
        Dim FSTream As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
        Dim BNStream As New BinaryReader(FSTream)
        Dim FAttach() As Byte = BNStream.ReadBytes(BNStream.BaseStream.Length)
        CMD.Parameters.Add(New SqlParameter("@AtachFile", SqlDbType.VarBinary)).Value = FAttach
        CMD.Parameters.Add(New SqlParameter("@AtachNM", SqlDbType.NVarChar, 50)).Value = T5.Text
        CMD.Parameters.Add(New SqlParameter("@AtachDT", SqlDbType.NVarChar, 50)).Value = TD1.Text
        CMD.Parameters.Add(New SqlParameter("@AtachAdres", SqlDbType.NVarChar, 250)).Value = T7.Text
        CMD.ExecuteNonQuery()
        SQLCON.Close()
        FSTream.Close()
        BNStream.Close()
        MsgBox("Ok ", MsgBoxStyle.Information)
        SearchID()
        ClearTxT()
    Catch ex As Exception
        MsgBox(ex.Message)
        SQLCON.Close()
        WaitPic.Visible = False
    End Try

Using the code above, saves a word document but the information is only encoded characters

Answer : To retrieve Data (Image,Pdf,RAR,...) i am Using This Code :

   Try
        ItDataset.Clear()
        Flt = "Select ID ,  AtachAdres + AtachNM AS 'Fname' , AtachFile from TBLAtach where ID = " & T1.Text & ""
        ItDataset = GeneralDataManager.InquireData(ItDataset, Flt, "TBLAtach")
        If Me.BindingContext(ItDataset, "TBLAtach").Count > 0 Then
            Dim FName As String = ItDataset.Tables("TBLAtach").Rows(0).Item("Fname")
            Dim FAttach() As Byte = CType(ItDataset.Tables("TBLAtach").Rows(0).Item("AtachFile"), Byte())
            Dim FStream As New FileStream(FName.ToString, FileMode.OpenOrCreate, FileAccess.Write)
            FStream.Write(FAttach, 0, (FAttach.Length))
            Process.Start(FName)
            FStream.Close()
        End If
        WaitPic.Visible = False
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

And Change Your Connection To Your Data Base Con Connection

i hope this Answer Is Good For You . Thank You .

answered Jul 11, 2017 at 11:51

Aladein

3123 silver badges14 bronze badges

4 Comments

Kabethy Over a year ago

Thanks Aladein, are you working with Crystal reports? Excuse my ignorance but I do not understand your code =(. I am not sure how your code will make the difference and display information and not encoded information. Perhaps I did not explained myself well.

Aladein Over a year ago

No .. i am not working with Crystal reports .. this is vb.net code.

Aladein Over a year ago

this is my last project i working it .(Archive project )

Kabethy Over a year ago

Thanks Aladein I explain a little bit more above =)

Collectives™ on Stack Overflow

How to Read Image Data (storing word document) from SQL Server Table and save it to local folder [duplicate]

2 Answers 2

2 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Linked

Related