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