CODEWITHSUNDEEP
c#sql-serverstored-procedures
Daniel
Daniel

Reputation: 2295

Stored Procedure providing inconsistent return values

I'm working on a web form that validates data that the user uploads in the form of an Excel file. The code iterates through each row in the spreadsheet and checks against various rules, one of which is that the reference must be a unique value. I have a stored procedure that takes the userID and referenceNum as parameters:

BEGIN
    SET NOCOUNT ON;

    DECLARE @Status AS BIT

    IF EXISTS (SELECT [TransactionMstID] 
               FROM [dbo].[tbl_TransactionMst] 
               WHERE [TransactionRef] = @DocumentNumber 
                 AND [SupplierID] = @SupplierID)
    BEGIN 
        SET @Status = 0
    END
    ELSE
    BEGIN
        SET @Status = 1
    END

    SELECT @Status AS [Status]
END

When I try different scenarios in SQL Server Management Studio (SSMS), I get the desired outputs e.g. a "0" when the reference exists and a "1" when it doesn't.

The problem arises in my C# code, it executes the stored procedure, but in my testing I get the same result irrespective of whether the data exists or not.

Here's the core of the C#:

    bool returnValue = true;

    if (Docnumber != null)
    {
        SqlConnection con = new SqlConnection(GlobalSettings.connection);
        con.Open();

        SqlCommand Cmd = new SqlCommand("p_ValRefNumber", con);
        Cmd.CommandType = System.Data.CommandType.StoredProcedure;

        Cmd.Parameters.AddWithValue("@DocumentNumber", Docnumber);
        Cmd.Parameters.AddWithValue("@SupplierID", SupplierID);

        Cmd.ExecuteNonQuery();

        SqlDataReader dr = Cmd.ExecuteReader();

        while (dr.Read())
        {
            bool Status = convertor.ConvertToBool(dr["Status"]);
            string test = dr["Status"].ToString();
            int testint = convertor.ConvertToInt(dr["Status"].ToString());

            if (Status == false)
            {
                //throw new System.Exception(CEObj.GetErrorDesc(101));
                returnValue = false;
            }
        }

        dr.Close();
        con.Close();
    }

    return returnValue;
}

No matter what the value of docnumber is in testing, it always shows as True. I've added a breakpoint so that I can check each time and then test in SSMS and I get conflicting results.

Is my logic wrong? Does Visual Studio treat the values differently? How is the result not consistent when converting it to a string - at the very least? It always seems to read a value of "1" in VS but varies in SSMS

Edit: here's my converter method's code:

public static bool ConvertToBool(object value)
{
        bool result = false;

        if (value != null)
        {
            bool.TryParse(value.ToString(), out result);
        }

        return result;
}

Upvotes: 1

Views: 172

Answers (1)

Chris R. Timmons
Chris R. Timmons

Reputation: 2197

bool.TryParse isn't doing what the convertor (sic) code thinks it does.

bool.TryParse returns true if the value parameter equals bool.TrueString, which is the literal string "True". It returns false for any other value, which means it returns false for both 0 and 1.

Also, T-SQL bit values are numbers. The converter code isn't really necessary - just convert the return value to an Int32 and do a comparison.

using (var con = new SqlConnection(GlobalSettings.connection))
{
  con.Open();
  using (var cmd = new SqlCommand() { Connection = con, CommandType = CommandType.StoredProcedure, CommandText = "p_ValRefNumber" })
  {
    /* Assuming both parameters are integers.
       Change SqlDbType if necessary. */
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@DocumentNumber", SqlDbType = SqlDbType.Int, Value = Docnumber });
    cmd.Parameters.Add(new SqlParameter() { ParameterName = "@SupplierID", SqlDbType = SqlDbType.Int, Value = SupplierID });

    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
      return dr.Read() && (Convert.ToInt32(dr["Status"]) == 1)
    }
  }
}

Upvotes: 2

Related Questions