Uploading files in varbinary(MAX)

Question

I have a database table with two field of type varbinary(max), which i use to store files.

However, I am unable to upload the files as expected. I've been stumped for this problem for a long time and I'm not sure what I can do to resolve it. There is an error message:

An exception of type 'System.Data.SqlClient.SqlException' occurred in
System.Data.dll but was not handled in user code
Additional information: Incorrect syntax near the keyword 'title'.

Its a must for me to implement 3-tier architecture in ASP.NET.

Data Access Layer

public class Submission { 

  private string _title;
  private byte[] _slides, _codes;

  //Connection string 
  private string _connStr = Properties.Settings.Default.DBConnStr;

  public Submission(string title, byte[] slides, byte[] codes) {
  _title = title;
  _slides = slides;
  _codes = codes;
  }

  //UPLOAD files
  public int SubmissionInsert()
  {
          string queryStr = "INSERT INTO Submission(title,slides,codes)" +
              "VALUES('" +
              _title + "', '" +
              _slides + "', '" +
              _codes + "')";

          SqlConnection con = new SqlConnection(_connStr);
          SqlCommand cmd = new SqlCommand(queryStr, con);

          con.Open();
          int nofRow = 0;
          nofRow = cmd.ExecuteNonQuery();

          con.Close();

          return nofRow;
  }
}

Business Logic Layer

public class SubmissionBLL
{
    public string submissionUpload(string title, byte[] slides, byte[] codes)
    {
        string returnValue = "";

        if (title.Length == 0)
            returnValue+= "Title cannot be empty";
        if (slides == null)
            returnValue += "Slides cannot be empty";
        if (codes == null)
            returnValue += "Codes cannot be empty";

        //if there are no errors
        if (returnValue.Length == 0)
        {
            Submission sub = new Submission(title,slides,codes);

            int nofRows = 0;
            nofRows = sub.SubmissionInsert();

            if (nofRows > 0)
                returnValue = "Submission is successful!";
            else
                returnValue = "Submission failure. Please try again.";
        }

        return returnValue;
  }

Presentation Layer - Code-behind

 protected void btn_submit_Click(object sender, EventArgs e)
    {
        string input = "";
        byte[] slideArr = null, codeArr= null;

        string strTestFilePath, strTestFileName, strContentType;
        Int32 intFileSize, intFileLength;
        Stream strmStream;

        if (f_codes.HasFile)
        {
                strTestFilePath = f_codes.PostedFile.FileName;
                strTestFileName = Path.GetFileName(strTestFilePath);
                intFileSize = f_codes.PostedFile.ContentLength;
                strContentType = f_codes.PostedFile.ContentType;

                //Convert the source codes file to byte stream to save to database
                strmStream = f_codes.PostedFile.InputStream;
                intFileLength = (Int32)strmStream.Length;
                codeArr = new byte[intFileLength + 1];
                strmStream.Read(codeArr, 0, intFileLength);
                strmStream.Close();

        }

         if (f_slide.HasFile)
        {
                strTestFilePath = f_slide.PostedFile.FileName;
                strTestFileName = Path.GetFileName(strTestFilePath);
                intFileSize = f_slide.PostedFile.ContentLength;
                strContentType = f_slide.PostedFile.ContentType;

                strmStream = f_slide.PostedFile.InputStream;
                intFileLength = (Int32)strmStream.Length;
                slideArr = new byte[intFileLength + 1];
                strmStream.Read(slideArr, 0, intFileLength);
                strmStream.Close();
            }

         //Pass to BLL
         input = sub.submissionUpload(tb_title.Text,slideArr,codeArr);
         //Display error messages
         lbl_message.Text = input;
    }

I tried to debug with IntelliTrace and it shows a message

ADO.NET:Execute NonQuery "INSERT INTO Submission(title,slides,codes)VALUES( 'My Water Saving Project', 'System.Byte[]','System.Byte[]')"

---

Am I doing this correctly? I tried to run and the exception error is still present.

string queryStr = "INSERT INTO Submission(title,slides,codes)" + "VALUES('"+
            _title + "', '" +
           "0x" + BitConverter.ToString(_slides).Replace("-", "")+ "', '" +
           "0x" + BitConverter.ToString(_codes).Replace("-", "")  + "')";

Answer 1

I also got the same error when I am uploading doc USING ADO.NET and Storedproc.

I am using stored proc to upload word file to the table's column type varbinary(max).
There are so many examples with insert query to insert document but my scenario was stored proc. I spent lot of time in figuring out the solution. 

Stored Proc:`Alter PROC [dbo].[usp_GMS_SaveEngagementDocument](
        @pDocumentID INT=0,
        @pEngagementID INT,
        @pDocumentName NVARCHAR(100),
        @pContentType NVARCHAR(100),
        @pDocumentType NVARCHAR(100),
        @pDocumentContent VARBINARY(max)=null,
       @pUserID INT)
AS
BEGIN
--INSERT
END`



SOLUTION:

    param = new SqlParameter();
                param.ParameterName = "@pDocumentContent";
                param.Direction = ParameterDirection.Input;
                param.Value = document.DocumentContent;
                param.DbType = DbType.Binary;
                param.SqlDbType = SqlDbType.Binary;
                paramList.Add(param);



Setting SQLDBType as Binary and DbType as Binary solved my problem In calling stored proc.

END

Answer 2

"0x" + BitConverter.ToString(_slides).Replace("-", "")+ "', '" +

You should not convert byte to string. Instead, you want to use the parametrized query (to avoid sql injection) and insert those byte arrays straight to database.

public int SubmissionInsert(string title, byte[] slides, byte[] codes)
{
    int nofRow;
    string query = "INSERT  INTO Submission ( title, slides, codes )" +
                    "VALUES  ( @Title, @Slides, @Codes );";

    using (var con = new SqlConnection(_connStr))
    {
        con.Open();
        using (var cmd = new SqlCommand(query, con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Title", title);
            cmd.Parameters.AddWithValue("@Slides", slides);
            cmd.Parameters.AddWithValue("@Codes", codes);
            nofRow = cmd.ExecuteNonQuery();
        }
    }
    return nofRow;
}

Answer 3

Your issue is with the type conversion. If you are inserting the value as a string (and you are by using those single quotes), you need to insert the HEX values, and prefix it with 0x.

This should help you out: "0x" + BitConverter.ToString(byteArray).Replace("-", "")