将值插入带有标识的两个表中 [英] Insert values into two tables with identity

问题描述

 SqlCommand cmd =  new  SqlCommand（  order1_sp，conn）; 
 conn.Open（）; 
 cmd.Parameters.Add（  @ OrderNo，SqlDbType.Int）.Value = details.OrderNo; 
 cmd.Parameters.Add（  @ CustomerName，SqlDbType.NVarChar）.Value = details.CustomerName; 
 cmd.Parameters.Add（  @ TableNo，SqlDbType.Int）.Value = details.TableNo; 
 cmd.Parameters.Add（  @ WaiterNo，SqlDbType.Int）.Value = details.WaiterNo; 
 cmd.Parameters.Add（  @ Tax，SqlDbType.Int）.Value = details.Tax; 
 cmd.Parameters.Add（  @ TotalPrice，SqlDbType.Int）.Value = details.TotalPrice; 
 cmd.Parameters.Add（  @ GrandTotal，SqlDbType.Float）.Value = details.GrandTotal; 
 cmd.CommandType = CommandType.StoredProcedure; 
  //   Todo执行订单sp  
 
  Int32  OrderNo =  1 ; 
  //   todo获取标识122填写OrderId  
  //   details.OrderDetails  
  foreach （OrderDetail objOrderDetails  in  details.OrderDetails）
 {
  //  调用sp命令详细信息sp  
 cmd =  new  SqlCommand（  orderdetails_sp，conn）; 
 cmd.Parameters.Add（  @ OrderNo，SqlDbType.Int）.Value =订单号; 
 cmd.Parameters.Add（  @ Code，SqlDbType.Int）.Value = objOrderDetails.Code; 
 cmd.Parameters.Add（  @ Description，SqlDbType.VarChar）.Value = objOrderDetails.Descritpion; 
 cmd.Parameters.Add（  @ Rate，SqlDbType.Int）.Value = objOrderDetails.Rate; 
 cmd.Parameters.Add（  @ Qty，SqlDbType.Int）.Value = objOrderDetails.Qty; 
 cmd.Parameters.Add（  @ Total，SqlDbType.VarChar）.Value = objOrderDetails.Total; 
 cmd.CommandType = CommandType.StoredProcedure; 
  //  执行订单明细sp  
 
} 

解决方案

看起来您想要将数据插入两个表并捕获第一个表的主键值并将其插入第二个表中。您可以使用 SCOPE_IDENTITY [ ^ ]获取主键的值。

这是一个示例方法

  BEGIN  TRY 
  BEGIN   TRANSACTION  
 
 
  DECLARE   @ OrderID   INT  
  INSERT   INTO 订单（OrderNo，CustomerName，TableNo） VALUES （ @OrderNo ， @ CustomerName ， @ TableNo ）
 
 < span class =code-comment>  -   获取插入行的主键值 
  SET   @ OrderID  =  SCOPE_IDENTITY （）
 
  INSERT   INTO  orderdetails（OrderID，OrderNo，Code，Description） VALUES （ @ OrderID ，  @ OrderNo ， @ Code ， @ Description  ）
 
 
  COMMIT   TRANSACTION  
  END  TRY 
  BEGIN  CATCH 
  ROLLBACK   TRANSACTION  
   -   错误处理 
  END  CATCH 

编辑：

如果您要插入多个订单单个订单的详细信息，您可以拥有2个存储过程。

第一个存储过程插入订单数据并返回OrderID

这样的东西

  DECLARE   @ OrderID   INT  
  INSERT   INTO 订单（OrderNo，CustomerName，TableNo） VALUES  （ @ OrderNo ， @ CustomerName ， @ TableNo ）
 
   -   获取插入行的主键值 
  SET   @ OrderID  =  SCOPE_IDENTITY  （）
  SELECT   @ OrderID   AS  OrderID 
 

然后你可以使用 SqlCommand .ExecuteScalar方法 [ ^ ]检索OrderID并将其存储在应用程序的变量中。您可以在循环查看订单详细信息时将此变量值作为订单ID传递。

