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