A more efficient way to work with DataSets

Question

I have the following code, repeated on each Form, as part of the Update process. When the page loads the BLL returns a DataSet, say

_personInfo = ConnectBLL.BLL.Person.GetPerson(personID);

I store that DataSet in a Form level variable which I then use to check against for changes during the Validate/Update process. I pass a row at a time(though there is never more then one row) to a Function that takes the value in a control and compares it to its corresponding column value in the DataSet. If it finds it different then it sets that column = to the new value and adds the name to a List of what's changed.

// Load Person info
        using (var tmpPersonDT = tmpPersonDS.Tables[0])
        {
            if (tmpPersonDT.Rows.Count > 0)
            {
                foreach (DataRow row in tmpPersonDT.Rows)
                {
                    CheckPersonData(row);
                }

            }
        }

// Snippet of the CheckPersonData() that is being called....
    if (!object.Equals(row["ResidencyCountyID"], lkuResidenceCounty.EditValue))
    {
        row["ResidencyCountyID"] = lkuResidenceCounty.EditValue;
        _whatChanged.Add("ResidencyCounty");
    }

if (!object.Equals(row["ResponsibilityCountyID"], lkuResponsibleCounty.EditValue))
{
    row["ResponsibilityCountyID"] = lkuResponsibleCounty.EditValue;
    _whatChanged.Add("ResponsibilityCounty");
}

if (!object.Equals(row["HispanicOriginFlag"], chkHispanic.EditValue))
{
    row["HispanicOriginFlag"] = chkHispanic.EditValue;
    _whatChanged.Add("HispanicOriginFlag");
}

if (!object.Equals(row["CitizenFlag"], chkCitizen.EditValue))
{
    row["CitizenFlag"] = chkCitizen.EditValue;
    _whatChanged.Add("CitizenFlag");
}

if (!object.Equals(row["VeteranFlag"], chkVeteran.EditValue))
{
    row["VeteranFlag"] = chkVeteran.EditValue;
    _whatChanged.Add("VeteranFlag");
}

What I am trying to get an answer to, is this really the most efficient way of going about this?

If nothing else I would like to create a function to do the comparing instead of repeating 30 times(per form varies) but I can't quite figure that out. I thought maybe I could use row[].ItemArray but that only has the values. I would have to KNOW ahead of time what order the items were in and bank on them not changing....

Am I missing something obvious for working with DataSets/DataTables in a CRUD app?

---

juliandewitt's post below is fantastic!

I just, now, need some direction on how to use that in the above. Any links that anyone can point me to would be appreciated. Even better if you can post an example.

Are there any drawbacks to using DataRows like that?

Answer 1

It looks like you're doing a lot of manual labor which could be alleviated by Databinding your controls directly to your DataSet/Table. Databinding plumbs together your datasource (your dataset/table in this case) with your UI. When the value changes in the UI it will update the datasource.

DataBinding is a BIG topic that warrants researching and testing. There are some gotcha's with databinding to a DataTable/Set (the row changes don't get commited until the current row changes, which is annoying in your case of only working with a single row at a time--but there's workarounds).

Reworded: Another thing to consider is using business objects to represent the data in in your Sets/Tables. ORM (object-relational-mappers) can handle this for you, but they are large and hugely powerful frameworks that aren't easy to master. It's an entirely different paradigm from working with DataSet's/Tables at the UI layer and is more true to object-oriented programming. DataSets and Tables are very good for working with tabular data, but they don't lend themselves too well to working with entities. For example, you would work against an instance of a Person object with properties like IsHispanic and IsCitizen rahtner than essentially working against cells in a table (no more *myPersonTable[0]["HispanicOriginFlag"]....).

Further: Unrelated to your question, but relevant to CRUD operations revolving around ADO.NET: it pays to become familiar with the state-tracking built into a DataTable/DataSet. There's lots build into ADO.NET to help make these apps easy to glue together, which would clean up tons of code like you've shown.

As always RAD tools have the trade-off of giving up control for productivity--but writing them off without understanding them is guaranteeing that you will spend your days writing code like you've shown.

Even More: To build further on my previous Further, when you discover the ability to combine Visual Studio's DataSet generator with the built-in rowstate tracking of DataTables, and change-tracking of DataSets it can be very easy to write a full CRUD system in little time.

