コード例 #1
0
 /**
  * @brief Class constructor
  * @param User user The user that is identified by the Session
  **/
 public GreyhoundSession(String session_id, User user)
 {
     this.session_id = session_id;
     this.user_id = user.User_Id;
     this.role_id = (int)user.Role_Id;
     this.validity = DateTime.Now.AddMinutes(30);
 }
コード例 #2
0
        /**
         * @brief Returns all race details from a race identified by an ID
         * @param string races_race_id The race id index to return the details from the database
         * @param string sortBy The sort field to apply to the query
         * @param string sortType The sort type to apply to the query
         * @return RaceDetail[] An array with all races.
         **/
        public static RaceDetail[] GetRaceDetailsByRaceID(User user, string races_race_id, string sortBy, string sortType)
        {
            //Setup the command to run
            string strCommand = GET_RACES_DETAILS_BY_RACE_ID_SQL;
            strCommand = strCommand.Replace("@races_race_id", races_race_id);
            strCommand = strCommand.Replace("@sortBy", sortBy);
            strCommand = strCommand.Replace("@sortType", sortType);

            Tools.printDebug(null, strCommand, null);

            List<RaceDetail> resultList = executeQuery(user, strCommand);

            return resultList.ToArray<RaceDetail>();
        }
コード例 #3
0
        /**
         * @brief Returns the details of all races that occur(ed) in a particular date
         * @param string date_time The date string that identifies the date to retrieve from the database
         * @param string sortBy The sort field to apply to the query
         * @param string sortType The sort type to apply to the query
         * @return RaceDetail[] An array with all races.
         **/
        public static RaceDetail[] GetRaceDetailsByDate(User user, string date_time, string sortBy, string sortType)
        {
            //Setup the command to run
            string strCommand = GET_RACES_DETAILS_BY_DATE_SQL;
            strCommand = strCommand.Replace("@date_time", date_time);
            strCommand = strCommand.Replace("@sortBy", sortBy);
            strCommand = strCommand.Replace("@sortType", sortType);

            Tools.printDebug(null, strCommand, null);

            List<RaceDetail> resultList = executeQuery(user, strCommand);

            return resultList.ToArray<RaceDetail>();
        }
コード例 #4
0
 /**
  * @brief Returns an array of races_details by races_id query from Greyhound database system
  * @return An array of races_details
  */
 public static RaceDetail[] GetRaceDetailsByRaceID(User user, string races_race_id, string sortBy, string sortType)
 {
     return RaceDetailDAL.GetRaceDetailsByRaceID(user, races_race_id, sortBy, sortType);
 }
コード例 #5
0
 /**
  * @brief Returns an array of races_details by date query from Greyhound database system
  * @return An array of races_details
  */
 public static RaceDetail[] GetRaceDetailsByDate(User user, string date, string sortBy, string sortType)
 {
     return RaceDetailDAL.GetRaceDetailsByDate(user, date, sortBy, sortType);
 }
