有时候我们需要保存一些binary data进数据库。SQL Server提供一个叫做image的特殊数据类型供我们保存binary data。Binary data可以是图片、文档等。
准备工作,在库Im_Test中建立一张表Im_Info,此表中有两个字段,分别为Pr_Id (INT),Pr_Info (IMAGE),用来存储图形编号及图形信息。其语法如下:
Pr_Id INT NULL ,
Pr_Info IMAGE NULL
)
第一步: 往表中插入一条记录,并初始化PR_INFO字段。其语法如下:
第二步往表中写入图形信息。其语法如下:
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 ,其语法如下:
exec sp_dboption Im_Test , ' select into/bulkcopy ' ,True
C#读取Image数据类型:
(1)控制台应用程序下演示插入图片
{
// 将需要存储的图片读取为数据流
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)控制台应用程序下读出并生成图片到物理位置
{
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 " ;
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
MyData = ( byte [])sdr[ " ImgFile " ];
Response.ContentType = " image/gif " ;
Response.BinaryWrite(MyData);
conn.Close();
Response.Write( " 读取成功 " );
}
<img src="picshow.aspx" width="500" height="300" />
(5)Winform下将图片写入到sql数据库image类型字段中的方法和以上方法基本一致,仅区别于可以利用多个对话框来帮助选取存储图片等,各个属性可以方便的利用上
(6)Winform下读取图片在picturebox控件中显示出来
方法一:利用MemoryStream 和System.Drawing.Image
{
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();
}
}
方法二:将流直接读取成图片并写入到物理位置,然后再行利用该图片呈现
{
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();
}
}
url:http://greatverve.cnblogs.com/archive/2011/07/11/mssql-image-read-write.html