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