How to get specific element in foreach loop in c#?

Question

I have a list and I need to update some of the data in this list.

I have a update T-SQL query:

Update Report
Set ['+@Period+'] = @Amount
Where Group = 1

I think that I can use foreach for this update.

string period=startDate.ToString("yy/MM/dd").Substring(0, 2) + startDate.ToString("yy/MM/dd").Substring(3, 2);

foreach (var item in report)
{
   if (item.Grup == 1)
   {
      item.??? = amount; //My period names in the table like _2101, _2102 etc 
   }
}

I use foreach because I have a while condition outside of the foreach and the update operation will occur for other periods. How can I get the item.period?

--Edit--

My table looks like that and I want to update null values one by one. This method returns the report table.

What does the list look like? Expected output? Please exclude the T-SQL information from the question and just replace it with a demo list similar to your DB data as it contributes nothing to the question and allows others to quickly read through the question — Ermiya Eskandary
– Ermiya Eskandary, Commented Oct 3, 2021 at 18:07
@RobertHarvey period is a variable. I have item._2101, item._2102 . I want to get these ones using something like that item.period — mordeby
– mordeby, Commented Oct 3, 2021 at 18:12
Sounds like you need to put _2101 and _2102 into a collection within item so that you can iterate over it. Hard to tell without seeing what item actually looks like. — Robert Harvey
– Robert Harvey, Commented Oct 3, 2021 at 18:14
@mordeby You mean "How can I get the period property of every item variable?" - I would agree with Robert but hard to say without you adding more information. — Ermiya Eskandary
– Ermiya Eskandary, Commented Oct 3, 2021 at 18:15

New Rhino · Accepted Answer · 2021-10-03 18:50:50Z

It's not a good idea to store periods in columns as such if you're planning to add more. It would be easier to change your table structure so that it can support having its own table for periods.

But if for some reasons you really have to do it. You could get these columns name using the following t-sql (assuming your table is on the [dbo] schema and the columns you need all start with an underscore) :

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Report' AND TABLE_SCHEMA='dbo' AND COLUMN_NAME LIKE '\_%' ESCAPE '\'

Then using these column names you could build a new query with these column names :

"SELECT " + string.Join(',', columnNames) + " FROM Report"

using Dapper you could do the following :

IEnumerable<string> columnNames;

using (var connection = new SqlConnection(dbConnectionString))
{
    columnNames = connection.Query<string>(@"
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Report' AND TABLE_SCHEMA='dbo' AND COLUMN_NAME LIKE '\_%' ESCAPE '\'");
}

IEnumerable<dynamic> reports;

using (var connection = new SqlConnection(dbConnectionString))
{
    reports = connection.Query("SELECT " + string.Join(',', columnNames) + " FROM Report");
}

foreach(var report in reports)
{
    report._2021 = (object)1.0;
}

This way you can access the values of _2101, _2102, etc.

But creating a period table to store these would make 100% more sense.

Collectives™ on Stack Overflow

How to get specific element in foreach loop in c#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related