/// <summary> /// Deletes a shoe /// </summary> /// <param name="id"></param> public int DeleteShoe(int id) { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.DeleteShoe); statment.AddParameter("Id", id); return(shoeTrackerDatabase.ExecuteStoredProced(statment)); }
/// <summary> /// Get shoe by child name /// </summary> /// <param name="childName"></param> /// <returns></returns> public List <ShoeTrackerDto> GetShoesByChildName(string childName) { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.GetShoeByChildName); statment.AddParameter("ChildName", childName); return(shoeTrackerDatabase.ExecuteStoredProc(statment, MapShoeAndDescription).ToList()); }
/// <summary> /// gets the onthly budget /// </summary> /// <returns></returns> public MonthlyBudget GetMonthlyBudget() { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.GetMonthlyBudgetQuery); MonthlyBudget monthlyBudget = shoeTrackerDatabase.ExecuteStoredProc(statment, MapMonthlyBudget).FirstOrDefault(); return(monthlyBudget); }
/// <summary> /// Adds receipt path /// </summary> /// <param name="id"></param> /// <param name="path"></param> /// <returns></returns> public int AddReceiptPath(int id, string path) { ShoeTrackerSqlStatment statement = new ShoeTrackerSqlStatment(StoredProcedureConstants.AddReceiptPath); statement.AddParameter("Id", id); statement.AddParameter("path", path); return(shoeTrackerDatabase.ExecuteStoredProced(statement)); }
/// <summary> /// get path /// </summary> /// <param name="id"></param> /// <returns></returns> public List <string> GetReceiptPath(int id) { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.GetReceiptPath); statment.AddParameter("Id", id); List <string> path = shoeTrackerDatabase.ExecuteStoredProc(statment, MapReceiptPath).ToList(); return(path); }
/// <summary> /// Gets shoe by id /// </summary> /// <param name="id"></param> /// <returns></returns> public List <ShoeTrackerDto> GetShoeById(int id) { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.GetShoeById); statment.AddParameter("Id", id); List <ShoeTrackerDto> shoeTracker = shoeTrackerDatabase.ExecuteStoredProc(statment, MapShoe).ToList(); return(shoeTracker); }
/// <summary> /// Executes the stored procedure /// </summary> /// <param name="statement"></param> public void ExecuteStoredProcedWithoutScalar(ShoeTrackerSqlStatment statement) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.StoredProcedure; this.PrepareSql(command, statement.sql, statement.GetParameters()); command.ExecuteNonQuery(); } }
/// <summary> /// Adds a shoe to the db /// </summary> /// <param name="shoeTrackerDto"></param> public int AddShoe(ShoeTrackerDto shoeTrackerDto) { ShoeTrackerSqlStatment statement = new ShoeTrackerSqlStatment(StoredProcedureConstants.AddShoeProductStoredProcedure); statement.AddParameter("ShoeTypeId", shoeTrackerDto.ShoeTypeId); statement.AddParameter("Date", shoeTrackerDto.Date); statement.AddParameter("Childname", shoeTrackerDto.ChildName); statement.AddParameter("Price", shoeTrackerDto.Price); statement.AddParameter("Size", shoeTrackerDto.Size); statement.AddParameter("Link", shoeTrackerDto.Link); return(shoeTrackerDatabase.ExecuteStoredProced(statement)); }
/// <summary> /// Executes the stored procedure /// </summary> /// <param name="statement"></param> public int ExecuteStoredProced(ShoeTrackerSqlStatment statement) { int scope; using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.StoredProcedure; this.PrepareSql(command, statement.sql, statement.GetParameters()); scope = int.Parse(command.ExecuteScalar().ToString()); } return(scope); }
/// <summary> /// Executes with mapper /// </summary> /// <typeparam name="T"></typeparam> /// <param name="statement"></param> /// <param name="mapper"></param> /// <returns></returns> public IEnumerable <T> ExecuteStoredProc <T>(ShoeTrackerSqlStatment statement, ShoeTrackerDatabaseRepo.MapRecord <T> mapper) { using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { sqlConnection.Open(); SqlCommand cmd = sqlConnection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; this.PrepareSql(cmd, statement.sql, statement.GetParameters()); SqlDataReader reader = cmd.ExecuteReader(); int count = 0; while (reader.Read()) { yield return(mapper(new SqlReaderWrapper((IDataReader)reader), count++)); } } }
/// <summary> /// insert budget /// </summary> /// <param name="food"></param> /// <param name="gas"></param> /// <param name="shopping"></param> /// <returns></returns> public int InsertMonthlyBudget(string category, int amount) { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.InsertMonthlyBudget); category = category.ToLower(); if (category == "food") { statment.AddParameter("food", amount); } else if (category == "gas") { statment.AddParameter("gas", amount); } else { statment.AddParameter("shopping", amount); } int scalar = shoeTrackerDatabase.ExecuteStoredProced(statment); return(scalar); }
/// <summary> /// Gets all names /// </summary> /// <returns></returns> public List <Names> GetNames() { ShoeTrackerSqlStatment statment = new ShoeTrackerSqlStatment(StoredProcedureConstants.GetAllNames); return(shoeTrackerDatabase.ExecuteStoredProc(statment, MapNames).ToList()); }