Here's a quick run-down on some of the steps involved:

1. Establish your database schema
2. In Visual Studio add a new DataSet item to a project
3. Find the Server Explorer (Under View)
4. Add your SQL Server as a Data Connection
5. Drag your table / stored proc / View into the DataSet's designer.
6. Right-click the "TableAdapter" that Visual Studio has generated for you; go to Configure
7. Configure the CRUD commands for the DataSet (the Select, Insert, Update, Delete commands)

With that you've created a Strongly-Typed DataSet. The DataSet will contain a DataTable property named after the table / view / stored procedure used to generate the DataSet. That Table property will contain strongly-typed rows, which lets you access the cells within that row as properties rather than untyped items in an object array.

So if you've generated a new DataSet named MyDbTables, with a table named tblCustomer which contains some columns like CustomerId, Name, etc... then you can work with it like this:

This is a variety examples rolled into one, showing some of the common methods used for CRUD work--look into the methods and particulary into the TableAdapter class

    public void MyDtDemo()
    {
        // A TableAdapter is used to perform the CRUD operations to sync the DataSet/Table and Database
        var myTa = new ClassLibrary4.MyDbTablesTableAdapters.tblCustomersTableAdapter();
        var myDataSet = new MyDbTables();

        // 'Fill' will execute the TableAdapter's SELECT command to populate the DataTable
        myTa.Fill(myDataSet.tblCustomers);

        // Create a new Customer, and add him to the tblCustomers table
        var newCustomer = myDataSet.tblCustomers.NewtblCustomersRow();
        newCustomer.Name = "John Smith";
        myDataSet.tblCustomers.AddtblCustomersRow(newCustomer);

        // Show the pending changes in the DataTable
        var myTableChanges = myDataSet.tblCustomers.GetChanges();

        // Or get the changes by change-state
        var myNewCustomers = myDataSet.tblCustomers.GetChanges(System.Data.DataRowState.Added);

        // Cancel the changes (if you don't want to commit them)
        myDataSet.tblCustomers.RejectChanges();

        // - Or Commit them back to the Database using the TableAdapter again
        myTa.Update(myDataSet);
    }

Also, pay attention to the RejectChanges() and AcceptChanges() methods of both DataSets and DataTables. They essentially tell your dataset that it has no changes (either by rejecting all changes, or 'commiting' all changes), but be aware that calling AcceptChanges() and then trying to do an update will have no effect--the DataSet has lost track of any changes and assumes it is an accurate reflection of the Database.

And even more! Here's a reworked version of your example showing some of the rowstate tracking features, assuming you've followed my steps to create strongly-typed DataSets/Tables/Rows

    public void CheckRows()
    {
       MyPersonDS tmpPersonDS = new MyPersonDS();

        // Load Person info
       using (var tmpPersonDT = tmpPersonDS.PersonDT)
       {
           foreach (MyPersonRow row in tmpPersonDT.Rows)
           {
               CheckPersonData(row);
           }
       }

    }

    public void CheckPersonData(MyPersonRow row)
    {
        // If DataBinding is used, then show if the row is unchanged / modified / new...
        System.Diagnostics.Debug.WriteLine("Row State: " + row.RowState.ToString());

        System.Diagnostics.Debug.WriteLine("Row Changes:");
        System.Diagnostics.Debug.WriteLine(BuildRowChangeSummary(row));

        // If not DataBound then update the strongly-types Row properties
        row.ResidencyCountyID = lkuResidencyCountyId.EditValue;


    }

    public string BuildRowChangeSummary(DataRow row)
    {
        System.Text.StringBuilder result = new System.Text.StringBuilder();

        int rowColumnCount = row.Table.Columns.Count;
        for (int index = 0; index < rowColumnCount; ++index)
        {
            result.Append(string.Format("Original value of {0}: {1}\r\n", row.Table.Columns[index].ColumnName, row[index, DataRowVersion.Original]));
            result.Append(string.Format("Current  value of {0}: {1}\r\n", row.Table.Columns[index].ColumnName, row[index, DataRowVersion.Current]));

            if (index < rowColumnCount - 1) { result.Append("\r\n"); }
        }

        return result.ToString();
    }