CODEWITHSUNDEEP
asp.net.netoracle-databaseplsqldeveloper
rakesh
rakesh

Reputation: 13

Inline Query to Procedure

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);

Upvotes: 1

Views: 510

Answers (1)

Icarus
Icarus

Reputation: 63966

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.

Upvotes: 1

Related Questions