c#/.NET SQLite -- REINDEX 不起作用？

发布于 2024-08-25 11:02:28 字数 532 浏览 11 评论 0原文

我正在尝试对使用 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”表。在上面的示例中，您还会看到我尝试过的其他查询字符串也不起作用。

请帮忙，

谢谢

原文

I'm trying to reindex a table in a simple database that I created using SQLite.NET and VS2008. I need to reindex tables after every DELETE command and here is the code snippet I have written (it does not work):

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()

When run within my program, the code produces no errors, but it also doesn't reindex the "tf_questions" table. In the above example, you will also see the other query strings I've tried that also don't work.

Please help,

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你的心境我的脸 2024-09-01 11:02:28

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

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 命令，但该代码可以工作。

I figured out a workaround for my problem. Consider the following code:

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...");
    }
}

This code works, though I would have liked to have used the built-in SQL REINDEX command.

回复收藏 0 原文