C# Save data from DB (BLOB)

[countnazgul]

Hello,
I've got very odd problem.
I use windows application to store files in data base (MySql) and then to extract this files from data base and save it on hdd. But when i save them back to hdd tihs files are 1Kb less from original file. When i insert pictures this 1kb obviously is not imported and the pictures is shown. But other files when try to be opened is corrupted or if they open some error message is shown.
So the code that i use to save file in DB is:

          MySqlConnection con = new MySqlConnection("SERVER=localhost;" +
                "DATABASE=dbname;" +
                "UID=user;" +
                "PASSWORD=user;");
 
            MySqlDataAdapter da = new MySqlDataAdapter("Select * From test", con);
            MySqlCommandBuilder MyCB = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet("test");
 
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            FileStream fs = new FileStream(@"C:\image.rar", FileMode.OpenOrCreate, FileAccess.Read);
 
            byte[] MyData = new byte[fs.Length];
            fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
 
            fs.Close();
 
            da.Fill(ds, "test");
 
            DataRow myRow;
            myRow = ds.Tables["test"].NewRow();
 
            myRow["id"] = "12";
            myRow["blobdata"] = MyData;
            ds.Tables["test"].Rows.Add(myRow);
            da.Update(ds, "test");
 
            con.Close();
 

The code that extract files from database and save them to hdd is:

          MySqlConnection con = new MySqlConnection("SERVER=localhost;" +
                "DATABASE=dbname;" +
                "UID=user;" +
                "PASSWORD=user;");
 
            MySqlDataAdapter da = new MySqlDataAdapter("Select * From test where id = 12", con);
            MySqlCommandBuilder MyCB = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet("test");
 
            byte[] MyData = new byte[0];
 
            da.Fill(ds, "test");
            DataRow myRow;
            myRow = ds.Tables["test"].Rows[0];
 
            MyData = (byte[])myRow["blobdata"];
            int ArraySize = new int();
            ArraySize = MyData.GetUpperBound(0);
 
            FileStream fs = new FileStream(@"C:\Data\image.rar", FileMode.OpenOrCreate, FileAccess.Write);
            fs.Write(MyData, 0, ArraySize);
            fs.Close();
 

Can someone help me to find missing Kb :-)

Thanks!

[Plater]

fs.Read() returns an integer value that says how many bytes were actually read. It's possible the amount of data you want cannot be read with one Read() call, you should start by checking there?
(Or am I looking at the wrong spot for missing data?)

I would also recomend looking at the column DataType for the "blobdata" column. It might be coming in as not the expected datatype and thus some data is being truncated?(The lost data kinda sounds like a string conversion is happing somewhere)

[countnazgul]

fs.Read() returns an integer value that says how many bytes were actually read. It's possible the amount of data you want cannot be read with one Read() call, you should start by checking there?
(Or am I looking at the wrong spot for missing data?)

I would also recomend looking at the column DataType for the "blobdata" column. It might be coming in as not the expected datatype and thus some data is being truncated?(The lost data kinda sounds like a string conversion is happing somewhere)

Sorry is not 1Kb but 1b.
The column "blobdata" is from type LONGBLOB (i try and with another BLOB type but the result is the same)

[Plater]

Ok, on closer inspection I think the problem is this:

int ArraySize = new int();
ArraySize = MyData.GetUpperBound(0);
 

What is that? It should not be needed (and would explain why you are off by one byte)

You should be using more like this:

fs.Write(MyData, 0, MyData.Length);
 

[Curtis Rutland]

Ok, on closer inspection I think the problem is this:

int ArraySize = new int();
ArraySize = MyData.GetUpperBound(0);
 

What is that? It should not be needed (and would explain why you are off by one byte)

You should be using more like this:

fs.Write(MyData, 0, MyData.Length);
 

That's the only thing that I did differently in a similar project I did recently and everything works fine for me. I also think that may be where your problem stems from.

[countnazgul]

Thanks for answers!
I find solution that is similar to this but works for me ;-)
And here is the code:

 
            MySqlConnection Conn = new MySqlConnection("SERVER=localhost;" +
            "DATABASE=db;" +
            "UID=user;" +
            "PASSWORD=root;");
 
            MySqlCommand Cmd = new MySqlCommand("Select * From test where id = 19", Conn);
            Cmd.CommandType = CommandType.Text;
            Conn.Open();
            MySqlDataReader Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            FileStream FStream = null;
            BinaryWriter BWriter = null;
 
            byte[] Binary = null;
            const int ChunkSize = 100;
            int SizeToWrite = 0;
            MemoryStream MStream = null;
 
            while (Reader.Read())
            {
                FStream = new FileStream(@"c:\Data\Nomenclature123.xls", FileMode.OpenOrCreate, FileAccess.Write);
                BWriter = new BinaryWriter(FStream);
                Binary = (Reader["blobdata"]) as byte[];
                SizeToWrite = ChunkSize;
                MStream = new MemoryStream(Binary);
 
                for (int i = 0; i < Binary.GetUpperBound(0) - 1; i = i + ChunkSize)
                {
                    if (i + ChunkSize >= Binary.Length) SizeToWrite = Binary.Length - i;
                    byte[] Chunk = new byte[SizeToWrite];
                    MStream.Read(Chunk, 0, SizeToWrite);
                    BWriter.Write(Chunk);
                    BWriter.Flush();
                }
                BWriter.Close();
                FStream.Close();
            }
            FStream.Dispose();
 

Thanks again!