1

How do i write a procedure for login query in .net :

StringBuilder sql;
  sql = new StringBuilder("Select User From Login_user ");
  sql.Append(" Where User_Name_upper = '" + strUserName.ToString().ToUpper() + "'");
  OracleCommand cmdUserDetails = new OracleCommand(sql.ToString(), conSODEV);
Icarus
  • 63,293
  • 14
  • 100
  • 115
rakesh
  • 13
  • 2

1 Answers1

1

It would be something like this:

create or replace procedure usernameExists (username in VARCHAR ) is      
    l_user user_tables.owner%type;
BEGIN
    Select User 
    into l_user
    From Login_user 
    where User_Name_upper = upper(username);
EXCEPTION
    when NO_DATA_FOUND then
        raise_application_error(-20000, 'User does not exist!');
END;

Then you'd call it like this:

 OracleCommand cmdUserDetails = new OracleCommand("usernameExists", conSODEV);
 cmdUserDetails.Parameters.AddWithValue("username",username_variable);
 IDataReader reader = cmdUserDetails.ExecuteReader();

Note 1: I am not an Oracle developer but it should be VERY close to that.

Note 2: Above procedure will simply return records if the username exists. You mentioned a "login procedure" but your original query does not have any code that checks for password as well. If you want to implement a "login procedure" then you need to match both, username and password in the select statement and you also need to make sure that you check for case sensitivity properly.

APC
  • 144,005
  • 19
  • 170
  • 281
Icarus
  • 63,293
  • 14
  • 100
  • 115
  • PROCEDURE usernameExists( username IN VARCHAR2 ) AS l_user VARCHAR2(20); BEGIN Select USER INTO l_user From Login_user where User_Name_upper = upper(username); EXCEPTION when NO_DATA_FOUND then raise_application_error(-20000, 'User does not exist!'); END PortalLogin; – rakesh Apr 03 '12 at 14:52
  • in .net: cmd.CommandText = "usernameExists "; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("username", OracleType.VarChar, 20, strUserName.ToString().ToUpper()); strUSER__T = (string)cmd.ExecuteScalar(); return strUSER__T; – rakesh Apr 03 '12 at 14:54