/// <summary>
        /// Execute execution unit and return the result set as reader, note that this reader has to be closed from the caller to close the associated connection (i.e. use try-finally)
        /// </summary>
        /// <param name="unit">execution unit mapped to stored procedure that returns cursors attached to select statement</param>
        /// <returns>data reader that reads values from the mapped cursor; using multiple cursors require to use reader.NextResult()</returns>
        public static IDataReader ExecuteDataReader(ExecutionUnit unit)
        {
            SqlConnection connection = GetConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = unit.StoredProcedureName;
            SqlParameter[] parameters = unit.Parameters;

            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            connection.Open();

            try
            {
                //set time out for the command, if not supplied in the connection then use 2 minutes
                //command.CommandTimeout = connection.ConnectionTimeout <= 0 ? 120 : connection.ConnectionTimeout;
                command.CommandTimeout = 120;

                IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                command.Dispose(); command = null;

                return reader;
            }
            catch (Exception ex)
            {
                connection.Close(); connection = null;
                throw ex;
            }
        }
Example #2
0
        public Member AuthenticateMember(string userName, string password)
        {
            ExecutionUnit unit = new ExecutionUnit("AuthenticateMember");

            unit.AddParameter("@UserName", SqlDbType.VarChar, ParameterDirection.Input, userName);
            unit.AddParameter("@Password", SqlDbType.VarChar, ParameterDirection.Input, password);

            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                Member member = null;
                while (reader.Read())
                {
                    member = new Member();
                    member.ID = DatabaseAccessEngine.GetInt32(reader, "ID");
                    member.Status = DatabaseAccessEngine.GetBoolean(reader, "Status");
                    member.Password = DatabaseAccessEngine.GetString(reader, "Password");
                    member.UserName = DatabaseAccessEngine.GetString(reader, "Username");
                    member.FirstName = DatabaseAccessEngine.GetString(reader, "FirstName");
                    member.LastName = DatabaseAccessEngine.GetString(reader, "LastName");
                    member.BirthDate = DatabaseAccessEngine.GetDateTime(reader, "BirthDate");
                    member.Age = DatabaseAccessEngine.GetInt32(reader, "Age");
                    member.IsChild = member.Age <= Constants.ADULT_AGE;
                    member.PhotoFilePath = DatabaseAccessEngine.GetString(reader, "PhotoFilePath");
                    member.Email = DatabaseAccessEngine.GetString(reader, "Email");
                    member.IsMaleGender = DatabaseAccessEngine.GetBoolean(reader, "Gender");
                    member.NotifyBySMS = DatabaseAccessEngine.GetBoolean(reader, "NotifyBySMS");
                    member.NotifyByEmail = DatabaseAccessEngine.GetBoolean(reader, "NotifyByEmail");
                    member.MobilePhone = DatabaseAccessEngine.GetString(reader, "MobilePhone");
                    member.HomePhone = DatabaseAccessEngine.GetString(reader, "HomePhone");
                    member.State = DatabaseAccessEngine.GetInt32(reader, "State");
                    member.ZipCode = DatabaseAccessEngine.GetString(reader, "ZipCode");
                    member.Address = DatabaseAccessEngine.GetString(reader, "Address");
                    member.ActivityLevel = DatabaseAccessEngine.GetString(reader, "ActivityLevel");
                    member.ExerciseIntensityLevel = DatabaseAccessEngine.GetString(reader, "ExerciseIntensityLevel");
                    member.RegistrationDate = DatabaseAccessEngine.GetDateTime(reader, "RegistrationDate");

                    member.Nationality = new Country();
                    member.Nationality.ID = DatabaseAccessEngine.GetInt32(reader, "NationalityCountryID");
                    member.Nationality.Name = DatabaseAccessEngine.GetString(reader, "NationalityCountryName");
                    member.Nationality.FlagFilePath = DatabaseAccessEngine.GetString(reader, "NationalityCountryFlagFilePath");

                    break;
                }
                if (member == null)
                    return null;

                return member;
            }
        }
