There are a couple different ways of doing this. The most efficient would be to use a single stored procedure that would check current username/password and then update if appropriate. It would return a value indicating success/failure, and its cause. In this case, 0 could be "updated successfully", 1 could be "invalid user name", and 2 "invalid current password":
ALTER PROCEDURE UpdatePassword
(
@username varchar (50),
@password varchar (10),
@newpassword varchar (10)
)
AS
IF NOT EXISTS(SELECT Password FROM Users WHERE UserName=@username)
RETURN 1
IF NOT EXISTS(SELECT Password FROM Users WHERE UserName=@username AND Password=@password)
RETURN 2
UPDATE Users SET Password=@newpassword WHERE UserName=@username AND Password=@password
RETURN 0
You can execute this SP with the following code:
IDbConnection cn = new System.Data.SqlClient.SqlConnection("Data Source=.;Database=Dottext;Integrated Security=true;");
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "UpdatePassword";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter prm = cmd.CreateParameter();
prm.ParameterName = "@username";
prm.Value = txtUserName.Text;
cmd.Parameters.Add( prm );
prm = cmd.CreateParameter();
prm.ParameterName = "@password";
prm.Value = txtPassword.Text;
cmd.Parameters.Add( prm );
prm = cmd.CreateParameter();
prm.ParameterName = "@newpassword";
prm.Value = txtNewPassword.Text;
cmd.Parameters.Add( prm );
prm = cmd.CreateParameter();
prm.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add( prm );
cn.Open();
cmd.ExecuteNonQuery();
int result = (int) prm.Value;
switch (result)
{
case 0:
//Everything OK
break;
case 1:
//Username wrong
break;
case 2:
//Current pwd wrong
break;
}
I suggest you use the latest Microsoft Data Access Application block instead of manually doing all this: http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp.
Actually, you should take a look at all application blocks, as they usually solve frequent problems you will find in a consistent, well-documented and best-practices-based way.
This was first published in February 2004