internal static List <CareVisit> GetList(int careRequestID)
{
List <CareVisit> visits = new List <CareVisit>();
CareVisit visit;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Pets"].ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("Select * From dbo.CareVisit Where CareRequestID = @careRequestID Order By VisitDateTime;", connection))
{
command.Parameters.AddWithValue("careRequestID", careRequestID);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
visit = new CareVisit();
visit.ID = ((int)reader["ID"]);
visit.CareProvider = new CareProvider((int)reader["CareProviderID"]);
visit.VisitDateTime = ((DateTime)reader["VisitDateTime"]);
visit.IsComplete = ((bool)reader["IsComplete"]);
visit.Tasks = CareVisitTaskController.GetList((int)visit.ID);
visits.Add(visit);
}
}
}
return(visits);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
public CareVisit Get(int id)
{
CareVisit visit = new CareVisit();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Pets"].ConnectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("Select * From dbo.vwCareVisit Where ID = @id", connection))
{
command.Parameters.AddWithValue("id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
visit.ID = ((int)reader["ID"]);
visit.VisitDateTime = ((DateTime)reader["VisitDateTime"]);
visit.CareProvider = new CareProvider((int)reader["CareProviderID"], (string)reader["CareProviderName"]);
visit.Customer = CustomerController.FindByID((int)reader["CustomerID"]);
visit.Tasks = CareVisitTaskController.GetList(id);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(visit);
}
internal static void SaveList(int careRequestID, List <CareVisit> visits, SqlConnection connection, SqlTransaction transaction)
{
DataTable visitIDs = new DataTable();
visitIDs.Columns.Add(new DataColumn("ID", typeof(int)));
//Step 1 - Insert or Update Visit and collect IDs
foreach (CareVisit visit in visits)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.Transaction = transaction;
command.Parameters.AddWithValue("CareRequestID", careRequestID);
command.Parameters.AddWithValue("CareProviderID", visit.CareProvider.ID);
command.Parameters.AddWithValue("VisitDateTime", visit.VisitDateTime);
if (visit.ID == null)
{
command.CommandText = "Insert Into dbo.CareVisit (CareRequestID, CareProviderID, VisitDateTime) OUTPUT Inserted.ID Values (@CareRequestID, @CareProviderID, @VisitDateTime); ";
visit.ID = (int)command.ExecuteScalar();
}
else
{
command.Parameters.AddWithValue("ID", visit.ID);
command.CommandText = "Update dbo.CareVisit Set CareRequestID = @CareRequestID, CareProviderID = @CareProviderID, VisitDateTime = @VisitDateTime Where ID = @ID;";
command.ExecuteNonQuery();
}
visitIDs.Rows.Add(visit.ID);
CareVisitTaskController.SaveList((int)visit.ID, visit.Tasks, connection, transaction);
}
}
//Step 2 - Delete Visits removed via UI
using (SqlCommand command = new SqlCommand("Delete From dbo.CareVisit Where CareRequestID = @careRequestID And ID Not In (Select ID From @visitIDs);", connection, transaction))
{
command.Parameters.AddWithValue("careRequestID", careRequestID);
SqlParameter tableTypeParameter = command.Parameters.AddWithValue("visitIDs", visitIDs);
tableTypeParameter.SqlDbType = SqlDbType.Structured;
tableTypeParameter.TypeName = "dbo.typeID";
command.ExecuteNonQuery();
}
}
