Getting String Value From Database in C#

Question

I have a database table in which i am saving created users along with username now what i want to get string value from my database if username exists in database and then i will show error message "UserName Exists or Choose new one etc etc"

here is my stored procedure

ALTER PROCEDURE [dbo].[p_SaveUpdate_AdminUserAccount]

 (    
 @Id_User int,    
 @nm_UserName varchar(50),      
 @nm_UserPassword varchar(50),      
 @nm_UserRole int      
 )    
AS    
BEGIN    
IF @Id_User=0    
 BEGIN 
 Declare @count int
    Select @count=COUNT(1) from ref_Admin_UserAccount where nm_UserName=@nm_UserName
    if(@count > 0)
    begin
        select -1 as a;
    end
    else
    BEGIN   
  SET NOCOUNT ON;  
  Insert into ref_Admin_UserAccount(nm_UserName,nm_UserPassword,nm_UserRole)  

values(@nm_UserName,@nm_UserPassword,@nm_UserRole) 
SELECT SCOPE_IDENTITY(); 
end

END    
ELSE    
 BEGIN    
  SET NOCOUNT ON;    
  UPDATE ref_Admin_UserAccount    
   SET    
   nm_UserName=@nm_UserName,    
   nm_UserPassword=@nm_UserPassword,    
   nm_UserRole=@nm_UserRole   
  WHERE Id_User=@Id_User;    
  SELECT @Id_User;    
End    
End 

and here is my code to save values in database from presentation layer using 3 tier archiecture

 public int SaveAdminUserAccountInformation(AdminAccountProperties oAdminUser)
        {
            try
            {
                SqlParameter[] parm = new SqlParameter[4];

                parm[0] = new SqlParameter(PARM_ADMIN_USER_ID, SqlDbType.Int);
                parm[0].Value = oAdminUser.UserID;
                parm[1] = new SqlParameter(PARM_USER_NAME, SqlDbType.VarChar);
                parm[1].Value = oAdminUser.UserName;
                parm[2] = new SqlParameter(PARM_ADMIN_USER_PASSWORD, SqlDbType.VarChar);
                parm[2].Value = oAdminUser.Password;
                parm[3] = new SqlParameter(PARM_USER_ROLE, SqlDbType.Int);
                parm[3].Value = oAdminUser.UserRole;
                return Convert.ToInt32(SqlHelper.ExecuteScalar(this._ConnString, CommandType.StoredProcedure, SQL_ADMIN_USER_INSERT_UPDATE, parm));

            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

please give me some help.

Answer 1

Your stored procedure returns -1 if username exists so i suggest you to return int rather than string in your Data Access Layer. here is some modifications in your code

 public int SaveAdminUserAccountInformation(AdminAccountProperties oAdminUser)
        {
            try
            {
                SqlParameter[] parm = new SqlParameter[4];

                parm[0] = new SqlParameter(PARM_ADMIN_USER_ID, SqlDbType.Int);
                parm[0].Value = oAdminUser.UserID;
                parm[1] = new SqlParameter(PARM_USER_NAME, SqlDbType.VarChar);
                parm[1].Value = oAdminUser.UserName;
                parm[2] = new SqlParameter(PARM_ADMIN_USER_PASSWORD, SqlDbType.VarChar);
                parm[2].Value = oAdminUser.Password;
                parm[3] = new SqlParameter(PARM_USER_ROLE, SqlDbType.Int);
                parm[3].Value = oAdminUser.UserRole;
                int a =Convert.ToInt32(SqlHelper.ExecuteScalar(this._ConnString, CommandType.StoredProcedure, SQL_ADMIN_USER_INSERT_UPDATE, parm));
return a;

            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

after this on basis of return value do what ever you want to do in presentation layer.