ADO.NET：需要帮助来理解“数据集”的基础知识

As context, I am new to ADO.NET and have been using 'Programming ADO.NET 2.0' by David Sceppa to help build my knowledge.

作为上下文,我是ADO.NET的新手,并且一直使用David Sceppa的“Programming ADO.NET 2.0”来帮助我建立自己的知识。

I have been trying to understand the Dataset object but think I may have completely misunderstood the point and am looking for guidance.

我一直试图理解数据集对象,但认为我可能完全误解了这一点并且正在寻找指导。

As an example, I have built a really simple Form with a combobox with an aim of filling the combobox with the names of people in a database ("MyDatabase"). The following code works fine for me:

举个例子,我用一个组合框构建了一个非常简单的Form,目的是用数据库中的人名(“MyDatabase”)填充组合框。以下代码适用于我:

    Private Sub frmEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim strConn, strSQL As String
    strConn = "Data Source=.\SQLExpress;Initial Catalog=MyDatabase;Integrated Security=True;"
    strSQL = "SELECT LastName, FirstName FROM EmployeeTable"

    Dim da As New SqlDataAdapter(strSQL, strConn)
    Dim ds As New DataSet()
    da.Fill(ds, "AllEmployeesList")

    For i As Integer = 0 To ds.Tables("AllEmployeesList").Rows.Count - 1
        Dim row As DataRow = ds.Tables("AllEmployeesList").Rows(i)
        cbAllEmployeesList.Items.Add(row("LastName") & ", " & row("FirstName"))
    Next

End Sub

Now suppose I have a button on my Form ('GetAge') which is designed to retrieve the age of the employee selected in the combobox from the dataset "AllEmployeesList" and display in a TextBox on the same Form.

现在假设我的表单('GetAge')上有一个按钮,用于从数据集“AllEmployeesList”中检索组合框中所选员工的年龄,并显示在同一表单上的TextBox中。

The bit I really don't understand is how I can interact with the original dataset that I have created to get the age? It seems to me that the dataset is only in memory during the Load event? If my dataset persists beyond the Load event then where can I find it?

我真的不明白的是我如何与我创建的原始数据集进行交互以获得年龄?在我看来,数据集只在Load事件期间在内存中?如果我的数据集持续超出Load事件,那么我在哪里可以找到它?

My understanding is that a dataset object is an offline cache of data and has no links to the underlying database.This is useful as it allows you to manipulate the data without keeping a connection open and later on you can submit any changes in the Dataset back to the original database. So once I have built my dataset in the Load event how can I then further interact with it?

我的理解是数据集对象是数据的脱机缓存,并且没有到底层数据库的链接。这很有用,因为它允许您在不保持连接打开的情况下操作数据,稍后您可以在数据集中提交任何更改到原始数据库。因此,一旦我在Load事件中构建了我的数据集,我该如何进一步与它进行交互?

I suspect there is a large error in my understanding of what a Dataset object is. Can anybody set me straight?

我怀疑我对数据集对象的理解存在很大的错误。任何人都可以让我直截了当吗?

Thanks to anybody who can help

感谢任何可以提供帮助的人

Alex

5 个解决方案

#1

A data set can hold multiple data tables, so if you fill that same dataset that already has the "AllEmployeesList" datatable filled, you can fill another datatable with the age under another table name. Picture a dataset as an in-memory database.

数据集可以包含多个数据表,因此如果填充已填充“AllEmployeesList”数据表的相同数据集,则可以使用另一个表名称下的年龄填充另一个数据表。将数据集描绘为内存数据库。

You can store the dataset in the datasource of the datagrid view, or make it a form level variable so you can interact with it without casting anytime.

您可以将数据集存储在datagrid视图的数据源中,也可以将其设置为表单级变量,以便您可以随时与其进行交互。

Another part of datasets to be aware of is you can make a design-time dataset so things are more type-safe and explicit.

要注意的数据集的另一部分是您可以创建设计时数据集,以便更安全,更明确。

#2

You seem to have a good grasp on the concept and reason of the DataSet. Your question is really more about managing state than the ins and outs of a DataSet.

