将Emp表的主键插入Dept是一个外键 [英] Inserting Primary Key of Emp table into Dept is a foreign key

问题描述

我有一种情况，我想将EMP的主键放入DEPT表中

EMP emp.id emp.name emp.date

DEPT dept.id dept.name emp.id

I had a situation where I want to put Primary key of EMP into DEPT Tables are
EMP emp.id emp.name emp.date
DEPT dept.id dept.name emp.id

using (SqlConnection conn = new SqlConnection(conStr))
           {

               SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "INSERT INTO EMP(empname,empdate) VALUES(@Name, @Date);"
           + "INSERT INTO DEP(deptname, empid) VALUES(@deptname, @empid);"
               cmd.CommandType = CommandType.Text;
               cmd.Connection = conn;
               cmd.Parameters.AddWithValue("@Name", txtb1.Text);
               cmd.Parameters.AddWithValue("@Date", txtb2.Text);
               cmd.Parameters.AddWithValue("@deptname", txtb3.Text);
               cmd.Parameters.AddWithValue("@empid", "select empid from emp where empid = new inserted record");

从emp中选择empid = empid = new inserted record这意味着我想选择我自己的ID现在要插入...作为可行的或任何其他方式...

"select empid from emp where empid = new inserted record" this means that i want to select id which i am going to insert now... as that posible or any other way to do so...

推荐答案

如评论中所述，存储过程会更好做法。但是，您仍然可以使用内联SQL执行此操作：

As mentioned in the comments, a stored procedure would be a better approach. However, you can still do it with inline SQL:

const string query = @"DECLARE @EmpID int;

INSERT INTO EMP (empname, empdate) VALUES (@Name, @Date);
SET @EmpID = Scope_Identity();

INSERT INTO DEP (deptname, empid) VALUES (@deptname, @EmpID);";

using (SqlConnection conn = new SqlConnection(conStr))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@Name", txtb1.Text);
    cmd.Parameters.AddWithValue("@Date", txtb2.Text);
    cmd.Parameters.AddWithValue("@deptname", txtb3.Text);
    
    conn.Open();
    cmd.ExecuteNonQuery();
}

这篇关于将Emp表的主键插入Dept是一个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！