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

Answer 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)

Answer 2

A simple enumeration will do the trick:

string email = string.Empty;
foreach (Row row in tbl.Rows)
{
    string employeeName = (string)row["EmployeeName"];
    if (employeeName == "John")
    {
        email = (string)row["Email"];
        break;
    }
}

or you can try LINQ:

var email = (from row in tbl.Rows
            where (string)row["EmployeeName"] == "John"
            select (string)row["Email"]).First ();

I'm not checking the code in VS so "typos reserved". Sorry for C# version but I don't know VB.

Answer 3

1. As a DataSet just stores multiple DataTables and manages relationships and views amongst them you are fine using a ::shudder:: DataTable here.

2. You should consider what, exactly, is going on here. When you say "SELECT * FROM EmployeeTable" you're going to do just that - get all that data from the database and THEN attempt to filter it outside of the database in application code. This is not a good idea for a number of reasons, but for now let's just say that we should let databases perform a job they are really good at and that is filtering and sorting data.

You will likely find Linq to SQL a much, much easier technology to implement rather than suffering through ADO.NET. There are numerous tutorials on the subject so here's one to get you started (though it is older). Linq to SQL will essentially allow you to bring your set-based SQL knowledge forward to the application domain AND give you good performance to boot (i.e. you can start with "SELECT * FROM Employees" and add filters onto it successively such as "WHERE Name = 'Smith'" which ultimately execute on the database).

If you absolutely, positively must use ADO.NET and have to take this approach you can do:

yourDataTable.Select("EmployeeName='" + yourSelectedValue + "'")

Answer 4

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

Answer 5

With 100 records (or 1,000,000 if indexed on employee name) your best option for this specific case is to retrieve the email address as you need it using the ExecuteScalar method of the SqlCommand object. Pulling the whole table across the wire just to get a single field is almost always a bad idea. Select * even with a where clause is usually a bad idea too, for both performance and maintenance reasons.

The following code will get you the EmailAddress for an employee name.

Using cn as new SqlConnection("Data Source=.\SQLExpress;Initial Catalog=MyDatabase;Integrated Security=True;")
    Using cmd as new SqlCommand("SELECT EmailAddress From Employees WHERE EmployeeName = '" & employeeName & "'", cn)
        Return TryCast(cmd.ExecuteScalar(), String)
    End Using
End Using

As others have said, DataTables are for offline storage in the relatively rare occasions when you need it. And you're right, there's no reason to use a DataSet unless you need to offline-cache a whole database (or subset of a database).