ASP.NET SQL Server如何判断是否终止存储过程

Question

i具有以下存储过程，将15米读数插入数据库中。如果在插入过程中没有错误，则该过程正常工作。我遇到问题的地方是何时意外插入重复记录（违反了PK约束）。在这种情况下，该语句已终止，我的@insertsuccess变量未适当设置。

这是存储的proc：

CREATE PROCEDURE [dbo].[sp_insert_meter_readings] 
    -- Add the parameters for the stored procedure here
    @DateTime datetime,
    @MainCityMeasValue float,
    @ReheatMeterMeasValue float,
    @CasterOilMeasValue float,
    @MillCityMeasValue float,
    @NumOneMeterMeasValue float,
    @NumTwoMeterMeasValue float,
    @NumThreeMeterMeasValue float,
    @NumSixMeterMeasValue float,
    @MillWellMeasValue float,
    @MainWellMeasValue float,
    @SprayMakeUpMeasValue float,
    @MachMakeUpMeasValue float,
    @NumFiveCitySprayMakeUpMeasValue float,
    @NumFiveCityMachMakeUpMeasValue float,
    @NumFiveBlowDownMeasValue float,
    @InsertSuccess int output
AS
BEGIN
    DECLARE
    @MainWellMeasNum AS INT = 42,
    @MainCityMeasNum AS INT = 43,
    @NumOneMeterMeasNum AS INT = 45,
    @NumTwoMeterMeasNum AS INT = 44,
    @NumThreeMeterMeasNum AS INT = 46,
    @NumSixMeterMeasNum AS INT = 47,
    @ReheatMeterMeasNum AS INT = 49,
    @MillWellMeasNum AS INT = 50,
    @MillCityMeasNum AS INT = 258,
    @SprayMakeUpMeasNum AS INT = 348,
    @MachMakeUpMeasNum AS INT = 349,
    @NumFiveCityMachMakeUpMeasNum AS INT = 372,
    @NumFiveCitySprayMakeUpMeasNum AS INT = 373,
    @NumFiveBlowDownMeasNum AS INT = 374,
    @CasterOilMeasNum AS INT = 436
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    INSERT INTO MainTable VALUES (@DateTime, @MainCityMeasNum, @MainCityMeasValue),
                             (@DateTime, @ReheatMeterMeasNum, @ReheatMeterMeasValue),
                             (@DateTime, @CasterOilMeasNum, @CasterOilMeasValue),
                             (@DateTime, @MillCityMeasNum, @MillCityMeasValue),
                             (@DateTime, @NumOneMeterMeasNum, @NumOneMeterMeasValue),
                             (@DateTime, @NumTwoMeterMeasNum, @NumTwoMeterMeasValue),
                             (@DateTime, @NumThreeMeterMeasNum, @NumThreeMeterMeasValue),
                             (@DateTime, @NumSixMeterMeasNum, @NumSixMeterMeasValue),
                             (@DateTime, @MillWellMeasNum, @MillWellMeasValue),
                             (@DateTime, @MainWellMeasNum, @MainWellMeasValue),
                             (@DateTime, @SprayMakeUpMeasNum, @SprayMakeUpMeasValue),
                             (@DateTime, @MachMakeUpMeasNum, @MachMakeUpMeasValue),
                             (@DateTime, @NumFiveCitySprayMakeUpMeasNum, @NumFiveCitySprayMakeUpMeasValue),
                             (@DateTime, @NumFiveCityMachMakeUpMeasNum, @NumFiveCityMachMakeUpMeasValue),
                             (@DateTime, @NumFiveBlowDownMeasNum, @NumFiveBlowDownMeasValue)
    IF @@ROWCOUNT = 15
    SET @InsertSuccess = 1
    ELSE
    SET @InsertSuccess = 0
END