尝试以下链接。



您可以使用以下命令返回标识字段。在程序中插入查询后，你必须写 return @@ identity 。

 cm.Parameters.Add（  @ ReturnValue，SqlDbType.Int）; 
 cm.Parameters [  @ ReturnValue]。Direction = ParameterDirection.ReturnValue; 

http://stackoverflow.com/questions/5412256/return-value-using-sqlcommand [ ^ ]



谢谢

SqlCommand cmd = new SqlCommand("order1_sp", conn);
        conn.Open();
        cmd.Parameters.Add("@OrderNo", SqlDbType.Int).Value = details.OrderNo;
        cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = details.CustomerName;
        cmd.Parameters.Add("@TableNo", SqlDbType.Int).Value = details.TableNo;
        cmd.Parameters.Add("@WaiterNo", SqlDbType.Int).Value = details.WaiterNo;
        cmd.Parameters.Add("@Tax", SqlDbType.Int).Value = details.Tax;
        cmd.Parameters.Add("@TotalPrice", SqlDbType.Int).Value = details.TotalPrice;
        cmd.Parameters.Add("@GrandTotal", SqlDbType.Float).Value = details.GrandTotal;
        cmd.CommandType = CommandType.StoredProcedure;
        // Todo execute order sp 

        Int32 OrderNo = 1;
        //todo get the identity  122 fill the OrderId
        //details.OrderDetails
        foreach (OrderDetail objOrderDetails in details.OrderDetails)
        {
            // call sp order details sp
            cmd = new SqlCommand("orderdetails_sp", conn);
            cmd.Parameters.Add("@OrderNo", SqlDbType.Int).Value = OrderNo;
            cmd.Parameters.Add("@Code", SqlDbType.Int).Value = objOrderDetails.Code;
            cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = objOrderDetails.Descritpion;
            cmd.Parameters.Add("@Rate", SqlDbType.Int).Value = objOrderDetails.Rate;
            cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = objOrderDetails.Qty;
            cmd.Parameters.Add("@Total", SqlDbType.VarChar).Value = objOrderDetails.Total;
            cmd.CommandType = CommandType.StoredProcedure;
            // execute order detail sp

        }

解决方案

It looks like you want to insert data into two tables and capture the primary key value of the first table and insert it into the second table. You can use SCOPE_IDENTITY [^] to get the value of the primary key.
Here is a sample approach

BEGIN TRY  
  BEGIN TRANSACTION  
 
    
      DECLARE @OrderID INT
      INSERT INTO Orders (OrderNo, CustomerName, TableNo) VALUES (@OrderNo, @CustomerName, @TableNo)
	
	  --Get the primary key value for the row inserted
      SET @OrderID = SCOPE_IDENTITY()   
 
      INSERT INTO orderdetails (OrderID, OrderNo, Code, Description) VALUES (@OrderID, @OrderNo, @Code, @Description)

  
  COMMIT TRANSACTION  
 END TRY  
 BEGIN CATCH  
  ROLLBACK TRANSACTION  
  --Error handling
 END CATCH

Edit:
If your are inserting multiple order details for a single order you can have 2 stored procedures.
The 1st stored procedure inserts the order data and returns the OrderID
Something like this

DECLARE @OrderID INT
INSERT INTO Orders (OrderNo, CustomerName, TableNo) VALUES (@OrderNo, @CustomerName, @TableNo)

--Get the primary key value for the row inserted
SET @OrderID = SCOPE_IDENTITY()
SELECT @OrderID AS OrderID

Then you can use SqlCommand.ExecuteScalar Method[^] to retrieve the OrderID and store it in a variable in your application. You can pass this variable value as Order ID while looping through the order details.

Try the following link.

You can use the following for returning the identity field. you must have to write return @@identity after the insert query in your procedure.

cm.Parameters.Add("@ReturnValue", SqlDbType.Int);
cm.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

http://stackoverflow.com/questions/5412256/return-value-using-sqlcommand[^]

Thanks

这篇关于将值插入带有标识的两个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！