英文:

Get Data between 2 dates C# in SQL

问题 {#heading}

在我的SQL代码中，我试图获取两个日期之间的数据。我使用了datetime数据类型的列来保存用户的操作（按按钮）。以下是我的代码：

CREATE TABLE Actions_Data
(
    [id] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Auto] datetime NULL,
    [Manual] datetime NULL,
    [Buttons_Off] datetime NULL,
    [start_pump1] datetime NULL,
    [stop_pump1] datetime NULL,
)

以下是我的C#代码：

SqlConnection con = new SqlConnection(connectionString); 
con.Open();

SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM Actions_Data WHERE Auto BETWEEN '" + dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'", con);

DataSet ds = new DataSet();
sqlDa.Fill(ds, "Actions_Data");

dataGridView1.DataSource = ds.Tables["Actions_Data"];
con.Close();

这样，我可以获取名为Auto的列的数据。您知道如何获取表中的所有数据吗？（我尝试在Auto的位置使用*，但不起作用）提前感谢您的帮助！

我的目的是在两个日期之间搜索我在DataGridView中拥有的所有数据。

我遵循了这个视频：https://youtu.be/2hJGolhxFJo 英文:

I am trying to get data between two dates, in my SQL code I use columns with datatype datetime in order to save the actions of a user (pressing button).

My code:

CREATE TABLE Actions_Data
(
	[id] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Auto] datetime NULL,
	[Manual] datetime NULL,
	[Buttons_Off] datetime NULL,
	[start_pump1] datetime NULL,
	[stop_pump1] datetime NULL,
)

My C# code:

SqlConnection con = new SqlConnection(connectionString); 
con.Open();

SqlDataAdapter sqlDa = new SqlDataAdapter(\"SELECT \* FROM  Actions_Data WHERE Auto BETWEEN \'\" + dateTimePicker1.Value.ToString(\"yyyy- MM- dd HH: mm:ss\") + \"\' AND \'\" + dateTimePicker2.Value.ToString(\"yyyy- MM- dd HH: mm:ss\") + \"\'\", con);


DataSet ds = new DataSet();
sqlDa.Fill(ds, \"Actions_Data\");

`dataGridView1.DataSource = ds.Tables["Actions_Data"];
con.Close();
`

In this way I can get the data of only one column named Auto. Do you know how can I get all the data of my table? (I tried * in the position of Auto but it doesn't work) Thank you in advance!

My purpose is the ability to search all the data that I have in a datagridview between two dates

I followed this video: https://youtu.be/2hJGolhxFJo

答案1 {#1}

得分: 4

"Parameterized queries". 使用它们。

using var con = new SqlConnection("connection string here");
using var da = new SqlDataAdapter(@"
SELECT * 
FROM Actions_Data 
WHERE Auto >= @StartDate AND Auto < @EndDate", con);

da.SelectCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateTimePicker1.Value;
da.SelectCommand.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = dateTimePicker2.Value;

Separately, this WILL return data for all the columns. If you don't see other columns, look at the code that reads and displays the results because I promise you this query will grab all of them from the table. And as others have said, you're generally better off listing the column names you need than using the * wildcard.

Based on the edits, the problem is the definition of the dataGridView1 control. There's only one column defined for the control. That's where you need to make the change. Also, that video is NOT GOOD. It's demonstrating a number of poor practices, not the least of which is using string concatenation to put the data into the query instead of parameters. 英文:

Parameterized queries . USE THEM.

using var con = new SqlConnection("connection string here");
using var da = new SqlDataAdapter(@"
SELECT * 
FROM Actions_Data 
WHERE Auto >= @StartDate AND Auto < @EndDate", con);
`da.SelectCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateTimePicker1.Value;
da.SelectCommand.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = dateTimePicker2.Value;
`

Separately, this WILL return data for all the columns. If you don't see other columns, look at the code the reads and displays the results, because I promise you this query will grab all of them from the table. And as others have said, you're generally better off listing the column names you need than using the * wildcard.

Based on the edits, the problem is the definition of the dataGridView1 control. There's only one column defined for the control. That's where you need to make the change. Also, that video is NOT GOOD. It's demonstrating a number of a poor practices, not the least of which is using string concatenation to put the data into the query instead of parameters.