，以防万一有人需要，这是我的代码：

 int insertSuccess;
            SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            con.Open();
        using (SqlCommand com = new SqlCommand("sp_insert_meter_readings", con))
        {
            com.CommandType = System.Data.CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@DateTime", Session["curShift"]));
            com.Parameters.Add(new SqlParameter("@MainCityMeasValue", Session["mainCityMeter"]));
            com.Parameters.Add(new SqlParameter("@ReheatMeterMeasValue", Session["reheatMeter"]));
            com.Parameters.Add(new SqlParameter("@CasterOilMeasValue", Session["casterOilMeter"]));
            com.Parameters.Add(new SqlParameter("@MillCityMeasValue", Session["millCityMeter"]));
            com.Parameters.Add(new SqlParameter("@NumOneMeterMeasValue", Session["numOneMeter"]));
            com.Parameters.Add(new SqlParameter("@NumTwoMeterMeasValue", Session["numTwoMeter"]));
            com.Parameters.Add(new SqlParameter("@NumThreeMeterMeasValue", Session["numThreeMeter"]));
            com.Parameters.Add(new SqlParameter("@NumSixMeterMeasValue", Session["numSixmeter"]));
            com.Parameters.Add(new SqlParameter("@MillWellMeasValue", Session["millWellMeter"]));
            com.Parameters.Add(new SqlParameter("@MainWellMeasValue", Session["mainWellMeter"]));
            com.Parameters.Add(new SqlParameter("@SprayMakeUpMeasValue", Session["sprayMakeUpWellMeter"]));
            com.Parameters.Add(new SqlParameter("@MachMakeUpMeasValue", Session["machMakeUpWellMeter"]));
            com.Parameters.Add(new SqlParameter("@NumFiveCitySprayMakeUpMeasValue", Session["numFiveCitySprayMakeUpMeter"]));
            com.Parameters.Add(new SqlParameter("@NumFiveCityMachMakeUpMeasValue", Session["numFiveMachMakeUpMeter"]));
            com.Parameters.Add(new SqlParameter("@NumFiveBlowDownMeasValue", Session["numFiveBlowDownMeter"]));
            var returnParam1 = new SqlParameter
            {
                ParameterName = "@InsertSuccess",
                Direction = System.Data.ParameterDirection.Output,
                SqlDbType = System.Data.SqlDbType.Int
            };
            com.Parameters.Add(returnParam1);
            com.ExecuteNonQuery();
            insertSuccess = (int)com.Parameters["@InsertSuccess"].Value;
            if (insertSuccess == 1)
            {
                MessageLabel.ForeColor = System.Drawing.Color.DarkGreen;
                MessageLabel.Text = "Insert Successful.";
            }
            else
            {
                MessageLabel.ForeColor = System.Drawing.Color.Red;
                MessageLabel.Text = "There was an error with the insert.";
            }

            con.Close();
        } 

调试时，我可以看到SP在com.executenonquery（）行中终止，并且我的代码不会提高到insertsuccess =（int）com.parameters ["@insertsuccess"]。value; value;行，所以我的消息永远不会显示给用户。

我的研究仅显示了如何使用sp中的if @@ rowcount块从SP中返回消息。

还有其他建议吗？

谢谢

Answer 1

修改存储过程以包含try catch块，以便您始终从中获得返回值。

CREATE PROCEDURE [dbo].[sp_insert_meter_readings] 
-- Add the parameters for the stored procedure here
@DateTime datetime,
@MainCityMeasValue float,
@ReheatMeterMeasValue float,
@CasterOilMeasValue float,
@MillCityMeasValue float,
@NumOneMeterMeasValue float,
@NumTwoMeterMeasValue float,
@NumThreeMeterMeasValue float,
@NumSixMeterMeasValue float,
@MillWellMeasValue float,
@MainWellMeasValue float,
@SprayMakeUpMeasValue float,
@MachMakeUpMeasValue float,
@NumFiveCitySprayMakeUpMeasValue float,
@NumFiveCityMachMakeUpMeasValue float,
@NumFiveBlowDownMeasValue float,
@InsertSuccess int output
AS
BEGIN
DECLARE
@MainWellMeasNum AS INT = 42,
@MainCityMeasNum AS INT = 43,
@NumOneMeterMeasNum AS INT = 45,
@NumTwoMeterMeasNum AS INT = 44,
@NumThreeMeterMeasNum AS INT = 46,
@NumSixMeterMeasNum AS INT = 47,
@ReheatMeterMeasNum AS INT = 49,
@MillWellMeasNum AS INT = 50,
@MillCityMeasNum AS INT = 258,
@SprayMakeUpMeasNum AS INT = 348,
@MachMakeUpMeasNum AS INT = 349,
@NumFiveCityMachMakeUpMeasNum AS INT = 372,
@NumFiveCitySprayMakeUpMeasNum AS INT = 373,
@NumFiveBlowDownMeasNum AS INT = 374,
@CasterOilMeasNum AS INT = 436
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY
INSERT INTO MainTable VALUES (@DateTime, @MainCityMeasNum, @MainCityMeasValue),
                         (@DateTime, @ReheatMeterMeasNum, @ReheatMeterMeasValue),
                         (@DateTime, @CasterOilMeasNum, @CasterOilMeasValue),
                         (@DateTime, @MillCityMeasNum, @MillCityMeasValue),
                         (@DateTime, @NumOneMeterMeasNum, @NumOneMeterMeasValue),
                         (@DateTime, @NumTwoMeterMeasNum, @NumTwoMeterMeasValue),
                         (@DateTime, @NumThreeMeterMeasNum, @NumThreeMeterMeasValue),
                         (@DateTime, @NumSixMeterMeasNum, @NumSixMeterMeasValue),
                         (@DateTime, @MillWellMeasNum, @MillWellMeasValue),
                         (@DateTime, @MainWellMeasNum, @MainWellMeasValue),
                         (@DateTime, @SprayMakeUpMeasNum, @SprayMakeUpMeasValue),
                         (@DateTime, @MachMakeUpMeasNum, @MachMakeUpMeasValue),
                         (@DateTime, @NumFiveCitySprayMakeUpMeasNum, @NumFiveCitySprayMakeUpMeasValue),
                         (@DateTime, @NumFiveCityMachMakeUpMeasNum, @NumFiveCityMachMakeUpMeasValue),
                         (@DateTime, @NumFiveBlowDownMeasNum, @NumFiveBlowDownMeasValue)
SET @InsertSuccess = 1
END TRY 
BEGIN CATCH
SET @InsertSuccess = 0        
END CATCH
END