有時候我們需要保存一些binary data進數據庫。SQL Server提供一個叫做image的特殊數據類型供我們保存binary data。Binary data可以是圖片、文檔等。

準備工作，在庫Im_Test中建立一張表Im_Info，此表中有兩個字段，分別為Pr_Id （INT），Pr_Info (IMAGE)，用來存儲圖形編號及圖形信息。其語法如下：?

CREATETEALE?Im_Info?(?
Pr_Id?INTNULL，?
Pr_Info?IMAGENULL
)

第一步: 往表中插入一條記錄，并初始化PR_INFO字段。其語法如下：?

INSERTINTOIm_Info?VALUES（1，0xFFFFFFFF）

第二步往表中寫入圖形信息。其語法如下：?

DECLARE@@ptrvalvarbinary(16)?
SELECT@@ptrval=TEXTPTR(Pr_Info)?
FROMIm_Info?
WHEREPr_Id?=1
WRITETEXTIm_Text.Im_Info?
@@ptrval0x624fd543fd…..

其中0x624fd543fd….. 為圖形的十六進制數據，可以通過C 、Java等工具獲得。

注意在寫入圖形信息前必須先將此數據庫的 'select into/bulkcopy' 屬性設置為 True ，其語法如下：?

usemaster?
execsp_dboption?Im_Test?,'select?into/bulkcopy',True

C#讀取Image數據類型：

（1）控制臺應用程序下演示插入圖片

publicvoidInsertIMG()
????????{
????????????//將需要存儲的圖片讀取為數據流
FileStream?fs?=newFileStream(@"E:\c.jpg",?FileMode.Open,FileAccess.Read);
????????????Byte[]?btye2?=newbyte[fs.Length];
????????????fs.Read(btye2?,?0,?Convert.ToInt32(fs.Length));
????????????fs.Close();
????????????
????????????using(SqlConnection?conn?=newSqlConnection(sqlconnstr))
????????????{
????????????????conn.Open();
????????????????SqlCommand?cmd?=newSqlCommand();
????????????????cmd.Connection?=conn;
????????????????cmd.CommandText?="insert?into?T_Img(imgfile)?values(@imgfile)";
????????????????SqlParameter?par?=newSqlParameter("@imgfile",?SqlDbType.Image);
????????????????par.Value?=bt;
????????????????cmd.Parameters.Add(par);
????????????????intt=(int)(cmd.ExecuteNonQuery());
????????????????if(t?>0)
????????????????{
????????????????????Console.WriteLine("插入成功");
????????????????}
????????????????conn.Close();
????????????}
????????}

（2）控制臺應用程序下讀出并生成圖片到物理位置

publicvoidRead()
????????{
????????????byte[]?MyData?=newbyte[0];
????????????using(SqlConnection?conn?=newSqlConnection(sqlconnstr))
????????????{
????????????????conn.Open();
????????????????SqlCommand?cmd?=newSqlCommand();
????????????????cmd.Connection?=conn;
????????????????cmd.CommandText?="select?*?from?T_img";
????????????????SqlDataReader?sdr?=cmd.ExecuteReader();
????????????????sdr.Read();
????????????????MyData?=(byte[])sdr["ImgFile"];//讀取第一個圖片的位流
intArraySize=MyData.GetUpperBound(0);//獲得數據庫中存儲的位流數組的維度上限，用作讀取流的上限
FileStream?fs?=newFileStream(@"c:\00.jpg",?FileMode.OpenOrCreate,?FileAccess.Write);
????????????????fs.Write(MyData,?0,?ArraySize);
????????????????fs.Close();???//--?寫入到c:\00.jpg。
conn.Close();
????????????????Console.WriteLine("讀取成功");//查看硬盤上的文件
}
????????}

（3）Web下picshow.aspx頁將圖片讀取出來并寫入到瀏覽器上呈現

?? ?public?void?Read()

