与单个存储过程相比,单独SQL命令的性能

我继承了一些与GP接口的代码。在调试问题之前查看代码时,我发现一个函数可以撤消对一组表的更新。

这段代码包含许多SQL命令,我想知道使用一个输入参数(即表)的单个存储过程是否可以获得更好的性能 ds公司 而不是分别在表的每一行上运行所有这些命令。

    private void revertSave()
    {
        try
        {
            SqlConnection conn = GetConnectionToGP();
            if (conn != null)
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //records from InventoryTransaction Master, Detail, and Serial tables when window is opened.
                    foreach (DataRow dr in _ds.Tables[0].Rows)
                    {
                        cmd.Connection = conn;
                        //TRANSACTION tables
                        cmd.CommandText = "UPDATE InventoryTransaction_Master " +
                            "SET Completed = 0 WHERE DocumentNumber = " + SqlString(dr["DocumentNumber"].ToString().Trim());
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "UPDATE InventoryTransaction_Serial " +
                            "SET BIN = '' " +
                            "WHERE DocumentNumber = " + SqlString(dr["DocumentNumber"].ToString().Trim()) +
                            " and DocumentType = 1 and ItemNumber = " + SqlString(dr["PrefixedItemNumber"].ToString().Trim()) +
                            " and LineSequenceNumber = " + SqlString(dr["LineSequenceNumber"].ToString().Trim()) +
                            " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                        cmd.ExecuteNonQuery();

                        //TRANSFER tables
                        cmd.CommandText = "SELECT DISTINCT detail.DocumentNumber " +
                            "FROM  InventoryTransfer_Detail detail " +
                            "INNER JOIN InventoryTransfer_Serial serial ON detail.DocumentNumber = serial.DocumentNumber " +
                            "and detail.ItemNumber = serial.ItemNumber " +
                            "and detail.LineSequenceNumber = serial.LineSequenceNumber " +
                            "WHERE SessionID = " + SqlString(dr["SessionID"].ToString().Trim()) +
                            " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                        {
                            cmd.CommandText = "UPDATE InventoryTransfer_Master " +
                                "SET Completed = 0 WHERE DocumentNumber = " + SqlString(obj.ToString());
                            cmd.ExecuteNonQuery();

                            cmd.CommandText = "UPDATE InventoryTransfer_Serial " +
                                "SET OriginalBin = '', NewBin = '' " +
                                "WHERE DocumentNumber = " + SqlString(obj.ToString()) + " and SerialNumber = " + SqlString(dr["SerialNumber"].ToString().Trim());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

            //this.Close();
        }
        catch (Exception ex)
        {
            ELog el = new ELog(ex, CLASS_NAME + " revertSave");
        }
    }