How Can I Save Big File To NVARCHAR(Max) Column In The Database?

helo..
I want to save file it size about 200MB to the database and after that

I want to get it from the database, the colmun type that i want to

save the file to it is NVARCHAR(Max).

I am using MS sql server 2005.

I was using this code to do this with Image Or Ntext column type but

it dose not working with NVARCHAR(Max) column?





Function SaveFileToDB(ByVal FileName As String, ByVal TblName As String, ByVal FldName As String, ByVal ColumnIDName As String, ByVal RowID As String) As Boolean

Try

Dim addEmp As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT @Pointer = TEXTPTR(" & FldName & ") FROM " & TblName & " WHERE " & ColumnIDName & " = '" & RowID & "'", MyConnection)

Dim trParm As SqlClient.SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)

trParm.Direction = ParameterDirection.Output

addEmp.ExecuteNonQuery()

'''''''''''''''''''''''''''''''''''''

Dim bufferLen As Integer = 1048576

Dim appendToPhoto As SqlClient.SqlCommand = New SqlClient.SqlCommand("UPDATETEXT " & TblName & "." & FldName & " @Pointer @Offset 0 @Bytes", MyConnection)

Dim ptrParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)

ptrParm.Value = trParm.Value

Dim photoParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)

Dim offsetParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)

offsetParm.Value = 0

Dim fs As IO.FileStream = New IO.FileStream(FileName, IO.FileMode.Open, IO.FileAccess.Read)

Dim br As IO.BinaryReader = New IO.BinaryReader(fs)

Dim buffer() As Byte = br.ReadBytes(bufferLen)

Dim offset_ctr As Long = 0

Do While buffer.Length > 0

photoParm.Value = buffer

appendToPhoto.ExecuteNonQuery()

offset_ctr += bufferLen

offsetParm.Value = offset_ctr

buffer = br.ReadBytes(bufferLen)

My.Application.DoEvents()

Loop

br.Close()

fs.Close()

Return True

Catch ex As Exception

MyErrStr = ex.Message

Return False

End Try

End Function





Public Function ReadFileFromDB(ByVal MyCommandText As String, ByVal FileColumnNumber As Integer, ByVal DSTFileName As String) As Boolean

Try

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(MyCommandText, MyConnection)

Dim stream As IO.FileStream

Dim writer As IO.BinaryWriter

Dim bufferSize As Integer = 1048576

Dim outByte(bufferSize - 1) As Byte

Dim retval As Long

Dim startIndex As Long = 0

Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()

reader.Read()

stream = New IO.FileStream(DSTFileName, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)

writer = New IO.BinaryWriter(stream)

startIndex = 0

retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)

Do While retval = bufferSize

writer.Write(outByte)

writer.Flush()

startIndex += bufferSize

retval = reader.GetBytes(FileColumnNumber, startIndex, outByte, 0, bufferSize)

My.Application.DoEvents()

Loop

writer.Write(outByte, 0, retval - 1)

writer.Flush()

writer.Close()

stream.Close()

reader.Close()

Return True

Catch ex As Exception

MyErrStr = ex.Message

Return False

End Try

End Function