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