如何从2个不同的表中添加信息 [英] How Do I Add Information From 2 Different Tables

问题描述

private void nbtnAddStaff_Click(object sender, EventArgs e)
        {
            try
            {
                string username = txtUserName.Text;
                string password = txtPassword.Text;
                string name = txtName.Text;
                string telephone = txtTelephone.Text;
                string phoneNumber = txtPhno.Text;
                string Address = txtAddress.Text;
                string role = comboBox1.SelectedItem.ToString();
                string eid = txtEid.Text;
                string officeAddress= txtOfficeAddress.Text;
                SqlConnection con = new SqlConnection("Data Source=AISASQITSVC02;Initial Catalog=DApp20140408;Integrated Security=True");
                string qr = "Insert into Equip_Person (Name,Address,Telephone_Number) values(@name,@address,@telephone_number)";
                string query="Insert into Equip_Employee(EID,OfficeAddress,PhoneExtNo,UserName,Password,Role) values(@EID,@OfficeAddress,@phoneNumber,@username,@password,@role)";
                SqlCommand cmd = new SqlCommand(qr, con);
                SqlCommand cmnd= new SqlCommand(query,con);
                cmd.Parameters.AddWithValue(@name, txtName.Text);
                cmd.Parameters.AddWithValue(@Address, txtAddress.Text);
                cmd.Parameters.AddWithValue(@telephone, txtTelephone.Text);
                cmnd.Parameters.AddWithValue(@eid, txtEid.Text);
                cmnd.Parameters.AddWithValue(@officeAddress, txtOfficeAddress.Text);
                cmnd.Parameters.AddWithValue(@username, txtUserName.Text);
                cmnd.Parameters.AddWithValue(@password, txtPassword.Text);
                cmnd.Parameters.AddWithValue(@phoneNumber, txtPhno.Text);
                cmnd.Parameters.AddWithValue(@role, comboBox1.SelectedItem.ToString());
                con.Open();
                int i = cmd.ExecuteNonQuery();

                if (i != 0)
                {
                    MessageBox.Show("Staff information added successfully");

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

        }

推荐答案

嗯......你还可以添加一行：

Well...you could add one more line:

int i = cmd.ExecuteNonQuery();
int j = cmnd.ExecuteNonQuery();

但是 - 你真的需要考虑你在这里做了什么。

你需要查看SqlTransactions - 因此，如果第二个插入失败，第一个插入可以撤消：否则您的数据库将处于未确定状态，后续代码将失败。

您还需要停止以明文形式存储密码 - 它是一个主要的安全风险！请看这里：密码存储：如何操作。 [ ^ ]



但是你不需要两个查询来做到这一点 - 你可以一体化完成。在你做到这一点之前，我认为你需要对你的数据库设计有一个很好的想法：从代码中可以清楚地看出这两个表在某种程度上是相关的 - 它们都包含与同一个人相关的信息。但是，您保存的数据无法将两个表行连接在一起。因此，当您稍后尝试检索它们时，它不起作用！您需要考虑如何在以后的代码中获取数据并重新设计数据库，以便您可以轻松获取所有相关信息。或者你以后会为自己做很多工作！

But - you really do need to think about what you are doing here.
You need to look up about SqlTransactions - so that if the second insert fails the first one can be undone: otherwise your DB is left in a undetermined state and subsequent code will fail.
You also need to stop storing passwords in clear text - it is a major security risk! Have a look here: Password Storage: How to do it.[^]

But you don't need two queries to do this - you can do it all in one. Before you get to that though, I think you need to have a good think about your database design: it's clear from the code that these two tables are related in some way - they both contain information related to the same individual. But there is no way in the data you are saving to "connect" the two table rows together. So when you try to retrieve them later, it doesn't work! You need to think about how you are going to fetch the data in later code and redesign your DB so that you can get all related info easily. Or you are going to make a lot of work for yourself later!

这篇关于如何从2个不同的表中添加信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！