update a mediumblob in mysql?

Question

0.00/5 (No votes)

See more:

VS2012

Hi,
I would like to update a mediumblob with image data.
I am using MySQL, VS 2013
The table is defined with
Pat_Picture_ID = int
Patient_ID = int
BildDaten = medium blob

Used functions:

C#

public Image ByteArrayToImage(byte[] inp)
{
    MemoryStream ms = new MemoryStream(inp);
    return Image.FromStream(ms);
}

public byte[] ImageToByteArray(string fileName)
{
    FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    BinaryReader reader = new BinaryReader(fs);
    return reader.ReadBytes((int)fs.Length);
}

public byte[] ImageToByteArray(Image img)
{
    MemoryStream ms = new MemoryStream();
    img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
    return ms.ToArray();
}

I tried in several ways:
1)

C#

DBPic = new MySqlDataAdapter("select * from pat_picture_old where Pat_Picture_ID = 9972", DBC);
sPic = new DataSet();
DBPic.Fill(sPic);
sPic.Tables[0].Rows[0]["BildDaten"] = ImageToByteArray(@"C:\Users\Michael\Pictures\Paris 2010\3\12-14037.jpg");
MySqlCommandBuilder cmb = new MySqlCommandBuilder(DBPic);
DBPic.UpdateCommand = cmb.GetUpdateCommand();
try
{
    if (DBPic.Update(sPic) > 0)
        richTextBoxExtended1.RichTextBox.AddImage(ByteArrayToImage((byte[])sPic.Tables[0].Rows[0]["BildDaten"]));
}
catch (MySqlException mEX)
{
    MessageBox.Show(mEX.Message);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Result: "timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding".

2)

C#

MySqlCommand cmd = new MySqlCommand("update pat_picture_old set BildDaten = ?bData where Pat_Picture_ID = 9972", DBC);
cmd.Parameters.Add("?bData", MySqlDbType.MediumBlob).Value = ImageToByteArray(@"C:\Users\Michael\Pictures\Paris 2010\3\12-14037.jpg");
try
{
    if (cmd.ExecuteNonQuery() > 0)
        richTextBoxExtended1.RichTextBox.AddImage(ByteArrayToImage((byte[])sPic.Tables[0].Rows[0]["BildDaten"]));
}
catch (MySqlException mEX)
{
    MessageBox.Show(mEX.Message);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Result: "timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding".
Inspecting the BildDaten field after these actions the content is 13 Bytes (System Array)

Just to check Inserting I tried

C#

rPic = sPic.Tables[0].NewRow();
rPic["Patient_ID"] = 233;
rPic["BildDaten"] = ImageToByteArray(@"C:\Users\Michael\Pictures\Paris 2010\3\12-14037.jpg");
sPic.Tables[0].Rows.Add(rPic);
DBPic.InsertCommand = cmb.GetInsertCommand();
try
{
    if (DBPic.Update(sPic) > 0)
        richTextBoxExtended1.RichTextBox.AddImage(ByteArrayToImage((byte[])sPic.Tables[0].Rows[1]["BildDaten"]));
}
catch (MySqlException mEX)
{
    MessageBox.Show(mEX.Message);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Result: "timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding".

Reading data worked without any problem

C#

DBC = new MySqlConnection(strConn);
DBC.Open();
MySqlDataAdapter DBPic = new MySqlDataAdapter("select * from pat_picture_old where Pat_Picture_ID = 9971", DBC);
DataSet sPic = new DataSet();
DBPic.Fill(sPic);
DataRow rPic = sPic.Tables[0].Rows[0];
richTextBoxExtended1.RichTextBox.AddImage(ByteArrayToImage((byte[])rPic["BildDaten"]));

I tried at least 10 different suggestions I found googling. All without result.
Knows anyone a solution?
Thanks in advance MiKr41

Posted 13-Nov-14 23:18pm

Member 10284036

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2014-11-13T23:39:00

I too would have pulled whatever hair I had left (too late) out - I think this ..

public byte[] ImageToByteArray(string fileName)
        {
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            BinaryReader reader = new BinaryReader(fs);
            return reader.ReadBytes((int)fs.Length);
        }

is not giving you all the bytes you need - the only way I made my insert images was akin to this copied from SO

byte[] rawData = File.ReadAllBytes(@"d:\Untitled.gif");
FileInfo info = new FileInfo(@"d:\Untitled.gif");
int fileSize = Convert.ToInt32(info.Length);
using(MySqlConnection connection = new MySqlConnection("server=192.168.1.104;uid=root;pwd=root;database=cady234;"))
{
    using(MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT INTO file (file_name, file_size, file) VALUES (?fileName, ?fileSize, ?rawData);";
        MySqlParameter fileNameParameter = new MySqlParameter("?fileName", MySqlDbType.VarChar, 256);
        MySqlParameter fileSizeParameter = new MySqlParameter("?fileSize", MySqlDbType.Int32, 11);
        MySqlParameter fileContentParameter = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length);
        fileNameParameter.Value = "test name";
        fileSizeParameter.Value = fileSize;
        fileContentParameter.Value = rawData;
        command.Parameters.Add(fileNameParameter);
        command.Parameters.Add(fileSizeParameter);
        command.Parameters.Add(fileContentParameter);
        connection.Open();
        command.ExecuteNonQuery();
    }
}

link : http://stackoverflow.com/questions/13208349/how-to-insert-blob-datatype[^]

I remember seeing some discussion on the web about some special parameters that were needed for blobs on the MySQL server side, in the end I didn't need them