ADO.NET: DataSet or DataTable and how to retrieve data

Question

SO community

I am getting started with ADO.NET and am fairly new to programming. I have had some support already from this community which has been very useful and had another question.

In simple terms, I am creating a simple windows form with a combobox of employee names and a button so that when the user clicks the button the selected employee's email address appears in a textbox on the form.

In my project I have database and on loading the form I connect to my database and create a datatable as shown below:

Public Class GetEmployeeDataset

Private tbl As New DataTable

Public Sub New()
    Dim strConn, strSQL As String
    strConn = "Data Source=.\SQLExpress;Initial Catalog=MyDatabase;Integrated Security=True;"
    strSQL = "SELECT * FROM EmployeeTable"

    Dim da As New SqlDataAdapter(strSQL, strConn)
    da.Fill(tbl)
End Sub

End Class

At this point I have two questions:

1. I am using DataTable as opposed to a DataSet because, as I understand it, a DataSet is a collection of DataTables and I only have one simple DataTable here (5 columns, 100 rows) so it seems easier just to use a DataTable. Anything wrong with that?
2. Now that I have a DataTable ('tbl' in my example) how can I query that DataTable to retrieve the correct data i.e. EmailAddress based on employee name? In SQL it would be "SELECT EmailAddress WHERE EmployeeName = SelectedItem". I have looked at using the Select method of the DataTable but to no avail and then I went down the RowFilter method of DataView but without any success.

Can anybody point me in the right direction please?

Alex

Solution 1

I don't use DataSets or DataTables at all. The only reason I have found to use them is if you want to hold all of the data "offline," make changes to the data, then commit those changes back to the database as needed.

If all you're doing is grabbing data from the server based on user interaction, it may be overkill to use a DataTable.

Edit: Oh, and to answer your actual questions:

1. Sounds fine since you only need one table.
2. Use the Select method on the DataTable (which will return an array of DataRows that satisfy the where condition)

Solution 2

Staying with the ADO objects, this is how you could use the DataView...

  Dim dv As DataView
  Dim strFilter As String
  Dim strEmail As String = ""  

  strFilter = "EmployeeName = '" & cbo.Text & "'"  

  dv = tbl.DefaultView
  dv.RowFilter = strFilter  

  If dv.Count > 0 Then
     strEmail = dv(0).Item("EmailAddress").ToString
  End If

Comments

[Admin]

Thanks for you help. I think this helps me out and I'll give it a go. Alex