Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 26 Productivity Tips for Managing Data (Part 1 of 2) : Page 2

Regardless of your .NET language of choice, managing data is a vital skill for most applications.

Tip 2: Date Arithmetic
The .NET Framework classes offer many different capabilities for date arithmetic that you can use to calculate the difference between two dates, determine future dates, and more. Let's take a look at some of them.

// Create two dates, Nov 1 2005 and Oct 1, 2005 DateTime dAgingDate = new DateTime(2005,11,1); DateTime dInvDate = new DateTime(2005, 10, 1); TimeSpan ts = dAgingDate.Subtract(dInvoiceDate); int nDiffDays = ts.Days; // Determine future dates DateTime dNextDay = dtInvoiceDate.AddDays(1); DateTime dNextMonth = dtInvoiceDate.AddMonths(1); DateTime dNextYear = dtInvoiceDate.AddYears(1); // Determine # of days in October 2002 int nDaysInMonth = DateTime.DaysInMonth(2002, 10); // Is the year a leap year? if(DateTime.IsLeapYear(2006)==true) // Determine if a string represents a valid date public bool IsValidDate(string cText) { bool lIsGoodDate = true; // Convert in a try/catch try { DateTime dt = Convert.ToDateTime(cText); } catch (Exception) { lIsGoodDate = false; } return lIsGoodDate; } // Gets today (time will be midnight) DateTime dToday = DateTime.Today; // Get the current date/time DateTime dNow = DateTime.Now;

Look on the Web to find many other date math capabilities. I found an excellent one recently.

Tip 3: Data Relations
As stated in Tip 1, you can more easily manage and work with related DataTables if you define typed datasets. By defining relationships in the DataSet Designer, Visual Studio automatically generates the relation and provides a default method for retrieving child rows in a parent/child relationship. In the following example, a strongly-typed Order Header row object exposes a method called GetdtOrderDtlRows.

dsOrders odsOrders = new dsOrders(); // Run some process to fill the DataSet foreach(dsOrders.dtOrderHdrRow oHdrRow in odsOrders.dtOrderHdr.Rows) foreach(dsOrders.dtOrderDtlRow oDtlRow in oHdrRow.GetdtOrderDtlRows()) nProductPK = oDetailRow.ProductPK;

Tip 4: Filtering Data
ADO.NET provides two different ways to filter data: Use a DataView and a RowFilter, or use a DataTable.Select() call. New .NET developers sometimes ask which technique to use. As you're about to see, it depends on the situation.

ADO.NET provides two different ways to filter data: Use a DataView and a RowFilter, or use a DataTable.Select().
In general, use a DataView for binding filtered results, and use an array of DataRows for scanning through the filtered results to perform processing, calculations, etc.

First, a DataView is simply a view of a DataTable, with a particular filter and sort order. Look at these examples of different filter and sort capabilities.

string cFilter; // Examples of RowFilter cFilter = " Amount > 1000 "; cFilter = "FirstName = 'Ken' OR EmpFlag = true"; cFilter = " City LIKE '%whatever%' "; cFilter = "EmployeeID IN (12,144,54)"; // This assumes a Parent Relation cFilter = "Parent(RelName) = 'National'"; string cSort = "Location ASC, Salary DESC"; DataView Dv = new DataView(MyTable, cFilter, cSort, DataViewRowState.CurrentRows);

In the code snippet above, I used one of the DataView overloads to specify the filter and sort all at once. Sometimes developers will use the overload to merely supply the DataTable and then set the RowFilter and Sort properties in subsequent lines of code. This is actually less efficient and causes additional (and unnecessary) processing. It's better to make one call.

Also notice one of the examples uses a parent relation. If you have established a DataRelation and wish to filter on related parent (or child) rows, you can specify the Parent (or Child) keyword, along with the name of the DataRelation.

Finally, you can create several DataViews on a DataTable, each with their own filter and sort.

By contrast, the results of a DataTable.Select() are a collection of DataRow objects. You can use the same filter and sort syntax that you used for a DataView.

DataRow[] aRows = MyTable.Select(cFilter,cSort);

So when should you use a DataView, and when should you not? The answer is fairly simple. Use a DataView when you want to bind the results of a filter. The DataView implements IEnumerable, ICollection, and IList, making it ideal for databinding. You cannot bind a collection of DataRows that DataTable.Select() returns.

By contrast, however, you can do something with a collection of DataRows that you cannot do with a DataView-you can cast each of the rows as one of the strongly-typed objects from a typed dataset.

dsOrders odsOrder = new dsOrders(); // Run some process to populate orders // Now perform a select and filter DataRow[] aRows = odsOrder.dtOrderHdr.Select(cFilter, cOrder); // We can cast the collection of rows // as the strongly-typed rows from our typed DS foreach (dsOrders.dtOrderHdrRow oRow in aRows) { cOrderNumber = oHeaderRow.OrderNumber;}

So you generally want to use DataViews for binding filtered data, and an array of rows for instances where you need to scan through the rows to perform calculations or other processing.

Occasionally, you'll encounter a situation where you have to copy a DataView back to a DataTable. You may need to copy a DataView to XML, or you may need to bind the results of a DataView to a .NET library or product that does not recognize DataViews (such as Crystal Reports). Visual Studio .NET 2003 does not provide any built-in capabilities to convert a DataView to a DataTable; fortunately, you can build a small but useful generic function to do the job.

public DataTable ViewToTable(DataView dv) { // Create a new table structure from the table // object of the view, then loop through the view // and import the rows into the new table DataTable DtReturn = dv.Table.Clone(); foreach (DataRowView drv in dv) DtReturn.ImportRow(drv.Row); // Note that we have to use ImportRow // There's no such thing as Row.Copy return DtReturn; }

You could modify this code rather easily to create a DataTable from an array of DataRows.

Last, but absolutely not least-Visual Studio 2005 developers can benefit from a new built-in ADO.NET function called ToTable() that converts DataViews to DataTables.

DataTable dtFromView = dv.ToTable();

ToTable also has another capability that will be music to the ears of developers who need the ability to filter on distinct values. Visual Studio 2003 developers commonly asked whether ADO.NET had an equivalent of SQL's SELECT DISTINCT. As Visual Studio 2003 did not natively provide this functionality, developers had to either do without the capability, or they had to write custom code to accomplish the task.

ToTable contains an overload that allows developers to specify a list of columns in a similar manner that one would use in a SELECT DISTINCT statement.

bool lDistinct = true; DataTable dtFromView = dv.ToTable(lDistinct, "Column1", "Column2"); // You can even create a unique list from the // DefaultViewof a DataTable DataTable dtUnique = dtMyTable.DefaultView.ToTable(true, "Column1", "Column2");

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.