开发环境:Windows XP Professional SP3、VS2008、Winform、MySQL5.0、MySQL.Data.dll 6.2.3.0
1、从硬盘上读取一图片,将其转化为流,然后存储到此BLOB字段中
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();// 离开 using 块, connection 会自行关闭
}
}
2、读取此BLOB字段,将其转化为图片显示在Picturebox控件上
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=数据库名;server=服务器IP;user id=用户名;pwd=密码";
然后conn.ConnectionString = remote;即可。
后记:
之前在.net中用的mysql库是:MySQLDriverCS,但是一直没有搞定,而且用官方给的读取blob字段也失败。于是改用MySql.Data.dll ,注意Mysql.Data5.0版本不支持读取Blob字段,所以需要用较高版本,我用的是MySQL.Data.dll 6.2.3.0。