在C#(WPF)中如何获取DataGrid表头列名？需实现字段搜索与类型获取

Hey there! Let's break down how to pull your DataGrid's column names and their corresponding data types, then use that info to build safe, targeted field searches with SQL queries. Here's how to approach it depending on your tech stack:

1. Extract Column Names & Data Types #

For .NET (WPF/WinForms) #

If your DataGrid is bound to a DataTable or DataSet, this is the easiest route:

• First, get the underlying DataTable from your DataGrid's source:

// If bound to a DataView
DataTable dataTable = (yourDataGrid.ItemsSource as DataView)?.Table;
// Or if directly bound to a DataTable
// DataTable dataTable = yourDataGrid.ItemsSource as DataTable;

• Loop through the Columns collection to grab names and types:

foreach (DataColumn col in dataTable.Columns)
{
    string gridColumnName = col.ColumnName; // Matches your DataGrid's column header (usually)
    Type dataType = col.DataType; // The CLR type corresponding to the SQL field
    Console.WriteLine($"Column: {gridColumnName}, Type: {dataType.Name}");
}

If you're binding to a custom model (like ObservableCollection<YourEntity>), use reflection to pull property details:

Type modelType = typeof(YourEntity);
foreach (var prop in modelType.GetProperties())
{
    string gridColumnName = prop.Name; // Assumes your DataGrid columns bind directly to property names
    Type dataType = prop.PropertyType;
    Console.WriteLine($"Column: {gridColumnName}, Type: {dataType.Name}");
}

For Frontend DataGrids (e.g., AG Grid, React Data Grid) #

If you're working with a frontend grid, start with your column definitions. Take AG Grid as an example:

// Your column config (map header names to SQL fields here)
const columnDefs = [
    { headerName: "User ID", field: "user_id", filter: "agNumberColumnFilter" },
    { headerName: "Username", field: "username", filter: "agTextColumnFilter" }
];

// Extract the info you need
columnDefs.forEach(col => {
    const gridHeader = col.headerName;
    const sqlField = col.field; // This is the actual SQL column name to use in queries
    // Infer data type from filter type or add an explicit 'dataType' property to your config
    const dataType = col.filter === "agNumberColumnFilter" ? "INT" : "VARCHAR";
    console.log(`Grid Header: ${gridHeader}, SQL Field: ${sqlField}, Type: ${dataType}`);
});

2. Build a Safe Single-Field Search Query #

Once you have the SQL field name and data type, always use parameterized queries to avoid SQL injection. Here's how to do it in .NET:

string targetSqlField = "username"; // From your DataGrid extraction
string searchInput = "john_doe"; // User's search value
Type fieldType = typeof(string); // Corresponding data type

// Parameterized query (never concatenate user input directly!)
string query = $"SELECT * FROM your_table WHERE [{targetSqlField}] = @SearchValue";

using (SqlConnection conn = new SqlConnection("your_connection_string"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        // Map CLR type to SqlDbType for the parameter
        cmd.Parameters.Add("@SearchValue", GetSqlDbType(fieldType)).Value = searchInput;
        
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            // Process your results and bind back to the DataGrid
            while (reader.Read())
            {
                // ...
            }
        }
    }
}

// Helper method to convert CLR types to SqlDbType
private SqlDbType GetSqlDbType(Type clrType)
{
    if (clrType == typeof(int)) return SqlDbType.Int;
    if (clrType == typeof(string)) return SqlDbType.NVarChar;
    if (clrType == typeof(DateTime)) return SqlDbType.DateTime;
    // Add more mappings as needed for your schema
    return SqlDbType.VarChar;
}

Key Notes #

• Field Mapping: If your DataGrid header names don't match SQL column names, create a mapping dictionary (e.g., Dictionary<string, string> gridToSqlMap = new() { { "Username", "user_name" } };) to translate between them.
• Type Safety: Ensure the user's search input matches the SQL field's data type—add validation if needed to avoid conversion errors.
• Injection Prevention: Parameterized queries are non-negotiable here; never directly insert user input into your SQL string.

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