コード例 #6
0
        /**
         * @brief Adds a user to the database
         * @param User user The user to add
         **/
        public static int UpdateUserSettings(User user)
        {
            int result = -1;

            bool error = false;
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
            OracleTransaction transaction = null;
            try
            {
                //Open the connection
                connection.Open();
                //Start a local transaction
                transaction = connection.BeginTransaction();

                //First we add the user to the database
                //Setup the command to run
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.Transaction = transaction;

                if (user.User_Id > 0)
                {
                    command.Parameters.Clear();
                    command.BindByName = true;
                    if (user.Password != null && user.Password.Length > 0)
                    {
                        command.CommandText = UPDATE_USER_PASSWORD;
                        command.Parameters.Add(":password", user.Password);
                    }
                    else
                    {
                        command.CommandText = UPDATE_USER_NO_PASSWORD;
                    }

                    //First we set the parameters
                    command.Parameters.Add(":user_id", user.User_Id);
                    command.Parameters.Add(":name", user.Name);
                    command.Parameters.Add(":mobile", user.Mobile);
                    command.Parameters.Add(":address", user.Address);
                    command.Parameters.Add(":paypal_id", user.Paypal_Id);
                    command.Parameters.Add(":betfair_id", user.Betfair_Id);

                    Tools.printDebug(command.CommandText.ToString());
                    Tools.printDebug(command.Parameters.ToString());

                    //Run the command
                    result = command.ExecuteNonQuery();
                    Tools.printDebug("UPDATE USER RESULT: " + result);
                }
            }
            catch (Exception exception)
            {
                error = true;
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                if (transaction != null)
                {
                    if (error)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                //Closes the datareader and connection to the database
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return result;
        }
コード例 #7
0
        private static List<Greyhound> executeQuery(User user, String strCommand)
        {
            List<Greyhound> resultList = new List<Greyhound>();

            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                //Fetches all rows
                while (sqlDataReader.Read())
                {
                    int grey_id = Convert.ToInt32(sqlDataReader[GREYHOUNDS_GREYHOUND_ID_FIELD].ToString());
                    string name = sqlDataReader[GREYHOUNDS_NAME_FIELD].ToString();
                    string trainer = sqlDataReader[GREYHOUNDS_TRAINER_FIELD].ToString();
                    string birth_date = sqlDataReader[GREYHOUNDS_BIRTH_DATE_FIELD].ToString();
                    string score = sqlDataReader[GREYHOUNDS_SCORE_FIELD].ToString();

                    Greyhound greyhound = new Greyhound(
                        grey_id,
                        name,
                        trainer,
                        birth_date,
                        score
                        );

                    resultList.Add(greyhound);
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return resultList;
        }
コード例 #8
0
 /**
  * @brief Registers a new user in Greyhound database system
  * @return The user_id of the registered user
  */
 public static int RegisterNewUser(User user)
 {
     return UserDAL.RegisterNewUser(user);
 }
コード例 #9
0
 /**
  * @brief Returns all race events at a given date from Greyhound database system
  * @return An array with all race events at the given date.
  */
 public static RaceEvent[] GetRaceEventsAtDate(User user, string date_time)
 {
     return RaceEventDAL.GetRaceEventsAtDate(user, date_time);
 }
コード例 #10
0
        /**
         * @brief Returns all races from DB
         * @param string date_time The date_time to apply to the query
         * @return RaceEvent[] An array with all stadiums, races and races details (RaceEvent array) at the date provided
         **/
        public static RaceEvent[] GetRaceEventsAtDate(User user, string m_date_time)
        {
            List<RaceEvent> resultList = new List<RaceEvent>();
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();
                //Setup the command to run
                string strCommand = GET_ALL_RACE_EVENTS_AT_DATE_SQL;
                strCommand = strCommand.Replace("@date_time", m_date_time.ToString());

                Tools.printDebug(null, strCommand, null);

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                //Fetches all rows
                int previous_std_id = -1;
                int previous_race_id = -1;

                Stadium stadium = null;
                Race race = null;
                while (sqlDataReader.Read())
                {
                    if (stadium != null && race != null)
                    {
                        RaceEvent raceEvent = new RaceEvent(stadium, race);
                        resultList.Add(raceEvent);

                        previous_std_id = -1;
                        previous_race_id = -1;
                        stadium = null;
                        race = null;
                    }

                    int std_id = Convert.ToInt32(sqlDataReader[STADIUMS_STADIUM_ID_FIELD].ToString());
                    if( previous_std_id != std_id ) {
                        previous_std_id = std_id;

                        string stadium_name = sqlDataReader[STADIUMS_NAME_FIELD].ToString();
                        string num_tracks = sqlDataReader[STADIUMS_NUM_TRACKS_FIELD].ToString();
                        string city = sqlDataReader[STADIUMS_CITY_FIELD].ToString();
                        string country = sqlDataReader[STADIUMS_COUNTRY_FIELD].ToString();

                        stadium = new Stadium(
                            std_id,
                            stadium_name,
                            num_tracks,
                            city,
                            country
                            );
                    }

                    int race_id = Convert.ToInt32(sqlDataReader[RACES_ID_FIELD].ToString());
                    if( previous_race_id != race_id ) {
                        previous_race_id = race_id;

                        int stadium_std_id = Convert.ToInt32(sqlDataReader[RACES_STADIUM_ID_FIELD].ToString());
                        string date_time = sqlDataReader[RACES_DATE_TIME_FIELD].ToString();
                        int race_number = Convert.ToInt32(sqlDataReader[RACES_RACE_NUMBER_FIELD].ToString());
                        Decimal track_length = Tools.StringToDecimal(sqlDataReader[RACES_TRACK_LENGTH_FIELD].ToString());
                        string grade = sqlDataReader[RACES_GRADE_FIELD].ToString();
                        string race_type = sqlDataReader[RACES_RACE_TYPE_FIELD].ToString();

                        race = new Race(
                            race_id,
                            stadium_std_id,
                            date_time,
                            race_number,
                            track_length,
                            grade,
                            race_type);
                    }
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            //Returns the list of fetched users in the form of an array
            return resultList.ToArray();
        }
コード例 #11
0
 /**
  * @brief Returns all users from Greyhound database system
  * @return An array with all users.
  */
 public static Greyhound[] GetGreyhoundsRunningAtDate(User user, string date_time)
 {
     return GreyhoundDAL.GetGreyhoundsRunningAtDate(user, date_time);
 }
コード例 #12
0
 /**
  * @brief Try to insert a bet into the database
  * @return the bet_id if sucessful, -1 otherwise
  */
 public static long InsertBet(User user, Bet bet)
 {
     return BetsDAL.InsertBet(user, bet);
 }
コード例 #13
0
 /**
  * @brief Returns all user's bets from Greyhound database system
  * @return An array with all user's bets
  */
 public static Bet[] GetBetsByUserId(User user)
 {
     return BetsDAL.GetBetsByUserId(user);
 }
コード例 #14
0
        public void RegisterNewUser(string user, string token,
            string name, string email, string password, string mobile,
            string address, string paypal_id, string betfair_id )
        {
            if (!AuthenticateUser(user, token))
            {
                String result = "{error:\"not authenticated\", items:[]}";
                SendResponse(result);
                return;
            }

            User newuser = new User(-1, Role.ROLES.REGISTERED_USER_ROLE, password, name, email, mobile, address, paypal_id, betfair_id);

            int res = UserBLL.RegisterNewUser(newuser);
            bool email_sent = Tools.sendMail(email, "" + res);
            if (res > 0 && email_sent)
            {
                String result = "{result: \"success\"}";

                SendResponse(result);
            }
            else
            {
                String errormsg = "'Unknown error!'";
                if( res > 0 )
                    errormsg = "'Failed to add the user account to the system'";
                else if( !email_sent )
                    errormsg = "'Failed to send the email address'";

                String result = "{result: 'error', errormsg: " + errormsg + "}";

                SendResponse(result);
            }
        }
コード例 #15
0
        private static List<RaceDetail> executeQuery(User user, String strCommand)
        {
            List<RaceDetail> resultList = new List<RaceDetail>();

            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                //Fetches all rows
                while (sqlDataReader.Read())
                {
                    int races_race_id = Convert.ToInt32(sqlDataReader[V_RACES_DETAILS_RACES_ID_FIELD].ToString());
                    int greyhounds_grey_id = Convert.ToInt32(sqlDataReader[GREYHOUNDS_GREYHOUND_ID_FIELD].ToString());
                    string greyhound_name = sqlDataReader[GREYHOUNDS_NAME_FIELD].ToString();
                    string trainer = sqlDataReader[GREYHOUNDS_TRAINER_FIELD].ToString();
                    string birth_date = sqlDataReader[GREYHOUNDS_BIRTH_DATE_FIELD].ToString();
                    string score = sqlDataReader[GREYHOUNDS_SCORE_FIELD].ToString();
                    score = score.Replace(",", ".");
                    int track_number = Convert.ToInt32(sqlDataReader[RACES_DETAILS_TRACK_NUMBER_FIELD].ToString());
                    bool race_completed = Tools.StringToBool(sqlDataReader[RACES_DETAILS_RACE_COMPLETED_FIELD].ToString());
                    string position = sqlDataReader[RACES_DETAILS_POSITION_FIELD].ToString();
                    string odd = sqlDataReader[ODDS_VALUE_FIELD].ToString();
                    string prediction = "";
                    if( user != null && user.Role_Id > Role.ROLES.ANONYMOUS_USER_ROLE ) {
                        prediction = sqlDataReader[PREDICTIONS_ADVANCED_VALUE_FIELD].ToString();
                    }
                    else {
                        prediction = sqlDataReader[PREDICTIONS_SIMPLE_VALUE_FIELD].ToString();
                    }
                    prediction = prediction.Replace(",", ".");
                    Decimal time = Tools.StringToDecimal(sqlDataReader[RACES_DETAILS_TIME_FIELD].ToString());

                    RaceDetail race_detail = new RaceDetail(
                        races_race_id,
                        greyhounds_grey_id,
                        greyhound_name,
                        trainer,
                        birth_date,
                        score,
                        track_number,
                        race_completed,
                        position,
                        odd,
                        prediction,
                        time);

                    resultList.Add(race_detail);
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return resultList;
        }
コード例 #16
0
        /**
         * @brief Try to insert a bet into the database
         * @return the bet_id if sucessful, -1 otherwise
         */
        public static long InsertBet(User user, Bet bet)
        {
            long result = -1;
            try
            {
                using (var connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id)))
                {
                    using (var Command = new OracleCommand())
                    {
                        Command.Connection = connection;
                        Command.CommandText = "INSERT_BET";
                        Command.CommandType = CommandType.StoredProcedure;

                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Long;
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "userId";
                            param.Value = bet.User_Id;
                            Command.Parameters.Add(param);
                        }
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Long;
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "betTypeId";
                            param.Value = bet.Bet_Type_Id;
                            Command.Parameters.Add(param);
                        }
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Long;
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "raceDetailId";
                            param.Value = bet.Race_Id;
                            Command.Parameters.Add(param);
                        }
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Long;
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "raceGreyId";
                            param.Value = bet.Grey_Id;
                            Command.Parameters.Add(param);
                        }
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Decimal;
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "betValue";
                            param.Value = bet.Bet_Value;
                            Command.Parameters.Add(param);
                        }
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = OracleDbType.Int32;
                            param.Direction = ParameterDirection.Output;
                            param.ParameterName = "out_betId";
                            Command.Parameters.Add(param);
                        }

                        connection.Open();
                        int res = Command.ExecuteNonQuery();

                        if (Command.Parameters["out_betId"].DbType == System.Data.DbType.Int32)
                        {
                            result = Int32.Parse((Command.Parameters["out_betId"].Value).ToString());
                            Tools.printDebug("Insert Bet Result: " + result);
                        }
                        else
                        {
                            Tools.printDebug("Failed to Insert the Bet");
                        }
                    }
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (Exception exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
            }

            return result;
        }
