none
find min and max values in a datatable using C#

    Question

  • Hello,
    I am searching for codes that could find the min and max(or first and last values) from a column in a datatable.

    I have stored the datatable with four column values i want to find the min and max values from the third column(index 2) and display it to the user.
    I tried many ways but all are causing exceptions...

    Last i tried this code but even this is not working..

     

    count = Convert.ToInt32(dt.Rows.Count);
    start = Convert.ToInt32(dt.Rows[0][2].ToString());
    end = Convert.ToInt32(dt.Rows[count-1][2].ToString());

    thanks

    vince

    Friday, July 01, 2011 7:45 PM

Answers

  • Hi Vince

    Plz try this

     

    int minAccountLevel = int.MaxValue; 
    int maxAccountLevel = int.MinValue; 
    foreach (DataRow dr in table.Rows) 
    { 
       
    int accountLevel = dr.Field<int>("AccountLevel"); 
        minAccountLevel
    = Math.Min(minAccountLevel, accountLevel); 
        maxAccountLevel
    = Math.Max(maxAccountLevel, accountLevel); 
    } 

    *note : accountlevel here is your column


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Friday, July 01, 2011 9:48 PM
  • As Sheng told you (and I hope you fill dataTable from dataBase), you shoud use sql query to get min and max value, like:

    "SELECT Min(ColumnName) FROM MyTable"

    The same goes for minimum.

     

    But if you insist to get the values from dataTable, you can use Compute method:

        DataTable table;
        public Form1()
        {
          InitializeComponent();
          table = new DataTable("myTable");
          table.Columns.Add("Column 1", typeof(int));
    
          table.Rows.Add(3);
          table.Rows.Add(4);
          table.Rows.Add(2);
        }      
    
        private void button1_Click(object sender, EventArgs e)
        {
          int maxValue = (int)table.Compute("MAX([Column 1])", ""); //computing the max value
        } 
    


     


    Mitja
    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Friday, July 01, 2011 11:18 PM
  • SQL is better for finding max and min as Sheng  said.

    You can create a stored procedure from where you can get table data and also maximum and minimum value.

    In stored procedure you have to use 2 output parameter one for max and another for minimum.

    Consider we have following table

    EmployeeID  EmployeeName       BasicSalary  TotalSalary
    ----------- ----------------- -------------------    -------------
    1                 Hasibul             35000.00           40000.00
    2                 vincezed           100000.00         150000.00
    3                 Tst                   500.00               700.00

     

    Now we will find out Max and Min  from stored procedure

    Consider following sotred procedure

    /*
    Declare
    @Max decimal(18,2), @Min decimal(18,2)
    exec empSalary_Max_Min @Max output, @Min output
    Select @Max Max, @Min MIn
    */
    
    
    Create Procedure empSalary_Max_Min
    (
    	 @MaxVal		decimal(18,2)	output
    	,@MinVal		decimal(18,2)	output
    )
    AS
    
    -- For Getting List
    Select * from empSalary
    
    -- For Getting Max Value
    Select @MaxVal = MAX(empSalary.BasicSalary) from empSalary
    
    -- For Getting Min Value
    Select @MinVal = MIN(empSalary.BasicSalary) from empSalary
    
    

    Now we will use this stored procedure for getting Table data and Max,Min value

    Here is the C# Code for getting value from stored procedure

    // Your DB Con string
          string cons = "user id=sa;password=start777; initial catalog = test_db; data source= hasibul-pc";
          SqlConnection con = new SqlConnection(cons);
          con.Open();
    
    
          //sql command : empSalary_Max_Min is procedure name
          SqlCommand cmd = new SqlCommand("empSalary_Max_Min", con);
          cmd.CommandType = CommandType.StoredProcedure;
    
    
          // Define @MaxVal and @MinVal as output parameter
          SqlParameter param = new SqlParameter();
          param.ParameterName = "@MaxVal";
          param.Size = 20;
          param.Direction = ParameterDirection.Output;
          cmd.Parameters.Add(param);
    
          param = new SqlParameter();
          param.ParameterName = "@MinVal";
          param.Size = 20;
          param.Direction = ParameterDirection.Output;
          cmd.Parameters.Add(param);
    
    
    
          DataTable dt = new DataTable();
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          da.Fill(dt); // Getting Table record
    
    
          // Getting max and min value
          string strMax = cmd.Parameters["@MaxVal"].Value.ToString ();
          string strMin = cmd.Parameters["@MinVal"].Value.ToString ();
    
    
          cmd.Dispose();
          con.Close();
          con.Dispose();
    

    Hope it will help you.

     

     

     


    Hasibul Haque,MCC,MCPD blog.e-rains.com
    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Saturday, July 02, 2011 4:42 AM

