C# WinForms项目：电影-演员-角色关联查询及ListBox取值问题

Hey there! Let's fix your Windows Forms movie app issue step by step — we'll cover extracting the MovieID, optimizing your SQL query, and getting that third ListBox working properly.

1. Extracting the Selected Movie's ID from the First ListBox #

First, let's make sure you're setting up your movie ListBox correctly to easily access the MovieID. How you do this depends on how you bound your data:

If you're using a DataTable (common with ADO.NET):

When loading your movie list, set the DisplayMember (what the user sees) and ValueMember (the hidden ID) explicitly:

// Example: Loading movies into the first ListBox
private void LoadMovies()
{
    string connString = "Your database connection string here";
    string query = "SELECT MovieID, Title FROM Movie";
    
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
        DataTable movieTable = new DataTable();
        adapter.Fill(movieTable);
        
        listBoxMovies.DataSource = movieTable;
        listBoxMovies.DisplayMember = "Title"; // Shows the movie name to users
        listBoxMovies.ValueMember = "MovieID"; // Stores the hidden ID we need
    }
}

Then, in the SelectedIndexChanged event of the movie ListBox, grab the ID like this:

private void listBoxMovies_SelectedIndexChanged(object sender, EventArgs e)
{
    // Guard against empty selections (e.g., user clicks blank space)
    if (listBoxMovies.SelectedItem == null) return;
    
    // Get the selected MovieID directly from SelectedValue
    int selectedMovieId = Convert.ToInt32(listBoxMovies.SelectedValue);
    
    // Use this ID to load characters/actors into the third ListBox
    LoadCharactersAndActors(selectedMovieId);
}

If you're using a custom Movie class:

If you're binding a list of strongly-typed Movie objects, cast the selected item to your class:

public class Movie
{
    public int MovieID { get; set; }
    public string Title { get; set; }
}

// In your load method
List<Movie> movies = GetMoviesFromDatabase(); // Your method to fetch movies
listBoxMovies.DataSource = movies;
listBoxMovies.DisplayMember = "Title";

// In the SelectedIndexChanged event
Movie selectedMovie = (Movie)listBoxMovies.SelectedItem;
int selectedMovieId = selectedMovie.MovieID;

2. Fixing the SQL Query (and Avoiding Injection!) #

Your original query joins the Movie table unnecessarily (since the Character table already has a MovieID). Plus, never concatenate user input/selected values directly into SQL — it's a huge injection risk. Use parameterized queries instead:

SELECT c.Name AS CharacterName, a.Name AS ActorName
FROM Character c
INNER JOIN Actor a ON a.ActorID = c.ActorID
WHERE c.MovieID = @MovieID

3. Loading Characters & Actors into the Third ListBox #

Create a method to fetch and bind the data using the parameterized query:

private void LoadCharactersAndActors(int movieId)
{
    string connString = "Your database connection string here";
    string query = @"SELECT c.Name AS CharacterName, a.Name AS ActorName
                     FROM Character c
                     INNER JOIN Actor a ON a.ActorID = c.ActorID
                     WHERE c.MovieID = @MovieID";
    
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            // Add the parameter to avoid SQL injection
            cmd.Parameters.AddWithValue("@MovieID", movieId);
            
            conn.Open();
            DataTable charActorTable = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(charActorTable);
            
            // Add a calculated column to show a friendly format like "Shawn (John)"
            charActorTable.Columns.Add("DisplayText", typeof(string), "CharacterName + ' (' + ActorName + ')'");
            
            // Bind to the third ListBox
            listBoxCharacters.DataSource = charActorTable;
            listBoxCharacters.DisplayMember = "DisplayText";
        }
    }
}

Quick Extra Tips #

• Double-check that the SelectedIndexChanged event is actually hooked up to your method (you can do this in the designer or via code).
• If you're using Entity Framework instead of raw ADO.NET, a LINQ query can simplify this even more:

  var charActors = db.Character
                      .Where(c => c.MovieID == selectedMovieId)
                      .Join(db.Actor, 
                            c => c.ActorID, 
                            a => a.ActorID, 
                            (c, a) => $"{c.Name} ({a.Name})")
                      .ToList();
  
  listBoxCharacters.DataSource = charActors;
  

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