none
How to make blank rows if respective rows identified in data table in C# RRS feed

  • Question

  • I have data table  and i have many rows in that data table.

    due to some condition i want to make all the columns should blank except first column.

    Example table

    EmpNo   Name     Course   Country

    E101      SAI         Physice  USA

    E102      RAM        Chemistry   Canada

    E!03       SAM        English      Australia

    E104      Ran         Maths        Canada

    i am passing two parameters (EmpNo and Name is the unique  file and passing to where condition)  to do this process in data table using C#..?

    Final Output  (after passing two parameters (empNo(E103) and Name  (Sam))

    EmpNo   Name     Course   Country

    E101      SAI         Physice  USA

    E102      RAM        Chemistry   Canada

    E!03       

    E104      Ran         Maths        Canada

     
    • Edited by Gani tpt Wednesday, January 8, 2020 1:20 AM Final output
    Wednesday, January 8, 2020 12:41 AM

Answers

  • Hi Gani,

    Thank you for posting here.

    You can use LINQ to filter out qualifying rows and then set their fields to empty strings.

    Here is the code:

            static void Main(string[] args)
            {
                DataTable dt = GetDataTable();
                
                DoWork(dt, "E103", "SAM");
            }
            static void DoWork(DataTable dataTable, String empNo, String name)
            {
                var result = from row in dataTable.AsEnumerable()
                             where row["EmpNo"].ToString() == empNo && row["Name"].ToString() == name
                             select row;
    
                foreach (DataRow item in result)
                {
                    for (int i = 1; i < item.ItemArray.Length; i++)
                    {
                        item.SetField<String>(i, "");
                    }
                }
    
            }
            static DataTable GetDataTable()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(String));
                dataTable.Columns.Add("Name", typeof(String));
                dataTable.Columns.Add("Course", typeof(String));
                dataTable.Columns.Add("Country", typeof(String));
    
                dataTable.Rows.Add("E101", "SAI", "Physice", "USA");
                dataTable.Rows.Add("E102", "RAM", "Chemistry", "Canada");
                dataTable.Rows.Add("E103", "SAM", "English", "Australia");
                dataTable.Rows.Add("E104", "Ran", "Maths", "Canada");
                return dataTable;
            }

    Before:

    After:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Wednesday, January 8, 2020 11:57 AM
    Wednesday, January 8, 2020 3:01 AM

All replies

  • Something along these lines.

    private static void Demo(string[] columnNames, string empNo, string name)
    {
        var dt = GetDataTable();
        for (int rowIndex = 0; rowIndex < dt.Rows.Count -1; rowIndex++)
        {
            foreach (var columnName in columnNames)
            {
                if (dt.Columns.Contains(columnName) && dt.Columns[columnName].DataType == typeof(string))
                {
                    if (dt.Rows[rowIndex].Field<string>("EmpNo") == empNo && dt.Rows[rowIndex].Field<string>("name") == name)
                    {
                        dt.Rows[rowIndex][columnName] = "";
                    }                        
                }
            }                
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, January 8, 2020 1:26 AM
    Moderator
  • Thanks a lot...

    In your demo code,

    Will retain the the first column..? (because first column value should be there for reference and remaining should be blank)...??? 

    Wednesday, January 8, 2020 1:35 AM
  • Thanks a lot...

    In your demo code,

    Will retain the the first column..? (because first column value should be there for reference and remaining should be blank)...??? 

    columnNames array would contain names of columns to set to empty values so as long as the first column name is not in the array it will work.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, January 8, 2020 1:49 AM
    Moderator
  • Hi Gani,

    Thank you for posting here.

    You can use LINQ to filter out qualifying rows and then set their fields to empty strings.

    Here is the code:

            static void Main(string[] args)
            {
                DataTable dt = GetDataTable();
                
                DoWork(dt, "E103", "SAM");
            }
            static void DoWork(DataTable dataTable, String empNo, String name)
            {
                var result = from row in dataTable.AsEnumerable()
                             where row["EmpNo"].ToString() == empNo && row["Name"].ToString() == name
                             select row;
    
                foreach (DataRow item in result)
                {
                    for (int i = 1; i < item.ItemArray.Length; i++)
                    {
                        item.SetField<String>(i, "");
                    }
                }
    
            }
            static DataTable GetDataTable()
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("EmpNo", typeof(String));
                dataTable.Columns.Add("Name", typeof(String));
                dataTable.Columns.Add("Course", typeof(String));
                dataTable.Columns.Add("Country", typeof(String));
    
                dataTable.Rows.Add("E101", "SAI", "Physice", "USA");
                dataTable.Rows.Add("E102", "RAM", "Chemistry", "Canada");
                dataTable.Rows.Add("E103", "SAM", "English", "Australia");
                dataTable.Rows.Add("E104", "Ran", "Maths", "Canada");
                return dataTable;
            }

    Before:

    After:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Wednesday, January 8, 2020 11:57 AM
    Wednesday, January 8, 2020 3:01 AM
  • yes. Exactly i want this.

    But, i got one error when i clear the cell value. I don't mention anywhere the column is double data type.

     for (int i = 1; i < item.ItemArray.Length; i++)
                    {
                        item.SetField<String>(i, "");  ==> In column 10th i got error.
                    }

    Error : Additional information: Input string was not in a correct format.Couldn't store <> "salary" in Column.  Expected type is Double.

    how to clear this double data type and error...?

    Wednesday, January 8, 2020 5:40 AM
  • Hi Gani,

    According to the error message, your datatable has a field of type "double" and name "salary".

    This method raised this error when it tried to convert an empty string to a value of type double.

    Modifying the empty string to null can solve this problem.

     item.SetField<String>(i, null);

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, January 8, 2020 6:29 AM
  • No..It will not clear the value rather it will retain the value..

    if i use  "0" then will update as zero. if i use null it will not reflect.

    item.SetField<String>(i, 0); ==> working. But i want to clear the cell.

    item.SetField<String>(i, not); ==> not working. value retain..

    Wednesday, January 8, 2020 10:28 AM
  • No..It will not clear the value rather it will retain the value..

    if i use  "0" then will update as zero. if i use null it will not reflect.

    item.SetField<String>(i, 0); ==> working. But i want to clear the cell.

    item.SetField<String>(i, not); ==> not working. value retain..

    You need to use DBNull.Value

    var dt = new DataTable();
    
    dt.Columns.Add(new DataColumn() { ColumnName = "salary",
        DataType = typeof(double)});
    
    dt.Rows.Add(1.5m);
    
    dt.Rows[0]["salary"] = DBNull.Value;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, January 8, 2020 10:54 AM
    Moderator
  • Thanks...done...
    Wednesday, January 8, 2020 11:57 AM
  • Thanks a lot..it's awesome...
    Wednesday, January 8, 2020 11:58 AM