Example #3
0
        public Int64 AddPedometerReading(MemberPedometerReading reading)
        {
            //prepare execution unit parameters and invoke stored procedure
            ExecutionUnit unit = new ExecutionUnit("AddPedometerReading");
            unit.AddParameter("@MemberID", SqlDbType.Int, ParameterDirection.Input, reading.Member.ID);
            unit.AddParameter("@ModelName", SqlDbType.VarChar, ParameterDirection.Input, reading.ModelName);
            unit.AddParameter("@SerialNumber", SqlDbType.NVarChar, ParameterDirection.Input, reading.SerialNumber);
            unit.AddParameter("@ReadingDate", SqlDbType.DateTime, ParameterDirection.Input, reading.ReadingDate);
            unit.AddParameter("@Steps", SqlDbType.BigInt, ParameterDirection.Input, reading.Steps);
            unit.AddParameter("@AerobicSteps", SqlDbType.BigInt, ParameterDirection.Input, reading.AerobicSteps);
            unit.AddParameter("@AerobicDuration", SqlDbType.Decimal, ParameterDirection.Input, reading.AerobicDuration);
            unit.AddParameter("@Distance", SqlDbType.Decimal, ParameterDirection.Input, reading.Distance);
            unit.AddParameter("@FatBurn", SqlDbType.Decimal, ParameterDirection.Input, reading.FatBurn);
            unit.AddParameter("@Calories", SqlDbType.Decimal, ParameterDirection.Input, reading.Calories);
              //  unit.AddParameter("@Status", SqlDbType.Bit, ParameterDirection.Input, 1);
            IDbDataParameter totalStepsParam = unit.AddParameter("@TotalSteps", SqlDbType.BigInt, ParameterDirection.Output, null);

            DatabaseAccessEngine.ExecuteNonQuery(unit);

            reading.TotalSteps = Convert.ToInt32(totalStepsParam.Value);

            //return total steps
            return reading.TotalSteps;
        }
Example #4
0
        public List<Country> GetCountries(string sortExp)
        {
            ExecutionUnit unit = new ExecutionUnit("GetCountries");
            unit.AddParameter("@SortExpression", SqlDbType.VarChar, ParameterDirection.Input, sortExp);

            List<Country> countries = new List<Country>();
            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                while (reader.Read())
                {
                    Country country = new Country();
                    country.ID = DatabaseAccessEngine.GetInt32(reader, "ID");
                    country.Name = DatabaseAccessEngine.GetString(reader, "Name");
                    country.Code = DatabaseAccessEngine.GetString(reader, "Code");
                    country.FlagFilePath = DatabaseAccessEngine.GetString(reader, "FlagFilePath");

                    countries.Add(country);
                }
            }

            return countries;
        }
