CODEWITHSUNDEEP
c#datatable
Rohan Rao
Rohan Rao

Reputation: 2613

How to update the particular column field value of DataTable with a string?

I am fetching the dates from a table into an IEnumerable using this code:

 IEnumerable<DateTime> obj = ds.Tables[0].AsEnumerable().Select(x => x.Field<DateTime>("WeekOffDays")).ToArray();

The obj shows the following data: 2020-01-04 12:00:00 AM 2020-01-05 12:00:00 AM 2020-01-18 12:00:00 AM 2020-01-19 12:00:00 AM

Also these are the dates of a pivot table:

enter image description here

What I want is, all the dates in variables obj that matches with the above table should have string value as "WO".

I have this:

    public DataTable GetHolidays(DataSet ds, List<string> dates, DataTable dt)
    {

    for (int i = 0; i <= dates.Count - 1; i++)
    {

        bool weekOff = ds.Tables[0].AsEnumerable().Any(x => dates.Contains(x.Field<DateTime>("WeekOff").ToString("yyyy-MM-dd")));

        //select the dates available
        IList<DateTime> obj = ds.Tables[0].AsEnumerable().Select(x => x.Field<DateTime>("WeekOff")).ToArray();

        if (weekOff)
        {
            // what to write to update the datatable value with "WO" for dates in **obj**


            //string wo = dates[i].ToString();
            //dt.Columns.Cast<DataColumn>().Where(r => r.ColumnName == wo).FirstOrDefault().DefaultValue = "WO";  // this doesn't work. No errors, but the field value doesn't get updated with WO


        }

    }

The line: dt.Columns.Cast().Where(r => r.ColumnName == wo).FirstOrDefault().DefaultValue = "WO";

this doesn't work. No errors, but the data table doesn't get updated with string WO

Upvotes: 0

Views: 337

Answers (1)

jdweng
jdweng

Reputation: 34431

Try code like below :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Division", typeof(string));
            dt.Columns.Add("Date", typeof(DateTime));
            dt.Columns.Add("Value", typeof(int));

            dt.Rows.Add(new object[] { 1, "abc", "A", new DateTime(2020, 1, 2), 2 });
            dt.Rows.Add(new object[] { 2, "abc", "B", new DateTime(2020, 1, 3), 3 });
            dt.Rows.Add(new object[] { 3, "def", "C", new DateTime(2020, 1, 4), 5 });
            dt.Rows.Add(new object[] { 4, "def", "A", new DateTime(2020, 1, 5), 6 });
            dt.Rows.Add(new object[] { 5, "ghi", "B", new DateTime(2020, 1, 6), 5 });
            dt.Rows.Add(new object[] { 6, "ghi", "C", new DateTime(2020, 1, 7), 4 });
            dt.Rows.Add(new object[] { 7, "klm", "D", new DateTime(2020, 1, 8), 3 });
            dt.Rows.Add(new object[] { 8, "klm", "A", new DateTime(2020, 1, 2), 4 });
            dt.Rows.Add(new object[] { 9, "123", "A", new DateTime(2020, 1, 5), 7 });
            dt.Rows.Add(new object[] { 10, "456", "A", new DateTime(2020, 1, 2), 4 });
            dt.Rows.Add(new object[] { 11, "789", "F", new DateTime(2020, 1, 7), 3 });
            dt.Rows.Add(new object[] { 12, "345", "A", new DateTime(2020, 1, 2), 2 });
            dt.Rows.Add(new object[] { 13, "794", "D", new DateTime(2020, 1, 9), 5 });
            dt.Rows.Add(new object[] { 14, "stu", "A", new DateTime(2020, 1, 12), 7 });
            dt.Rows.Add(new object[] { 15, "afg", "E", new DateTime(2020, 1, 12), 8 });
            dt.Rows.Add(new object[] { 16, "hij", "A", new DateTime(2020, 1, 2), 1 });

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Name", typeof(string));
            pivot.Columns.Add("Division", typeof(string));

            DateTime[] dates = dt.AsEnumerable().Select(x => x.Field<DateTime>("Date")).OrderBy(x => x).Distinct().ToArray();

            foreach (DateTime date in dates)
            {
                pivot.Columns.Add(date.Date.ToString(), typeof(int));
            }

            var groups = dt.AsEnumerable().GroupBy(x => new { name = x.Field<string>("name"), division = x.Field<string>("Division") }).ToList();

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Name"] = group.Key.name;
                newRow["Division"] = group.Key.division;
                foreach (DataRow row in group)
                {
                    string date = row.Field<DateTime>("Date").Date.ToString();
                    newRow[date] = row.Field<int>("Value");

                }


            }

        }
    }
}

Comments : There are a lot of variations to the code I posted.

1) I put results into a new table "var groups = dt.AsEnumerable().

2) I also assumed only one value for each cell. I used integer for cell values in columns with date. If you have more than one value per cell then change line to newRow[date] += row.Field("Value"); If you have strings then make following changes

a) dt.Columns.Add("Value", typeof(string));

b) pivot.Columns.Add(date.Date.ToString(), typeof(string));

c) newRow[date] = string.Join(",",newRow[date].Concat(row.Field("Value")));

3) I only put dates that existed into pivot table. You can put every date with following code

            DateTime startDate = new DateTime(2020, 1, 4);
            DateTime endDate = new DateTime(2020, 2, 4);
            for(DateTime date = startDate; date <= endDate; date = date.AddDays(1))
            {
                pivot.Columns.Add(date.Date.ToString(), typeof(int));  //or change to string
            }

Upvotes: 1

Related Questions