用Mysql Connect/Net来测试Blog数据

gm8pleasure

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以前的版本，不建议使用。
del.icio.us 标记: Mysql, Ado.net, c#