Example #5
0
        public int RegisterMember(Member member)
        {
            ////calculate fitness value parameters
            //decimal bmi, bodyFat;
            //Helper.CalculateFitnessParameters(fitnessValue.Weight, fitnessValue.Height, (int)((TimeSpan)DateTime.Now.Subtract(member.BirthDate)).TotalDays / 365, member.IsMaleGender, out bmi, out bodyFat);
            //fitnessValue.BMI = bmi; fitnessValue.BodyFat = bodyFat;

            //prepare execution unit parameters and invoke stored procedure
            ExecutionUnit unit = new ExecutionUnit("RegisterMember");
            unit.AddParameter("@UserName", SqlDbType.VarChar, ParameterDirection.Input, member.UserName);
            unit.AddParameter("@Password", SqlDbType.VarChar, ParameterDirection.Input, member.Password);
            unit.AddParameter("@FirstName", SqlDbType.NVarChar, ParameterDirection.Input, member.FirstName);
            unit.AddParameter("@LastName", SqlDbType.NVarChar, ParameterDirection.Input, member.LastName);
            unit.AddParameter("@BirthDate", SqlDbType.DateTime, ParameterDirection.Input, member.BirthDate);
            unit.AddParameter("@Email", SqlDbType.VarChar, ParameterDirection.Input, member.Email);
            unit.AddParameter("@Nationality", SqlDbType.Int, ParameterDirection.Input, member.Nationality.ID);
            unit.AddParameter("@Gender", SqlDbType.Bit, ParameterDirection.Input, member.IsMaleGender ? 1 : 0);
            unit.AddParameter("@Status", SqlDbType.Bit, ParameterDirection.Input, 1);

            unit.AddParameter("@NotifyBySMS", SqlDbType.Bit, ParameterDirection.Input, member.NotifyBySMS ? 1 : 0);
            unit.AddParameter("@NotifyByEmail", SqlDbType.Bit, ParameterDirection.Input, member.NotifyByEmail ? 1 : 0);

            unit.AddParameter("@MobilePhone", SqlDbType.VarChar, ParameterDirection.Input, member.MobilePhone);
            unit.AddParameter("@HomePhone", SqlDbType.VarChar, ParameterDirection.Input, member.HomePhone);

            unit.AddParameter("@State", SqlDbType.VarChar, ParameterDirection.Input, member.State);
            unit.AddParameter("@ZipCode", SqlDbType.VarChar, ParameterDirection.Input, member.ZipCode);
            unit.AddParameter("@Address", SqlDbType.VarChar, ParameterDirection.Input, member.Address);
            unit.AddParameter("@RegistrationDate", SqlDbType.DateTime, ParameterDirection.Input, DateTime.Now);

            IDbDataParameter memberIDParam = unit.AddParameter("@MemberID", SqlDbType.Int, ParameterDirection.Output, null);
            IDbDataParameter errorCodeParam = unit.AddParameter("@ErrorCode", SqlDbType.Int, ParameterDirection.Output, null);

            DatabaseAccessEngine.ExecuteNonQuery(unit);

            //check if any unique check failed
            int errorCode = Convert.ToInt32(errorCodeParam.Value);
            if (errorCode != 0)
            {
                ApplicationFault fault = new ApplicationFault(errorCode);
                throw new FaultException<ApplicationFault>(fault, "Duplicate Member");
            }

            member.ID = Convert.ToInt32(memberIDParam.Value);

            //return generated member id
            return member.ID;
        }
Example #6
0
        public Dictionary<State, int> MemberChart_State(bool? gender, int? fromAge, int? toAge)
        {
            ExecutionUnit unit = new ExecutionUnit("MemberChart_State");
            unit.AddParameter("@Gender", SqlDbType.Bit, ParameterDirection.Input, gender.HasValue ? (object)(gender.Value ? 1 : 0) : null);
            unit.AddParameter("@FromAge", SqlDbType.Int, ParameterDirection.Input, fromAge);
            unit.AddParameter("@ToAge", SqlDbType.Int, ParameterDirection.Input, toAge);

            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                Dictionary<State, int> summarybyState = new Dictionary<State, int>();
                while (reader.Read())
                {
                    State country = new State();
                    country.ID = DatabaseAccessEngine.GetInt32(reader, "ID");
                    country.Name = DatabaseAccessEngine.GetString(reader, "Name");

                    summarybyState.Add(country, DatabaseAccessEngine.GetInt32(reader, "MemberCount"));
                }

                return summarybyState;
            }
        }
Example #7
0
        //reports
        public Dictionary<string, int> MemberChart_Gender(int? state, int? fromAge, int? toAge)
        {
            ExecutionUnit unit = new ExecutionUnit("MemberChart_Gender");
            unit.AddParameter("@State", SqlDbType.Int, ParameterDirection.Input, state);
            unit.AddParameter("@FromAge", SqlDbType.Int, ParameterDirection.Input, fromAge);
            unit.AddParameter("@ToAge", SqlDbType.Int, ParameterDirection.Input, toAge);

            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                Dictionary<string, int> summarybyGender = new Dictionary<string, int>();
                while (reader.Read())
                {
                    summarybyGender.Add(DatabaseAccessEngine.GetString(reader, "Gender"), DatabaseAccessEngine.GetInt32(reader, "MemberCount"));
                }
                return summarybyGender;
            }
        }