{
????????byte[]?MyData?=newbyte[0];
????????using(SqlConnection?conn?=newSqlConnection(sqlconnstr))
????????{
????????????conn.Open();
????????????SqlCommand?cmd?=newSqlCommand();
????????????cmd.Connection?=conn;
????????????cmd.CommandText?="select?*?from?T_img";
????????????SqlDataReader?sdr?=cmd.ExecuteReader();
????????????sdr.Read();
????????????MyData?=(byte[])sdr["ImgFile"];
????????????Response.ContentType?="image/gif";
????????????Response.BinaryWrite(MyData);
????????????conn.Close();
????????????Response.Write("讀取成功");
????????} （4）在web中可以如上picshow.aspx頁面讀取并顯示圖片，而真正引用該圖片時如下示例

<img src="picshow.aspx" width="500" height="300" />
?（5）Winform下將圖片寫入到sql數據庫image類型字段中的方法和以上方法基本一致，僅區別于可以利用多個對話框來幫助選取存儲圖片等，各個屬性可以方便的利用上

（6）Winform下讀取圖片在picturebox控件中顯示出來

方法一：利用MemoryStream 和System.Drawing.Image

publicvoidRead()
????????{
????????????byte[]?MyData?=newbyte[0];
????????????using(SqlConnection?conn?=newSqlConnection(sqlconnstr))
????????????{
????????????????conn.Open();
????????????????SqlCommand?cmd?=newSqlCommand();
????????????????cmd.Connection?=conn;
????????????????cmd.CommandText?="select?*?from?T_img";
????????????????SqlDataReader?sdr?=cmd.ExecuteReader();
????????????????sdr.Read();
????????????????MyData?=(byte[])sdr["ImgFile"];
????????????????MemoryStream?mystream?=newMemoryStream(MyData);
????????????????//用指定的數據流來創建一個image圖片
System.Drawing.Image?img?=System.Drawing.Image.FromStream(mystream,?true);
????????????????
????????????????System.Windows.Forms.PictureBox?picbox?=newPictureBox();
????????????????picbox.Image?=img;
????????????????picbox.Left?=30;
????????????????picbox.Top?=80;
????????????????picbox.Width?=800;
????????????????picbox.Height?=500;
????????????????this.Controls.Add(picbox);
????????????????mystream.Close();
????????????????conn.Close();
????????????}
????????}

?方法二：將流直接讀取成圖片并寫入到物理位置，然后再行利用該圖片呈現

voidRead()
????????{
????????????using(SqlConnection?conn?=newSqlConnection(sqlconnstr))
????????????{
????????????????conn.Open();
????????????????SqlCommand?cmd?=newSqlCommand();
????????????????cmd.Connection?=conn;
????????????????cmd.CommandText?="select?*?from?T_img";
????????????????SqlDataReader?sdr?=cmd.ExecuteReader();
????????????????sdr.Read();
????????????????byte[]?Image_img?=(byte[])sdr["ImgFile"];
????????????????if(Image_img.Length?==0)
????????????????{
????????????????????return;
????????????????}
????????????????intfilelength?=Image_img.Length;
????????????????stringimageName?="1.jpg";
????????????????stringmyUrl?=Environment.CurrentDirectory?+"\\"+imageName;
????????????????FileStream?fs?=newFileStream(myUrl,?FileMode.OpenOrCreate,FileAccess.Write);
????????????????BinaryWriter?BW?=newBinaryWriter(fs);
????????????????BW.BaseStream.Write(Image_img,?0,?filelength);
????????????????BW.Flush();
????????????????BW.Close();
????????????????System.Windows.Forms.PictureBox?picbox?=newPictureBox();
????????????????
????????????????//為picbox添加圖片方法一
????????????????//picbox.ImageLocation?=?myUrl;
????????????????//picbox.Width?=?800;
????????????????//picbox.Height?=?300;
????????????????//為picbox添加圖片方法二
Bitmap?bitmap?=newBitmap(myUrl);
????????????????picbox.Width?=100;//bitmap.Width;
picbox.Height?=80;//bitmap.Height;
picbox.Image?=(Image)bitmap;
????????????????picbox.SizeMode?=System.Windows.Forms.PictureBoxSizeMode.StretchImage;
????????????????picbox.Left?=20;
????????????????picbox.Top?=30;
????????????????this.Controls.Add(picbox);
????????????????conn.Close();
????????????????
????????????}
????????}

總結

以上是生活随笔為你收集整理的C#+SQL Server图片存取的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。