C#读取Mysql blob字段

开发环境：Windows XP Professional SP3、VS2008、Winform、MySQL5.0、MySQL.Data6.2.3.0 1、从硬盘上读取一图片，将其转化为流，然后存储到此BLOB字段中 view plaincopy to clipboardprint? private void button1_Click( object sender,EventArgse) { byte

开发环境：Windows XP Professional SP3、VS2008、Winform、MySQL5.0、MySQL.Data6.2.3.0

1、从硬盘上读取一图片，将其转化为流，然后存储到此BLOB字段中

view plaincopy to clipboardprint?

1. private void button1_Click(object sender, EventArgs e)
2. {
3. byte[] bytes = null;
4. bytes = File.ReadAllBytes(@"C:/Documents and Settings/user/My Documents/My Pictures/11.jpg");
5. using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection())
6. {
7. conn.ConnectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
8. MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
9. cmd.CommandText = "insert into test(id,picture) values(@id,@picture)";
10. cmd.CommandType = CommandType.Text;
11. cmd.Parameters.Add("@id", MySql.Data.MySqlClient.MySqlDbType.Int32);
12. cmd.Parameters.Add("@picture", MySql.Data.MySqlClient.MySqlDbType.Blob);
14. cmd.Parameters[0].Value = 15;
15. cmd.Parameters[1].Value = bytes;
16. cmd.Connection = conn;
17. conn.Open();
19. int affectedrows = cmd.ExecuteNonQuery();
21. cmd.Dispose();
22. conn.Close();
23. }
25. }

private void button1_Click(object sender, EventArgs e) { byte[] bytes = null; bytes = File.ReadAllBytes(@"C:/Documents and Settings/user/My Documents/My Pictures/11.jpg"); using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandText = "insert into test(id,picture) values(@id,@picture)"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@id", MySql.Data.MySqlClient.MySqlDbType.Int32); cmd.Parameters.Add("@picture", MySql.Data.MySqlClient.MySqlDbType.Blob); cmd.Parameters[0].Value = 15; cmd.Parameters[1].Value = bytes; cmd.Connection = conn; conn.Open(); int affectedrows = cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } }

2、读取此BLOB字段，将其转化为图片显示在Picturebox控件上

view plaincopy to clipboardprint?

1. private void button2_Click(object sender, EventArgs e)
2. {
3. using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection())
4. {
5. conn.ConnectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
6. conn.Open();
8. MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
9. cmd.CommandType = CommandType.Text;
10. cmd.CommandText = "select id,picture from test where id = 11";
11. cmd.Connection = conn;
13. System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
14. byte[] buffer = null;
15. if (reader.HasRows)
16. {
17. reader.Read();
18. long len = reader.GetBytes(1, 0, null, 0, 0);//1是picture
19. buffer = new byte[len];
20. len = reader.GetBytes(1, 0, buffer, 0, (int)len);
22. System.IO.MemoryStream ms = new System.IO.MemoryStream(buffer);
23. System.Drawing.Image iamge = System.Drawing.Image.FromStream(ms);
24. pictureBox1.Image = iamge;
25. }
26. }
28. }

private void button2_Click(object sender, EventArgs e) { using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString; conn.Open(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select id,picture from test where id = 11"; cmd.Connection = conn; System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); byte[] buffer = null; if (reader.HasRows) { reader.Read(); long len = reader.GetBytes(1, 0, null, 0, 0);//1是picture buffer = new byte[len]; len = reader.GetBytes(1, 0, buffer, 0, (int)len); System.IO.MemoryStream ms = new System.IO.MemoryStream(buffer); System.Drawing.Image iamge = System.Drawing.Image.FromStream(ms); pictureBox1.Image = iamge; } } }

数据库相关文件配置在App.config中，如果不用配置文件，可以写成：

string remote = "Persist Security Info=False;database=lhwtouch;server=服务器IP;user id=用户名;pwd=密码";

然后conn.ConnectionString = remote;即可。

后记：

之前在.net中用的mysql库是：MySQLDriverCS，但是一直没有搞定，而且用官方给的读取blob字段也失败。于是改用MySql.Data，注意Mysql.Data5.0版本不支持读取Blob字段，所以需要用较高版本，我用的是MySQL.Data6.2.3.0。

MySql.Data6.2.3.0下载地址：http://download.csdn.net/source/2968152

MySql.Data5.0.9.0下载地址：http://download.csdn.net/source/2968157

代码提供者：http://hi.csdn.net/Dobzhansky，在此深表感谢！

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