ADO.NET: Need help to understand the basics of 'Dataset'

Question

Dear Helpers

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.

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:

    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.

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?

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?

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

Answer 1

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.

Edit: Added sample code

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

Assign ds to the datasource of data grid

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.

Answer 2

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.

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.

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

Answer 3

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.

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.

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

Answer 4

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.

Answer 5

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:

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.