Maximum value in datatable column c#

Question

I have a set of text files that I am reading into a datatable. I want to be able to read the frist column (Id) and find out the highest number. Each of the files goes from 0 to at least 21 sequentially. I tried suggestions from the following link: How to select min and max values of a column in a datatable?

Sadly, I could not any to work. The one suggestion that kind of worked is shown in the second last line, but it returns a value of 8 or 9. Any suggestions as to how to properly get the results I am looking for?

        string filePath = System.IO.Path.GetFullPath(curriculum);
        DataTable curriculmDataTable = new DataTable();

        curriculmDataTable.Columns.Add("Id");
        curriculmDataTable.Columns.Add("Course");
        curriculmDataTable.Columns.Add("Credit");

        // Read in a file line-by-line, and store it
        var txtFileLine = File.ReadAllLines(filePath).ToList();

        //Reads line splits data to colums at tab (ASCII value 9)
        txtFileLine.ForEach(line => curriculmDataTable.Rows.Add(line.Split((char)9)));


        //Suggestions from link
        int max = Convert.ToInt32(curriculmDataTable.Select("Id=max(Id)")[0][0]);   
        label1.Text = ""+ max;

Tim Schmelter · Accepted Answer · 2013-04-19T23:16:30.940

4

The problem is that you have created string columns but you want to get the max-values according to their numeric value. The best way is to store the corrrect type in the first place. Then you could either use DataTable.Compute or Linq-To-DataSet:

create an int column:

 curriculmDataTable.Columns.Add("Id", typeof(int));

convert the strings to int and add them to the table:

foreach(string line in File.ReadLines(filePath))
{
    DataRow row = curriculmDataTable.Rows.Add();
    string[] fields = line.Split(new[]{(char)9});
    int id;
    if(fields.Length == 3 && int.TryParse(fields[0], out id)
    {
        row.SetField("Id", id);
        row.SetField("Course", fields[1]);
        row.SetField("Credit", fields[2]);
    }
}

Now you can use Linq:

int maxID = curriculmDataTable.AsEnumerable().Max(r => r.Field<int>("Id"));

DataTable.Compute (works also with earlier .NET versions):

int maxID = (int)curriculmDataTable.Compute("Max(Id)", "")

edited Apr 19 '13 at 23:16

answered Apr 19 '13 at 20:56

Tim Schmelter

450,073
74
686
939

I tried what you suggested but it is throwing an error at me. At the foreach, I'm getting a System.NullReferenceException was unhandled Message=Object reference not set to an instance of an object. – user2297683 Apr 19 '13 at 23:00
Where do you get the exception, what object is `null`? – Tim Schmelter Apr 19 '13 at 23:03
I have a feeling it is due to my lack of knowledge, but txtFileLine is highlighted. – user2297683 Apr 19 '13 at 23:08
Then initialize it with `File.ReadAllLines(filePath)` or better, because it doesn't need to create a new collection in-memory, `File.ReadLines(filePath)`. Note that i've just edited my answer, see [revision history](http://stackoverflow.com/posts/16113368/revisions). – Tim Schmelter Apr 19 '13 at 23:12
I didn't end up needing your last reply. The problem was where I placed the foreach section (before the line var txtFileLine = File.ReadAllLines(filePath).ToList(); //Reads line splits data to colums at tab (ASCII value 9) txtFileLine.ForEach(line => curriculmDataTable.Rows.Add(line.Split((char)9)));) when I reordered it, things worked as you said they would. Thank you. – user2297683 Apr 19 '13 at 23:21
@user2297683: Note that my code uses less memory, `File.ReadAllLines(filePath).ToList()` creates first an array of all lines in the file, then `ToList` creates another `List` in memory. So it's more likely to get an `OutOfMemoryException` sometime. `File.ReadLines` on the other hand works like a `StreamReader`, it doesn't create anything new. – Tim Schmelter Apr 19 '13 at 23:29
Here are the first couple of lines from the txt file. – user2297683 Apr 19 '13 at 23:41
I went totally with your code, but now something else doesn't work. in a different method, I had the following: DataRow selectedDataRow = ((DataRowView)comboBoxCourseChoices.SelectedItem).Row; courseName = selectedDataRow["Course"].ToString(); creditValue = Convert.ToInt32(selectedDataRow["Credit"]);<==this line now throws the error:System.InvalidCastException was unhandled by user code Message=Object cannot be cast from DBNull to other types. – user2297683 Apr 19 '13 at 23:53
Here are the first couple of lines from the txt file:1 AST 117 Keyboarding for Computer Usage 1 2 BUS 100 Introduction to Business 3 3 ENG 131 Technical Report Writing 3 – user2297683 Apr 19 '13 at 23:54
@user2297683: If the field can be null use the strongly typed `Field` extension method which also support nullable types. For example: `int? creditValue = selectedDataRow.Field("Credit"); if(creditValue.HasValue)//...`. If you want to use the oldschool way use `selectedDataRow.IsNull("Credit")` to check if it's `DBNull.Value`. – Tim Schmelter Apr 20 '13 at 00:03
I have to walk away for the night. Thanks for your advice/help. I learn by doing and errors, and I make a lot of errors! – user2297683 Apr 20 '13 at 00:13
can you take a look at http://stackoverflow.com/questions/16116108/getting-an-invalidcastexception-was-unhandled it is part of this thread, but I wanted to post some code as to how I'm actually planning to use it, and I've got an InvalidCastException was unhandled. Thanks for helping me learn. – user2297683 Apr 20 '13 at 03:09

score 0 · Answer 2 · answered Apr 11 '15 at 06:20

0

We can get max value from the column in a dataTable using this syntax

var maxValue = dataTblDetails.Compute("max(ColumnName)", string.Empty);

answered Apr 11 '15 at 06:20

Praveen

9
2

Maximum value in datatable column c#

2 Answers2