如何显示SQL中今日未录入记录的动态按钮？

需求说明 #

• SQL表录入数据时自动同步记录日期
• 仅为今日无对应录入记录的项动态创建按钮（当日尚未添加该条目）
• 日期存储在SQL表中，可从已填充的ListView中便捷获取相关ID

初始代码示例 #

public void load()
{
    foreach (ListViewItem item in ListView.Items)
    {
       // item.SubItems[5].Text 为行ID
        SqlConnection conn = new SqlConnection(connstring);
        string strsql = "SELECT ID from Table1 WHERE ID = '" + item.SubItems[5].Text + "'";
        SqlCommand cmd = new SqlCommand(strsql, conn);
        SqlDataReader reader = null;
        cmd.Connection.Open();
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            System.Windows.Forms.Button test1Button = new System.Windows.Forms.Button();
            test1Button.Click+= new EventHandler(button1ButtonClick);
            test1Button.Text = reader["ID"].ToString();
            test1Button.Size = new System.Drawing.Size(120, 38);
            this.Controls.Add(test1Button);
            flowLayoutPanel.Controls.Add(test1Button);

            System.Windows.Forms.Button test2Button = new System.Windows.Forms.Button();
           test2Button.Click += new EventHandler(LabelBtn_Click);
             test2Button.Text = reader["ID"].ToString();
             test2Button.BackColor = Color.DarkRed;
             test2Button.ForeColor = Color.White;
             test2Button.Size = new System.Drawing.Size(120, 38);
            this.Controls.Add(test2Button);
           flowLayoutPanel2.Controls.Add(test2Button);

        }

    }
}

更新说明 #

已优化代码，采用表连接方式获取日期数据；注意：日期字段并非为空，而是当日尚未录入记录，直到用户提交结果后才会在数据库中生成对应日期的条目。

修正后的更新代码示例 #

注：原SQL查询逻辑错误，已调整为筛选今日无对应记录的项

public void load()
{
    // 先清空容器避免重复创建按钮
    flowLayoutPanel.Controls.Clear();
    flowLayoutPanel2.Controls.Clear();

    using (SqlConnection conn = new SqlConnection(connstring))
    {
        // 一次性查询所有今日未录入记录的ID，提升效率
        string strsql = @"
            SELECT t1.ID 
            FROM Table1 t1
            LEFT JOIN Table2 t2 ON t1.[Table1 _ID] = t2.[Table2 _ID] 
                AND CONVERT(Date, t2.yourDateColumn) = CONVERT(Date, CURRENT_TIMESTAMP)
            WHERE t2.[Table2 _ID] IS NULL";
        
        SqlCommand cmd = new SqlCommand(strsql, conn);
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                string itemId = reader["ID"].ToString();
                
                // 创建第一个按钮
                System.Windows.Forms.Button test1Button = new System.Windows.Forms.Button();
                test1Button.Click += button1ButtonClick;
                test1Button.Text = itemId;
                test1Button.Size = new System.Drawing.Size(120, 38);
                flowLayoutPanel.Controls.Add(test1Button);

                // 创建第二个按钮
                System.Windows.Forms.Button test2Button = new System.Windows.Forms.Button();
                test2Button.Click += LabelBtn_Click;
                test2Button.Text = itemId;
                test2Button.BackColor = Color.DarkRed;
                test2Button.ForeColor = Color.White;
                test2Button.Size = new System.Drawing.Size(120, 38);
                flowLayoutPanel2.Controls.Add(test2Button);
            }
        }
    }
}

关键优化点 #

• 使用LEFT JOIN结合WHERE t2.[Table2 _ID] IS NULL筛选今日无记录的项，避免循环查询提升效率
• 添加using语句自动释放数据库连接和资源，防止内存泄漏
• 先清空容器控件，避免重复创建按钮
• 移除不必要的this.Controls.Add，按钮已添加到FlowLayoutPanel中无需重复添加

内容的提问来源于stack exchange，提问作者okra