由于工作原因,需要向Sql Server数据库中插入二进制数据,例如音频文件图像文件.再网上找了下大概就是说使用Image类型,开始我尝试着使用Binary和VarBinary类型,结果只能上传8K的文件,走了很多弯路参照了很多代码终于发现使用Image类型就OK了,虽然Image的长度限制是16,但是据现在的测试上传10M的文件还是没问题的.往数据库里面写文件会大大增加数据库的体积,不过没办法,工作需要麻,又不是我自己要设计这样的东西- -#,上传方式我实现了同步和异步上传,两者的区别麻,好像异步要快一点,但两者都有界面卡住的问题.下载用SqlDataAdapte去做的,没有同步异步的区别,至于ADO.NET不太熟悉,没有用过,都是临时到网上找的资料,究竟如何搭配才能高效还没研究
直接给代码好了,下面是数据库结构
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[UpDown]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop table [ dbo ] . [ UpDown ]
GO
CREATE TABLE [ dbo ] . [ UpDown ] (
[ f_id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ f_content ] [ image ] NOT NULL ,
[ f_name ] [ char ] ( 256 ) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
drop table [ dbo ] . [ UpDown ]
GO
CREATE TABLE [ dbo ] . [ UpDown ] (
[ f_id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ f_content ] [ image ] NOT NULL ,
[ f_name ] [ char ] ( 256 ) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
这个是上传主类的结构视图 sqlCmd是一个sqlCommand的全局对象 sqlConn是sqlConnection的全局对象
BeginconnString属性返回的是异步调用时候的连接字串,connString则是同步时的
方法里面就有同步上传,异步上传,同步下载,没有异步下载,因为SqlDataAdapte不支持
下面是文件代码
Imports
System.Data.SqlClient
Imports System.IO
Public Class DBClass Class DBClass
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim _connStr As String
''' <summary>
''' 构造函数
''' </summary>
''' <remarks></remarks>
Public Sub New()Sub New(ByVal server As String, ByVal uin As String, ByVal pass As String, ByVal DataBase As String)
_connStr = "server=" + server + ";uid=" + uin + ";pwd=" + pass + ";database=" + DataBase
sqlConn = New SqlConnection(_connStr)
End Sub
''' <summary>
''' 连接字串
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
Public ReadOnly Property connString()Property connString() As String
Get
Return _connStr
End Get
End Property
Public ReadOnly Property BeginconnString()Property BeginconnString() As String
Get
Return _connStr + ";Asynchronous Processing=true"
End Get
End Property
''' <summary>
''' 上传文件
''' </summary>
''' <param name="filename">本地文件名</param>
''' <returns>bool</returns>
''' <remarks>同步上传</remarks>
Public Function UpLoadFile()Function UpLoadFile(ByVal filename As String) As Boolean
Dim fi As FileInfo = New FileInfo(filename)
If fi.Exists = False Then
Return False '直接返回失败
End If
Dim bData() As Byte
Using fs As FileStream = fi.OpenRead()
bData = New Byte(fi.Length - 1) {}
fs.Read(bData, 0, fi.Length)
End Using
'操作数据库
Dim strQuery As String = "INSERT INTO UpDown(f_content,f_name) VALUES (@FileData,@FileName)"
sqlConn.ConnectionString = connString
sqlCmd = New SqlCommand(strQuery, sqlConn)
sqlCmd.Parameters.AddWithValue("@FileName", fi.Name)
sqlCmd.Parameters.AddWithValue("@FileData", bData)
Try
sqlConn.Open()
Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
sqlReader.Close()
Catch ex As Exception
Return False
Finally
sqlCmd.Dispose()
sqlConn.Close() '关闭连接
End Try
'返回结果
Return True
End Function
''' <summary>
''' 异步上传
''' </summary>
''' <param name="filename"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function BeginUpLoadFile()Function BeginUpLoadFile(ByVal filename As String) As Boolean
Dim fi As FileInfo = New FileInfo(filename)
If fi.Exists = False Then
Return False '直接返回失败
End If
Dim bData() As Byte
Using fs As FileStream = fi.OpenRead()
bData = New Byte(fi.Length - 1) {}
fs.Read(bData, 0, fi.Length)
End Using
'操作数据库
Dim strQuery As String = "INSERT INTO UpDown(f_content,f_name) VALUES (@FileData,@FileName)"
sqlConn.ConnectionString = Me.BeginconnString
sqlCmd = New SqlCommand(strQuery, sqlConn)
sqlCmd.Parameters.AddWithValue("@FileName", fi.Name)
sqlCmd.Parameters.AddWithValue("@FileData", bData)
Try
sqlConn.Open()
Dim result As IAsyncResult = sqlCmd.BeginExecuteReader()
Dim count As Integer
While Not result.IsCompleted
count = count + 1
Threading.Thread.Sleep(200)
End While
Dim sqlReader As SqlDataReader = sqlCmd.EndExecuteReader(result)
While sqlReader.Read()
End While
sqlReader.Close()
Catch ex As Exception
Return False
Finally
sqlCmd.Dispose()
sqlConn.Close()
End Try
'返回结果
Return True
End Function
''' <summary>
''' 下载文件到本地
''' </summary>
''' <param name="ID">ID编号</param>
''' <param name="OutFile">本地保存路径</param>
''' <returns>bool</returns>
''' <remarks></remarks>
Public Function DownLoadFileByID()Function DownLoadFileByID(ByVal ID As Integer, ByVal OutFile As String) As Boolean
Dim strQuery As String = "SELECT f_content,f_name FROM UpDown WHERE f_id = '" + ID.ToString() + "'"
sqlConn.ConnectionString = Me.connString
Dim sqldaPter As SqlDataAdapter = New SqlDataAdapter(strQuery, sqlConn)
Dim sqlRecordSet As DataSet = New DataSet()
Dim bData() As Byte
Try
'从服务器获取文件
sqldaPter.Fill(sqlRecordSet, "UpDown")
Dim dr As DataRow
For Each dr In sqlRecordSet.Tables("UpDown").Rows
If (Not dr("f_content") Is DBNull.Value) Then
bData = DirectCast(dr("f_content"), Byte())
End If
Next
Catch ex As Exception
Return False
Finally
sqldaPter.Dispose()
sqlRecordSet.Dispose()
End Try
'存文件
If (Not bData Is Nothing) Then
Dim fi As FileInfo = New FileInfo(OutFile)
'不存在
If Not fi.Exists Then
Using fs As FileStream = fi.Create()
fs.Write(bData, 0, bData.Length)
End Using
Else
Using fs As FileStream = fi.OpenWrite()
fs.Write(bData, 0, bData.Length)
End Using
End If
End If
Return True
End Function
Public Function BeginDownLoadFileByID()Function BeginDownLoadFileByID(ByVal ID As Integer, ByVal OutFile As String) As Boolean
Return False
End Function
End Class
Imports System.IO
Public Class DBClass Class DBClass
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim _connStr As String
''' <summary>
''' 构造函数
''' </summary>
''' <remarks></remarks>
Public Sub New()Sub New(ByVal server As String, ByVal uin As String, ByVal pass As String, ByVal DataBase As String)
_connStr = "server=" + server + ";uid=" + uin + ";pwd=" + pass + ";database=" + DataBase
sqlConn = New SqlConnection(_connStr)
End Sub
''' <summary>
''' 连接字串
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
Public ReadOnly Property connString()Property connString() As String
Get
Return _connStr
End Get
End Property
Public ReadOnly Property BeginconnString()Property BeginconnString() As String
Get
Return _connStr + ";Asynchronous Processing=true"
End Get
End Property
''' <summary>
''' 上传文件
''' </summary>
''' <param name="filename">本地文件名</param>
''' <returns>bool</returns>
''' <remarks>同步上传</remarks>
Public Function UpLoadFile()Function UpLoadFile(ByVal filename As String) As Boolean
Dim fi As FileInfo = New FileInfo(filename)
If fi.Exists = False Then
Return False '直接返回失败
End If
Dim bData() As Byte
Using fs As FileStream = fi.OpenRead()
bData = New Byte(fi.Length - 1) {}
fs.Read(bData, 0, fi.Length)
End Using
'操作数据库
Dim strQuery As String = "INSERT INTO UpDown(f_content,f_name) VALUES (@FileData,@FileName)"
sqlConn.ConnectionString = connString
sqlCmd = New SqlCommand(strQuery, sqlConn)
sqlCmd.Parameters.AddWithValue("@FileName", fi.Name)
sqlCmd.Parameters.AddWithValue("@FileData", bData)
Try
sqlConn.Open()
Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
sqlReader.Close()
Catch ex As Exception
Return False
Finally
sqlCmd.Dispose()
sqlConn.Close() '关闭连接
End Try
'返回结果
Return True
End Function
''' <summary>
''' 异步上传
''' </summary>
''' <param name="filename"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function BeginUpLoadFile()Function BeginUpLoadFile(ByVal filename As String) As Boolean
Dim fi As FileInfo = New FileInfo(filename)
If fi.Exists = False Then
Return False '直接返回失败
End If
Dim bData() As Byte
Using fs As FileStream = fi.OpenRead()
bData = New Byte(fi.Length - 1) {}
fs.Read(bData, 0, fi.Length)
End Using
'操作数据库
Dim strQuery As String = "INSERT INTO UpDown(f_content,f_name) VALUES (@FileData,@FileName)"
sqlConn.ConnectionString = Me.BeginconnString
sqlCmd = New SqlCommand(strQuery, sqlConn)
sqlCmd.Parameters.AddWithValue("@FileName", fi.Name)
sqlCmd.Parameters.AddWithValue("@FileData", bData)
Try
sqlConn.Open()
Dim result As IAsyncResult = sqlCmd.BeginExecuteReader()
Dim count As Integer
While Not result.IsCompleted
count = count + 1
Threading.Thread.Sleep(200)
End While
Dim sqlReader As SqlDataReader = sqlCmd.EndExecuteReader(result)
While sqlReader.Read()
End While
sqlReader.Close()
Catch ex As Exception
Return False
Finally
sqlCmd.Dispose()
sqlConn.Close()
End Try
'返回结果
Return True
End Function
''' <summary>
''' 下载文件到本地
''' </summary>
''' <param name="ID">ID编号</param>
''' <param name="OutFile">本地保存路径</param>
''' <returns>bool</returns>
''' <remarks></remarks>
Public Function DownLoadFileByID()Function DownLoadFileByID(ByVal ID As Integer, ByVal OutFile As String) As Boolean
Dim strQuery As String = "SELECT f_content,f_name FROM UpDown WHERE f_id = '" + ID.ToString() + "'"
sqlConn.ConnectionString = Me.connString
Dim sqldaPter As SqlDataAdapter = New SqlDataAdapter(strQuery, sqlConn)
Dim sqlRecordSet As DataSet = New DataSet()
Dim bData() As Byte
Try
'从服务器获取文件
sqldaPter.Fill(sqlRecordSet, "UpDown")
Dim dr As DataRow
For Each dr In sqlRecordSet.Tables("UpDown").Rows
If (Not dr("f_content") Is DBNull.Value) Then
bData = DirectCast(dr("f_content"), Byte())
End If
Next
Catch ex As Exception
Return False
Finally
sqldaPter.Dispose()
sqlRecordSet.Dispose()
End Try
'存文件
If (Not bData Is Nothing) Then
Dim fi As FileInfo = New FileInfo(OutFile)
'不存在
If Not fi.Exists Then
Using fs As FileStream = fi.Create()
fs.Write(bData, 0, bData.Length)
End Using
Else
Using fs As FileStream = fi.OpenWrite()
fs.Write(bData, 0, bData.Length)
End Using
End If
End If
Return True
End Function
Public Function BeginDownLoadFileByID()Function BeginDownLoadFileByID(ByVal ID As Integer, ByVal OutFile As String) As Boolean
Return False
End Function
End Class
具体调用只要实例化DBClass对象后调用相应方法即可,这里不在给出