Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create procedure LogInUser
- @username nvarchar(64),
- @password nvarchar(64),
- @succeed bit out,
- @not_exist_err bit out
- as
- declare @exist_user nvarchar(64)
- select @exist_user = username from users
- where username = @username
- if @exist_user is null
- begin
- set @succeed = 0
- set @not_exist_err = 1
- return
- end
- else
- begin
- select * from users
- where username = @username and password = @password
- end
- return
- select @exist_user = username from users
- where username = @username
- select * from users
- where username = @username and password = @password
- declare @succeedResult bit
- declare @existErr bit
- exec LogInUser @username=admin, @password =admin, @succeed = @succeedResult, @not_exist_err = @existErr
- public User LogIn(User usr)
- {
- SqlConnection conn = A2.Controller.Utils.conn;
- SqlCommand loginCmd = new SqlCommand("LogInUser", conn);
- loginCmd.CommandType = CommandType.StoredProcedure;
- User result = new User();
- try
- {
- conn.Open();
- loginCmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = usr.Username;
- loginCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = usr.Password;
- loginCmd.Parameters.Add("@succeed", SqlDbType.Bit).Direction = ParameterDirection.Output;
- loginCmd.Parameters.Add("@not_exist_err", SqlDbType.Bit).Direction = ParameterDirection.Output;
- SqlDataReader dr = loginCmd.ExecuteReader();
- if (loginCmd.Parameters["@succeed"].Value != DBNull.Value){
- Console.WriteLine("User does not exist");
- SqlParameter notExistErr = loginCmd.Parameters["@not_exist_err"];
- if (notExistErr.Value != DBNull.Value){
- throw new NotExistException("The username or password is incorrect.", "Users");
- }
- }
- while (dr.Read()) {
- Console.WriteLine("Looping dr");
- result.Username = (string) dr["username"];
- result.Password = (string) dr["password"];
- result.FirstName = (string) dr["first_name"];
- result.MiddleName = (string) dr["middle_name"];
- result.LastName = (string) dr["last_name"];
- result.ManagerID = (int) dr["manager_id"];
- result.IsAdmin = (int) dr["is_admin"];
- return result;
- }
- Console.WriteLine("Done reading");
- }
- finally {
- if (conn.State == ConnectionState.Open) conn.Close();
- }
- return result;
- }
- SqlDataReader dr = loginCmd.ExecuteReader();
- if (loginCmd.Parameters["@succeed"].Value != DBNull.Value){
- SqlDataReader dr = loginCmd.ExecuteReader();
- if (!dr.HasRows)
- {
- Console.WriteLine("User does not exist");
- SqlParameter notExistErr = loginCmd.Parameters["@not_exist_err"];
- if (notExistErr.Value != DBNull.Value)
- {
- throw new NotExistException("The username or password is incorrect.", "Users");
- }
- }
- else
- {
- while (dr.Read())
- {
- Console.WriteLine("Looping dr");
- result.Username = (string) dr["@username"];
- result.Password = (string) dr["@password"];
- result.FirstName = (string) dr["@first_name"];
- result.MiddleName = (string) dr["@middle_name"];
- result.LastName = (string) dr["@last_name"];
- result.ManagerID = (int) dr["@manager_id"];
- result.IsAdmin = (int) dr["@is_admin"];
- return result;
- }
- }
- declare @succeedResult bit
- declare @existErr bit
- exec LogInUser @username=N'admin', @password =N'admin', @succeed = @succeedResult, @not_exist_err = @existErr
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement