c# - 如何使用存储过程更新现有行并插入新行？

Question

我在 C# 和 SQL Server 中有一个表单。我正在使用 DATAGRIDVIEW 添加新行并使用以下存储过程更新现有行：

ALTER PROCEDURE [dbo].[APPROVE_MICRO_RESULTS_DETAILS]
    @SAMPLE_ID int,
    @ORDER_ID int,
    @TESTID int,
    @SAMPLE_STATUS int,
    @organism_id int,
    @Antibiotic_id int,
    @sensitivityId int
AS
    -- just try to UPDATE the row
   UPDATE [dbo].[LAB_MICRO_RESULTS_DETAILS]
   SET [sample_id] = @sample_id,
       [order_id] = @order_id,
       [testid] = @testid,
       [sample_status] = @sample_status,
       [organism_id] = @organism_id,
       [Antibiotic_id] = @Antibiotic_id,
       [sensitivityId] = @sensitivityId
   WHERE order_id = @order_id
     AND TESTID = @TESTID

    -- check if update was unsuccessful --> row doesn't exist yet, so insert
   IF @@ROWCOUNT = 0
       INSERT INTO [dbo].[LAB_MICRO_RESULTS_DETAILS]
           ([sample_id], [order_id], [testid], [sample_status], [organism_id], [Antibiotic_id], [sensitivityId])
       VALUES (@sample_id, @order_id, @testid, @sample_status, @organism_id, @Antibiotic_id, @sensitivityId)
          

之前的现有行有 Id No 这是主键，新插入到 GRIDVIEW 的行仍然没有 Id No，如下图所示：

如何更新存储过程以更新现有行并将新行插入数据库？

先感谢您。

这是保存按钮中的代码：

for (int i = 0; i < dgvcultures.Rows.Count - 1; i++)
{
    result.APPROVE_MICRO_RESULTS_DETAILS(Convert.ToInt32(txtsample.Text),
                                         Convert.ToInt32(txtOrder.Text),
                                         Convert.ToInt32(txtTestId.Text),
                                         6,
                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[0].Value),
                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[2].Value),
                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[4].Value));
}

标签： c#sql-server

Answer 1

我通过划分交易解决了它，

我创建了 2 个存储过程，一个用于插入，一个用于更新：

    create proc [APPROVE_ADD_MICRO_RESULTS_DETAILS]
    
    @SAMPLE_ID int,
    @ORDER_ID int,
    @TESTID int,
    @SAMPLE_STATUS int,
    @organism_id int,
    @Antibiotic_id int,
    @sensitivityId int
    
    
    as 
    
        
    INSERT INTO [dbo].[LAB_MICRO_RESULTS_DETAILS]
               ([sample_id]
               ,[order_id]
               ,[testid]
               ,[sample_status]
               ,[organism_id]
               ,[Antibiotic_id]
               ,[sensitivityId])
            
         VALUES
               (@sample_id
               ,@order_id
               ,@testid
               ,@sample_status
               ,@organism_id
               ,@Antibiotic_id
               ,@sensitivityId)

------------------------------------------

create proc [APPROVE_UPDATE_MICRO_RESULTS_DETAILS]

@SAMPLE_ID int,
@ORDER_ID int,
@TESTID int,
@SAMPLE_STATUS int,
@organism_id int,
@Antibiotic_id int,
@sensitivityId int,
@cult_id int

as 

    -- just try to UPDATE the row
   UPDATE [dbo].[LAB_MICRO_RESULTS_DETAILS]
   SET [sample_id] = @sample_id
   ,[order_id] = @order_id
   ,[testid] = @testid
   ,[sample_status] = @sample_status
   ,[organism_id] = @organism_id
   ,[Antibiotic_id] = @Antibiotic_id
   ,[sensitivityId] = @sensitivityId
   
   WHERE order_id = @order_id
   and   TESTID = @TESTID
   and   cult_id  = @cult_id

另外，如果我想从 DATAGRIDVIEWS 中删除行

我创建了 datagridview 删除复选框并填充了它：

DataGridViewCheckBoxColumn checkboxdelete = new DataGridViewCheckBoxColumn();
  dgvcultures.Columns.Insert(0, checkboxdelete);
                checkboxdelete.HeaderText = "Delete";
                checkboxdelete.Name = "delete";

最后在保存按钮中，我删除了选中的行和

使用以下代码插入新行并更新更新的行：

 // delete checked rows 
                foreach (DataGridViewRow row in dgvcultures.Rows)
                {
                    
                    bool status = (bool)row.Cells[0].FormattedValue;
                    if (status)
                    {
                        string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                        SqlConnection con = new SqlConnection(cs);
                        SqlCommand cmd = new SqlCommand("delete from LAB_MICRO_RESULTS_DETAILS where order_id = '" + txtOrder.Text  + "' and cult_id = '" + row.Cells[7].Value.ToString() + "'", con);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }

                // insert new inserted rows 
                for (int i = 0; i < dgvcultures.Rows.Count ; i++)
                {
                    string idNo = dgvcultures.Rows[i].Cells[7].Value.ToString();

                    if (String.IsNullOrEmpty(idNo) || idNo == null || idNo == DBNull.Value.ToString())
                    {
                        result.APPROVE_ADD_MICRO_RESULTS_DETAILS(Convert.ToInt32(txtsample.Text),
                        Convert.ToInt32(txtOrder.Text),
                        Convert.ToInt32(txtTestId.Text),
                        6,
                        Convert.ToInt32(dgvcultures.Rows[i].Cells[1].Value),
                        Convert.ToInt32(dgvcultures.Rows[i].Cells[3].Value),
                        Convert.ToInt32(dgvcultures.Rows[i].Cells[5].Value));
                    }

                    // update rows
                    else if (!String.IsNullOrEmpty(idNo) || idNo != null || idNo != DBNull.Value.ToString())
                    {
                        result.APPROVE_UPDATE_MICRO_RESULTS_DETAILS(Convert.ToInt32(txtsample.Text),
                                                         Convert.ToInt32(txtOrder.Text),
                                                         Convert.ToInt32(txtTestId.Text),
                                                         6,
                                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[1].Value),
                                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[3].Value),
                                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[5].Value),
                                                         Convert.ToInt32(dgvcultures.Rows[i].Cells[7].Value));
                    }

                

            }