C#在Datagridview中显示两个日期之间的数据.日期时间选取器或文本框中的日期

Question

我正在C#中编写一个代码，在该代码中我使用OLEDB连接字符串连接MS访问数据库。我有一个表单，我在datagridview中根据一些条件显示数据库中的数据。以下是标准：

a( 人员(来自中的数据库

b( 进程(来自文本框中的数据库(

c( 起始日期(Datetimepicker(

d( 截止日期(Datetimepicker(

结果我想要的：首先我选择人员和流程，然后选择开始日期，然后选择结束日期，然后单击查看按钮。其应当显示基于我选择的上述标准的来自MS接入的数据。

我尝试过以下方法：

代码：1.人员和流程过滤器：

DataView DV = new DataView(dt1);
DV.RowFilter = string.Format("[Person] LIKE '%{0}%'", textBox5.Text);
dataGridView1.DataSource = DV;

2. 对于两次约会之间的约会时间，我尝试了很多次，在谷歌上搜索了很多，但都没有找到答案。我尝试了以下内容：

a(

SqlConnection con = new 
SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
string queryString = "";
queryString = "SELECT * FROM Table1 WHERE dob BETWEEN @startdate AND @enddate";
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(queryString, con);
sqlCmd.Parameters.Add("@startdate", System.Data.SqlDbType.Date).Value = textBox7.Text;
sqlCmd.Parameters.Add("@enddate", System.Data.SqlDbType.Date).Value = textBox8.Text;
System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
System.Data.DataSet dataSet = new System.Data.DataSet();
dataAdapter.Fill(dataSet);
GridView1.DataSource = dataSet;
GridView1.DataBind();

b(

string FD = "";
FD = dateTimePicker4.Value.ToString("dd-MM-yyyy");
string TD = "";
TD = dateTimePicker5.Value.ToString("dd-MM-yyyy");
connection.Close();
connection.Open();
OleDbCommand command123 = new OleDbCommand();
command123.Connection = connection;
string query123 = "select * from Table1 where [P Date] between date '"# + dateTimePicker4.Text.ToString() + #"' and date '"# + dateTimePicker5.Text.ToString() + #"'"
command123.CommandText = query123;
OleDbDataAdapter da123 = new OleDbDataAdapter(command123);
DataTable dt123 = new DataTable();
da123.Fill(dt123);
dataGridView1.DataSource = dt123;

c(

DataTable dt = new DataTable();
da.Fill(dt);
DataView DV = new DataView(dt1);
DV.RowFilter = string.Format("[P Date] >=" + textBox7.Text + " and <" + textBox8.Text + "");
dataGridView1.DataSource = DV;

d(

DataTable dt1 = new DataTable();
DataView DV = new DataView(dt1);
//DV.RowFilter = "[P Date] IN (#11/01/2019#, #11/11/2019#)";
//DV.RowFilter = "[P Date] >=#"+dateTimePicker4.Text+"# and [P Date] <=#"+dateTimePicker5.Text+"#";
//dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values
//DV.RowFilter ="([P Date] >=CDate('dateTimePicker4.Text')) and ([P Date] <=CDate('dateTimePicker5.Text'))";
//DV.RowFilter = string.Format(CultureInfo.InvariantCulture.DateTimeFormat, "([P Date]>=#{dateTimePicker4.text}#) and ([P Date] <=#{dateTimePicker5.Text}#)");
dataGridView1.DataSource = DV;
For connection I am trying OLEDB connection.
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DatabasePath.accdb;
Jet OLEDB:Database Password=password";
connection.Close();
connection.Open();
OleDbCommand command1 = new OleDbCommand();
command1.Connection = connection;
//Select all column use belw query
string query = "select * from Table1";
command1.CommandText = query;
OleDbDataAdapter da = new OleDbDataAdapter(command1);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
connection.Close();
this code is working but problem is that it shows all data not based on criteria based data.
I expect the output in Datagridview based on all criteria. Show only data which falls under all criteria.

Answer 1

我看不出你在描述你的结果有什么问题。如果你正在寻求如何做到这一点的一般帮助，这里有一个例子。请记住：这个示例很简单，不使用参数查询。这取决于你。

转换的C#

string wQueryCreated, wQueryChanged, wQueryCreatedBy, wQueryChandedBy;
if (this.fiCreated.Checked == true)
wQueryCreated = " AND DateCreated >= '" + Format(this.dtpFiCreatedOd.Value, "yyyy-MM-dd 00:00") + "' AND DateCreated <= '" + Format(this.dtpFiCreatedDo.Value, "yyyy-MM-dd 23:59") + " ' ";
else
wQueryCreated = "";
if (this.fiChanged.Checked == true)
wQueryChanged = " AND DateModified BETWEEN '" + Format(this.dtpDateChangedFrom.Value, "yyyy-MM-dd") + "' AND '" + Format(this.dtpDateChangedTo.Value, "yyyy-MM-dd") + "' ";
else
wQueryChanged = "";
if (this.fiCreatedBy.Checked == true)
wQueryCreatedBy = " AND PersonCreatedBy = " + this.fiCreatedBy.SelectedValue + " ";
else
wQueryCreatedBy = "";
if (this.fiChandedBy.Checked == true)
wQueryChandedBy = " AND PersonModifiedBy = " + this.fiChandedBy.SelectedValue + " ";
else
wQueryChandedBy = "";
// use query conditions in SELECT statement     
queryString = "SELECT * FROM Table1 WHERE DateDeleted IS NULL " + wQueryCreated + wQueryChanged + wQueryCreatedBy + wQueryChandedBy + "; ";

原始VB.NET:

Dim wQueryCreated, wQueryChanged, wQueryCreatedBy, wQueryChandedBy As String
If Me.fiCreated.Checked = True Then  ' filter date created
wQueryCreated = " AND DateCreated >= '" & Format(Me.dtpFiCreatedOd.Value, "yyyy-MM-dd 00:00") & "' AND DateCreated <= '" & Format(Me.dtpFiCreatedDo.Value, "yyyy-MM-dd 23:59") & " ' "
else
wQueryCreated = ""
End If
If Me.fiChanged.Checked = True Then  ' filter datechanged
wQueryChanged = " AND DateModified BETWEEN '" & Format(Me.dtpDateChangedFrom.Value, "yyyy-MM-dd") & "' AND '" & Format(Me.dtpDateChangedTo.Value, "yyyy-MM-dd") & "' "
else
wQueryChanged = ""
End If
If Me.fiCreatedBy.Checked = True Then  ' filter chandged by person
wQueryCreatedBy = " AND PersonCreatedBy = " & Me.fiCreatedBy.SelectedValue & " "
else
wQueryCreatedBy = ""
End If
If Me.fiChandedBy.Checked = True Then  ' filter modified by person
wQueryChandedBy = " AND PersonModifiedBy = " & Me.fiChandedBy.SelectedValue & " "
else
wQueryChandedBy = ""
End If
' use query conditions in SELECT statement      
queryString = "SELECT * FROM Table1 WHERE DateDeleted IS NULL " & wQueryCreated & wQueryChanged & wQueryCreatedBy & wQueryChandedBy & "; "

本示例使用4个条件，总共从6个字段获取信息(dtp前缀表示DateTimePicker(。请注意，第一个条件和第二个条件之间的日期处理有点不同，它们的作用类似。

还要注意，这个方法需要一个固定的条件。它可以是1=1，但令人反感的是，您通常像我在示例中那样使用它，即排除软删除的行(DateDeleted IS NULL(。您需要一个固定条件，这样您就不必关心其他条件中的起始" AND "，无论它们的用法如何。

您可以使用"无尽"的条件并将它们组合起来，即使是不同的数据类型。如果需要限制条件的组合，请在运行查询之前验证是否已填充这些条件。显然，您不必测试已检查的CheckBoxes，您可以检查ComboBox1.SelectedValue > -1(在ComboBox中选择了某些内容(或TextBox1.Text.Length >= 3(在TextBox中至少输入了3个字符(等。