Say I have this stored procedure that I have no control over (and no access to the third party db).
How do I know if it worked?
BEGIN
Update USR
Set usr_psswrd = @NewPassword
where
usr_usrnme = @UserName and usr_psswrd = @OldPassword
END
I know how to get rows when there's a select statement in a stored procedure and read those rows but I have no idea how to check if this stored procedure worked or not.
This is what I'm doing so far that doesn't work. The stored procedure works because the password does change I just don't know what to do after the fact.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "USP_ChangePassword";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = email;
command.Parameters.Add("@OldPassword", SqlDbType.VarChar).Value = oldPW;
command.Parameters.Add("@NewPassword", SqlDbType.VarChar).Value = newPW;
try
{
// Open the connection and execute the reader.
connection.Open();
command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
success = true;
}
reader.Close();
}
catch (SqlException ex)
{
System.Diagnostics.Debug.Write("SqlException Error " + ex.Number + ": " + ex.Message);
}
catch (InvalidOperationException ex)
{
System.Diagnostics.Debug.Write("Invalid Op Error: " + ex.Message);
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write("Error: " + ex.Message);
}
finally
{
connection.Close();
}
}
IN the documentation about ExecuteNonQuery you could find
> Return Value
> Type: System.Int32
> The number of rows affected.
So you could change your code to
try
{
// Open the connection and execute the reader.
connection.Open();
int rowsUpdated = command.ExecuteNonQuery();
if(rowsUpdated > 0)
{
success = true;
}
}
This is the normal behavior of ExecuteNonQuery, but check if your stored procedure contains the statements
SET NOCOUNT ON
if you have this line, then ExecuteNonQuery cannot return the number of rows affected and you get always a -1 as return value. If you cannot change that stored procedure, then you are in trouble.
The only workaround that comes to mind is to get back the user data with a SELECT query and check against the inserted data (a very uncomfortable situation)
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다