C # read MySQL blob field (reprint)

http://blog.csdn.net/config_man/article/details/6123191

Development environment: Windows XP Professional SP3, VS2008, Winform, MySQL5.0, MySQL.Data.dll 6.2.3.0

1. Read a picture from your hard disk, convert it to a stream, and store it in this BLOB field

[CSharp]View Plaincopyprint?

2. Private void button1_click (object sender, EventArgs e)
4. {
6. byte[] bytes = null;
8. bytes = File.readallbytes (@"c:/documents and settings/user/my documents/my pictures/11.jpg");
10. using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection ())
12. {
14. Conn. ConnectionString = configurationmanager.connectionstrings["Test"].  ConnectionString;
16. MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand ();
18. Cmd.commandtext = "INSERT INTO Test (id,picture) VALUES (@id, @picture)";
20. Cmd.commandtype = CommandType.Text;
22. Cmd.  Parameters.Add ("@id", MySql.Data.MySqlClient.MySqlDbType.Int32);
24. Cmd.  Parameters.Add ("@picture", MySql.Data.MySqlClient.MySqlDbType.Blob);
28. Cmd. Parameters[0]. Value = 15;
30. Cmd. PARAMETERS[1]. Value = bytes;
32. Cmd. Connection = conn;
34. Conn. Open ();
38. int affectedrows = cmd.  ExecuteNonQuery ();
42. Cmd. Dispose (); //Can not be called here,
44. Conn. Close (); //leave using block, connection shuts itself down
46. }
48. }

2. Read this BLOB field and convert it to a picture displayed on the PictureBox control

[CSharp]View Plaincopyprint?

2. Private void button2_click (object sender, EventArgs e)
4. {
6. using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection ())
8. {
10. Conn. ConnectionString = configurationmanager.connectionstrings["Test"].  ConnectionString;
12. Conn. Open ();
16. MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand ();
18. Cmd.commandtype = CommandType.Text;
20. Cmd.commandtext = "Select Id,picture from test where id = 11";
22. Cmd. Connection = conn;
26. System.Data.Common.DbDataReader reader = cmd. ExecuteReader ();
28. byte[] buffer = null;
30. if (reader. HasRows)
32. {
34. Reader. Read ();
36. Long len = reader. GetBytes (1, 0, null, 0, 0); 1 is picture
38. Buffer = new Byte[len];
40. Len = reader.  GetBytes (1, 0, buffer, 0, (int) len);
44. System.IO.MemoryStream ms = New System.IO.MemoryStream (buffer);
46. System.Drawing.Image iamge = System.Drawing.Image.FromStream (ms);
48. pictureBox1.Image = iamge;
50. }
52. }
54. }

The database-related files are configured in app. Config, and if you don't use a configuration file, you can write:

String remote = "Persist Security info=false;database= database name; server= server ip;user id= user name; pwd= password";

Then Conn. ConnectionString = remote;

Postscript:

The MySQL library used in. NET was: Mysqldrivercs, but it was never done, and the Read BLOB field was also failed with the official feed. Instead of using MySql.Data.dll, note that the mysql.data5.0 version does not support reading BLOB fields, so a higher version is required, and I use MySQL.Data.dll 6.2.3.0.

MySql.Data.dll 6.2.3.0:http://download.csdn.net/source/2968152

MySql.Data.dll 5.0.9.0:http://download.csdn.net/source/2968157

C # read MySQL blob field (reprint)