c# - 为什么我不能在这里使用 SQLite 命令生成器？

Question

当我在进行更改后尝试单击 Button1 时，出现此错误：

System.InvalidOperationException：“不支持对多个基表进行动态 SQL 生成。”

现在，这个错误是有道理的，所以我用谷歌搜索它，并想出了这个 Question/Answer。我尝试重构我原来的 SQL 查询：

SELECT T.TournamentName 'Tournament Name', R1.CharacterName Champion, R2.CharacterName 'Runner Up'
FROM SmashTournaments T 
INNER JOIN SmashRoster R1 
ON T.Champion = R1.SmashRosterID 
INNER JOIN SmashRoster R2 
ON T.RunnerUp = R2.SmashRosterID

对此，根据其他问题的回答：

SELECT DISTINCT T.TournamentName 'Tournament Name', (SELECT R1.CharacterName 
                                                     FROM SmashRoster R1
                                                     WHERE T.Champion = R1.SmashRosterID) 'Champion', 
                                                    (SELECT R2.CharacterName 
                                                     FROM SmashRoster R2
                                                     WHERE T.RunnerUp = R2.SmashRosterID) 'Runner Up'
FROM SmashTournaments T 
INNER JOIN SmashRoster R1 
ON T.Champion = R1.SmashRosterID 
INNER JOIN SmashRoster R2 
ON T.RunnerUp = R2.SmashRosterID

编辑：这个查询也不起作用，同样的错误：

SELECT DISTINCT T.TournamentName 'Tournament Name', (SELECT R1.CharacterName 
                                                     FROM SmashRoster R1
                                                     WHERE T.Champion = R1.SmashRosterID) 'Champion', 
                                                    (SELECT R2.CharacterName 
                                                     FROM SmashRoster R2
                                                     WHERE T.RunnerUp = R2.SmashRosterID) 'RunnerUp'
FROM SmashTournaments T 

但是运行此代码时我仍然收到错误消息：

public partial class SmashTournaments : Form
{
  public SmashTournaments()
  {
     InitializeComponent();
  }
  SQLiteConnection cnn;
  SQLiteDataAdapter adapter;
  DataSet dataSetx;
  SQLiteCommandBuilder builder;

  private void SmashTournaments_Activated(object sender, EventArgs e)
  {
   LoadSmashTournaments();
  }

  public void LoadSmashTournaments()
  {
   cnn = new SQLiteConnection(@"Data Source=C:\SQLiteDB\SQLiteDB.db;Version=3;");
   adapter = new SQLiteDataAdapter(
      @"SELECT DISTINCT T.TournamentName 'Tournament Name', (SELECT R1.CharacterName 
                                                             FROM SmashRoster R1
                                                             WHERE T.Champion = R1.SmashRosterID) 'Champion', 
                                                            (SELECT R2.CharacterName 
                                                             FROM SmashRoster R2
                                                             WHERE T.RunnerUp = R2.SmashRosterID) 'RunnerUp'
        FROM SmashTournaments T 
        INNER JOIN SmashRoster R1 
        ON T.Champion = R1.SmashRosterID 
        INNER JOIN SmashRoster R2 
        ON T.RunnerUp = R2.SmashRosterID", cnn);

   dataSetx = new DataSet();
   cnn.Open();
   adapter.Fill(dataSetx);
   tournamentsGridView.DataSource = dataSetx.Tables[0];
  }

  private void button1_Click(object sender, EventArgs e)
  {
   builder = new SQLiteCommandBuilder(adapter);
   adapter.Update(dataSetx); //error thrown here
   LoadSmashTournaments();
  }
}

那么，其他答案解决方案是否不起作用？如果没有，我应该怎么做这样的事情？

标签： c#winformssqlite