TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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:
DateTime date_min
DateTime date_max
I'd like to define
mysql Parameters
max(date) and min(date) before the lopp from below query:
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:
SqlParameter param =
new
SqlParameter();
param.ParameterName =
"@max_date"
;
param.Value = input_max_date;
Then i want to use like this:
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 :
@min_date = MIN(order_status.BEGIN_DATE)
@max_date = MAX(order_status.END_DATE)
then i have the loop:
for
(DateTime date = date_min; date <= date_max; date = date.AddDays(1))
{
var cmd =
new
MySqlCommand(query1, connection);
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+
"';"
;
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+
"');"
;
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';"
;
using
(var command =
new
MySqlCommand(query1, connection))
{
using
(var reader1 = command.ExecuteReader())
{
while
(reader1.Read())
{
date_min = Convert.ToDateTime(reader1[0]);
date_max = Convert.ToDateTime(reader1[1]);
}
}
}
var cmd1 =
new
MySqlCommand(query2, connection);
var cmd2 =
new
MySqlCommand(query3, connection);
var cmd3 =
new
MySqlCommand(query4, connection);
}
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:
public
partial
class
GenerateChartsOfOrders : Form
{
DateTime date_min;
DateTime date_max;
public
GenerateChartsOfOrders()
{
InitializeComponent();
}
public
void
loaddata2()
{
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';"
;
for
(DateTime date = date_min; date <= date_max; date = date.AddDays(1))
{
var cmd =
new
MySqlCommand(query1, connection);
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+
"';"
;
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+
"');"
;
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';"
;
using
(var command =
new
MySqlCommand(query1, connection))
{
using
(var reader1 = command.ExecuteReader())
{
while
(reader1.Read())
{
date_min = Convert.ToDateTime(reader1[0]);
date_max = Convert.ToDateTime(reader1[1]);
}
}
}
var cmd1 =
new
MySqlCommand(query2, connection);
var cmd2 =
new
MySqlCommand(query3, connection);
var cmd3 =
new
MySqlCommand(query4, connection);
}
}
}
Reply
Answers (
1
)
open outlook file using html object
Need Suggestions to Refactor Code