如何解决Visual Studio中的数据集和数据库连接问题？ [英] How to resolve issue with DataSet and Database connection in Visual Studio?

问题描述

我正在将VB.NET与MS Access数据库一起使用。有两个相互关联的表。

I am using VB.NET with an MS Access database. There are two tables with a relationship with each other.

我按照以下步骤与数据集和绑定源建立数据库连接。

I followed the following to make a database connection with the dataset and binding Source.

1. 在数据源下添加新的数据源


2. 数据库作为数据源类型


3. 数据集作为数据库模型>>


4. 选择数据连接


5. 在数据库对象下，我选择了想要客户表之类的表，同时也单击了视图


6. 然后完成。
>

7. 现在位于数据源，选择数据集，然后选择客户表，然后将详细信息和数据网格视图拖动到表单


8. 现在运行应用程序。

1. Under Data Source add new Data Source
2. Database as a data source type
3. Dataset as a database model >>
4. Chosen Data connection
5. Under the database object, I selected Table (s) which want for the purpose like customer table also clicked on views
6. Then finish.
7. Now at Data source, selected Dataset then Table of Customers and drag details and data grid view to the form and add buttons for adding, deleting updating the records.
8. Now run the application.

运行应用程序后，但不是查看，添加，向上日期，并从数据库中删除记录。

After running the application, But it's not viewing, adding, updating, and deleting records from/to the database.

向数据库添加记录的代码

Code for adding a record to the database

CustomersBindingSource.AddNew()

用于将记录更新到数据库的代码

Code for updating a record to the database

CustomersBindingSource.EndEdit()
CustomersTableAdapter.Update(SrsbdbDataSet.Customers)

用于从数据库中删除记录的代码

Code for deleting a record from the database

CustomersBindingSource.RemoveCurrent()

我还编辑了app.config文件中的连接字符串，以检查连接字符串问题，但对该问题没有用。

I also edited a connection string from the app.config file to check the connection string issue but not useful for the issue.

请让我知道我在哪里做错了。

Please let me know where I'm doing wrong.

推荐答案

CustomersBindingSource。 AddNew（）

CustomersBindingSource.AddNew()

这不会将记录添加到访问数据库，而是将记录添加到BindingSource的列表中，哪个（在BindingSource上调用EndEdit时）被推送进入 YourDataSetName.Customers DataTable作为新的DataRow-如果要查看 YourDataSetName.Customers 中的所有行您会看到有一些文件（可能是从数据库中下载的，当您启动该应用程序时下载了），并且它们有一个 DataRowState 保持不变，然后添加了一个新的，DataRowState已添加

This doesn't add a record to the access database, it adds a record to the BindingSource's list, which (when EndEdit is called on the BindingSource) is pushed into the YourDataSetName.Customers DataTable as a new DataRow - if you were to look at all the rows in YourDataSetName.Customers you'd see that there are some (downloaded from the db probably, when you started the app) and they have a DataRowState of Unchanged, and then there is the new one you added, with a DataRowState of Added

尚未将任何内容保存到数据库。此数据在数据集的数据表中仅是 ，它是数据库表的客户端表示。它本身不是数据库表。它当然可以比数据库表具有更多或更少的列和不同类型。它只是数据库数据的临时存储；您可以下载，添加，更改，删除，也许保存等。相关的DataRow会跟踪您对数据所做的所有操作，并注意是否已添加/修改/删除/未更改等

Nothing has been saved to the DB yet. This data is only in the dataset's datatable, which is a client side representation of a database table. It is not a database table in and of itself. It can certainly have more or fewer columns and of different types, than the database table. It's just temporary storage for database data; you download some, add some, change some, delete some, maybe save it etc. The relevant DataRow tracks all these things you do to its data and notes whether it is Added/Modified/Deleted/Unchanged etc

TableAdapter是在DataTable和数据库之间来回推送数据的东西

The TableAdapter is the thing that pushes the data back and forth between the DataTable and the database

您调用 CustomersTableAdapter.Update（）要将数据保存到数据库时。将它命名为 Update 代表微软是一个废话，因为它使人们认为它仅执行SQL UPDATE查询；如果它被称为 SaveChanges （后来是； EF使用SaveChanges），那就更清楚了。您只需要记住一个-更新即保存。

You call CustomersTableAdapter.Update() when you want to save the data to the DB. Naming it Update was a crap idea on Microsoft's behalf, because it leads people to think it only performs SQL UPDATE queries; if it had been called SaveChanges (and later it was; EF uses SaveChanges) it would be more clear.. You just have to remember that one - "Update means Save"

因此，您调用 Update（此处是数据表或数据集），并将其所有已修改/删除/添加的行传递给DataTable。 TableAdapter逐行扫描整个DataTable，查看每行的DataRowState。如果已添加，则TableAdapter将调用其内置的INSERT SQL查询来保存行。如果已修改，则执行SQL UPDATE。删除状态会导致SQL DELETE。数据行知道下载的原始数据和现在的数据。有时这对于弄清楚是否有人在我们拥有该行时保存了这一行至关重要，因此我们可以避免用我们的行覆盖他们的更改。

So you call Update(datatable or dataset here) and pass in your DataTable with all its modified/deleted/added rows. The TableAdapter scans the whole DataTable row by row looking at the DataRowState of each row. If it's Added, then the TableAdapter will call its built in INSERT SQL query to save the row. If it's Modified, SQL UPDATE is performed. Deleted state causes an SQL DELETE. A datarow knows the original data that was downloaded and the data as it is now; this is sometimes vital in working out if someone else saved this row in the time we had it, so we can avoid overwriting their changes with ours

