public RegistrationResponse RegisterUser(RegistrationRequest request) { RegistrationResponse resp = new RegistrationResponse(); // Check repeated users List<SqlParameter> sqlParam = new List<SqlParameter>(); DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName); DataSet ds = DBUtility.ExecuteDataset("Select * from USERINFO where Name = @Name", sqlParam); if (DBUtility.hasResult(ds)) { // If a user with the same name provided in the request already exists. throw new ServiceException(ResponseMessages.RES_REG_FAIL_USER_EXISTS); } log.Debug("Creating new user now..."); sqlParam.Clear(); DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName); DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, request.password); DBUtility.AddSqlParam(sqlParam, "@Email", SqlDbType.NVarChar, ""); DBUtility.AddSqlParam(sqlParam, "@ActivationStatus", SqlDbType.SmallInt, 1); DBUtility.AddSqlParam(sqlParam, "@Level", SqlDbType.SmallInt, 1); DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, 0); DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"])); DBUtility.AddSqlParam(sqlParam, "@LastLoginDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"])); DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System"); sqlStr = "INSERT INTO USERINFO (Name, Password, Email, ActivationStatus, Level, Coin, CreatedOn, LastLoginDate, LastModifiedBy) VALUES (" + "@Name, @Password, @Email, @ActivationStatus, @Level, @Coin, @CreatedOn, @LastLoginDate, @LastModifiedBy);"; try { // Send a command to create a user. // If this step is done, a user will be created in the database regardless of method the user chose for the registration process. log.Debug("Sending command to create user. SQL:" + sqlStr); if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1) { throw new ServiceException("Registration failed. Reason: User creation failed."); } // If registration is successful, get the newest UserInfo. UserInfo userInfo = new UserInfo(); LoginRequest userInfoReq = new LoginRequest(); userInfoReq.UserName = request.UserName; userInfoReq.Password = request.password; userInfoReq.Route = request.Route; LoginResponse userInfoRes = new LoginResponse(); userInfoRes = LoginUser(userInfoReq); // Takes care of the credentials switch (request.Route) { case "0": { // If user is created with a native credential, user registration is done. Return the result and an UserInfo object. // Create a UserInfo Object resp.Result = true; resp.Message = "User successfully created with a native account."; resp.User = userInfoRes.user; return resp; } case "1": // If user is created with a third party credential, contiune to update the credential in the table CREDENTIALS. case "2": break; default: { // If the route is unrecognized throw new ServiceException("Registration failed. Reason: Unrecognized route. (b)"); } } sqlParam.Clear(); // If user is created with a third party credential, update the credential in the table CREDENTIALS. string lastest_id = DBUtility.ExecuteDataset("SELECT TOP 1 Id FROM USERINFO ORDER BY Id DESC", new List<SqlParameter>()).Tables[0].Rows[0]["Id"].ToString(); DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.NVarChar, lastest_id); DBUtility.AddSqlParam(sqlParam, "@SocialNetwork", SqlDbType.NVarChar, request.Route); DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName); string updateCredStr = "INSERT INTO CREDENTIALS (UserInfo, SocialNetwork, Name) VALUES (@UserInfo, @SocialNetwork, @Name);"; // Send a command to update user's third party credential. if (DBUtility.ExecuteNonQuery(updateCredStr, sqlParam) != 1) { throw new ServiceException(ResponseMessages.RES_LOGIN_FAIL_TO_UPDATE); } resp.Result = true; resp.Message = ResponseMessages.RES_REG_SUCC_THIRD_PARTY; resp.User = userInfoRes.user; return resp; } catch (Exception e) { throw e; } }
/// <summary> /// Get user information /// </summary> /// <param name="Id"> user ID</param> /// <returns> user information </returns> public UserResponse GetUserInfo(UserRequest request) { UserResponse userResp = new UserResponse(); List<SqlParameter> sqlParam = new List<SqlParameter>(); UserInfo user = new UserInfo(); if(request.UserId >0 || request.UserId == null) { DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, request.UserId); var ds = DBUtility.ExecuteDataset("Select * from UserInfo where id = @UserId", sqlParam); if (DBUtility.hasResult(ds)) { DataRow dr = ds.Tables[0].Rows[0]; user.UserId = dr["Id"].ToString(); user.UserName = dr["Name"].ToString(); user.Password = dr["Password"].ToString(); user.Email = dr["Email"].ToString(); user.ActivationStatus = dr["ActivationStatus"].ToString(); user.Level = dr["Level"].ToString(); user.Coin = dr["Coin"].ToString(); user.CreatedOn = ((DateTime)dr["CreatedOn"]).ToString("yyyy-MM-dd HH:mm:ss"); user.LastLoginDate = ((DateTime)dr["LastLoginDate"]).ToString("yyyy-MM-dd HH:mm:ss"); user.FavoriteList = GetUserFavoriteListById(request.UserId); userResp.User = user; } return userResp; } else { throw new ServiceException("Invalid UserId: " + request.UserId); } }
public LoginResponse LoginUser(LoginRequest request) { string sqlStr = ""; LoginResponse userResponse = new LoginResponse(); List<SqlParameter> sqlParam = new List<SqlParameter>(); DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName); DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, request.Password); DBUtility.AddSqlParam(sqlParam, "@Route", SqlDbType.NVarChar, request.Route); // Identify the route and corresponding credential. // If there is a match, then the user is authenticated, return user info. switch (request.Route) { case "0": { sqlStr = "SELECT * FROM USERINFO where Name = @Name and Password = @Password;"; break; } case "1": case "2": { sqlStr = "SELECT USERINFO.* FROM USERINFO, CREDENTIALS " + "where USERINFO.usr_id = CREDENTIALS.usr_id " + "and CREDENTIALS.credential_type = @Route and CREDENTIALS.credential_id = @Route;"; break; } default: { // Route error throw new ServiceException(ResponseMessages.RES_REG_FAIL_WRONG_ROUTE); } } try { DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam); if (DBUtility.hasResult(ds)) { // Serialize the first user returned([0]) from database and send it back. UserInfo user = new UserInfo(); // Get user's favorite List List<FavoriteInfo> favList = new List<FavoriteInfo>(); user.FavoriteList = favList; DataRow dataRow = ds.Tables[0].Rows[0]; user.UserId = dataRow["Id"].ToString(); user.UserName = dataRow["Name"].ToString(); user.Password = dataRow["Password"].ToString(); user.Email = dataRow["Email"].ToString(); user.ActivationStatus = dataRow["ActivationStatus"].ToString(); user.Level = dataRow["Level"].ToString(); user.Coin = dataRow["Coin"].ToString(); user.CreatedOn = ((DateTime)dataRow["LastLoginDate"]).ToString(ConfigurationManager.AppSettings["DateFormat"]); user.LastLoginDate = ((DateTime)dataRow["LastLoginDate"]).ToString(ConfigurationManager.AppSettings["DateFormat"]); userResponse.user = user; } else { throw new ServiceException(ResponseMessages.RES_LOGIN_FAIL_NO_USER); } return userResponse; } catch (Exception e) { throw e; } }
/// <summary> /// Get All rows from UserInfo table /// </summary> /// <returns> List<UserInfo> </UserResponse></returns> public List<UserInfo> GetAllUserInfo() { List<SqlParameter> sqlParam = new List<SqlParameter>(); List<UserInfo> userInfo = new List<UserInfo>(); ; var ds = DBUtility.ExecuteDataset("Select * from UserInfo", sqlParam); if (DBUtility.hasResult(ds)) { foreach (DataRow dr in ds.Tables[0].Rows) { UserInfo user = new UserInfo(); user.UserId = dr["Id"].ToString(); user.UserName = dr["Name"].ToString(); user.Password = dr["Password"].ToString(); user.Email = dr["Email"].ToString(); user.ActivationStatus = dr["ActivationStatus"].ToString(); user.Level = dr["Level"].ToString(); user.Coin = dr["Coin"].ToString(); user.CreatedOn = ((DateTime)dr["CreatedOn"]).ToString("yyyy-MM-dd HH:mm:ss"); user.LastLoginDate = ((DateTime)dr["LastLoginDate"]).ToString("yyyy-MM-dd HH:mm:ss"); int userId = Convert.ToInt32(dr["Id"]); user.FavoriteList = GetUserFavoriteListById(userId); userInfo.Add(user); } //return userInfo; } return userInfo; }