SQLServer中的Image数据类型的操作
准备⼯作，在库Im_Test中建⽴⼀张表Im_Info，此表中有两个字段，分别为Pr_Id （INT），Pr_Info (IMAGE)，⽤来存储图形编号及图形信息。其语法如下：
CREATE TEALE Im_Info (
Pr_Id INT NULL ，
Pr_Info IMAGE NULL
)
第⼀步: 往表中插⼊⼀条记录，并初始化PR_INFO字段。其语法如下：
INSERT INTO Im_Info VALUES （1 ，0xFFFFFFFF）
第⼆步往表中写⼊图形信息。其语法如下：
DECLARE @@ptrval varbinary(16)
SELECT @@ptrval = TEXTPTR(Pr_Info)
FROM Im_Info
WHERE Pr_Id = 1
WRITETEXT Im_Text.Im_Info
@@ptrval 0x624fd543fd…..
其中0x624fd543fd….. 为图形的⼗六进制数据，可以通过C 、Java等⼯具获得。
注意在写⼊图形信息前必须先将此数据库的 'select into/bulkcopy' 属性设置为 True ，其语法如下：
use master
exec sp_dboption Im_Test ,'select into/bulkcopy' ,True
C#读取Image数据类型：
（1）控制台应⽤程序下演⽰插⼊图⽚
public void InsertIMG()
{
//将需要存储的图⽚读取为数据流
FileStream fs = new FileStream(@"E:\c.jpg", FileMode.Open,FileAccess.Read);
Byte[] btye2 = new byte[fs.Length];
fs.Read(btye2 , 0, Convert.ToInt32(fs.Length));
fs.Close();
using (SqlConnection conn = new SqlConnection(sqlconnstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into T_Img(imgfile) values(@imgfile)";
SqlParameter par = new SqlParameter("@imgfile", SqlDbType.Image);
par.Value = bt;
cmd.Parameters.Add(par);
int t=(int)(cmd.ExecuteNonQuery());
if (t > 0)
{
Console.WriteLine("插⼊成功");
}
conn.Close();
}
}
（2）控制台应⽤程序下读出并⽣成图⽚到物理位置
public void Read()
{
byte[] MyData = new byte[0];
using (SqlConnection conn = new SqlConnection(sqlconnstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from T_img";
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
MyData = (byte[])sdr["ImgFile"];//读取第⼀个图⽚的位流
int ArraySize= MyData.GetUpperBound(0);//获得数据库中存储的位流数组的维度上限，⽤作读取流的上限
FileStream fs = new FileStream(@"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 = new byte[0];
using (SqlConnection conn = new SqlConnection(sqlconnstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from T_img";
java连接sqlserver数据库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
public void Read()
{
byte[] MyData = new byte[0];
using (SqlConnection conn = new SqlConnection(sqlconnstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select * from T_img";
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
MyData = (byte[])sdr["ImgFile"];
MemoryStream mystream = new MemoryStream(MyData);
//⽤指定的数据流来创建⼀个image图⽚
System.Drawing.Image img = System.Drawing.Image.FromStream(mystream, true);
System.Windows.Forms.PictureBox picbox = new PictureBox();
picbox.Image = img;
picbox.Left = 30;
picbox.Top = 80;
picbox.Width = 800;
picbox.Height = 500;
this.Controls.Add(picbox);
mystream.Close();
conn.Close();
}
}
⽅法⼆：将流直接读取成图⽚并写⼊到物理位置，然后再⾏利⽤该图⽚呈现
void Read()
{
using (SqlConnection conn = new SqlConnection(sqlconnstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
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;
}
int filelength = Image_img.Length;
string imageName = "1.jpg";
string myUrl = Environment.CurrentDirectory + "\\" + imageName;
FileStream fs = new FileStream(myUrl, FileMode.OpenOrCreate,FileAccess.Write);                BinaryWriter BW = new BinaryWriter(fs);
BW.BaseStream.Write(Image_img, 0, filelength);
BW.Flush();
BW.Close();
System.Windows.Forms.PictureBox picbox = new PictureBox();
//为picbox添加图⽚⽅法⼀
//picbox.ImageLocation = myUrl;
//picbox.Width = 800;
//picbox.Height = 300;
//为picbox添加图⽚⽅法⼆
Bitmap bitmap = new Bitmap(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();
}
}