Click here to Skip to main content
15,918,808 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am reading a file into a DataTable from Excel, which is structured like so:
Date        Name        Amount      Split
01/01/2014  John Smith  $150.00     
01/01/2014  John Smith  $75.00      
01/01/2014  Bob Smith               $25.00
01/01/2014  John Smith              $50.00


The way that this is structured, the $150 line is one record, while the remaining three rows are one record as well as the two split amounts total to the $75.00. I need to process the single rows differently than the multi-line rows, so I wrote a function to go through and add a column with an ID like so:
C#
int countID = 0;

for (int index = 0; index < dataGridView1.Rows.Count; index++)
{
    if (index + 1 != dataGridView1.Rows.Count)
        if (dataGridView1.Rows[index].Cells["Split"].Value == null ||
            dataGridView1.Rows[index].Cells["Split"].Value.ToString() == "")
            countID++;

    dataGridView1.Rows[index].Cells["ID"].Value = countID.ToString();
}


And this get's me the same number for the rows that are the same, however I was thinking there might be a better way to loop through these, possibly using LINQ or something (my LINQ knowledge is very basic).
Posted
Comments
Maciej Los 24-May-14 15:27pm    
As far i understand that you want to add ID column, which stores the same values for each Name. Am i right?
hpjchobbes 24-May-14 22:02pm    
That's what I'm doing as I think it'll be easier to grab the rows that are the same to process. There are two scenarios with a row. It could either be just one row is a complete set of data, or there could be multiple rows that make up one set of data. The determining factor is if the next row doesn't have a value in the split column, then I know just the one row is complete. If the row below does have a value in the split column, then the data is comprised of 2 - N number of rows, until I get to a row that doesn't have a value in that column.

I implemented a loop through that will assign a made up ID number so I can pull the rows that represent one transaction, but if feels a little 'kludgy' and I have been looking into LINQ and was thinking there is a better way. I'm still fairly new to LINQ, though.
Maciej Los 25-May-14 6:22am    
Not sure we understand each other. Row is a row and data set is data set. Please, do not mix it. One row could be a complete data set only in case when data set contains single record.
Finally, do you want to achieve the same what does your code using Linq?
hpjchobbes 25-May-14 10:13am    
Sorry, when I say a set of data, I don't mean a DataSet, I mean a transaction. This data represents sales commission. A sales transaction can have one or more commission reps assigned to it. In the example, the first row is one sale, while the next three rows are a second sale. The data that I get doesn't come with the sale ID number, so the only way that I know they are related is when I come to a row and the next row doesn't have a value in the Amount field. When that happens, then all the following rows that have a value in the Split column represent one sale.
Maciej Los 25-May-14 13:10pm    
Thank you for explanation. Please, see my answer.

1 solution

hpjchobbes wrote:
The data that I get doesn't come with the sale ID number, so the only way that I know they are related is when I come to a row and the next row doesn't have a value in the Amount field. When that happens, then all the following rows that have a value in the Split column represent one sale.


If you would like to manage data like a database, you need to create relationships between clients and their sales/transactions. Have a look at below 'diagram':
1 client      <----------> ∞ transactions 
1 transaction <----------> ∞ articles/products 
etc.
There is no relationship like one-row-to-rows-below. Think of it: when you change sort order, you'll loose your "relationship".

Conclusion: I'd strongly recommend to read about relational database[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900