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