Johny Blumer

Johny Blumer

  • NA
  • 3
  • 1.1k

How to define mysql parameters min(date) and max(date)

Apr 15 2019 7:06 AM
Hi everyone.
 
I'm semi-new from C# and i've never store objects from mysql query. In this case i've declared 2 Datetimes variables:
  1. DateTime date_min  
  2. DateTime date_max  
I'd like to define mysql Parameters max(date) and min(date) before the lopp from below query:
  1. string query1 = "SELECT MIN(order_status.BEGIN_DATE) AS 'BEGIN DATE', MAX(order_status.END_DATE) AS 'END DATE' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE orders.NUMBER_ORDER = 'TEST';";  
I'd try like this for example: 
  1. SqlParameter param  = new SqlParameter();  
  2. param.ParameterName = "@max_date";  
  3. param.Value = input_max_date;  
Then i want to use like this:
  1. string query1 = "SELECT @min_date AS 'BEGIN DATE', @max_date AS 'END DATE' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE orders.NUMBER_ORDER = 'TEST';";  
because of :
  1. @min_date = MIN(order_status.BEGIN_DATE)  
  2. @max_date = MAX(order_status.END_DATE)  
then i have the loop:
  1. for (DateTime date = date_min; date <= date_max; date = date.AddDays(1))  
  2.     {  
  3.         var cmd = new MySqlCommand(query1, connection);  
  4.   
  5.         string query2 = "SELECT COUNT(leave.ID_WORKER) AS 'NUMBER OF WORKERS ON LEAVE' FROM project1.leave WHERE DATE(leave.BEGIN_DATE) <= '"+date+"' AND DATE(leave.END_DATE) >= '"+date+"';";  
  6.         string query3 = "SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s') AS 'TIME OF ALL ORDERS IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '"+date+"' AND DATE(order_status.END_DATE) = '"+date+"');";  
  7.         string query4 = "SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s') AS 'TIME OF ORDER IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER  = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '"+date+"' AND DATE(order_status.END_DATE) = '"+date+"') AND orders.NUMBER_ORDER = 'TEST';";  
  8.   
  9. using (var command = new MySqlCommand(query1, connection))  
  10.                 {  
  11.                     using (var reader1 = command.ExecuteReader())  
  12.                     {  
  13.                         while (reader1.Read())  
  14.                         {  
  15.                             date_min = Convert.ToDateTime(reader1[0]);  
  16.                             date_max = Convert.ToDateTime(reader1[1]);  
  17.                         }  
  18.                     }  
  19.                 }  
  20.                 var cmd1 = new MySqlCommand(query2, connection);  
  21.                 var cmd2 = new MySqlCommand(query3, connection);  
  22.                 var cmd3 = new MySqlCommand(query4, connection);  
  23.     }  
Can someone please show what should kind of code lines should I write? maybe cmd.Parameters is enough? THX for any help.
 
There is my code:
  1.  public partial class GenerateChartsOfOrders : Form  
  2.     {  
  3.         DateTime date_min;  
  4.         DateTime date_max;  
  5.         public GenerateChartsOfOrders()  
  6.         {  
  7.             InitializeComponent();  
  8.         }  
  9.         public void loaddata2()  
  10.         {  
  11. string query1 = "SELECT MIN(order_status.BEGIN_DATE) AS 'BEGIN DATE', MAX(order_status.END_DATE) AS 'END DATE' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE orders.NUMBER_ORDER = 'TEST';";  
  12.   
  13.             for (DateTime date = date_min; date <= date_max; date = date.AddDays(1))  
  14.             {     
  15.                 var cmd = new MySqlCommand(query1, connection);  
  16.   
  17.                 string query2 = "SELECT COUNT(leave.ID_WORKER) AS 'NUMBER OF WORKERS ON LEAVE' FROM project1.leave WHERE DATE(leave.BEGIN_DATE) <= '"+date+"' AND DATE(leave.END_DATE) >= '"+date+"';";  
  18.                 string query3 = "SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s') AS 'TIME OF ALL ORDERS IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '"+date+"' AND DATE(order_status.END_DATE) = '"+date+"');";  
  19.                 string query4 = "SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s') AS 'TIME OF ORDER IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER  = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '"+date+"' AND DATE(order_status.END_DATE) = '"+date+"') AND orders.NUMBER_ORDER = 'TEST';";  
  20.   
  21. using (var command = new MySqlCommand(query1, connection))  
  22.                     {  
  23.                         using (var reader1 = command.ExecuteReader())  
  24.                         {  
  25.                             while (reader1.Read())  
  26.                             {  
  27.                                 date_min = Convert.ToDateTime(reader1[0]);  
  28.                                 date_max = Convert.ToDateTime(reader1[1]);  
  29.                             }  
  30.                         }  
  31.                     }  
  32.   
  33.                     var cmd1 = new MySqlCommand(query2, connection);  
  34.                     var cmd2 = new MySqlCommand(query3, connection);  
  35.                     var cmd3 = new MySqlCommand(query4, connection);  
  36.             }  
  37.         }  
  38.     }

Answers (1)