如何根据当前日期自动从数据库更新剩余天数到datagridview？ [英] How to update the remaining days automatically from database to datagridview according to current date?

问题描述

我正在使用C＃.Net创建一个Windows应用程序，我有DataGridView，一个DateTimePicker和一个名为days left的文本框，并在那个窗口中有一个保存按钮。我有一个名为vehicle的数据库表，它包含两个名为expiry date和DaysLeft的列，我想将数据从windows表单存储到数据库表中，它应该将DaysLeft存储为-ve或+ ve根据当前日期的值，我也想根据当前日期在DataGridView中自动更新DaysLeft列。



我尝试过：



我试过以下代码

 private void dateTimePickerKhaas_ValueChanged（object sender， EventArgs e）
 {
 DateTime from = dateTimePickerKhaas.Value; 
 DateTime to = DateTime.Now; 
 TimeSpan Tspan = from  -  to; 
 double days = Tspan.TotalDays; 
 txtKDaysLeft.Text = days.ToString（0）; 
} 

 private void btnKSave_Click（object sender，EventArgs e）
 {
 con.Open（）; 
 String query =INSERT INTO table_khaas（ExpiryDate，DaysLeft）VALUES（'+ dateTimePickerKhaas.Value.ToString（MM / dd / yyyy）+'，'+ txtKDaysLeft.Text +'） ; 
 SqlDataAdapter sda = new SqlDataAdapter（query，con）; 
 DataTable dt = new DataTable（）; 
 sda.Fill（dt）; 
 dataGridViewKhaas.DataSource = dt; 
 
 con.Close（）; 
 grd_fillKhaas（）; 
 
 
 MessageBox.Show（成功插入）; 
 dateTimePickerKhaas.Value = DateTime.Now; 
 txtKDaysLeft.Text =; 
 
} 

 public void grd_fillKhaas（）
 {
 //con.Open（ ）; 
 String query =SELECT * FROM table_khaas; 
 SqlDataAdapter sda = new SqlDataAdapter（query，con）; 
 DataTable dt = new DataTable（）; 
 sda.Fill（dt）; 
 dataGridViewKhaas.DataSource = dt; 
 //con.Close（）; 
} 

解决方案

删除 DaysLeft 列，然后计算它当您查询表格时：

  public   void  grd_fillKhaas（）
 {
 使用（ var  connection =  new  SqlConnection（  ...））
 {
  var  sda =  new  SqlDataAdapter（  SELECT ExpiryDate，DateDiff（dd，GetDate（），ExpiryDate）As DaysLeft FROM table_khaas，connection）; 
  var  dt =  new  DataTable（）; 
 sda.Fill（dt）; 
 dataGridViewKhaas.DataSource = dt; 
} 
} 

  private   void  btnKSave_Click（ object  sender，EventArgs e）
 {
  using （ var  connection =  new  SqlConnection（  ...））
 使用（< span class =code-keyword> var  command =  new  SqlCommand（  INSERT INTO table_khaas（ExpiryDate）VALUES（@ExpiryDate），connection））
 {
 command.Parameters.AddWithValue（  @ ExpiryDate，dateTimePickerKhaas.Value）; 
 
 connection.Open（）; 
 command.ExecuteNonQuery（）; 
} 
 
 grd_fillKhaas（）; 
 
 
 MessageBox.Show（ 成功插入）; 
 dateTimePickerKhaas.Value = DateTime.Now; 
 txtKDaysLeft.Text =  ; 
} 

DATEDIFF（Transact-SQL）| Microsoft Docs [ ^ ]

Quote：

我有一个名为vehicle的数据库表它包括两个名为失效日期和DaysLeft的列，

建议：永远不要将易变数据放在数据库中，这是一个设计流程。

易失性数据是经常变化的数据，就像每天一样。因为要使用该列DaysLeft，除非您知道已经完成更新，否则必须更新它。相信我，这是一个真实情况下的主要痛苦。



永远不要通过连接字符串来构建SQL查询。迟早，你会使用用户输入，这样就打开了一个名为SQL注入的漏洞，这对你的数据库很危险并且容易出错。

一个名字中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

I am making a windows application using C# .Net, I have DataGridView, a DateTimePicker and a textbox named as "days left" and a save button in that windows from. I have a database table named as "vehicle" and it includes two columns named as "expiry date" and "DaysLeft", I want to store data from windows form to the database table and it should store the DaysLeft either -ve or +ve value according to current date and I also want to update DaysLeft column automatically in DataGridView according to current date.

What I have tried:

I have tried the following code

private void dateTimePickerKhaas_ValueChanged(object sender, EventArgs e)
        {
            DateTime from = dateTimePickerKhaas.Value;
            DateTime to = DateTime.Now;
            TimeSpan Tspan = from - to;
            double days = Tspan.TotalDays;
            txtKDaysLeft.Text = days.ToString("0");
        }

private void btnKSave_Click(object sender, EventArgs e)
        {
                con.Open();
                String query = "INSERT INTO table_khaas ( ExpiryDate, DaysLeft) VALUES( '" + dateTimePickerKhaas.Value.ToString("MM/dd/yyyy") + "'  ,'" +txtKDaysLeft.Text + "')";
                SqlDataAdapter sda = new SqlDataAdapter(query, con);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridViewKhaas.DataSource = dt;
                
                con.Close();
                grd_fillKhaas();


                MessageBox.Show("Inserted Successfully");
                dateTimePickerKhaas.Value = DateTime.Now;
                txtKDaysLeft.Text = "";
                        
        }

public void grd_fillKhaas()
        {
            //con.Open();
            String query = "SELECT * FROM table_khaas";
            SqlDataAdapter sda = new SqlDataAdapter(query, con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridViewKhaas.DataSource = dt;
            //con.Close();
        }

解决方案

Remove the DaysLeft column, and calculate it when you query the table:

public void grd_fillKhaas()
{
    using (var connection = new SqlConnection("..."))
    {
        var sda = new SqlDataAdapter("SELECT ExpiryDate, DateDiff(dd, GetDate(), ExpiryDate) As DaysLeft FROM table_khaas", connection);
        var dt = new DataTable();
        sda.Fill(dt);
        dataGridViewKhaas.DataSource = dt;
    }
}

private void btnKSave_Click(object sender, EventArgs e)
{
    using (var connection = new SqlConnection("..."))
    using (var command = new SqlCommand("INSERT INTO table_khaas ( ExpiryDate ) VALUES ( @ExpiryDate )", connection))
    {
        command.Parameters.AddWithValue("@ExpiryDate", dateTimePickerKhaas.Value);
        
        connection.Open();
        command.ExecuteNonQuery();
    }
    
    grd_fillKhaas();
    

    MessageBox.Show("Inserted Successfully");
    dateTimePickerKhaas.Value = DateTime.Now;
    txtKDaysLeft.Text = "";
}

DATEDIFF (Transact-SQL) | Microsoft Docs[^]

Quote:

I have a database table named as "vehicle" and it includes two columns named as "expiry date" and "DaysLeft",

Advice: Never put volatile data in database, it is a design flow.
Volatile data is data that change very often, like every day. Because in order to use that column "DaysLeft", you always have to update it unless you know that update have already been done. Believe me, it is a major pain in real situation.

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, anf this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]

这篇关于如何根据当前日期自动从数据库更新剩余天数到datagridview？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！