c# - 数据集到 xml 空值

Question

我有下面的代码，我从 3 个表中获取数据并编写一个 xml。
我想在 xml 中写入(当记录列具有 null 值时)具有 null 值的列。例如 if (Category_name == Null ) 在 xml 上写入 (Null) 现在代码跳过该列并且在 xml 上什至没有此列。

 string xmlFileData = "";

    string[] tables = new string[] { "category",  "company", "config" };
    string query;
    xmlFileData += "<MyXml>";
    SqlConnection conn;
    dbconnect obj;
    obj = new dbconnect();//initailizing class object
    for (int i = 0; i < tables.Length; i++)
    {
        string ifemptquery;
        DataSet ds = new DataSet();

        DataSet ds1 = new DataSet();
        conn = obj.getConnection(); //calling connection function

        ifemptquery = "SELECT * FROM " + tables[i] ";
        SqlCommand cmd1 = new SqlCommand(ifemptquery, conn);
        conn.Open();
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
        DataTable dt1 = new DataTable();
        da1.Fill(dt1);
        conn.Close();
        if (dt1.Rows.Count > 0)
        {
            query = "SELECT * FROM " + tables[i] ";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            conn.Dispose();
            ds.DataSetName = tables[i];
            string vartbname = tables[i];
            string trimed_tbname = vartbname.Replace("_", "");
            ds.Tables[0].TableName = trimed_tbname;
            xmlFileData += ds.GetXml();
        }
        else
        {

        }


    }
    xmlFileData += "</MyXml>";
    File.WriteAllText(Server.MapPath("~/xmlbackup/") + "Backup.xml", xmlFileData);

Answer 1

我一直在全世界寻找使用 DataSet.WriteXML() 将空字段写入 XML 的解决方案。 Vlad 发布的答案是我在我的项目中也使用过的答案，但我发现以下内容以性能优化得多的方式工作。为了您的方便，我创建了一个函数。通过调用以下函数并替换表，一个接一个地更改数据集表。

private DataTable GetNullFilledDataTableForXML(DataTable dtSource)
{
    // Create a target table with same structure as source and fields as strings
    // We can change the column datatype as long as there is no data loaded
    DataTable dtTarget = dtSource.Clone();
    foreach (DataColumn col in dtTarget.Columns)
        col.DataType = typeof(string);

    // Start importing the source into target by ItemArray copying which 
    // is found to be reasonably fast for nulk operations. VS 2015 is reporting
    // 500-525 milliseconds for loading 100,000 records x 10 columns 
    // after null conversion in every cell which may be usable in many
    // circumstances.
    // Machine config: i5 2nd Gen, 8 GB RAM, Windows 7 64bit, VS 2015 Update 1
    int colCountInTarget = dtTarget.Columns.Count;
    foreach (DataRow sourceRow in dtSource.Rows)
    {
        // Get a new row loaded with data from source row
        DataRow targetRow = dtTarget.NewRow();
        targetRow.ItemArray = sourceRow.ItemArray;

        // Update DBNull.Values to empty string in the new (target) row
        // We can safely assign empty string since the target table columns
        // are all of string type
        for (int ctr = 0; ctr < colCountInTarget; ctr++)
            if (targetRow[ctr] == DBNull.Value)
                targetRow[ctr] = String.Empty;

        // Now add the null filled row to target datatable
        dtTarget.Rows.Add(targetRow);
    }

    // Return the target datatable
    return dtTarget;
}