convert varbinary to xml C#

Question

i have A C# winforms Application that save files to sqlserver Database (2014) to varbinary(MAX) field

Function To save

 byte[] Bytefile;
        using (SqlConnection conn = new SqlConnection(DataHelper.GetConnection()))
        {
            conn.Open();
            DataTable dt = new DataTable();
            SqlCommand comm = new SqlCommand("Delete  T_Articale_Files where Artricle_id=" + ID, conn);

            comm.ExecuteNonQuery();
            foreach (string file in Directory.GetFiles(varFilePath))
            {

                using (var stream = new FileStream(Path.Combine(varFilePath, file), FileMode.Open, FileAccess.Read))
                {
                    using (var reader = new BinaryReader(stream))
                    {
                        Bytefile = reader.ReadBytes((int)stream.Length);
                    }
                }


                using (var sqlWrite = new SqlCommand("INSERT INTO T_Articale_Files (Artricle_id,FileName,FileData) Values(@ID,@FileName,@File)", conn))
                {
                    sqlWrite.Parameters.Add("@ID", SqlDbType.Int, 10).Value = ID;
                    sqlWrite.Parameters.Add("@FileName", SqlDbType.NVarChar, 50).Value = Path.GetFileName(file);
                    sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = Bytefile;
                    sqlWrite.ExecuteNonQuery();
                }
            }
        }

Function to Retrieve

 using (SqlConnection conn = new SqlConnection(DataHelper.GetConnection()))
        //   using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        {
            conn.Open();
            DataTable dt = new DataTable();
            SqlCommand comm = new SqlCommand("SELECT id,FileName FROM T_Articale_Files WHERE Artricle_id = @varID", conn);
            comm.Parameters.AddWithValue("@varID", varID);
            dt.Load(comm.ExecuteReader());
            foreach (DataRow item in dt.Rows)
            {
                using (var sqlQuery = new SqlCommand(@"SELECT FileData FROM T_Articale_Files WHERE id = @ID", conn))
                {
                    sqlQuery.Parameters.AddWithValue("@ID", item["id"]);

                    using (var sqlQueryResult = sqlQuery.ExecuteReader())
                        while (sqlQueryResult.Read())
                        {

                            var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                            sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                            using (var fs = new FileStream(Path.Combine(varPathToNewLocation, item["FileName"].ToString()), FileMode.Create, FileAccess.Write))
                                fs.Write(blob, 0, blob.Length);
                        }
                }
            }

        }

that work fine Now i have asked to convert the database to XML for PCs that does not have Connection to server

Function to Convert to XML

 var xmlFileData = "";
        DataSet ds = new DataSet();
        var tables = new[] { "V_Articale", "T_Articale", "T_City", "T_Classification", "T_Country", "T_Locations", "T_milishia", "T_Search", "T_statistics", "T_TerrorGroups", "T_Tribes", "T_Users", "T_Articale_Files" };
        foreach (var table in tables)
        {

            var query = "SELECT * FROM " + table;
            SqlConnection conn = GetConnection();
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable(table);
            da.Fill(dt);
            conn.Close();
            conn.Dispose();
            ds.Tables.Add(dt);
            if(table== "T_Articale_Files")
            {
                foreach (DataRow item in dt.Rows)
                {
                    Byte[] file = GetBytes(item["FileData"].ToString());
                }
            }

        }
        xmlFileData = ds.GetXml();

it work fine except for the Binary filed it is converted to text when

The output XML

<T_Articale_Files>
<id>6</id>
<Artricle_id>1013</Artricle_id>
<FileName>falcon banner.jpg</FileName>
<FileData>/9j/4AAQSkZJRgABAgEASABIAAD/4QleRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaAAUAAAABAAAAYgEbAAUA</FileData>

when try to convert it back to image it gives me 1kb file with the string not the actual image

is there special convert for the binary field Please Advise

Thank you

Edit Problem Solved thanks to @grek40 Solution Convert to XML

  var xmlstream = new StringWriter();
        ds.WriteXml(xmlstream, XmlWriteMode.WriteSchema);
        string xmlWithSchema = xmlstream.ToString();

where ds is a dataset

Convert back to file

  private  void databaseFileRead(int varID, string varPathToNewLocation)
    {
         DataSet ds = new DataSet();
        ds.ReadXml(XMLpath);
        DataTable dt = new DataTable();
        dt = ds.Tables["T_Articale_Files"];
        DataView dv = new DataView(dt);
        dv.RowFilter = "Artricle_id=" + varID;

        if (dv.Count > 0)
        {
            foreach (DataRowView item in dv)
            {
                byte[] stringArray = (byte[])(item["FileData"]);
                File.WriteAllBytes(Path.Combine(Filepath, item["FileName"].ToString()), stringArray  ); // save image to disk


            }

        }

Answer 1

In order to have a reversible string encoding of binary data, you could use Base64 encoding

public byte[] StrToByteArray(string str)
{
    return Convert.FromBase64String(str);
}

public string ByteArrToString(byte[] byteArr)
{
    return Convert.ToBase64String(byteArr);
}

transform bytes to string which is stored in xml and restore bytes from string on usage.

Initially the data are correctly written as xml. The problem is most likely with the retrieve function. Since the xml doesn't contain schema information, it will treat the <FileData> as text unless it is instructed differently.

In order to allow correct re-reading, you need either a pre-defined schema when reading or you have to write the schema with the tables:

dataSet.WriteXml(filenameOrStream, XmlWriteMode.WriteSchema)
// later read the xml and it will respect the schema information
dataSet.ReadXml(filenameOrStream);

A small sample of the different aspects:

var sourceDataSet = new DataSet();
var sourceTable = new DataTable("TableWithBinary");

sourceDataSet.Tables.Add(sourceTable);

sourceTable.Columns.Add("Id");
sourceTable.Columns.Add("File", typeof(byte[]));

sourceTable.Rows.Add(1, new byte[] { 1, 0, 2 });
sourceTable.Rows.Add(2, new byte[] { 1, 3, 2 });

// write option 1
string schema = sourceDataSet.GetXmlSchema();
string getxml = sourceDataSet.GetXml();

// write option 2
var writexmlstream = new StringWriter();
sourceDataSet.WriteXml(writexmlstream, XmlWriteMode.WriteSchema);
string writexmlWithSchema = writexmlstream.ToString();

// read wrong (missing schema)
var targetCorrupted = new DataSet();
targetCorrupted.ReadXml(new StringReader(getxml));

// read correct with schema in every xml file
var targetFromXmlWithSchema = new DataSet();
targetFromXmlWithSchema.ReadXml(new StringReader(writexmlWithSchema));

// read correct with separate schema definition and data
var targetFromXml = new DataSet();
targetFromXml.ReadXmlSchema(new StringReader(schema));
targetFromXml.ReadXml(new StringReader(getxml));