c# - 如何使用C#实现对某年某金额的查询

Question

这是一个 SQL 数据库的小演示，其中可以从 SQL 服务器添加、更新和删除成员。

单个SQL Server DB 中有两个表，一个是“members”，第二个是“overview”。

• 成员(member)中有独立的ID栏和成员(member)个人信息，如姓名、地址电话等。
• 总的来说只有三列，分别是 dID、年份和金额。

有一个单一的窗口窗体，语言是c#，项目是在Visual Studio 2010中构建的，当然还有SQL Server 2010中的数据库。

Windows 窗体有一个“重置、插入、更新和删除”按钮。

• 除了 dID 文本框之外还有一个按钮，可以在其中插入不同的 ID，单击“搜索”按钮后，关于该成员的最后一个条目显示为填写所有显示姓名地址电话的文本框。这提供了可以看到成员完整信息并可以进行更改或可以从 dB 中删除的功能。
• 具体有两个文本框，分别是Year & Amount，表示该成员(member)在某年支付了一定的金额。

但是正如我在文本框中提到的，您只能看到最后输入的内容。我想要实现的功能是，在插入人 x 的 dID 之后，我只能在能够插入的年份文本框中说任何前一年和新闻搜索，通常应该用信息填充所有文本框，并在金额中文本框应向我显示 dB 中的条目，根据我输入的年份，那里有多少金额，或者什么都没有，这意味着成员(member)可能没有支付某一年的费用。

我需要帮助以编程方式实现此逻辑，因此我想请求帮助。

当前程序如下:

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Data; 
using System.Data.SqlClient;
using System.Drawing; 
using System.Linq; 
using System.Text; 
using System.Windows.Forms;

namespace SQLDatabase 
{
     public partial class SQLDBDisplay : Form
     {
     SqlConnection con = new SqlConnection("Data Source=JG-PC\\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=True");

     public SQLDBDisplay()
     {
         InitializeComponent();
     }
     SqlDataAdapter da;
     DataSet ds = new DataSet();


     private void btnSearch_Click(object sender, EventArgs e)
     {
         SqlDataReader reader;
         SqlCommand cmd = new SqlCommand();
         try
         {
             string sql = "SELECT * FROM members where dID =  '" + txtdID.Text + "' ";
             txtYear.Text = sql;
             cmd.Connection = con;
             cmd.CommandText = sql;
             con.Open();
             reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 txtID.Text = reader["ID"].ToString();
                 txtName.Text = reader["Name"].ToString();
                 txtAddress.Text = reader["Address"].ToString();
                 txtMobile.Text = reader["Mobile"].ToString();
                 txtEmail.Text = reader["Email"].ToString();
                 txtdID.Text = reader["dID"].ToString();

             }
             con.Close();

             sql = "SELECT * FROM Overview where dID =  '" + txtdID.Text + "' ";
             txtYear.Text = txtYear.Text + " : " + sql;
             cmd.Connection = con;
             cmd.CommandText = sql;
             con.Open();
             reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 txtYear.Text = reader["Year"].ToString();
                 txtAmount.Text = reader["Amount"].ToString();
                 txtdID.Text = reader["dID"].ToString();

             }
             con.Close();
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message.ToString());
         }
     }

     private void btnReset_Click(object sender, EventArgs e)
     {
         txtdID.Text = ""; txtName.Text = ""; txtAddress.Text = "";
         txtMobile.Text = ""; txtEmail.Text = ""; txtYear.Text = "";
         txtAmount.Text = "";
     }

     private void btnInsert_Click(object sender, EventArgs e)
     {
         SqlCommand cmd = new SqlCommand();
         string Sql = "INSERT INTO members (dID, Name, Address, Email, Mobile) VALUES ( '" + txtdID.Text+ "','" + txtName.Text + "','"
+ txtAddress.Text + "', '" + txtEmail.Text + "', '" + txtMobile.Text + "')";
         cmd.CommandText = Sql;
         cmd.Connection = con;
         con.Open();
         cmd.ExecuteNonQuery();
         con.Close();
         Sql = "INSERT INTO Overview (dID, Year, Amount) VALUES ('"+ txtdID.Text +"' ,'" + txtYear.Text + "','" + txtAmount.Text +
"')";
         cmd.CommandText = Sql;
         cmd.Connection = con;
         con.Open();
         cmd.ExecuteNonQuery();
         con.Close();
         MessageBox.Show("Record Inserted Scuessfully!!!");
         for (int i = 0; i < this.Controls.Count; i++)
         {
             if (this.Controls[i] is TextBox)
             {
                 this.Controls[i].Text = "";
             }
         }
     }

     private void btnUpdate_Click(object sender, EventArgs e)
     {
          try
         {
             SqlCommand cmd = new SqlCommand();
             string Sql = "Update members set Name = '" + txtName.Text + "', Address = '" + txtAddress.Text + "', Email = '" +
txtEmail.Text + "', Mobile = '" + txtMobile.Text + "'  WHERE dID = '"
+ txtdID.Text + "'";
             cmd.CommandText = Sql;
             cmd.Connection = con;
             con.Open();
             cmd.ExecuteNonQuery();
             con.Close();

             Sql = "Update overview set Year = '" + txtYear.Text + "', Amount = '" + txtAmount.Text + "' WHERE dID = '"+ txtdID.Text+"'";
             cmd.CommandText = Sql;
             cmd.Connection = con;
             con.Open();
             cmd.ExecuteNonQuery();
             MessageBox.Show("Data Scuessfully Updated");
             con.Close();
         }
         catch (Exception error)
         {
             MessageBox.Show(error.ToString());
         }

          for (int i = 0; i < this.Controls.Count; i++)
          {
              if (this.Controls[i] is TextBox)
              {
                  this.Controls[i].Text = "";
              }
          }
     }

     private void btnDelete_Click(object sender, EventArgs e)
     {
         SqlCommand cmd = con.CreateCommand();
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "DELETE FROM members WHERE dID = '"+ txtdID.Text +"'";
         con.Open();
         cmd.ExecuteNonQuery();

         cmd.CommandText = "DELETE FROM overview WHERE dID = '" + txtdID.Text + "'";
         cmd.ExecuteNonQuery();
         da = new SqlDataAdapter(cmd);

         MessageBox.Show("Record Scuessfully Deleted !");
         con.Close();

         for (int i = 0; i < this.Controls.Count; i++)
         {
             if (this.Controls[i] is TextBox)
             {
                 this.Controls[i].Text = "";
             }
         }
     }


     private void btnClose_Click(object sender, EventArgs e)
     {
         Application.Exit();
     }

 } }

Answer 1

为了解决人们对参数和 sql 注入(inject)的评论，我倾向于在连接到任何数据库时使用下面的代码。

using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING"))
{
    try
    {
        using(SqlCommand command = new SqlCommand())
        {
            command.CommandText = "SELECT * FROM members where dID = @MyId";
            command.Connection = connection;

            // Set the SqlDbType to your corresponding type
            command.Parameters.Add("@MyId", SqlDbType.VarChar).Value = txtdID.Text;

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                txtID.Text = reader["ID"].ToString();
                txtName.Text = reader["Name"].ToString();
                txtAddress.Text = reader["Address"].ToString();
                txtMobile.Text = reader["Mobile"].ToString();
                txtEmail.Text = reader["Email"].ToString();
                txtdID.Text = reader["dID"].ToString();

            }
        }
    }
    finally
    {
        connection.Close();
    }
 }