public CareRequest Get(int id)
{
careRequest = new CareRequest();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Pets"].ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("Select * From dbo.vwCareRequest Where ID = @id", connection))
{
command.Parameters.AddWithValue("id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
careRequest = GetCareRequestFromReader(reader);
}
}
}
careRequest.Customer = CustomerController.FindByID((int)careRequest.Customer.ID);
careRequest.Visits = CareVisitController.GetList((int)careRequest.ID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(careRequest);
}
public CareRequest Post(CareRequest careRequest)
{
SqlTransaction transaction;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Pets"].ConnectionString))
{
connection.Open();
transaction = connection.BeginTransaction();
try
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.Transaction = transaction;
command.Parameters.AddWithValue("CustomerID", careRequest.Customer.ID);
command.Parameters.AddWithValue("StartDate", careRequest.StartDate);
command.Parameters.AddWithValue("EndDate", careRequest.EndDate);
if (careRequest.ID == null)
{
command.CommandText = "Insert Into dbo.CareRequest (CustomerID, StartDate, EndDate) OUTPUT Inserted.ID Values (@CustomerID, @StartDate, @EndDate);";
careRequest.ID = (int)command.ExecuteScalar();
}
else
{
command.CommandText = "Update dbo.CareRequest Set CustomerID = @CustomerID, StartDate = @StartDate, EndDate = @EndDate Where ID = @ID;";
command.Parameters.AddWithValue("ID", careRequest.ID);
command.ExecuteNonQuery();
}
}
CareVisitController.SaveList((int)careRequest.ID, careRequest.Visits, connection, transaction);
transaction.Commit();
return(Get((int)careRequest.ID)); //return the newly created CareRequest so the caller has fresh IDs, etc.
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
}
}
