stuck while uploading image file in sql server though vb.net

Question

Reposting the same question because after posting, could not edit the question. please read the bottom especially, if u read this question before -

ok so this is the code i used from this site. everything works fine, but i need a little different code to upload image, and i dont know what to do - here's the code -

Private Sub btnAttach_Click(ByVal sender As System.Object, _  
ByVal e As System.EventArgs) Handles btnAttach.Click  
    Dim iLength As Integer = CType(File1.PostedFile.InputStream.Length, Integer)  
    If iLength = 0 Then Exit Sub 'not a valid file  
    Dim sContentType As String = File1.PostedFile.ContentType  
    Dim sFileName As String, i As Integer  
    Dim bytContent As Byte()  
    ReDim bytContent(iLength) 'byte array, set to file size  

'strip the path off the filename  '
i = InStrRev(File1.PostedFile.FileName.Trim, "\")  
If i = 0 Then  
    sFileName = File1.PostedFile.FileName.Trim  
Else  
    sFileName = Right(File1.PostedFile.FileName.Trim, Len(File1.PostedFile.FileName.Trim) - i)  
End If  

Try  
    File1.PostedFile.InputStream.Read(bytContent, 0, iLength)  
    With cmdInsertAttachment  
        .Parameters("@FileName").Value = sFileName  
        .Parameters("@FileSize").Value = iLength  
        .Parameters("@FileData").Value = bytContent  
        .Parameters("@ContentType").Value = sContentType  
        .ExecuteNonQuery()  
    End With  
Catch ex As Exception  
    'Handle your database error here  
    dbConn.Close()  
End Try  
Response.Redirect(Request.Url.ToString) 'Refresh page 
End Sub  

everything works fine except when it comes to this part -

With cmdInsertAttachment  
    .Parameters("@FileName").Value = sFileName  
    .Parameters("@FileSize").Value = iLength  
    .Parameters("@FileData").Value = bytContent  
    .Parameters("@ContentType").Value = sContentType  
    .ExecuteNonQuery()  
End With  

I am not using with cmdinsertattachment. I am using Html - Input(File) from the HTML toolbox. the ID of the input file is ID="upldimg".

so how do i insert this into my table which is -

Column1 ID identity
Column2 Img image
Column 3 Description varchar(200).

please tell me the insert statement like -

INSERT into table1 (Img, Description) values (???, txtdescription.text)

Do i do upldimg.text in the insert statement?

Answer 1

1. Use the appropiate data types. IMAGE is deprecated, use VARBINARY(MAX) instead
2. Don't allocate byte arrays for arbitrary image sizes. Use chunks.
3. Don't block the response to wait for a database upload of an entire file.

If I would do this, I would use a IIS module to handle the chunking of the file while is being received in the HTTP request, as to avoid creating the temporary file altogether. Avery simplistic approach would look like this:

create table Uploads (
  Id int identity(1,1) primary key
  , FileName varchar(256)
  , ContentType varchar(256)
  , FileData varbinary(max)); 

and the aspx:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack && FileUpload1.HasFile)
        {
            ThreadPool.QueueUserWorkItem(
                new WaitCallback(TransferToDatabase), FileUpload1);
        }
    }

    protected void TransferToDatabase(object args)
    {
        try
        {
            Debug.Assert(args is FileUpload);
            FileUpload upload = (FileUpload)args;
            using (SqlConnection conn = new SqlConnection(
                Settings.Default.connString))
            {
                conn.Open();
                using (SqlTransaction trn = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand(@"
INSERT INTO Uploads(FileName, ContentType, FileData)
VALUES (@FileName, @ContentType, @initialChunk);
SET @id = SCOPE_IDENTITY();", conn, trn);
                    cmd.Parameters.AddWithValue("@FileName", upload.PostedFile.FileName);
                    cmd.Parameters.AddWithValue("@contentType", upload.PostedFile.ContentType);
                    SqlParameter paramId = new SqlParameter("@id", SqlDbType.Int);
                    paramId.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paramId);

                    byte[] chunk = new byte[4096];
                    int offset = upload.FileContent.Read(chunk, 0, 4096);
                    byte[] initialChunk = chunk;
                    if (offset < 4096)
                    {
                        // can't pass only part of a byte[] as parameter value
                        // must copy out the appropiate size
                        initialChunk = new byte[offset];
                        Array.Copy(chunk, initialChunk, offset);
                    }
                    cmd.Parameters.AddWithValue("@initialChunk", initialChunk);
                    cmd.ExecuteNonQuery();

                    // Add the rest of the data
                    if (offset == 4096)
                    {
                        SqlParameter paramChunk = new SqlParameter("@chunk", SqlDbType.VarBinary, 4096);
                        SqlParameter paramLength = new SqlParameter("@length", SqlDbType.BigInt);
                        SqlCommand cmdAddChunk = new SqlCommand(@"
UPDATE Uploads
   SET FileData.Write(@chunk, NULL, @length)
WHERE id = @id", conn, trn);
                        cmdAddChunk.Parameters.AddWithValue("@id", paramId.Value);
                        cmdAddChunk.Parameters.Add(paramChunk);
                        cmdAddChunk.Parameters.Add(paramLength);

                        do
                        {
                            int chunkSize = upload.FileContent.Read(chunk, 0, 4096);
                            if (0 == chunkSize)
                            {
                                break;
                            }
                            paramChunk.Value = chunk;
                            paramLength.Value = chunkSize;

                            cmdAddChunk.ExecuteNonQuery();

                            offset += chunkSize;

                        } while (true);
                    }

                    trn.Commit();
                }
            }
        }
        catch (Exception e)
        {
            // Log to the appropiate error logging infrastructure
            Debug.Write(e);
        }
    }