Example #8
0
        public Dictionary<int, int> MemberChart_Age(int? state, bool? gender)
        {
            ExecutionUnit unit = new ExecutionUnit("MemberChart_Age");
            unit.AddParameter("@State", SqlDbType.Int, ParameterDirection.Input, state);
            unit.AddParameter("@Gender", SqlDbType.Bit, ParameterDirection.Input, gender.HasValue ? (object)(gender.Value ? 1 : 0) : null);

            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                Dictionary<int, int> summarybyAge = new Dictionary<int, int>();
                while (reader.Read())
                {
                    summarybyAge.Add(DatabaseAccessEngine.GetInt32(reader, "Age"), DatabaseAccessEngine.GetInt32(reader, "MemberCount"));
                }
                return summarybyAge;
            }
        }
Example #9
0
        public List<State> GetStates(string sortExp)
        {
            ExecutionUnit unit = new ExecutionUnit("GetStates");
            unit.AddParameter("@SortExpression", SqlDbType.VarChar, ParameterDirection.Input, sortExp);

            List<State> states = new List<State>();
            using (IDataReader reader = DatabaseAccessEngine.ExecuteDataReader(unit))
            {
                while (reader.Read())
                {
                    State state = new State();
                    state.ID = DatabaseAccessEngine.GetInt32(reader, "ID");
                    state.Name = DatabaseAccessEngine.GetString(reader, "Name");
                    states.Add(state);
                }
            }

            return states;
        }
        /// <summary>
        /// Execute execution unit and return the result set as data set
        /// </summary>
        /// <param name="unit">execution unit mapped to stored procedure that returns cursors attached to select statement</param>
        /// <returns>data set with tables that each mapped to a cursor select statement result</returns>
        public static DataSet ExecuteDataSet(ExecutionUnit unit)
        {
            SqlConnection connection = GetConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = unit.StoredProcedureName;
            SqlParameter[] parameters = unit.Parameters;

            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            SqlDataAdapter adapter = GetDataAdapter(command);
            DataSet dataSet = new DataSet();

            connection.Open();
            try
            {
                //set time out for the command, if not supplied in the connection then use 2 minutes
                //command.CommandTimeout = connection.ConnectionTimeout <= 0 ? 120 : connection.ConnectionTimeout;
                command.CommandTimeout = 120;

                adapter.Fill(dataSet);
                return dataSet;
            }
            finally
            {
                command.Parameters.Clear();
                command.Dispose(); command = null;

                connection.Close();
                connection.Dispose(); connection = null;
            }
        }
        /// <summary>
        /// Execute execution unit with transaction
        /// </summary>
        /// <param name="unit">execution unit mapped to stored procedure and passing parameters</param>
        /// <param name="transaction">transaction to be used with execution, passing null means to execute the passed unit without transaction</param>
        public static void ExecuteNonQuery(ExecutionUnit unit, SqlTransaction transaction)
        {
            SqlCommand command = null;
            try
            {
                SqlConnection connection = transaction.Connection;
                command = connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = unit.StoredProcedureName;
                SqlParameter[] parameters = unit.Parameters;

                if (parameters != null)
                {
                    for (int j = 0; j < parameters.Length; j++)
                    {
                        command.Parameters.Add(parameters[j]);
                    }
                }

                //set time out for the command, if not supplied in the connection then use 2 minutes
                //command.CommandTimeout = connection.ConnectionTimeout <= 0 ? 120 : connection.ConnectionTimeout;
                command.CommandTimeout = 120;
                command.ExecuteNonQuery();
            }
            finally
            {
                command.Parameters.Clear();
                command.Dispose(); command = null;
            }
        }