如何从代码中插入主键作为SQL数据库表中的参数？

I have two tables (Primary_Table and Secondary_table). Primary_Table contains primary key which is a foreign key for Secondary_table. Whenever I insert a new row into Primary_table I need to take its primary key and insert it into secondary_table. I am facing difficulties in taking primary key as a parameter. I need help on this.

我有两个表(Primary_Table和Secondary_table)。 Primary_Table包含主键,它是Secondary_table的外键。每当我在Primary_table中插入一个新行时,我需要获取其主键并将其插入secondary_table。我将主键作为参数面临困难。我需要帮助。

 

string connString = "Database=MyServerDB;Server=ATLW732FV000169\SQLEXPRESS;Integrated Security=True;connect timeout = 30"; SqlConnection myConnection = new SqlConnection(connString); try { myConnection.Open(); }
 catch (Exception e) { Console.WriteLine(e.ToString()); }
string connString =“Database = MyServerDB; Server = ATLW732FV000169 \ SQLEXPRESS; Integrated Security = True; connect timeout = 30”; SqlConnection myConnection = new SqlConnection(connString);试试{myConnection.Open(); } catch(Exception e){Console.WriteLine(e.ToString()); }

        using (SqlCommand command = new SqlCommand("INSERT INTO Primary_Table (TransactionType, Country) "VALUES (@transactionType, @country);"  +
            "INSERT INTO Secondary_table (BookingID,TripID) VALUES (@bookingID, @tripID);" , myConnection))
        {           
            // declaring Primary_Table paramteres
            command.Parameters.AddWithValue("@transactionType", "S");
            command.Parameters.AddWithValue("@country", "US");

            // declaring Secondary_table parameters

            command.Parameters.Add("@bookingID", *****);  // Not sure how to insert bookingID (priamry key) into Secondary Table
            command.Parameters.AddWithValue("@tripID", "tr");

            try
            {
                Int32 lines = command.ExecuteNonQuery();
                Console.WriteLine("Lines affected " + lines);
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

3 个解决方案

#1

Define a trigger AFTER INSERT on Primary_Table that will take primary key of inserted data and insert it in to secondary_table.

在Primary_Table上定义一个触发器AFTER INSERT,它将获取插入数据的主键并将其插入到secondary_table中。

#2

Do this:

After your first insert -

第一次插入后 -

DECLARE @var int

@var = select top 1 [pk] from table1 order by pk desc

This will get you the most recent record from table1 with @var being the pk, then simply...

这将从table1获取最新的记录,其中@var是pk,然后简单地......

insert into table2 ([fk]) values (@var)

This is easiest to do inside a stored procedure, which is considered best practice anyways (don't do inline SQL in your .NET code ever).

这在存储过程中最容易做,无论如何这被认为是最佳实践(不要在.NET代码中进行内联SQL)。

#3

you can insert in to primary and secondary table via one storedprocedure in sql :

您可以通过sql中的一个storedprocedure插入主表和辅助表:

declare @Primary_Id int

声明@Primary_Id int

INSERT INTO Primary_Table (TransactionType, Country) VALUES (@transactionType, @country) set @Primary_Id =@@identity

INSERT INTO Primary_Table(TransactionType,Country)VALUES(@ tranactionType,@ country)设置@Primary_Id = @@ identity

INSERT INTO Secondary_table (BookingID,TripID) VALUES (@bookingID, @Primary_Id)

INSERT INTO Secondary_table(BookingID,TripID)VALUES(@ bookingID,@ Primary_Id)

and then in code : command.CommandType= CommandType.StoredProcedure;

然后在代码中:command.CommandType = CommandType.StoredProcedure;

#1