saving pdf file to sql server

[andy_ro]

Hi group,

I have an web application where the user can upload a pdf file. This file is
stored in a table, in a column of type ntext.
The user can later request the content of this column, and the application
should open a new page and load the pdf content.

My problem:
If I read the uploaded pdf file into a string variable and then I
response.write it right away to another page, it shows fine (I can see a pdf
document opened with acrobat reader).
But if I save the string in the sql database and then later retreive it from
there, it will not show anymore.
It seems like it is being somehow transformed during saving to/retreiving
from the sqlserver ...

Any ideas ?

thanks,
Andrei.

Following is the code I use. The commented block sends back the pdf info to
the browser.
Dim mf As HttpPostedFile = myfile.PostedFile

Dim strFile As String = myfile.Value

Dim nFileLen As Integer = mf.ContentLength

Dim b(nFileLen) As Byte

mf.InputStream.Read(b, 0, nFileLen)

Dim str As String = Encoding.Unicode.GetChars(b)

str = Replace(str, "'", "''")

'str = Replace(str, "''", "'")

'Dim b1() As Byte

'b1 = Encoding.Unicode.GetBytes(str)

'Response.ContentType = "application/pdf"

'Response.BinaryWrite(b1)

'Response.Flush()

'Response.Close()

'save file to DB

Dim cn As New SqlClient.SqlConnection("server=(local);initial
catalog=atest;integrated security=SSPI;")

cn.Open()

Dim cmd As New SqlClient.SqlCommand("insert into cust (name, info) values("
& _

"'" & strFile & "','" & str & "')", cn)

cmd.ExecuteNonQuery()

...........
Dim cmd As New SqlCommand("select name,info from cust where nume='" &
cboFis.SelectedValue & "'", cn)

Dim dr As SqlDataReader

dr = cmd.ExecuteReader()

Dim str As String

Dim b() As Byte

If dr.Read() Then

str = dr("info")

str = Replace(str, "''", "'")

b = Encoding.Unicode.GetBytes(str)

Response.ClearContent()

Response.ClearHeaders()

Response.ContentType = "application/pdf"

Response.BinaryWrite(b)

Response.Flush()

Response.Close()

End If

 

[Natty Gur]

Hi,

I think it is better to save the PDF file as binary data in the SQL
server instead of translating it from / to string.

Natty Gur, CTO
Dao2Com Ltd.
28th Baruch Hirsch st. Bnei-Brak
Israel , 51114

Phone Numbers:
Office: +972-(0)3-5786668
Fax: +972-(0)3-5703475
Mobile: +972-(0)58-888377

Know the overall picture

 

[PJ]

use the Image datatype in sqlserver. Put the .InputStream from the
PostedFile into a byte array. The byte array will be your parameter into
the stored proc that takes an image datatype. Careful if you happen to be
deriving parameters from the proc...as .net will not type the Image SqlType
correctly....I believe it makes it ntext or something....

~PJ

 

[andrei]

Thank you, Natty and PJ,

I used an Image field in SQLServer with a stored procedure that writes to it
and it works perfect.

Andrei.

 

[syri]

how to view the file.

Hi everyone,
i have tow questions:
1- how did you pass the pdf file to your stored procedure? i mean how to pass the image field? is the file path as string enough?
2- after you succeded in saving the file, how can we view it again?

i have one more request:
can you please post your stored procedure if it contains any new idea?

THX.