C# DataReader to Two Dimensional Array?

[bill_kearns]

OK my programming/accountant friends, this is very long but I need your help again with an ad-hoc report! I am having a hard time getting my brain around solving this part of an application I am programming and was hoping to get your point of view. Maybe I'm going about it all wrong?

I am trying to get multiple column fields from different rows of a DataReader, do some math and output the results.

The table columns I am using in the DataReader are:
[Vendor Name] NvarChar
[Budget Period] NvarChar - Represents beginning or end of a fiscal year (January – June or July – December).
[Fiscal Year] NvarChar - represents the budget year.
[Remaining Balance] Double - The remaining balance for vendor at this point in the year.
[Total Allocated] Double - Vendors total budget allowance for the year

My DataReader returns about 400 rows which detail annual spending of 85 vendors in a 3 year period. Each vendor will usually have 6 records, unless the 3rd year of the report is in the future in this case I insert a record on the fly with $0.00 values to prevent errors.

A sample from 2 vendors would look like this.

[Vendor Name];[Budget Period];[Fiscal Year];[Remaining Balance];[Total Allocated]
VENDOR_01;GH;06/07;0.00;309994.00
VENDOR_01;GH;07/08;0.00;187672.00
VENDOR_01;GJ;07/08;43213.19;366520.00
VENDOR_01;GJ;08/09;0.00;204424.00
VENDOR_01;GK;08/09;111060.63;437325.00
VENDOR_01;GK;09/10;34711.08;246297.00
VENDOR_02;GH;06/07;12567.47;362796.00
VENDOR_02;GH;07/08;1364.03;183603.00
VENDOR_02;GJ;07/08;1524.80;367206.00
VENDOR_02;GJ;08/09;3262.00;189224.00
VENDOR_02;GK;08/09;2802.21;392036.00
VENDOR_02;GK;09/10;7541.04;220533.00

The math I need to output for each vendor would be, in the case of VENDOR_01,

(Row 1, Column 4) + (Row 2, Column 4) = X

The main question is “How do I arrive at X?” and this is where I am stuck.

So in my pseudo-code thinking I am trying to approach it this way.

string m_vendor_name = "";

myDataReader.Read();

m_vendor_name = myDataReader["Vendor Name"].ToString();

int i = 0;
string[,] vendorStats = new string[6, 5];
int ts0 = 0; int ts1 = 0;
while (myDataReader.Read())
{
    if (ts1 == 5)
    {
	ts0++; ts1 = 0; // reset ts1 count.
    }
    if (myDataReader["Vendor Name"].ToString() == m_vendor_name)
    {
	vendorStats[ts0, ts1] = myDataReader["Vendor Name"].ToString(); ts1++;
	vendorStats[ts0, ts1] = myDataReader["Budget Period"].ToString(); ts1++;
	vendorStats[ts0, ts1] = myDataReader["Fiscal Year"].ToString(); ts1++;
	vendorStats[ts0, ts1] = myDataReader["Remaining Balance"].ToString(); ts1++;
	vendorStats[ts0, ts1] = myDataReader["Total Allocated"].ToString(); ts1++;
    }

    vendorStats = new string[6, 5];

    i++;
}

string m_vendor_name = vendorStats[0, 0].ToString();
string m_yrBal1 = vendorStats[0, 3].ToString();
string m_yrBal2 = vendorStats[1, 3].ToString();
string m_yrBal3 = vendorStats[2, 3].ToString();
string m_yrBal4 = vendorStats[3, 3].ToString();
string m_yrBal5 = vendorStats[4, 3].ToString();
string m_yrBal6 = vendorStats[5, 3].ToString();

[mcriscolo]

Is the data coming in to you sorted? Or, can you sort it?

If so, then you can use standard break totaling. That is, you have "holder" variables for the vendor and the budget period. Then you process each row of your set, totaling up the values for your financial fields until the budget period of the current record doesn't match the hold version of the budget period. This means you are about to process a new budget period - you then output a line with the hold vendor, hold budget period and the totals. You then zero out the totals, replace the hold budget period with the current budget period and continue. You will also check to see if/when the vendor changes, and do the same sort of thing.

FInally, when you reach the end of your dataset, you output the values for hold vendor hold budget period and your accumulated totals. No need to create an array of the data, since you will process it as you go.

[bill_kearns]

Thanks this does seem prommising. I have never tried this When I google for terms like "C# SQL break totaling" I don't get much back. So I can't actually see it in action but I think I understand and will give it a shot. I'll let you know either way and post some code for future readers if successful!