1、首先从Mysql上面下载Connecter
2、建立数据表
use test;
create table blobtest(
id integer primary key auto_increment,
imgdata mediumblob
);
3、存储文件到Blob字段
MySqlConnection conn = null;
try
{
string connstr = "Database=Test;Data Source=localhost;User Id=root";
conn = new MySqlConnection(connstr);
//conn.Open();
string query = "insert into test.blobtest (imgdata) values (?img)";
string tmp = null;
MySqlCommand cmd = new MySqlCommand(query, conn);
FileStream fs = new FileStream(@"d:\PS8940009M11Y转化机封.dwg", FileMode.Open, FileAccess.Read);
Byte[] bytes = new Byte[fs.Length];
fs.Read(bytes, 0, (int)bytes.Length);
fs.Close();
MySqlParameter param = new MySqlParameter("img",
MySqlDbType.MediumBlob,
(int)bytes.Length,
ParameterDirection.Input,
false,
0,
0,
null,
DataRowVersion.Current,
bytes);
//MySqlParameter param=new MySqlParameter("@img",MySqlDbType.MediumBlob);
//param.Value=bytes;
//cmd.Parameters.AddWithValue("@img",bytes);
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
4、从数据库到处Blob到文件
string connstr = "Database=Test;Data Source=localhost;User Id=root";
MySqlConnection conn = new MySqlConnection(connstr);
MySqlCommand cmd = new MySqlCommand("select imgdata from blobtest where id=15", conn);
MySqlDataReader sdr = null;
conn.Open();
sdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
sdr.Read();
//long startIndex = 0;
//int retval;
//int buffer_len = 1024;
//Byte[] bytes = new Byte[buffer_len];
//FileStream fs = new FileStream(@"d:\a2.dwg", FileMode.OpenOrCreate, FileAccess.Write);
//BinaryWriter writer = new BinaryWriter(fs);
//retval = (int)sdr.GetBytes(0, startIndex, bytes, 0, buffer_len);
//while (retval == buffer_len)
//{
// writer.Write(bytes);
// writer.Flush();
// startIndex += buffer_len;
// retval = (int)sdr.GetBytes(0, startIndex, bytes, 0, buffer_len);
//}
int retval;
FileStream fs = new FileStream(@"d:\a2.dwg", FileMode.OpenOrCreate, FileAccess.Write);
Byte[] bytes = new Byte[(sdr.GetBytes(0, 0, null, 0, int.MaxValue))];
retval = (int)sdr.GetBytes(0, 0, bytes, 0, (int)bytes.Length);
fs.Write(bytes, 0, (int)bytes.Length);
//writer.Close();
sdr.Close();
conn.Close();
//writer.Close();
fs.Close();
5、几个误区
1、同Ado.net不同,MysqlCommand的Parameter的表示是“?”,而不是“@”。
2、ByteFx.Data就是Mysql Connector/Net以前的版本,不建议使用。