XML string to Varbinary(MAX) using LinqToEntities

Question

I have serialized a database values to XML and kept it inside the Apps debug folder. Now i want to deserialize it and insert to SQL database.

Here is what i did:

XmlDocument XDoc = new XmlDocument();

            XDoc.Load("Serialized.xml");
            var xmlcontents = XDoc.InnerXml; //All XML Contents
            DataSet ds = new DataSet();
            StringReader sr = new StringReader(xmlcontents);
            ds.ReadXml(sr);
            DataTable offlineDT = ds.Tables[0];
            var context = new SysventLinqClassDataContext();


            context.Connection.Open();
            //The transaction Part
            using (context.Transaction = context.Connection.BeginTransaction())
            {
                using (var bulkCopy = new SqlBulkCopy(context.Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                {
                    bulkCopy.ColumnMappings.Add("DocTitle", "DocTitle");
                    bulkCopy.ColumnMappings.Add("DateOfReceipt", "DateOfReceipt");
                    bulkCopy.ColumnMappings.Add("Descriptions", "Descriptions");
                    bulkCopy.ColumnMappings.Add("HouseID", "HouseID");
                    bulkCopy.ColumnMappings.Add("DocData", "DocData".ToString());
                    bulkCopy.ColumnMappings.Add("SiteID", "SiteID");
                    bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID");


                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.DestinationTableName = "tblDocument";
                    bulkCopy.WriteToServer(offlineDT);
                }

            }

The class contains :

public string DocTitle { get; set; }
        public string DateOfReceipt { get; set; }       
        public string Descriptions { get; set; }

        [System.Xml.Serialization.XmlElementAttribute("DocData", DataType ="base64Binary")]
        public byte[] DocData { get; set; } //This is a PDF document as varbinary(MAX) in mySQL Database
        public int SiteID { get; set; }
        public int HouseID { get; set; }
        public int DesignID { get; set; }
        public int EmployeeID { get; set; }


        public void Save(string filename) //TO be called inside the main
        {
            using (var stream = new FileStream(filename, FileMode.Create))
            {
                var xmlData = new XmlSerializer(typeof(Class1));
                xmlData.Serialize(stream, this);
            }
        }

The onButton click to save the file:

FileStream fStream = File.OpenRead(filepath);
                byte[] contents = new byte[fStream.Length];
                fStream.Read(contents, 0, (int)fStream.Length);
                fStream.Close();


                Byte[] hexByte = new Byte[2]{Convert.ToByte(100),
                Convert.ToByte(50)};

Thus; DocData = contents;

Now its showing the error: The given value of type String from the data source cannot be converted to type varbinary of the specified target column.

Answer 1

The error is 'The given value of type String from the data source cannot be converted to type varbinary of the specified target column

That is a very frequent error. For some type like varbinary, you must specify the column type for the DataColumn otherwise, is considered as a string and cannot be converted.

However, you cannot change column type once the DataTable is filled with data. One workaround is cloning the table and importing the data

(Original Answer from Akhil here)

DataTable dtCloned = dt.Clone();
dtCloned.Columns[0].DataType = typeof(byte[]);
foreach (DataRow row in dt.Rows) 
{
    dtCloned.ImportRow(row);
}