在vb时期, 向sql server 中插入二进制数据, 是通过 adodb.stream 实现, 在.net中, 对 “流”格式的操作更加强大而简单,本篇演示向sql server 中插入数据并读出的功能.
在窗体上添加一个 openfiledialog 控件, 两个picturebox, 代码如下:
--------------------------------------------------------------------------------------------
imports system.io
public class form1
inherits system.windows.forms.form
private sub button4_click(byval sender as system.object, byval e as system.eventargs) handles button4.click
me.dispose(true)
end sub
private sub button1_click(byval sender as system.object, byval e as system.eventargs) handles button1.click
if ofdpic.showdialog = dialogresult.ok then
pbpreview.image = image.fromfile(ofdpic.filename)
end if
end sub
private sub button2_click(byval sender as system.object, byval e as system.eventargs) handles button2.click
if pbpreview.image is nothing then
msgbox("请先选择图片", msgboxstyle.exclamation)
exit sub
end if
dim fs as filestream = new filestream(ofdpic.filename, filemode.open, fileaccess.read)
dim bt(fs.length) as byte
fs.read(bt, 0, fs.length)
fs.close()
fs = nothing
dim sqlconn as sqlclient.sqlconnection = new sqlclient.sqlconnection("server=(local);user id=sa;password=123;database=pubs")
sqlconn.open()
dim sqlcmd as new sqlclient.sqlcommand("sp_insertimage", sqlconn)
sqlcmd.commandtype = commandtype.storedprocedure
sqlcmd.parameters.add("@img", sqldbtype.image).value = bt
sqlcmd.executenonquery()
sqlcmd = nothing
sqlconn.close()
sqlconn = nothing
msgbox("图片插入成功", msgboxstyle.information)
end sub
private sub button3_click(byval sender as system.object, byval e as system.eventargs) handles button3.click
dim sqlconn as sqlclient.sqlconnection = new sqlclient.sqlconnection("server=(local);user id=sa;password=123;database=pubs")
sqlconn.open()
dim sqlcmd as new sqlclient.sqlcommand("select img from test where t_id=3", sqlconn)
sqlcmd.commandtype = commandtype.text
dim bt() as byte = sqlcmd.executescalar()
if not bt is nothing then
if bt.length > 0 then
dim fs as memorystream = new memorystream(bt)
pbreview.image = image.fromstream(fs)
'fs.close
'fs = nothing
'可以自己试着将上面两句的注释去掉, 看有什么效果 ^_^
else
msgbox("无图片")
end if
else
msgbox("无数据")
end if
sqlcmd = nothing
sqlconn.close()
sqlconn = nothing
end sub
end class
-----------------------------------------------------------------------------
数据库部分:
----------------
use pubs
go
create table test(t_id int identity(1,1), img image)
go
create procedure sp_insertimage
@img image
as
insert into test (img) values (@img)
go
