public List<Reward> FindAll()
{
List<Reward> Rewards = new List<Reward>();
string query = "SELECT * FROM Reward";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Reward r = new Reward();
r.RewardID = reader.GetInt32(reader.GetOrdinal("RewardID"));
r.RewardTitle = reader.GetString(reader.GetOrdinal("RewardTitle"));
r.RewardDescription = reader.GetString(reader.GetOrdinal("RewardDescription"));
r.StartPrice = reader.GetInt32(reader.GetOrdinal("StartPrice"));
r.Limit = reader.GetInt32(reader.GetOrdinal("Limit"));
r.ProjectID = reader.GetInt32(reader.GetOrdinal("ProjectID"));
Rewards.Add(r);
}
}
}
}
return Rewards;
}
public void Delete(Reward reward)
{
string query = "DELETE FROM Reward";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
}
public Reward Save(Reward reward)
{
string query = @"INSERT INTO Reward (
RewardTitle,
RewardDescription,
StartPrice,
Limit,
ProjectID
) VALUES (
@RewardTitle,
@RewardDescription,
@StartPrice,
@Limit,
@ProjectID
)";
using (SqlConnection db = new SqlConnection(CONN_STR))
{
if (db.State != System.Data.ConnectionState.Open)
{
db.Open();
}
using (SqlCommand cmd = db.CreateCommand())
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue("RewardTitle", reward.RewardTitle);
cmd.Parameters.AddWithValue("RewardDescription", reward.RewardDescription != null ? reward.RewardDescription : "None");
cmd.Parameters.AddWithValue("StartPrice", reward.StartPrice);
cmd.Parameters.AddWithValue("Limit", reward.Limit);
cmd.Parameters.AddWithValue("ProjectID", reward.ProjectID);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity";
reward.RewardID = (int)(decimal)cmd.ExecuteScalar();
}
}
return reward;
}