Get a blob file in MySql database

Question

I'm trying to get a Blob(winWord doc. stored in MySql) file from MySql. All is working but when I try to open it MSWORD tells me that the file has problems on its contents.This is my code:

myConn.Open();
            MySqlDataReader myReader;
            long CurrentIndex = 0;
            long BytesReturned;

            using (myReader = view.ExecuteReader())
            {
                while (myReader.Read())
                {
                    if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        string strFilename = saveFileDialog1.FileName;
                        FileStream fs = new FileStream(strFilename, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                        BinaryWriter bw = new BinaryWriter(fs);
                        CurrentIndex = 0;
                        long len = 100;
                        byte[] blob = new byte[len];
                        int id = myReader.GetOrdinal("word");
                        BytesReturned = myReader.GetBytes(id, CurrentIndex, blob, 0, (int)len);

                        while (BytesReturned == (int)len)
                        {
                            bw.Write(blob);
                            bw.Flush();
                            CurrentIndex += (int)len;
                            BytesReturned = myReader.GetBytes(id, CurrentIndex, blob, 0, (int)len);


                        }
                        bw.Write(blob, 0, (int)len - 1);
                        bw.Flush();
                        bw.Close();
                        fs.Close();

                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Thanks in advance.

codercat · Accepted Answer · 2013-12-30 09:00:50Z

1

The following documentation is suggests you store and retrive your file in database

http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-blob.html

answered Dec 30, 2013 at 9:00

codercat

23.3k9 gold badges64 silver badges85 bronze badges

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

1 Comment

WiiLF Over a year ago

Broken link, can't follow it.

Vladimir Liubimov · Accepted Answer · 2015-08-12 13:58:53Z

This algorithm work just fine, but there a little mistake in code.

String

bw.Write(blob, 0, (int)len - 1);

after while statement, need replace with

bw.Write(blob, 0, BytesReturned);

The result code (make saving blob to file separate method, and ading some "using" to FileStream and BinaryReader):

    public bool ReadDBBlobToFile ( MySqlDataReader parReader, string parFilePath, string parColumnName )
    {
        bool retResult = false;
        if ( parReader == null )
        {
            throw new NullReferenceException ( "MySqlCommand is null" );
        }

        int id = parReader.GetOrdinal(parColumnName);
        if ( !parReader.IsDBNull ( id ) )
        {
            string dir = Path.GetDirectoryName(parFilePath);
            if ( string.IsNullOrWhiteSpace ( dir ) )
            {
                dir = Path.GetDirectoryName ( Path.GetFullPath ( parFilePath ) );
            }
            Directory.CreateDirectory ( dir );

            using ( FileStream fs = new FileStream ( parFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite ) )
            {
                using ( BinaryWriter bw = new BinaryWriter ( fs ) )
                {
                    long CurrentIndex = 0;
                    long len = 100;
                    byte[] blob = new byte[len];

                    long BytesReturned = parReader.GetBytes ( id, CurrentIndex, blob, 0, ( int ) len );

                    while ( BytesReturned == len )
                    {
                        bw.Write ( blob );
                        bw.Flush ( );
                        CurrentIndex += len;
                        BytesReturned = parReader.GetBytes ( id, CurrentIndex, blob, 0, ( int ) len );
                    }
                    if ( BytesReturned > 0 )
                    {
                        bw.Write ( blob, 0, ( int ) BytesReturned );
                    }
                    bw.Flush ( );
                    bw.Close ( );
                }
                fs.Close ( );
            }
            retResult = true;
        }
        else
        {
            retResult = false;
        }
        return retResult;
    }


    myConn.Open();
        using (MySqlDataReader myReader = view.ExecuteReader())
        {
            while (myReader.Read())
            {
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string strFilename = saveFileDialog1.FileName;
                    ReadDBBlobToFile ( myReader, strFilename, "word" );
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Collectives™ on Stack Overflow

Get a blob file in MySql database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related