C#讀取Mysql blob欄位

開發環境：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);  
13.   
14.               cmd.Parameters[0].Value = 15;  
15.               cmd.Parameters[1].Value = bytes;  
16.               cmd.Connection = conn;  
17.               conn.Open();  
18.   
19.               int affectedrows = cmd.ExecuteNonQuery();  
20.   
21.               cmd.Dispose();  
22.               conn.Close();  
23.           }  
24.   
25.       }  

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

 

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();  
7.   
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;  
12.   
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);  
21.   
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.             }  
27.   
28.         }  

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

 

資料庫相關檔案配置在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