在C#中基于列名对比DataTable并生成指定输出表的技术问询

Let's break down how to achieve this data transformation. The core idea is to use the mapping table (Table1) to map columns from the source table (Table2) to the desired key-value pairs in Table3. Here's a practical implementation in C#:

Step 1: Define and Populate Your Input Tables #

First, let's replicate your sample tables to work with:

// Create Table1 (Mapping Table)
DataTable table1 = new DataTable("Table1");
table1.Columns.Add("Key", typeof(string));
table1.Columns.Add("SourceField", typeof(string));
table1.Rows.Add(null, "name");
table1.Rows.Add("A101", "V1");
table1.Rows.Add("A102", "V2");
table1.Rows.Add("A103", "V3");

// Create Table2 (Source Table)
DataTable table2 = new DataTable("Table2");
table2.Columns.Add("Name", typeof(string));
table2.Columns.Add("V1", typeof(int));
table2.Columns.Add("V2", typeof(int));
table2.Columns.Add("V3", typeof(int));
table2.Rows.Add("10001", 1, 2, 3);

Step 2: Create the Output Table (Table3) #

Define the structure of your target table:

DataTable table3 = new DataTable("Table3");
table3.Columns.Add("Name", typeof(string));
table3.Columns.Add("Value", typeof(int));
table3.Columns.Add("Key", typeof(string));

Step 3: Implement the Mapping Logic #

Loop through each row in the source table, then use the mapping table to pull the correct values and populate Table3. We'll first extract the name field mapping (where Key is null) to get the Name value, then process the other mappings:

// Get the name field mapping (the row where Key is null)
var nameMapping = table1.AsEnumerable().FirstOrDefault(row => row.IsNull("Key"));
string nameColumn = nameMapping?["SourceField"].ToString();

// Get the value-key mappings (exclude the null Key row)
var valueMappings = table1.AsEnumerable().Where(row => !row.IsNull("Key"));

// Process each row in the source table
foreach (DataRow sourceRow in table2.Rows)
{
    string nameValue = sourceRow[nameColumn].ToString();
    
    // For each value-key mapping, add a row to Table3
    foreach (DataRow mappingRow in valueMappings)
    {
        string sourceField = mappingRow["SourceField"].ToString();
        string keyValue = mappingRow["Key"].ToString();
        int fieldValue = Convert.ToInt32(sourceRow[sourceField]);
        
        table3.Rows.Add(nameValue, fieldValue, keyValue);
    }
}

Step 4: Verify the Result #

If you inspect table3 now, it will match your desired output exactly:

Notes #

• This approach handles multiple rows in Table2 automatically—just add more rows to Table2 and the loop will process each one.
• We use AsEnumerable() to leverage LINQ for filtering the mapping table, which makes the code cleaner.
• Make sure to handle potential null values or type conversion issues if your actual data has edge cases (e.g., non-integer values in V1/V2/V3).

内容的提问来源于stack exchange，提问作者MANISH