用Mysql Connect/Net来测试Blog数据

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#