public LoginViewModel AuthenticateUser(LoginViewModelParam param) { var result = new LoginViewModel(param); try { using (SqlConnection conn = _connectionHelper.DefaultConnectionDB()) { using (SqlCommand cmd = new SqlCommand(SpHelper.AuthenticateUser, conn)) { conn.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserName", param.UserName); cmd.Parameters.AddWithValue("@Password", param.Password); using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { // get details from reader if (rdr.HasRows) { while (rdr.Read()) { result.IsSuccess = rdr.CustomGetValue <bool>("IsSuccess"); result.Message = rdr.CustomGetValue <string>("Message"); } } if (rdr.NextResult()) { while (rdr.Read()) { result.UserID = rdr.CustomGetValue <string>("UserID"); result.UserName = rdr.CustomGetValue <string>("UserName"); result.Password = rdr.CustomGetValue <string>("Password"); result.EmailAddress = rdr.CustomGetValue <string>("Email"); result.FirstName = rdr.CustomGetValue <string>("FirstName"); result.LastName = rdr.CustomGetValue <string>("LastName"); result.IsActive = rdr.CustomGetValue <bool>("IsActive"); result.Role = rdr.CustomGetValue <string>("Role"); } } } } } } catch (Exception ex) { _log.Write("Exception in DataAccess; Message: " + ex.Message); } finally { } return(result); }