我可以计算DataGridView中两个日期列之间的持续时间 [英] Ho do I calcualate duration of days between two date columns in DataGridView

问题描述

我有一个数据库文件和Windows窗体，当选择一个可用的复选框时，根据用户的选择生成某些查询，但我需要生成一个新列，将其添加到临时表中，其中包含以下数据



第1栏：供应商名称，第2栏：持续时间



然后我需要获得每个供应商的平均天数填写在消息框中，下面是我目前为查询所做的代码，但我不知道从哪里开始获取每个SupplierName的平均值以及如何在新的临时表中填充所需的两列

I have a database file and windows form to generate certain queries based on user's choice when selecting one of number of available check-boxes but I need to generate a new column to be added to temp table with the below data

Column1: SupplierName , Column2: Duration of days

then I need to get the average of days per supplier to be populated in a messagebox, below are the codes i made so far for the queries but i don't know where to start to get the average per SupplierName and how to populate the needed two columns in new temp table

Try
            If cnnOLEDB.State = ConnectionState.Open Then
                cnnOLEDB.Close()
            End If
            cnnOLEDB.ConnectionString = strConnectionString
            cnnOLEDB.Open()
            Dim sqlQry As String = "Select * From Customers "
            'Only add "WHERE" if at least one of the boxes is checked
            sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked Or SupplierFeedbackDate.Checked, " WHERE ", "")
            'Only add the parameter for Supplier if Supplier box is checked
            If SupplierName.Checked Then
                If cbo_Supplier.Text = vbNullString Then
                    sqlQry &= "SupplierName IS NOT NULL"
                Else
                    sqlQry &= "SupplierName = '" & cbo_Supplier.Text & "'"
                End If
            End If
            If Supplier_Feedback.Checked Then
                If cbo_Feedback.Text = vbNullString Then
                    'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
                    sqlQry &= IIf(SupplierName.Checked, " AND ", "")
                    sqlQry &= "Supplier_Feedback IS NOT NULL"
                Else
                    'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
                    sqlQry &= IIf(SupplierName.Checked, " AND ", "")
                    sqlQry &= "Supplier_Feedback = '" & cbo_Feedback.Text & "'"
                End If
            End If
            If Reason.Checked Then
                If cbo_Reason.Text = vbNullString Then
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
                    sqlQry &= "Reason IS NOT NULL"
                Else
                    'Only add " AND " if we have a previous filter in place already
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
                    sqlQry &= "Reason = '" & cbo_Reason.Text & "'"
                End If
            End If
            If ServiceName.Checked Then
                If TXTServiceName.Text = vbNullString Then
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
                    sqlQry &= "ServiceName IS NOT NULL"
                Else
                    'Only add " AND " if we have a previous filter in place already
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
                    sqlQry &= "ServiceName LIKE '%" & TXTServiceName.Text & "%'"
                End If
            End If
            If DateAdded.Checked Then
                'Only add " AND " if we have a previous filter in place already
                'I.e. one or both of Supplier and/or Action is checked
                sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked, " AND ", "")
                sqlQry &= "DateAdded >= #" + cbo_DateSent.Value + "# And DateAdded <= #" + cbo_DateSentTo.Value + "#"
            End If
            If SupplierFeedbackDate.Checked Then
                'Only add " AND " if we have a previous filter in place already
                'I.e. one or both of Supplier and/or Action is checked
                sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked, " AND ", "")
                sqlQry &= "SupplierFeedbackDate >= #" + cbo_FeedbackDate.Value + "# And SupplierFeedbackDate <= #" + cbo_FeedbackDateTo.Value + "#"
            End If
            SQLText.Text = sqlQry
            Dim da As OleDbDataAdapter
            Dim ds As DataSet = New DataSet
            da = New OleDbDataAdapter(sqlQry, cnnOLEDB)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            da.Fill(ds, "Customers")
            DataGridViewReports.DataSource = ds
            DataGridViewReports.DataMember = "Customers"
            lbl_RowCount.Text = DataGridViewReports.RowCount
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        End Try
        cnnOLEDB.Close()

推荐答案

修复代码中一些更明显的问题：