All replies

  • If you want to find min and max, use SQL, not C#. Remember to create an index for each of the columns you want to get min/max values from so a full table scan can be avoided.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Friday, July 01, 2011 8:55 PM
  • Hi Vince

    Plz try this

     

    int minAccountLevel = int.MaxValue; 
    int maxAccountLevel = int.MinValue; 
    foreach (DataRow dr in table.Rows) 
    { 
       
    int accountLevel = dr.Field<int>("AccountLevel"); 
        minAccountLevel
    = Math.Min(minAccountLevel, accountLevel); 
        maxAccountLevel
    = Math.Max(maxAccountLevel, accountLevel); 
    } 

    *note : accountlevel here is your column


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Friday, July 01, 2011 9:48 PM
  • As Sheng told you (and I hope you fill dataTable from dataBase), you shoud use sql query to get min and max value, like:

    "SELECT Min(ColumnName) FROM MyTable"

    The same goes for minimum.

     

    But if you insist to get the values from dataTable, you can use Compute method:

        DataTable table;
        public Form1()
        {
          InitializeComponent();
          table = new DataTable("myTable");
          table.Columns.Add("Column 1", typeof(int));
    
          table.Rows.Add(3);
          table.Rows.Add(4);
          table.Rows.Add(2);
        }      
    
        private void button1_Click(object sender, EventArgs e)
        {
          int maxValue = (int)table.Compute("MAX([Column 1])", ""); //computing the max value
        } 
    


     


    Mitja
    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Friday, July 01, 2011 11:18 PM
  • SQL is better for finding max and min as Sheng  said.

    You can create a stored procedure from where you can get table data and also maximum and minimum value.

    In stored procedure you have to use 2 output parameter one for max and another for minimum.

    Consider we have following table

    EmployeeID  EmployeeName       BasicSalary  TotalSalary
    ----------- ----------------- -------------------    -------------
    1                 Hasibul             35000.00           40000.00
    2                 vincezed           100000.00         150000.00
    3                 Tst                   500.00               700.00

     

    Now we will find out Max and Min  from stored procedure

    Consider following sotred procedure

    /*
    Declare
    @Max decimal(18,2), @Min decimal(18,2)
    exec empSalary_Max_Min @Max output, @Min output
    Select @Max Max, @Min MIn
    */
    
    
    Create Procedure empSalary_Max_Min
    (
    	 @MaxVal		decimal(18,2)	output
    	,@MinVal		decimal(18,2)	output
    )
    AS
    
    -- For Getting List
    Select * from empSalary
    
    -- For Getting Max Value
    Select @MaxVal = MAX(empSalary.BasicSalary) from empSalary
    
    -- For Getting Min Value
    Select @MinVal = MIN(empSalary.BasicSalary) from empSalary
    
    

    Now we will use this stored procedure for getting Table data and Max,Min value

    Here is the C# Code for getting value from stored procedure

    // Your DB Con string
          string cons = "user id=sa;password=start777; initial catalog = test_db; data source= hasibul-pc";
          SqlConnection con = new SqlConnection(cons);
          con.Open();
    
    
          //sql command : empSalary_Max_Min is procedure name
          SqlCommand cmd = new SqlCommand("empSalary_Max_Min", con);
          cmd.CommandType = CommandType.StoredProcedure;
    
    
          // Define @MaxVal and @MinVal as output parameter
          SqlParameter param = new SqlParameter();
          param.ParameterName = "@MaxVal";
          param.Size = 20;
          param.Direction = ParameterDirection.Output;
          cmd.Parameters.Add(param);
    
          param = new SqlParameter();
          param.ParameterName = "@MinVal";
          param.Size = 20;
          param.Direction = ParameterDirection.Output;
          cmd.Parameters.Add(param);
    
    
    
          DataTable dt = new DataTable();
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          da.Fill(dt); // Getting Table record
    
    
          // Getting max and min value
          string strMax = cmd.Parameters["@MaxVal"].Value.ToString ();
          string strMin = cmd.Parameters["@MinVal"].Value.ToString ();
    
    
          cmd.Dispose();
          con.Close();
          con.Dispose();
    

    Hope it will help you.

     

     

     


    Hasibul Haque,MCC,MCPD blog.e-rains.com
    • Marked as answer by Aspen VJ Monday, July 11, 2011 2:22 AM
    Saturday, July 02, 2011 4:42 AM
  • Using LINQ:

     

    using System.Linq;
    
    List<int> rowsForColumn3 = dt.AsEnumerable().Select(x => x.Field<int>(2)).ToList();
    int min = rowsForColumn3.Min();
    int max = rowsForColumn3.Max();
    

     


    But see this discussion as some question whether LINQ is the most efficient way:

    http://stackoverflow.com/questions/2442525/how-to-select-min-and-max-values-of-a-column-in-a-datatable


    Brad

    http://www.renchdev.com
    Saturday, July 02, 2011 8:25 AM