将varbinary转换为xml C#

Question

我有一个C#winform应用程序，将文件保存到sqlserver数据库（2014）到VAR二进制（MAX）字段

功能保存

 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();
                }
            }
        }

要检索的函数

 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);
                        }
                }
            }

        }

这工作很好，现在我已经要求将数据库转换为XML，用于未连接到服务器的PC

转换为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();

它工作正常，除了二进制文件，当

输出XML

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

当尝试将其转换回图像时，它会给我1kb的文件，字符串不是实际图像

二进制字段有特殊的转换吗？

非常感谢。

编辑问题解决感谢@grek40解决方案转换为XML

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

其中ds是一个数据集

转换回文件

  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

XML中二进制文件的标准格式是base64。我不认为，上面有什么问题。。。

您必须记住，XML中禁止使用几个字符。您不能仅仅在两个XML标记之间填充二进制数据。如果有一个的数字代码

显示的字符串看起来很像base64代码。

试试这个

DECLARE @string VARCHAR(100)='Hello World with forbidden characters (< & >)';
DECLARE @binary VARBINARY(MAX) = CAST(@string AS VARBINARY(MAX));
DECLARE @xml XML=(SELECT @string AS string, @binary AS bin FOR XML PATH('test'));

SELECT @xml;

--结果（编码实体和二进制隐式转换为base64）

<test>
  <string>Hello World with forbidden characters (&lt; &amp; &gt;)</string>
  <bin>SGVsbG8gV29ybGQgd2l0aCBmb3JiaWRkZW4gY2hhcmFjdGVycyAoPCAmID4p</bin>
</test>

--现在我们从XML中读取（实体被重新编码，二进制表示为十六进制字符串，可以将其重新转换为前者VARCHAR（MAX）

SELECT @xml.value('(/test/string)[1]','nvarchar(max)') AS TheStringAsIs
      ,@xml.value('(/test/bin)[1]','varbinary(max)') AS TheStringAsBinary_HEX
      ,CAST(@xml.value('(/test/bin)[1]','varbinary(max)') AS VARCHAR(100)) AS ReConverted

结果

The string *as-is*:  Hello World with forbidden characters (< & >)  
binary data HEX:     0x48656C6C6F20576F726C64207769746820666F7262696464656E206368617261637465727320283C2026203E29   
ReConverted:         Hello World with forbidden characters (< & >) 

Answer 2

为了对二进制数据进行可逆字符串编码，可以使用Base64编码

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

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

将字节转换为存储在xml中的字符串，并在使用时从字符串还原字节。

最初，数据被正确地写入xml。问题很可能出在检索函数上。由于xml不包含模式信息，它将处理

为了允许正确的重读，您需要在读取时使用预定义的模式，或者使用表编写模式：

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

不同方面的小样本：

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));