在此过程结束时，数据具有保存后，所有行状态都从原来的状态设置为Unchanged（因为现在数据库中的数据相同，因此行数据不再需要保存）。

At the end of this process, the data has been saved, the rowstates have all been set from whatever they were, to Unchanged (because the data in the db is now the same, the row data no longer needs saving).

在编辑文件时，过程的一部分就像出现在文本编辑器选项卡上的小*一样-处于已添加/已修改/已删除状态的数据行具有未保存的更改，需要保存。保存后，状态返回到未更改。我是否提到过应该将TableAdapter.Update命名为Save？

Think of that part of the process as being like the little * that appears on a text editor tab, when you edit the file - a datarow in state Added/Modified/Deleted has unsaved changes that need to be saved. After saving, the state goes back to Unchanged. Did I mention that TableAdapter.Update should have been called Save?

全部，保存的过程是要求编辑控件 EndEdit（），然后向 EndEdit 请求相关的绑定源-这样可确保我们拥有一个数据表，其中所有更改均已提交并可以保存，并且然后调用 tableadapter.Update 。用户输入的可能控件在失去焦点时会提交其编辑内容，因为用户单击保存按钮即可。但是调用endedit可以确保。如果您不确定，请创建一个新表单，将其上的DataGridView放到数据源窗口之外，然后查看保存按钮的连线方式-从内存中执行Validate，EndEdits和UpdateAll（TableAdapterManager，管理TableAdapter，以正确的顺序对其进行调用，以确保父行保存在子行之前）

All in, the process for saving would be to ask the editing control to EndEdit() then ask the relevant bindingsource to EndEdit - this ensures we have a datatable with all changes committed and ready to save, and then call the tableadapter.Update. Probably the control the user was typing in will commit its edits when it loses focus, as the user clicks the save button.. But calling endedit makes sure. If you're uncertain, create a new form, drop a DataGridView on it out of the Data Sources window and take a look how the Save button is wired up - from memory it does a Validate, couple of EndEdits and a UpdateAll (TableAdapterManager, manages TableAdapters, calls Update on them in the right order to make sure that parent rows save before child rows)

如果您开始进行更多修改，则该行状态将再次更改，但就像以前一样，将更改提交到数据库的是TableAdapter.Update（），无论您进行了哪种更改

If you started making more modifications, the row states would change again but just as before, the thing that commits the changes to the DB is TableAdapter.Update() regardless what kind of change you made

最后要注意的是Access是基于文件的数据库。可能您的项目位于例如：

The final thing to watch out for here is that Access is a file based database. Probably you have your project in e.g.:

C:\projects\accesswhatever\

例如，您有访问数据库您的桌面：

And you had your access db on e.g. your desktop:

c:\users\you\desktop\access.mdb

当您将访问数据库连接到事物中时，VS会显示一个冗长而冗长的对话框（没有人会读到；）），其中基本上说 i将db放入您的项目中，然后在构建 时将其复制到bin文件夹中。

When you connected the access db into things, VS presented a long and wordy dialog (that no-one reads ;) ) where it basically says "i'll put the db in your project, and I'll make it copy out to the bin folder when you build".

因此，您单击OK而不考虑它的后果和你建立。您的磁盘现在看起来像：

So you click OK without considering the ramifications of it and you build. Your disk now looks like:

 C:\users\you\desktop\access.mdb                            'call it DB X
 C:\projects\accesswhatever\access.mdb                      'call it DB Y
 C:\projects\accesswhatever\bin\debug\access.mdb            'call it DB Z

您正在运行的程序会将数据保存在最后一个数据库DB Z中。每次构建时（每次单击播放时都可能会发生这种情况，如果您更改了代码），Visual Studio将删除Z并将Y复制到Z。

Your running program will save data in the last one, DB Z. Every time you build (which might happen every time you click play, if you make code changes), visual studio will delete Z and copy Y to Z.

您现在真的很困惑；您的代码表示正在保存。您正在桌面上查看DB X，还是在项目库中查看DB Y，想知道这些数据到底在哪里？

You're now really confused; your code says it's saving. You're looking in either DB X on your desktop, or DB Y in your project base, and wondering where the heck is this data?

它在DB Z中，在<$ c中$ c> bin\debug 文件夹，位于app.exe旁边-只需记住，每次构建时，VS都会擦除已更改的数据库，并用一个干净的数据库替换它。如果要更改此设置，请在解决方案资源管理器中单击数据库，然后在资源管理器中设置复制到输出。来自始终复制 如果更新则复制。现在，它仅在您更改模式时才会复制，因此。.添加一个新表，然后VS将使用一个新表擦除您精心策划的测试数据库。.但更像是OK，因为新的空数据库至少具有您的程序将在没有这些崩溃的情况下出现多余的表：）

It's in DB Z, in the bin\debug folder, next to your app.exe - just remember that every time you build, VS wipes your changed database and replaces it with a clean one from way back when. If you want to change this, click the DB in solution explorer and set "Copy To Output" from "Copy Always" to "Copy If Newer". Now it'll only copy whenever you make a schema change, so.. Add a new table and then VS will wipe your nicely curated test db with a new one.. But it's more like OK because the new empty DB at least has that extra table that your program will crash without :)

这篇关于如何解决Visual Studio中的数据集和数据库连接问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！