I average about 15-20 speaking engagements a year. Regardless of the topic, my first PowerPoint slide is a picture of Seargent Joe Friday and Officer Bill Gannon (from the old TV show Dragnet), with the caption "Just the Facts". It's a segue to my philosophy on presentations….plenty of code….and just the facts.
I've covered data handling in stored procedures and ADO.NET in many articles and in my book. I also talk to many people about data-handing. And that's today's topic. I've uploaded a data-handling sample onto my server, ADOProcessingSample.zip.
It's a contrived example from a very common "real-world" situation: reading customer invoices and producing a result set for an aging receivables report. Normally, I'd use the new PIVOT capability in SQL Server 2005 to read invoices into an analytical view for an aging report – but sometimes people can't use (or don't want to use) stored procedures. Maybe all they can do is query raw invoices back into a datatable, and then read through them and put them into the corresponding aging bracket, based on the date of the invoice and the "as of" date.
The sample is a simple .NET project, built using VS2005. The sample demonstrates the following:
- Adding data to strongly-typed rows and looping through the rows
- Performing date math
- Filtering
- Equivalent of a SELECT DISTINCT to get a unique list of accounts from a table
- Computing Subtotals
It also demonstrates other tasks, such as determining which aging bucket to use based on a date range in a table (as opposed to hard-coding).
Now, there's actually a second (and possibly more efficient) way to produce the result set for the report, by looping instead through the smaller bracket range and then filtering on invoices that match the date range. I've intentionally started with the "brute force" approach, to demonstrate some of the ADO.NET constructs that would be necessary in other situations. My next post will provide a second solution, and will use a much much larger dataset.
More reading:
- My book: Pro VS2005 Reporting using SQL Server and Crystal Reports
- Code Magazine article #1 (from 2006) on ADO.NET and data handling
- Code Magazine article #2 (from late 2005) on ADO.NET and data handling
KG
Comments