c#/.NET SQLite——REINDEX 不工作？

Question

我正在尝试重新索引我使用 SQLite.NET 和 VS2008 创建的简单数据库中的表。我需要在每个 DELETE 命令后重新索引表，这是我编写的代码片段(它不起作用):

SQLiteCommand currentCommand;
String tempString = "REINDEX tf_questions";
//String tempString = "REINDEX [main].tf_questions";
//String tempString = "REINDEX main.tf_questions";

currentCommand = new SQLiteCommand(myConnection);
currentCommand.CommandText = tempString;
currentCommand.ExecuteNonQuery()

在我的程序中运行时，代码没有产生任何错误，但它也没有重新索引“tf_questions”表。在上面的示例中，您还会看到我尝试过的其他查询字符串也不起作用。

请帮忙，

谢谢

Answer 1

我找到了解决问题的方法。考虑以下代码:

SQLiteCommand currentCommand;
String tempString;
String currentTableName = "tf_questions";
DataTable currentDataTable;
SQLiteDataAdapter myDataAdapter;

currentDataTable = new DataTable();
myDataAdapter = new SQLiteDataAdapter("SELECT * FROM " + currentTableName, myConnection);
myDataAdapter.Fill(currentDataTable);


//"tf_questions" is the name of the table
//"question_id" is the name of the primary key column in "tf_questions" (set to auto inc.)
//"myConnection" is and already open SQLiteConnection pointing to the db file

for (int i = currentDataTable.Rows.Count-1; i >=0 ; i--)
{
    currentCommand = new SQLiteCommand(myConnection);
    tempString = "UPDATE "+ currentTableName +"\nSET question_id=\'"+(i+1)+"\'\nWHERE (question_id=\'" +
        currentDataTable.Rows[i][currentDataTable.Columns.IndexOf("question_id")]+"\')";
    currentCommand.CommandText = tempString;

    if( currentCommand.ExecuteNonQuery() < 1 )
    {
        throw new Exception("There was an error executing the REINDEX protion of the code...");
    }
}

此代码有效，但我希望使用内置的 SQL REINDEX 命令。