More efficient way to work with DataSets

Question

I have the following code repeating on every Form , as part of the update process. When the page loads, the BLL returns a DataSet , say

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

I save this DataSet in a variable of the Form level, which I then use to check for changes during the check / update process. I pass the row at a time (although no more than one row) to Function , which takes a value in the control and compares it with its corresponding column value in the DataSet . If he finds it different, he sets this column = to a new value and adds the name to the List of what has 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 am I trying to get an answer to, is this really the most effective way to do this?

If nothing else, I would like to create a function to compare instead of repeating 30 times (for each form), but I cannot figure it out. I thought maybe I could use the string []. ItemArray, but that only matters. I would have to MEAN ahead of time in what order the items were, and the bank did not change for them ....

Am I missing something obvious to work with DataSets / DataTables in a CRUD application ?

---

juliandewitt's post below is fantastic!

Now I need some direction how to use this in the above. Any links anyone can point me will be appreciated. Even better if you can post an example.

Are there any disadvantages to using DataRows?

Answer 1

It looks like you are doing a lot of manual work that could be facilitated by linking your controls directly to your DataSet / Table. Databinding combines your data source (your dataset / table in this case) with your user interface. When a value is changed in the user interface, it will update the data source.

DataBinding is a BIG topic that requires research and testing. There are some getcha with data binding to DataTable / Set (row changes are not obtained until the current row changes, which is annoying in your case, only working with one row at a time, but there are workarounds).

Paraphrased: Another thing to consider is to use business objects to represent the data in your sets / tables. ORMs (object-relational-mappers) can handle this for you, but they are large and extremely powerful frameworks that are not easy to master. This is a completely different paradigm for working with DataSet / Tables at the user interface level and is more faithful to object-oriented programming. DataSets and Tables are very good for working with tabular data, but they do not work well with objects. For example, you should work against an instance of the Person object with properties like IsHispanic and IsCitizen rahtner, than essentially work with cells in the table (no more than * myPersonTable [0] ["HispanicOriginFlag"] ....).

Next: Not related to your question, but related to CRUD operations revolving around ADO.NET: it pays to familiarize yourself with the state tracking built into the DataTable / DataSet. There are many built-in ADO.NET to help these applications stick together easily, which will clear tons of code, as shown in the figure.

As always, RAD tools have a trade-off that allows you to give up performance control, but writing them off without understanding them ensures that you spend your days writing code, as you showed.

Even more: To continue working on my previous Next, when you discover the possibility of combining the Visual Studio DataSet generator with the built-in row tracking in DataTables and tracking DataSets changes, it can very easily write a complete CRUD system in a short time.

The following is a brief description of some of the steps:

• Install Database Schema
• In Visual Studio, add a new DataSet to the project.
• Find the server explorer (in the view)
• Add SQL Server as a Data Connection
• Drag the table / saved proc / View into the DataSet constructor.
• Right-click the "TableAdapter" that Visual Studio created for you; go to "Customize"
• Configure CRUD Commands for the DataSet (Select, Insert, Update, Delete Commands)

With this, you created a highly typed DataSet. The DataSet will contain a DataTable property named after the table / view / stored procedure used to generate the DataSet. The Table property will contain strongly typed strings that will allow you to access the cells inside this row as properties, rather than untyped elements in an array of objects.

So, if you created a new DataSet called MyDbTables with a table called tblCustomer that contains some columns, such as CustomerId, Name, etc., then you can work with it as follows:

This is a lot of examples, rolled into one, showing some of the common methods used to work with CRUD - check out the methods and especially in 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 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 note the RejectChanges () and AcceptChanges () methods of both the DataSet and DataTables. They essentially tell your dataset that it has no changes (either rejecting all the changes or "making" all the changes), but keep in mind that calling AcceptChanges () and then trying to update will have no effect - DataSet lost track of any changes and suggests that this is an accurate reflection of the database.

And even more! Here's a revised version of your example showing some rowstate tracking functions, assuming you 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(); } 

Answer 2

If you use a consistent naming convention for properties that map to dataset column names, reflection can make a single reusable method possible. However, you need to test it to make sure that it does not cause a performance problem.

To do this, scroll through the collection of columns from the table. Then, for each column, use reflection to find the matching property for the object being compared to the row. (You want to focus on the PropertyInfo class returned from obj.GetType (). GetProperties (...).)

Answer 3

Datarow also automatically tracks changes.

 DataRow _personInfo = ConnectBLL.BLL.Person.GetPerson(personID); // _personInfo.RowState = DataRowState.Unchanged _personInfo["columnName"] = "value"; _personInfo["columnName2"] = "value2"; _personInfo.EndEdit(); // _personInfo.RowState = DataRowState.Modified 

Now you can get the changed values ​​by querying a string and checking the values ​​as follows

 var org = fRow["columnName", DataRowVersion.Original]; var new = fRow["columnName",DataRowVersion.Current]; 

You can also listen to the columnchanged event to detect changes.

 fTable.ColumnChanged += new DataColumnChangeEventHandler(delegate(object sender, DataColumnChangeEventArgs e) { Console.WriteLine(e.Column.ColumnName); } );