CODEWITHSUNDEEP
c#foreach
mordeby
mordeby

Reputation: 77

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

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.

enter image description here

Upvotes: 1

Views: 840

Answers (1)

New Rhino
New Rhino

Reputation: 339

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.

Upvotes: 1

Related Questions