您似乎很好地掌握了DataSet的概念和原因。您的问题更多的是关于管理状态而不是DataSet的细节。

You never stated if you are using WebForms, WinForms, or something else. If you're using WinForms, promote the DataSet to be a member variable of the form. It'll stay in memory as long as the form is open.

如果您使用的是WebForms,WinForms或其他内容,您从未说过。如果您正在使用WinForms,请将DataSet提升为表单的成员变量。只要表格打开,它就会留在记忆中。

If you're using WebForms, then this becomes much more complex. This is a good overview to get you started.

如果您正在使用WebForms,那么这将变得更加复杂。这是一个很好的概述,可以帮助您入门。

#3

Unless your application needs to operate in a disconnected mode, it's not strictly necessary nor always a good idea to cache database data on the client. In this case, you're extracting the age data for all employees without knowing whether you'll ever need it for any of them.

除非您的应用程序需要在断开连接模式下运行,否则在客户端上缓存数据库数据并不是绝对必要的,也不总是一个好主意。在这种情况下,您将为所有员工提取年龄数据,而不知道您是否需要任何员工。

I would just pull the first and last name data (probably using SqlCommand.ExecuteReader) to populate the list box, and then make a separate call to the database to get the age if the user clicks the button. I posted an example of something similar using SqlCommand.ExecuteScalar on your other question.

我只需要拉取名字和姓氏数据(可能使用SqlCommand.ExecuteReader)来填充列表框,然后单独调用数据库以获取用户单击按钮时的年龄。我在你的另一个问题上发布了一个使用SqlCommand.ExecuteScalar类似的例子。

#4

When a Function or Sub has finished executing, all the variables you declared with the Dim statement will be gone. If you want a variable to exist as long as your form exists then declare a variable outside your Sub/Function:

当Function或Sub完成执行时,使用Dim语句声明的所有变量都将消失。如果您希望变量存在,只要您的表单存在,那么在Sub / Function之外声明一个变量:

Public Class frmEmployee
    Private dsEmployeeList As DataSet

    Private Sub frmEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    ...
    dsEmployeeList = New DataSet()
    da.Fill(dsEmployeeList, "AllEmployeesList")
    ...
    End Sub

    Private Sub GetAge_Click(sender As Object, e As EventArgs) Handles GetAge.Click
        Dim iRowIndex As Integer

        iRowIndex = cbAllEmployeesList.SelectedIndex 'In this case the rownumber is the same as the index of the selected item in the combobox

        'Check to see if an item from the combobox has been selected
        If iRowIndex >= 0 Then
            txtEmployeeAge.Text = dsEmployeeList.Tables("AllEmployeesList").Rows(iRowIndex).Item("Age").ToString()
        End If
    End Sub

This code might work but it's not a recommended solution. Like the previous poster said: only get the data you want, when you need it.

此代码可能有效,但不是推荐的解决方案。就像上一张海报所说的那样:只在你需要的时候得到你想要的数据。

#5

You should bind the DataGrid to the DataSet. When reqd you can retrieve the DataSet back from the DataGrid.DataSource and cast it to a DataSet.

您应该将DataGrid绑定到DataSet。当reqd时,您可以从DataGrid.DataSource返回DataSet并将其强制转换为DataSet。

Edit: Added sample code

编辑:添加示例代码

    DataSet ds = new DataSet();
    // Code to populate DataSet from your DB
    ...
    ...

Assign ds to the datasource of data grid

将ds分配给数据网格的数据源

this.dataGridView1.DataSource = ds;

To retrieve the dataset use the code below

要检索数据集,请使用以下代码

DataSet retrievedFromGrid = (DataSet)this.dataGridView1.DataSource;

However, if you need to perform operations on this DataSet a number of times and memory is not an issue, I would suggest you store it in a class variable to avoid the overhead of casting in a DataSet object from the DataGrid again and again.

但是,如果您需要多次对此DataSet执行操作并且内存不是问题,我建议您将其存储在类变量中,以避免一次又一次地从DataGrid中插入DataSet对象的开销。

#1