咨询：C#如何实现类似Java静态方法填充DataGridView的功能

Absolutely! C# Windows Forms has a straightforward, generic way to replicate your Java workflow—you don’t have to limit yourself to MySQL-specific implementations. The core idea mirrors what you’re doing in Java: use a separate class to handle database queries (with WHERE/JOIN support, just like your static Java method), then pass the result back to your form to bind to a DataGridView.

Core Concepts Mapping #

Let’s align this with your Java setup first to make it familiar:

• Java static query method → C# static (or singleton) service class method
• JTable → DataGridView (Windows Forms' equivalent)
• Data transfer → Use .NET’s DataTable (generic, works with all databases) or strongly typed collections (for type safety)

Step-by-Step Generic Implementation #

1. Create an External Data Service Class #

This class will handle all database queries, abstracting away the specific database provider (MySQL, SQL Server, PostgreSQL, etc.). We’ll use ADO.NET’s base classes (DbConnection, DbCommand, DbDataAdapter) to keep it generic.

using System.Data;
using System.Data.Common;

namespace YourAppNamespace
{
    // External service class (same namespace or separate, just reference it)
    public static class DataQueryService
    {
        // Generic method to execute any query and return a DataTable
        public static DataTable ExecuteQuery(DbConnection connection, string query, params DbParameter[] parameters)
        {
            var resultTable = new DataTable();
            
            // Use 'using' to auto-dispose resources (critical for memory management)
            using (connection)
            using (var command = connection.CreateCommand())
            {
                command.CommandText = query;
                command.Parameters.AddRange(parameters);
                
                connection.Open();
                using (var adapter = DbProviderFactories.GetFactory(connection).CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(resultTable);
                }
            }
            
            return resultTable;
        }

        // Optional async version (prevents UI freezing for slow queries)
        public static async Task<DataTable> ExecuteQueryAsync(DbConnection connection, string query, params DbParameter[] parameters)
        {
            var resultTable = new DataTable();
            
            using (connection)
            using (var command = connection.CreateCommand())
            {
                command.CommandText = query;
                command.Parameters.AddRange(parameters);
                
                await connection.OpenAsync();
                using (var adapter = DbProviderFactories.GetFactory(connection).CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(resultTable);
                }
            }
            
            return resultTable;
        }
    }
}

2. Call the Service from Your Windows Forms Form #

In your Form class, you’ll initialize the appropriate database connection (e.g., MySqlConnection, SqlConnection), call the service method, and bind the resulting DataTable to your DataGridView.

using System.Data;
using MySql.Data.MySqlClient; // Replace with your DB provider's namespace (e.g., System.Data.SqlClient for SQL Server)

namespace YourAppNamespace
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void LoadDataButton_Click(object sender, EventArgs e)
        {
            // Example: Query with WHERE and JOIN clauses
            string query = @"
                SELECT u.UserId, u.Username, r.RoleName
                FROM Users u
                JOIN Roles r ON u.RoleId = r.RoleId
                WHERE u.IsActive = @IsActive";

            // Initialize your DB connection (use your connection string)
            var connection = new MySqlConnection("YourConnectionStringHere");

            // Create parameters to avoid SQL injection (critical!)
            var parameters = new[]
            {
                new MySqlParameter("@IsActive", true)
            };

            // Call the external service
            DataTable results = DataQueryService.ExecuteQuery(connection, query, parameters);

            // Bind the results to DataGridView
            dataGridView1.DataSource = results;
        }

        // Optional async version to keep UI responsive
        private async void LoadDataAsyncButton_Click(object sender, EventArgs e)
        {
            string query = @"
                SELECT u.UserId, u.Username, r.RoleName
                FROM Users u
                JOIN Roles r ON u.RoleId = r.RoleId
                WHERE u.IsActive = @IsActive";

            var connection = new MySqlConnection("YourConnectionStringHere");
            var parameters = new[] { new MySqlParameter("@IsActive", true) };

            // Use async/await
            DataTable results = await DataQueryService.ExecuteQueryAsync(connection, query, parameters);
            dataGridView1.DataSource = results;
        }
    }
}

3. Optional: Strongly Typed Collections (Type-Safe Alternative) #

If you prefer type safety over the generic DataTable, you can modify the service to return a List<T> instead:

// Add this method to DataQueryService
public static List<T> ExecuteQuery<T>(DbConnection connection, string query, Func<DbDataReader, T> mapFunction, params DbParameter[] parameters)
{
    var results = new List<T>();
    
    using (connection)
    using (var command = connection.CreateCommand())
    {
        command.CommandText = query;
        command.Parameters.AddRange(parameters);
        
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                results.Add(mapFunction(reader));
            }
        }
    }
    
    return results;
}

Then use it in your form with a strongly typed class:

// Define your model class
public class UserRole
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string RoleName { get; set; }
}

// In your form's button click
private void LoadTypedDataButton_Click(object sender, EventArgs e)
{
    string query = @"SELECT u.UserId, u.Username, r.RoleName FROM Users u JOIN Roles r ON u.RoleId = r.RoleId WHERE u.IsActive = @IsActive";
    var connection = new MySqlConnection("YourConnectionStringHere");
    var parameters = new[] { new MySqlParameter("@IsActive", true) };

    // Map reader to your model
    List<UserRole> userRoles = DataQueryService.ExecuteQuery(connection, query, reader => new UserRole
    {
        UserId = reader.GetInt32("UserId"),
        Username = reader.GetString("Username"),
        RoleName = reader.GetString("RoleName")
    }, parameters);

    // Use BindingSource for better DataGridView interaction (sorting, filtering)
    var bindingSource = new BindingSource();
    bindingSource.DataSource = userRoles;
    dataGridView1.DataSource = bindingSource;
}

Key Best Practices #

• Always use parameterized queries: Never concatenate user input into SQL strings—this prevents SQL injection attacks.
• Dispose resources with using: Ensures database connections and commands are properly cleaned up.
• Use async/await for long-running queries: Keeps your UI responsive while data loads.
• Abstract database provider logic: The DbConnection approach lets you switch databases (e.g., from MySQL to SQL Server) by just changing the connection type and provider namespace.

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