C#Windows应用程序访问数据库数据不会在关闭时持续存在 [英] C# windows application access database data doesn't persist on close

问题描述

我正在使用C#创建Windows应用程序，由此访问了一个空的Access数据库，该数据库包含两个表:Provinces和Locations.我正在处理只处理Provinces表的表格，如下所示:

I'm creating a windows application using C# whereby I'm accessing an empty Access database which contains two tables: Provinces and Locations. I'm working on the form that just deals with the Provinces table which looks like this:

这是一个子表单.当它打开时，我可以插入/更新记录等.每当进行更改时，我都单击加载表"按钮以在DataGridView对象中显示更改.

This is a subform. When it is open, I can insert/update records etc. Whenever I make a change, I click on the Load Table button to display the changes in the DataGridView object.

如果关闭此子窗体并再次显示，则可以单击加载表"按钮，并调出所有数据以显示在DataGridView对象中.但是，如果我完全关闭该应用程序，那么我将丢失所有数据.我通过双击数据库文件以在Access中启动它来证明这一点，在其中可以看到数据肯定消失了.因为我不知道为什么数据不能持久保存在文件中，所以这已经成为一个谜.请告知.

If I close this subform and show it again, I can click on the Load Table button and recall all the data for display in the DataGridView object. However, if I close down the application altogether, then I lose all my data. I prove this by double clicking the database file to launch it in Access where I can see that the data is definitely gone. This has become a mystery as I can't figure out why the data doesn't persist in the file. Please advise.

下面是表单的代码.您可以从我的方法中看到，每次执行函数时，我都会关闭连接对象.所以我迷茫了为什么我在应用程序关闭时仍然丢失数据?

Below is the code for the form. You can see from my methods that I'm careful to close the connection object each and every time I perform a function. So I'm at a loss as to why I keep losing the data on application close?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace GTI_Taxi_Pricing
{
public partial class frmProv : Form
{
    private OleDbConnection connection = new OleDbConnection();
    public frmProv()
    {
        InitializeComponent();
        connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TaxiDB.accdb;Persist Security Info=False;";                
    }

    private void btnLoad_Click(object sender, EventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            String query = "SELECT * FROM Provinces;";
            command.CommandText = query;
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            connection.Close();
        }
        catch(Exception ex)
        {
            MessageBox.Show("Error: " + ex);
        }
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        if (txtName.Text == "")
        {
            MessageBox.Show("The name field must have a value.");
            return;
        }
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "INSERT INTO Provinces (name) VALUES ('" + txtName.Text + "')";

            command.ExecuteNonQuery();
            MessageBox.Show("Data Saved");
            connection.Close();
        }
        catch(Exception ex)
        {
            MessageBox.Show("Error: " + ex);
        }
    }

    private void btnNewRecord_Click(object sender, EventArgs e)
    {
        txtID.Text = "";
        txtName.Text = "";
    }

    private void btnEdit_Click(object sender, EventArgs e)
    {
        if (txtID.Text == "")
        {
            MessageBox.Show("The id field must have a value.");
            return;
        }
        if(txtName.Text == "")
        {
            MessageBox.Show("The name field must have a value.");
            return;
        }
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "UPDATE Provinces SET name='" + txtName.Text + "' WHERE id=" + txtID.Text + ";";

            command.ExecuteNonQuery();
            MessageBox.Show("Data Update Successful.");
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex);
        }
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
        if (txtID.Text == "")
        {
            MessageBox.Show("The id field must have a value.");
            return;
        }
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "DELETE FROM Provinces WHERE id=" + txtID.Text + ";";

            command.ExecuteNonQuery();
            MessageBox.Show("Record Deleted Successfully.");
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex);
        }
    }

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {
        if(e.RowIndex >= 0)
        {
            DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
            txtID.Text = row.Cells[0].Value.ToString();
            txtName.Text = row.Cells[1].Value.ToString();
        }
    }
}

}

推荐答案

这是基于文件的数据库(或附加的数据库文件)的常见方案.
您的连接字符串引用数据库而不使用任何路径.
这意味着您的数据库位于应用程序运行所在的目录中.
在插入，修改或删除数据时没有任何问题，但是当您从Visual Studio调试会话中重新启动应用程序时，就失去了一切.

This is a common scenario with file based database (or attached database files)
Your connection string refers to the database without using any path.
This means that your database is located in the same directory where your application runs.
You don't have any problem inserting, modifying or deleting data but you loose everything when you restart the app from INSIDE a Visual Studio Debug Session.

现在，如果您查看项目文件，则可能在其他文件之间列出了数据库文件.在该数据库文件的属性之间，您会注意到属性Copy to the Output directory及其值设置为Copy Always.

Now, if you look at your project files you probably have the database file listed between the other files. Between the properties of this database file you will notice the property Copy to the Output directory and its value set to Copy Always.

这意味着每次从Visual Studio环境中重新启动应用程序时，该文件都会从项目文件夹复制到输出目录(通常为BIN \ DEBUG或BIN \ x86 \ DEBUG)，但这会破坏上次运行时删除了已插入的已修改或已删除的数据

This means that every time you restart your application from inside the Visual Studio environment that file is copied from the project folder to the output directory (usually BIN\DEBUG or BIN\x86\DEBUG) but this destroys the database used in the previous run removing the data inserted modified or deleted

将属性Copy to Output Directory更改为Copy Never或Copy if Newer

但是Copy If Newer提出了MS-Access的另一个问题.如果使用Access o在Visual Studio的服务器连接"窗口中使用Access o打开位于项目目录中的数据库文件，则即使不进行任何更改，该文件也会立即被修改，因此如果更新则复制"将执行复制到输出目录的操作

However Copy If Newer presents another problem with MS-Access. If you open the database file located in your project directory using Access o using the Server Connection window of Visual Studio the file is immediately modified also if you don't change anything and thus the Copy If Newer will execute the copy to the output directory

这篇关于C#Windows应用程序访问数据库数据不会在关闭时持续存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！