Exemple #1
0
        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);
                }
            }
        }
Exemple #2
0
        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);
        }
Exemple #3
0
        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();
            }
        }