需要帮助从SQL Server CE数据库删除行 [英] Need help on deleting row(s) from a SQL Server CE database

问题描述

今天的另一个问题。这一次，我无法从SQL Server CE数据库中删除一行。

Another question today. This time, I'm having trouble deleting a row from a SQL Server CE database.

private void Form1_Load(object sender, EventArgs e)
{
        // Create a connection to the file datafile.sdf in the program folder
        string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\userDtbs.sdf";
        SqlCeConnection connection = new SqlCeConnection("datasource=" + dbfile);

        // Read all rows from the table test_table into a dataset (note, the adapter automatically opens the connection)
        SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM history", connection);
        DataSet data = new DataSet();
        adapter.Fill(data);

        //Delete from the database
        using (SqlCeCommand com = new SqlCeCommand("DELETE FROM accounts WHERE Id = 0", connection))
        {
            com.ExecuteNonQuery();
        }

        // Save data back to the databasefile
        var cmd = new SqlCeCommandBuilder(adapter);
        adapter.Update(data);

        // Close 
        connection.Close();
}

我的程序给我一个错误，告诉我 处于关闭状态，我不知道为什么它会在执行 DELETE 命令之前关闭。

My program's giving me an error telling me that connection is in a closed state, and I can't figure out why it would close before the DELETE command is executed.

推荐答案

请注意：使用 Command.ExecuteXXX（）执行命令需要首先打开连接。使用 SqlDataAdapter.Fill 将数据填充到 DataSet 中不需要，因为它在内部处理。以这种方式执行 SQL查询是直接的，不需要在上的任何更新 adapter （在删除后添加到代码中）。 更新仅用于保存对您的 DataSet 所做的更改。

Note that: executing command with Command.ExecuteXXX() requires the connection to be open first. Filling data into DataSet using SqlDataAdapter.Fill doesn't require that because it handles that internally. Executing the SQL query this way is direct and don't require any Update method call on adapter (as you add in your code after deleting). Update is just for saving changes made on your DataSet.

    //Delete from the database
    using (SqlCeCommand com = new SqlCeCommand("DELETE FROM accounts WHERE Id = 0", connection))
    {
        if(connection.State == ConnectionState.Closed) connection.Open();
        com.ExecuteNonQuery();
    }

这篇关于需要帮助从SQL Server CE数据库删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！