如何在vb.net 2008中存储和检索（.doc / .docx / .pdf）文件到sql server数据库

问题描述：

'以下代码将doc / docx / pdf转换为二进制格式并存储到sqlserver中。

'The below code are convert the doc/docx/pdf to binary format and store into sqlserver.

Private Sub btn_up_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_up.Click
        Try
            Dim fs As FileStream

            fs = New FileStream(sfile, FileMode.Open, FileAccess.Read)

            Dim docByte As Byte() = New Byte(fs.Length - 1) {}

            fs.Read(docByte, 0, System.Convert.ToInt32(fs.Length))

            fs.Close()
            'Insert statement for sql query
            Dim sqltxt As String
            sqltxt = "insert into test values('" & TextBox1.Text & "',@fdoc)"

            'store doc as Binary value using SQLParameter
            Dim docfile As New SqlParameter
            docfile.SqlDbType = SqlDbType.Binary
            docfile.ParameterName = "fdoc"
            docfile.Value = docByte
            sqlcmd = New SqlCommand(sqltxt, con)
            sqlcmd.Parameters.Add(docfile)
            sqlcmd.ExecuteNonQuery()
            MsgBox("Data Saved Successfully")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

请他们中的任何一个使用vb.net如何从数据库中检索原始文件

[edit]已添加代码块 - OriginalGriff [/编辑]

"Pls any of them tel how to retrieve the original file from database using vb.net"

[edit]Code block added - OriginalGriff[/edit]

答

要做的第一件事是更改保存数据的方式：insert语句有两个问题。

首先，您应该明确列出要保存数据的字段 - 匿名条目依赖于您的数据库将来不会更改。

The first thing to do is to change the way you are saving the data: there are two problems with your insert statement.
The first is that you should explicitly list the fields into which you want to save the data - anonymous entry relies on your DB not changing in the future.

sqltxt = "insert into test values('" & TextBox1.Text & "',@fdoc)"

成为：

Becomes:

sqltxt = "insert into test (fileName, fileData) values('" & TextBox1.Text & "',@fdoc)"

第二个是你真的应该使用你设置的参数化查询来指定文本 - 通过字符串连接传递TextBox内容是为了意外或故意的Sql注入攻击而打开您的数据库。您将文件数据作为参数传递，那么为什么不传递TextBox内容呢？

要检索它与您用于任何其他数据库的过程完全相同访问：

The second is that you really, really, should use the parametrized query you are setting up to specify the text as well - passing the TextBox content by string concatenation is opening your database up for an accidental or deliberate Sql Injection Attack. You are passing the file data as a parameter, so why not pass the TextBox content as well?

To retrieve it is exactly the same process you use for any other database access:

Using con As New SqlConnection(strConnect)
	con.Open()
	Using com As New SqlCommand("SELECT fileName, fileData FROM test", con)
		Using reader As SqlDataReader = com.ExecuteReader()
			While reader.Read()
				Dim fileName As String = DirectCast(reader("fileName"), String)
				Dim fileData As Byte() = DirectCast(reader("fileData"), Byte())
				...
			End While
		End Using
	End Using
End Using

如何在vb.net 2008中存储和检索（.doc / .docx / .pdf）文件到sql server数据库

相关推荐