Problem converting a byte array into datatable.

Question

Hi,

In my aspx page I have a HTML inputfile type which allows user to browse for a spreadsheet.Once the user choses the file to upload I want to read the content of the spreadsheet and store the content into mysql database table.

I am using the following code to read the content of the uploaded file and convert it into a datatable in order into insert it into database table.

if (filMyFile.PostedFile != null)
        {
            // Get a reference to PostedFile object
            HttpPostedFile myFile = filMyFile.PostedFile;

            // Get size of uploaded file
            int nFileLen = myFile.ContentLength;

            // make sure the size of the file is > 0
            if (nFileLen > 0)
            {
                // Allocate a buffer for reading of the file
                byte[] myData = new byte[nFileLen];

                // Read uploaded file from the Stream
                myFile.InputStream.Read(myData, 0, nFileLen);

                DataTable dt = new DataTable();

               MemoryStream st = new MemoryStream(myData);
               st.Position = 0;
               System.Runtime.Serialization.IFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
               dt=(DataTable)formatter.Deserialize(st);
}
}

But I am getting the following error when I am trying to deserialise the byte array into datatable.

Binary stream '0' does not contain a valid BinaryHeader. Possible causes are invalid stream or object version change between serialization and deserialization.

Could someone please tell me what am I doing wrong?

I've also tried converting the bytearray into string ,then converting the string back to byte array and convert into datatable.That is also throwing the same error.

Thanks.

Answer 1

What makes you think that you can deserialize a spreadsheet into a datatable? You can most definitely not! All you need to do is to save the byte[] (myData) to a VARBINARY column in an appropriate table in your mysql database.

EDIT

You can do something along these lines:

Suppose you have a mysql table:

create table MyTable(
   Id INTEGER AUTO_INCREMENT
   SpreadSheet BLOB 
) ENGINE=InnoDB;

You could use the following code to insert the uploaded file into the table:

string strConn="PROVIDER=MySQLProv;SERVER=192.168.1.8;DB= test;UID=test;PWD=;PORT=;";
OleDbConnection objConn;
objConn=new OleDbConnection (strConn);
objConn.Open();
string sql="INSERT INTO MyTable (SpreadSheet) VALUES(?)";
OleDbCommand cmd = new OleDbCommand(sql,objConn);
OleDbParameter param = new OleDbParameter ("@image",OleDbType.Binary );
param.Value = myData;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
objConn.Close ();