使用 ASP.NET/VB.NET 将文件上传到 SQL Server 2012

Uploading files to SQL Server 2012 with ASP.NET/VB.NET

提问by Brian

I followed a tutorial an ran the below code without any errors. The file "uploads", however no data is inserted into my SQL Server table.

我按照教程运行了下面的代码，没有任何错误。文件“上传”，但是没有数据插入到我的 SQL Server 表中。

Data should be inserted into the contenttable.

数据应插入content表中。

Content Table:

内容表：

Document.aspx

文档.aspx

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO

Partial Class Documents
    Inherits System.Web.UI.Page

    Protected Sub btnUploadContent_Click(sender As Object, e As EventArgs) Handles btnUploadContent.Click

        Dim filePath As String = FileUpload.PostedFile.FileName

        Dim filename As String = Path.GetFileName(filePath)

        Dim ext As String = Path.GetExtension(filename)

        Dim contenttype As String = String.Empty



        Select Case ext

            Case ".doc"

                contenttype = "application/vnd.ms-word"

                Exit Select

            Case ".docx"

                contenttype = "application/vnd.ms-word"

                Exit Select

            Case ".xls"

                contenttype = "application/vnd.ms-excel"

                Exit Select

            Case ".xlsx"

                contenttype = "application/vnd.ms-excel"

                Exit Select

            Case ".jpg"

                contenttype = "image/jpg"

                Exit Select

            Case ".png"

                contenttype = "image/png"

                Exit Select

            Case ".gif"

                contenttype = "image/gif"

                Exit Select

            Case ".pdf"

                contenttype = "application/pdf"

                Exit Select

        End Select

        If contenttype <> String.Empty Then

            Dim fs As Stream = FileUpload.PostedFile.InputStream

            Dim br As New BinaryReader(fs)

            Dim bytes As Byte() = br.ReadBytes(fs.Length)



            'insert the file into database

            Dim strQuery As String = "INSERT INTO content (content_name, content_type, content_file) VALUES (@Name, @ContentType, @Data)"

            Dim cmd As New SqlCommand(strQuery)

            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value() = contenttype

            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

            InsertUpdateData(cmd)

            lblMessage.ForeColor = System.Drawing.Color.Green

            lblMessage.Text = "File Uploaded Successfully"

        Else

            lblMessage.ForeColor = System.Drawing.Color.Red

            lblMessage.Text = "File format not recognised." + " Upload Image/Word/PDF/Excel formats"

        End If

    End Sub




    Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnStringDb1").ConnectionString()

        Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True;")

        cmd.CommandType = CommandType.Text

        cmd.Connection = conn

        Try

            conn.Open()

            cmd.ExecuteNonQuery()

            Return True

        Catch ex As Exception

            Response.Write(ex.Message)

            Return False

        Finally

            conn.Close()

            conn.Dispose()

        End Try

    End Function

End Class

Can anyone tell me what's going on ?

谁能告诉我这是怎么回事？

EDIT: Debug Breakpoint @ InsertUpdateData(cmd):

编辑：调试断点@ InsertUpdateData(cmd)：

        SqlDbType.Binary    Binary {1}  System.Data.SqlDbType
+       bytes   {Length=4136752}    Byte()
+       cmd {System.Data.SqlClient.SqlCommand}  System.Data.SqlClient.SqlCommand
+       cmd.Parameters  {System.Data.SqlClient.SqlParameterCollection}  System.Data.SqlClient.SqlParameterCollection

回答by Ihor Deyneka

I have created empty database and added table contentjust like you have and I used code almost the same as you and it worked fine.

我已经创建了空数据库并像您一样添加了表格内容，并且我使用的代码与您几乎相同，并且运行良好。

Again, if no exception occurs, please check your connection string and see whether the rows been added to the table in the db specified in connection string. Here is my code (which is working fine), a bit modified from yours:

同样，如果没有发生异常，请检查您的连接字符串，并查看连接字符串中指定的数据库中的行是否已添加到表中。这是我的代码（工作正常），对您的代码进行了一些修改：

Imports System.Data.SqlClient
Imports System.IO

Public Class _Default
Inherits System.Web.UI.Page

Protected Sub btnUploadContent_Click(sender As Object, e As EventArgs) Handles btnTest1.Click

    Dim fs As Stream = FileUpload.PostedFile.InputStream

    Dim br As New BinaryReader(fs)

    Dim bytes As Byte() = br.ReadBytes(fs.Length)


    'insert the file into database

    Dim strQuery As String = "INSERT INTO content (content_name, content_type, content_file) VALUES (@Name, @ContentType, @Data)"

    Dim cmd As New SqlCommand(strQuery)

    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "filename"

    cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value() = "jpg"

    cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

    InsertUpdateData(cmd)

End Sub




Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim conn As New SqlConnection("Data Source=(local);Initial Catalog=test;Integrated Security=True;")

    cmd.CommandType = CommandType.Text

    cmd.Connection = conn

    Try

        conn.Open()

        cmd.ExecuteNonQuery()

        Return True

    Catch ex As Exception

        Response.Write(ex.Message)

        Return False

    Finally

        conn.Close()

        conn.Dispose()

    End Try

End Function

End Class

I add sample of SQL to test on DB:

我添加了 SQL 示例以在 DB 上进行测试：

 INSERT INTO [master_db].[dbo].[content]
       ([content_name]
       ,[content_type]
       ,[content_file])
 VALUES
       ('test'
       ,'png'
       ,0x111111111111111)

 SELECT * FROM [master_db].[dbo].[content]

回答by JoelCool

I came across this post looking looking for an example. I used the example code you posted and had the same problem. I found and resolved the following issues and got it working:

我遇到了这篇文章，正在寻找一个例子。我使用了您发布的示例代码并遇到了同样的问题。我发现并解决了以下问题并使其正常工作：

• I created the db table as pictured. content_type of nchar(5) was the first problem since you were inserting something like "application/vnd.ms-word" which was too big.
• The next error was because I had not defined the content_id to be an identity column and since it wasn't listed in the insert statement it failed.
• Next I had an error as my db user didn't have insert privileges.
• The biggest problem is that the return message was always a success message because even though the InsertUpdateData function was catching errors it was not notifying the calling code. This made me think things were okay. doh! Using a breakpoint on the ExecuteNonQuery allowed me to see the errors.

• 我创建了如图所示的数据库表。nchar(5) 的 content_type 是第一个问题，因为您插入了太大的“application/vnd.ms-word”之类的内容。
• 下一个错误是因为我没有将 content_id 定义为标识列，并且由于它没有在插入语句中列出，所以它失败了。
• 接下来我遇到了一个错误，因为我的 db 用户没有插入权限。
• 最大的问题是返回消息始终是成功消息，因为即使 InsertUpdateData 函数捕获错误，它也不会通知调用代码。这让我觉得一切都还好。哦！在 ExecuteNonQuery 上使用断点可以让我看到错误。

Hope that helps the next person that stops by....

希望能帮助下一个路过的人......