• 使用参数化查询来修复 SQL注入 [< a href =http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html\"target =_ blank> ^ ]漏洞。
• 不要将连接和命令对象存储为类级字段;使用局部变量。
• 在使用块中包装实现 IDisposable 的对象。

Fixing some of the more obvious problems in your code:

• Use parameterized queries to fix the SQL Injection[^] vulnerability.
• Don't store connection and command objects as class-level fields; use local variables instead.
• Wrap objects that implement IDisposable in a Using block.

Private Function CreateConnection() As OleDbConnection
    Dim result As New OleDbConnection("** YOUR CONNECTION STRING HERE **")
    result.Open()
    Return result
End Function

Private Sub btnReport_SLA_Click(sender As Object, e As EventArgs) Handles btnReport_SLA.Click
    Try
        Using connection As OleDbConnection = CreateConnection()
            
            Using cmdDelete As OleDbCommand = connection.CreateCommand()
                cmdDelete.CommandText = "DELETE * FROM SLA"
                cmdDelete.ExecuteNonQuery()
            End Using
            
            Using cmdInsert As OleDbCommand = connection.CreateCommand()
                If DateAdded.Checked Then
                    cmdInsert.CommandText = "INSERT INTO SLA SELECT ID, Dial, ServiceName, SupplierName, DateAdded, SupplierFeedbackDate FROM Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' AND DateAdded between ? And ?"
                    cmdInsert.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                    cmdInsert.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
                    cmdInsert.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
                Else 
                    cmdInsert.CommandText = "INSERT INTO SLA SELECT ID, Dial, ServiceName, SupplierName, DateAdded, SupplierFeedbackDate FROM Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....'"
                    cmdInsert.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                End If
                
                cmdInsert.ExecuteNonQuery()
            End Using
            
            Using cmdUpdate As OleDbCommand = connection.CreateCommand()
                cmdUpdate.CommandText = "Update SLA SET ClosureDuration = SupplierFeedbackDate - DateAdded"
                cmdUpdate.ExecuteNonQuery()
            End Using
            
            If String.IsNullOrEmpty(cbo_Supplier.Text) Then
                MessageBox.Show("No supplier selected")
                Return
            End If
            
            Using cmdSelect As OleDbCommand = connection.CreateCommand()
                If DateAdded.Checked Then
                    cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND DateAdded between ? And ? AND SupplierFeedbackDate <> '.... / .... / .....' "
                    cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                    cmdSelect.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
                    cmdSelect.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
                Else
                    cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' "
                    cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                End If
                
                Using da As New OleDbDataAdapter(cmdSelect)
                    Dim ds As New DataSet()
                    da.Fill(ds, "Customers")
                    DataGridViewReports.DataSource = ds
                    DataGridViewReports.DataMember = "Customers"
                    lbl_RowCount.Text = DataGridViewReports.RowCount
                End Using
            End Using
            
            Using cmdAverage As OleDbCommand = connection.CreateCommand()
                cmdAverage.CommandText = "SELECT AVG(ClosureDuration) FROM SLA"
                Dim AVGResult As Integer = Convert.ToInt32(cmdAverage.ExecuteScalar())
                Dim SFound As String = If(AVGResult > 1, "s", "")
                
                If DateAdded.Checked Then
                    MessageBox.Show(String.Format("Response duration from {1} : {2} day{3}{0}{0}For all finished requests sent between {4} and {5}", _
                        vbNewLine, cbo_Supplier.Text, AVGResult, SFound, cbo_DateSent.Value, cbo_DateSentTo.Value));
                Else
                    MessageBox.Show(String.Format("Response duration from {1} : {2} day{3}{0}{0}For all finished requests.", _
                        vbNewLine, cbo_Supplier.Text, AVGResult, SFound));
                End If
            End Using
            
            Using cmdDelete As OleDbCommand = connection.CreateCommand()
                cmdDelete.CommandText = "DELETE * FROM SLA"
                cmdDelete.ExecuteNonQuery()
            End Using
            
        End Using
        
    Catch ex As OleDbException
        ' TODO: Log the full exception details somewhere:
        System.Diagnostics.Trace.TraceError(ex.ToString())
        MessageBox.Show(ex.Message)
    End Try
End Sub

万分感谢@Richard Deeming你的代码令人惊叹并且像魅力一样，但是我更正了以下部分

Thanks a million @Richard Deeming your code is amazing and works like a charm , however i corrected the below part from

Using cmdSelect As OleDbCommand = connection.CreateCommand()
                If DateAdded.Checked Then
                    cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND DateAdded between ? And ? AND SupplierFeedbackDate <> '.... / .... / .....' "
                    cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                    cmdInsert.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
                    cmdInsert.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
                Else
                    cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' "
                    cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                End If
 
                Using da As New OleDbDataAdapter(cmdSelect)
                    Dim ds As New DataSet()
                    da.Fill(ds, "Customers")
                    DataGridViewReports.DataSource = ds
                    DataGridViewReports.DataMember = "Customers"
                    lbl_RowCount.Text = DataGridViewReports.RowCount
                End Using
End Using

to

to

Using cmdSelect As OleDbCommand = connection.CreateCommand()
                    If DateAdded.Checked Then
                        cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND DateAdded Between ? And ? AND SupplierFeedbackDate <> '.... / .... / .....'"
                        cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                        cmdSelect.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
                        cmdSelect.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
                    Else
                        cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' "
                        cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
                    End If
                    Using da As New OleDbDataAdapter(cmdSelect)
                        Dim ds As New DataSet()
                        da.Fill(ds, "Customers")
                        DataGridViewReports.DataSource = ds
                        DataGridViewReports.DataMember = "Customers"
                        lbl_RowCount.Text = DataGridViewReports.RowCount
End Using

这篇关于我可以计算DataGridView中两个日期列之间的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！