kevinya

通常是apServer做load_balance的時候，又要想要針對兩台不同的apServer做上傳檔案的同步的時候，乾脆就直接上傳檔案到SQLServer
好處壞處都有，網路上可以自己搜尋，這邊只介紹上傳以及下載都從SQLServer的做法

table欄位

*.aspx
<asp:FileUpload ID="FileUpload1" runat="server" />
 <asp:Button ID="btnUpload" runat="server" Text="上傳" />

*.aspx.vb

'示範上傳檔案到SQLServer
    Protected Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
        Dim strSQL As String = ""
        Dim cmd As New SqlClient.SqlCommand
        Dim alCmd As New ArrayList
        Dim byteArray(FileUpload1.PostedFile.ContentLength - 1) As Byte '檔案轉乘二進位資料
        FileUpload1.PostedFile.InputStream.Read(byteArray, 0, FileUpload1.PostedFile.ContentLength)

        strSQL = "insert into MyUploadTable(content_type,binary_file,file_name)"
        strSQL &= "values(@content_type,@binary_file,@file_name)" & vbCrLf

        cmd.CommandText = strSQL
        'MIME是重要的檔案資訊，檔案以二進位的方式寫入SQLServer的必備資訊，詳情請google MIME
        cmd.Parameters.Add("@content_type", SqlDbType.VarChar).Value = MimeExtensionHelper.GetMimeType(FileUpload1.FileName)
        cmd.Parameters.Add("@binary_file", SqlDbType.VarBinary).Value = byteArray
        '檔案名稱，只是方便到時候下載的時候，取甚麼檔案名稱而已
        cmd.Parameters.Add("@file_name", SqlDbType.VarChar).Value = FileUpload1.FileName
        alCmd.Add(cmd)
        raiseTransaction(alCmd)
    End Sub

'//用來取得MIME TYPE的函式
    '//如果檔案是用二進位的方式寫入到sqlserver的話
    '//寫入之前必須先取得MIME,記錄在資料庫
    '//資料來源：http://stackoverflow.com/questions/1612767/file-extensions-and-mime-types-in-net
    '並且用c#, vb converter轉換成vb
    Public NotInheritable Class MimeExtensionHelper
        Private Sub New()
        End Sub
        Shared locker As New Object()
        Shared mimeMapping As Object
        Shared getMimeMappingMethodInfo As System.Reflection.MethodInfo

        Shared Sub New()
            Dim mimeMappingType As Type = System.Reflection.Assembly.GetAssembly(GetType(HttpRuntime)).[GetType]("System.Web.MimeMapping")
            If mimeMappingType Is Nothing Then
                Throw New SystemException("Couldnt find MimeMapping type")
            End If
            getMimeMappingMethodInfo = mimeMappingType.GetMethod("GetMimeMapping", _
                    System.Reflection.BindingFlags.[Static] Or System.Reflection.BindingFlags.NonPublic Or System.Reflection.BindingFlags.[Public])
            If getMimeMappingMethodInfo Is Nothing Then
                Throw New SystemException("Couldnt find GetMimeMapping method")
            End If
            If getMimeMappingMethodInfo.ReturnType <> GetType(String) Then
                Throw New SystemException("GetMimeMapping method has invalid return type")
            End If
            If getMimeMappingMethodInfo.GetParameters().Length <> 1 AndAlso getMimeMappingMethodInfo.GetParameters()(0).ParameterType <> GetType(String) Then
                Throw New SystemException("GetMimeMapping method has invalid parameters")
            End If
        End Sub
        Public Shared Function GetMimeType(filename As String) As String
            SyncLock locker
                Return DirectCast(getMimeMappingMethodInfo.Invoke(mimeMapping, New Object() {filename}), String)
            End SyncLock
        End Function
    End Class

'示範下載檔案
    Private Sub btnDownload_Click(sender As Object, e As System.EventArgs) Handles btnDownload.Click
        Dim dt As DataTable
        Dim strSQL As String = ""
        Dim cmd As New SqlClient.SqlCommand
        Dim alCmd As New ArrayList
        strSQL = "select * from MyUploadTable"
        cmd.CommandText = strSQL
        dt = getDataTable(cmd)

        If dt.Rows.Count > 0 Then
            Dim dr As DataRow = dt.Rows(0)
            Download_BinaryFile(dr("file_name").ToString, dr("binary_file"), dr("content_type").ToString)
        End If

    End Sub

'示範下載檔案
    Private Sub btnDownload_Click(sender As Object, e As System.EventArgs) Handles btnDownload.Click
        Dim dt As DataTable
        Dim strSQL As String = ""
        Dim cmd As New SqlClient.SqlCommand
        Dim alCmd As New ArrayList
        strSQL = "select * from MyUploadTable"
        cmd.CommandText = strSQL
        dt = getDataTable(cmd)

        If dt.Rows.Count > 0 Then
            Dim dr As DataRow = dt.Rows(0)
            Download_BinaryFile(dr("file_name").ToString, dr("binary_file"), dr("content_type").ToString)
        End If

    End Sub

    '從SQLServer下載檔案
    Private Sub Download_BinaryFile(strFileName As String, byteArray As Byte(), strContentType As String)
        Dim strUrlFileName As String = HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)

        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = strContentType
        '指定下載的檔名  
        Response.AddHeader("content-disposition", "attachment;filename=" & strUrlFileName)
        Response.BinaryWrite(byteArray)
        Response.Flush()
        Response.[End]()
    End Sub