コード例 #17
0
        /**
         * @brief Returns all races from DB
         * @param string date_time The date_time to apply to the query
         * @return RaceEvent[] An array with all stadiums, races and races details (RaceEvent array) at the date provided
         **/
        public static Bet[] GetBetsByUserId(User user)
        {
            List<Bet> betsList = new List<Bet>();
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(user.Role_Id));
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();
                //Setup the command to run
                string strCommand = GET_BETS_BY_USER_ID_SQL;
                strCommand = strCommand.Replace("@user_id", user.User_Id.ToString());

                Tools.printDebug(null, strCommand, null);

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                while (sqlDataReader.Read())
                {
                    long bet_id = Convert.ToInt64(sqlDataReader[BETS_BET_ID].ToString());
                    long user_id = Convert.ToInt64(sqlDataReader[BETS_USER_ID].ToString());
                    long bet_type_id = Convert.ToInt64(sqlDataReader[BETS_BET_TYPE_ID].ToString());
                    long race_id = Convert.ToInt64(sqlDataReader[BETS_RACE_ID].ToString());
                    long grey_id = Convert.ToInt64(sqlDataReader[BETS_GREY_ID].ToString());
                    string date_time = sqlDataReader[BETS_DATE_TIME].ToString();
                    Decimal bet_value = Tools.StringToDecimal(sqlDataReader[BETS_BET_VALUE].ToString());
                    int bet_result = (!sqlDataReader[BETS_BET_RESULT].ToString().Equals("")?
                        Convert.ToInt32(sqlDataReader[BETS_BET_RESULT].ToString()) : -1);
                    Decimal return_value = Tools.StringToDecimal(sqlDataReader[BETS_RETURN_VALUE].ToString());

                    Bet bet = new Bet(
                        bet_id,
                        user_id,
                        bet_type_id,
                        race_id,
                        grey_id,
                        date_time,
                        bet_value,
                        bet_result,
                        return_value
                    );
                    betsList.Add(bet);
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            //Returns the list of fetched bets in the form of an array
            return betsList.ToArray();
        }
