求一个sqlserver数据库存储过程
我要向数据库(2000,2005,2008)表中存储二进制文件,这个表如下
GO
CREATE TABLE [dbo].[附件表](
[ID] [nvarchar](50) NULL,
[文件名称] [nvarchar](50) NULL,
[文件扩展名] [nvarchar](15) NULL,
[内容] [image] NULL,
[上传日期] [smalldatetime] NULL,
[上传人] [nvarchar](50) NULL,
[说明] [nvarchar](100) NULL,
[工地名称] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
我采用流方式,太慢,等的要崩溃,没有写过存储过程,求保存该记录的存储过程,提高效率。
非常感谢!
下面是我采用的方式,太慢,一个1M的文件保存要花去1分多钟
Public Function SavePictureToDB(MyFile As String, MyMSF As MSFlexGrid, Myrow As Long) As Boolean
'将图片存入数据库
Dim rs As New ADODB.Recordset
Dim i As Integer
SavePictureToDB = False
On Error GoTo EH
Set stm = New ADODB.Stream
rs.Open "select ID,文件名称,文件扩展名,内容,上传日期,上传人,工地名称 from 附件表 ", ServerCn, adOpenKeyset, adLockOptimistic
With stm
.type = adTypeBinary
.Open
.LoadFromFile MyFile
End With
'序号|^编号|^文件名称|^后缀名|^上传日期|^上传人"
With rs
.AddNew
.Fields("ID") = Trim(MyMSF.TextMatrix(Myrow, 1))
.Fields("文件名称") = Trim(MyMSF.TextMatrix(Myrow, 2))
.Fields("文件扩展名") = Trim(MyMSF.TextMatrix(Myrow, 3))
.Fields("内容") = stm.Read
.Fields("上传日期") = Format(MyMSF.TextMatrix(Myrow, 4), "yyyy-mm-dd")
.Fields("上传人") = Trim(MyMSF.TextMatrix(Myrow, 5))
.Fields("工地名称") = Trim(MyMSF.TextMatrix(Myrow, 6))
DoEvents
.Update
'
End With
DoEvents
rs.Close
Set rs = Nothing
Set stm = Nothing
SavePictureToDB = True
Exit Function
EH: MsgBox err.Description, vbInformation, "Error"
End Function