internal static List <Pet> GetList(int customerID)
{
List <Pet> pets = new List <Pet>();
Pet pet;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Pets"].ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("Select * From dbo.Pet Where CustomerID = @customerID;", connection))
{
command.Parameters.AddWithValue("customerID", customerID);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
pet = new Pet();
pet.ID = ((int)reader["ID"]);
pet.Name = ((string)reader["Name"]);
pet.Type = new PetType((int)reader["TypeID"]);
pet.Description = ((string)reader["Description"]);
pet.Tasks = PetTaskController.GetList((int)pet.ID);
pets.Add(pet);
}
}
}
return(pets);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
internal static void SaveList(int customerID, List <Pet> pets, SqlConnection connection, SqlTransaction transaction)
{
try
{
DataTable petIDs = new DataTable();
petIDs.Columns.Add(new DataColumn("ID", typeof(int)));
//Step 1 - Insert or Update Pets and collect IDs
foreach (Pet pet in pets)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.Transaction = transaction;
command.Parameters.AddWithValue("Name", pet.Name);
command.Parameters.AddWithValue("CustomerID", customerID);
command.Parameters.AddWithValue("TypeID", pet.Type.ID);
command.Parameters.AddWithValue("Description", pet.Description);
if (pet.ID == null)
{
command.CommandText = "Insert Into dbo.Pet (Name, CustomerID, TypeID, Description) OUTPUT Inserted.ID Values (@Name, @CustomerID, @TypeID, @Description); ";
pet.ID = (int)command.ExecuteScalar();
}
else
{
command.Parameters.AddWithValue("ID", pet.ID);
command.CommandText = "Update dbo.Pet Set Name = @Name, TypeID = @TypeID, Description = @Description Where ID = @ID;";
command.ExecuteNonQuery();
}
petIDs.Rows.Add(pet.ID);
}
}
//Step 2 - Delete Pets removed via UI
using (SqlCommand command = new SqlCommand("Delete From dbo.Pet Where CustomerID = @customerID And ID Not In (Select ID From @petIDs);", connection, transaction))
{
command.Parameters.AddWithValue("customerID", customerID);
SqlParameter tableTypeParameter = command.Parameters.AddWithValue("petIDs", petIDs);
tableTypeParameter.SqlDbType = SqlDbType.Structured;
tableTypeParameter.TypeName = "dbo.typeID";
command.ExecuteNonQuery();
}
//Step 3 - Save Pet Tasks
foreach (Pet pet in pets)
{
PetTaskController.SaveList((int)pet.ID, pet.Tasks, connection, transaction);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}