コード例 #18
0
        /**
         * @brief Returns the user with the identified id from DB
         * @param string user_id The user_id filter to apply to the query
         * @return User A User object array with the id defined in the query
         **/
        public static User GetUserById(string user_id)
        {
            User user = null;
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();
                //Setup the command to run
                string strCommand = GET_USER_BY_USER_ID_SQL;
                strCommand = strCommand.Replace("@user_id", user_id);

                Tools.printDebug(null, strCommand, null);

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                //Fetches all rows
                while (sqlDataReader.Read())
                {
                    int userid = Convert.ToInt32(sqlDataReader[USERS_USER_ID_FIELD].ToString());
                    Role.ROLES role_id = (Role.ROLES)Convert.ToInt32(sqlDataReader[USERS_ROLE_ID_FIELD].ToString());
                    string name = sqlDataReader[USERS_NAME_FIELD].ToString();
                    string email = sqlDataReader[USERS_EMAIL_FIELD].ToString();
                    string mobile = sqlDataReader[USERS_MOBILE_FIELD].ToString();
                    string address = sqlDataReader[USERS_ADDRESS_FIELD].ToString();
                    string paypal_id = sqlDataReader[USERS_PAYPAL_ID_FIELD].ToString();
                    string betfair_id = sqlDataReader[USERS_BETFAIR_ID_FIELD].ToString();

                    user = new User(
                        userid,
                        role_id,
                        name,
                        email,
                        mobile,
                        address,
                        paypal_id,
                        betfair_id
                    );

                    break;
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return user;
        }
コード例 #19
0
 /**
  * @brief Updates an existent user account in Greyhound database system
  * @return The user_id of the registered user
  */
 public static int UpdateUserSettings(User user)
 {
     return UserDAL.UpdateUserSettings(user);
 }
コード例 #20
0
        /**
         * @brief Returns all users from the DB
         * @param string filter The filter to apply to the query
         * @param int startRec The start index to return from the database
         * @param int endRec The end index to return from the database
         * @param string sortBy The sort field to apply to the query
         * @param string sortType The sort type to apply to the query
         * @return User[] An array with all users.
         **/
        public static User[] GetUsersPaged(string filter, int startRec, int endRec, string sortBy, string sortType)
        {
            List<User> resultList = new List<User>();
            OracleConnection connection = new OracleConnection(ConfigurationBLL.DBConnectionString);
            OracleDataReader sqlDataReader = null;
            try
            {
                //Open the connection
                connection.Open();
                //Setup the command to run
                //Setup the command to run
                string strCommand = GET_ALL_USERS_PAGED_SQL;
                strCommand = strCommand.Replace("@start_rec", startRec.ToString());
                strCommand = strCommand.Replace("@end_rec", endRec.ToString());
                strCommand = strCommand.Replace("@filter", filter);
                strCommand = strCommand.Replace("@sortBy", sortBy);
                strCommand = strCommand.Replace("@sortType", sortType);

                Tools.printDebug(null, strCommand, null);

                OracleCommand command = new OracleCommand(strCommand);
                command.Connection = connection;
                //Runs the command
                sqlDataReader = command.ExecuteReader();

                //Fetches all rows
                while (sqlDataReader.Read())
                {
                    int user_id = Convert.ToInt32(sqlDataReader[USERS_USER_ID_FIELD].ToString());
                    Role.ROLES role_id = (Role.ROLES) Convert.ToInt32(sqlDataReader[USERS_ROLE_ID_FIELD].ToString());
                    string name = sqlDataReader[USERS_NAME_FIELD].ToString();
                    string email = sqlDataReader[USERS_EMAIL_FIELD].ToString();
                    string mobile = sqlDataReader[USERS_MOBILE_FIELD].ToString();
                    string address = sqlDataReader[USERS_ADDRESS_FIELD].ToString();
                    string paypal_id = sqlDataReader[USERS_PAYPAL_ID_FIELD].ToString();
                    string betfair_id = sqlDataReader[USERS_BETFAIR_ID_FIELD].ToString();

                    User user = new User(
                        user_id,
                        role_id,
                        name,
                        email,
                        mobile,
                        address,
                        paypal_id,
                        betfair_id
                        );

                    resultList.Add(user);
                }
            }
            catch (InvalidOperationException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (SqlException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new InvalidOperationException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (FormatException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new FormatException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            catch (OverflowException exception)
            {
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new OverflowException(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                //Closes the datareader and connection to the database
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }

            //Returns the list of fetched users in the form of an array
            return resultList.ToArray();
        }
コード例 #21
0
        ///**
        // * @brief Returns all greyhounds from DB as pages
        // * @return Greyhound[] An array with all greyhounds.
        // **/
        //public static Greyhound[] GetGreyhoundsPaged(string filter, int startRec, int endRec, string sortBy, string sortType)
        //{
        //    //Setup the command to run
        //    string strCommand = GET_ALL_GREYHOUNDS_PAGED_SQL;
        //    strCommand = strCommand.Replace("@start_rec", startRec.ToString());
        //    strCommand = strCommand.Replace("@end_rec", endRec.ToString());
        //    strCommand = strCommand.Replace("@filter", filter);
        //    strCommand = strCommand.Replace("@sortBy", sortBy);
        //    strCommand = strCommand.Replace("@sortType", sortType);
        //    List<Greyhound> resultList = executeQuery(strCommand);
        //    Tools.printDebug(null, strCommand, null);
        //    return resultList.ToArray();
        //}
        /**
        * @brief Returns all greyhounds from DB
        * @return Greyhound[] An array with all greyhounds.
        **/
        public static Greyhound[] GetGreyhoundsRunningAtDate(User user, string date_time)
        {
            //Setup the command to run
            string strCommand = GET_GREYHOUNDS_RACING_AT_DATE_SQL;
            strCommand = strCommand.Replace("@date_time", date_time.ToString());

            List<Greyhound> resultList = executeQuery(user, strCommand);
            Tools.printDebug(null, strCommand, null);

            return resultList.ToArray();
        }
コード例 #22
0
        /**
         * @brief Adds a user to the database
         * @param User user The user to add
         **/
        public static int RegisterNewUser(User user)
        {
            int lastInsertedId = -1;

            bool error = false;
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
            OracleTransaction transaction = null;
            try
            {
                //Open the connection
                connection.Open();
                //Start a local transaction
                transaction = connection.BeginTransaction();

                //First we add the user to the database
                //Setup the command to run
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.Transaction = transaction;

                //Get the next id
                command.CommandText = DatabaseSchema.SELECT_USERS_NEXT_ID;
                int user_id = Convert.ToInt32(command.ExecuteScalar());

                if (user_id > 0)
                {
                    command.Parameters.Clear();
                    command.CommandText = INSERT_USER;

                    //First we set the parameters
                    command.Parameters.Add("user_id", user_id);
                    command.Parameters.Add("password", user.Password);
                    command.Parameters.Add("role_id", (int)user.Role_Id);
                    command.Parameters.Add("name", user.Name);
                    command.Parameters.Add("email", user.Email);
                    command.Parameters.Add("registered", DatabaseSchema.USER_NOT_REGISTERED);
                    command.Parameters.Add("mobile", user.Mobile);
                    command.Parameters.Add("address", user.Address);
                    command.Parameters.Add("paypal_id", user.Paypal_Id);
                    command.Parameters.Add("betfair_id", user.Betfair_Id);

                    //Run the command
                    command.ExecuteNonQuery();
                    lastInsertedId = user_id;
                }
            }
            catch (Exception exception)
            {
                error = true;
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                if (transaction != null)
                {
                    if (error)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                //Closes the datareader and connection to the database
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return lastInsertedId;
        }