Lecture - Trauma Flow - Forms That Update


Trauma Flow - Forms That Update

 

 


 

Prerequisites

 

This is a solution to Assignment 4 of Trauma Flow System. COP 4834 Summer 2012 Assignment 4 - Forms that Update

 

Summary

 

I cover how to create forms that update in the Trauma Flow System. This demonstrates using a GridView to select data to edit, and then allowing edit by clicking on the form.

 

Video 

 

http://online1.daytonastate.edu/player2.php?id=56cb94cb34617aeadff1e79b53f38354 

 

Reference Materials

 

Database Code as Used in Video

ALTER PROCEDURE [dbo].[sp_UpdateForm] (
  @TraumaFormID INT,
  @PatientName VARCHAR(200) = NULL,
  @TimeOfArrival VARCHAR(30) = NULL,
  @Age VARCHAR(10) = NULL,
  @DateOfArrival VARCHAR(50) = NULL,
  @Allergies VARCHAR(200) = NULL,
  @Meds VARCHAR(200) = NULL,
  @ModeOfArrival INT = NULL,
  @EvacUnit VARCHAR(50) = NULL,
  @PMH VARCHAR(200) = NULL)
AS
BEGIN
 
UPDATE TraumaForm
 SET name = @PatientName
 WHERE id = @TraumaFormID     
-- sp_InsertMedicaldata
-- Arg 1 - The Form ID
-- Arg 2 - The XML String identifying the data.
-- Arg 3 - CodeID if this is data from a code (drop down)
-- Arg 4 - ValueText if this is User Entered Data
EXEC sp_InsertMedicalData @TraumaFormID, 'TimeOfArrival', NULL, @TimeOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientCurrentAge', NULL, @Age
EXEC sp_InsertMedicalData @TraumaFormID, 'DateOfArrival', NULL, @DateOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedAllergies', NULL, @Allergies
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedMedications', NULL, @Meds
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientModeOfArrival', @ModeOfArrival, NULL
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientTransportEvacUnit', NULL, @EvacUnit
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientPreviousMedicalHistory', NULL, @PMH
END  
 
ALTER PROCEDURE [dbo].[sp_InsertMedicalData] (
  @TraumaFormID INT,
  @NameText VARCHAR(200),
  @CodeID INT = NULL,
  @ValueText VARCHAR(MAX) = NULL
)
AS
BEGIN 

DECLARE @CategoryID INT
EXEC @CategoryID = dbo.func_GetCategoryID @CategoryText = @NameText

-- N determines if data exists in database
DECLARE @N INT
SELECT @N = COUNT(*) FROM MedicalData
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID

-- If data does not exist in database insert data
IF (@N = 0)
BEGIN
 INSERT INTO MedicalData
  (TraumaFormID, CategoryID, TakenDateTime, CodeID, ValueText)
 VALUES
  (@TraumaFormID, @CategoryID, GETDATE(), @CodeID, @ValueText)
END

-- If n does exist then update it
IF (@N != 0) AND (@TraumaFormID != 0)
BEGIN
UPDATE MedicalData
 SET   CodeID = @CodeID,
       ValueText = @ValueText     
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID
END 
END
GO 

 

TraumaFlowHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
namespace TraumaFlow.Code
{
    public class TraumaFlowHelper
    {
        public static string connectionName = "TraumaFlowConnectionString";
        public static string getConnectionString()
        {
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.getConnectionString(connectionName);
        }
        public static void fillTextBox(TextBox tb, string XMLName, int TraumaFormID)
        {
            tb.Text = getValue(XMLName, TraumaFormID);
        }
        public static string getValue(string XMLName, int TraumaFormID)
        {
            if (XMLName == "PatientReportedName") return TraumaFlowHelper.getPatientName(TraumaFormID);
            string sql = "SELECT Value FROM view_TextMedicalData ";
            sql += "WHERE XMLName = '" + XMLName + "'";
            sql += " AND id = " + Convert.ToString(TraumaFormID);
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static string getPatientName(int TraumaFormID)
        {
            string sql = "SELECT name FROM TraumaForm WHERE id = '" + TraumaFormID.ToString() + "'";
            
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static string getTraumaFormDate(int TraumaFormID)
        {
            string sql = "SELECT Arrival FROM TraumaForm WHERE id = '" + TraumaFormID.ToString() + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static int getCodeID(string XMLName, int TraumaFormID)
        {
            string sql = "SELECT CodeID FROM view_CodedMedicalData ";
            sql += "WHERE XMLName = '" + XMLName + "'";
            sql += " AND id = " + Convert.ToString(TraumaFormID);
            try
            {
                DatabaseHelper dh = new DatabaseHelper(connectionName);
                return Convert.ToInt32(dh.executeSqlReturnString(sql)); 
            }
            catch
            { 
                return 0; 
            }
        }
        public static DataTable matchStaffNames(string pattern)
        {
            string sql = "SELECT StaffName FROM StaffNames WHERE UPPER(StaffName) LIKE '%" + pattern.ToUpper() + "%'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnDataTable(sql);
        }
        public static DataTable getAllXMLNames()
        {
            string sql = "SELECT DISTINCT(NameText) FROM NameResolution";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnDataTable(sql);
        }
        public static string[] traumaTeamMembers()
        {
            string[] s = {"TRAUMA_A_NURSE","RUNNER","LAB_TECH","ED_MD","TRAUMA_B_NURSE","OR_NURSE_TECH", 
                          "RADIOLOGY_TECH", "ORTHO_TECH", "SCRIBE", "RESP_THERAPY", "CHAPLAIN", "OTHER"};
            return s;
        }
        public static void insertMedicalData(string nameText, string value)
        {
            // stored procedure has logic for insert OR update
            updateMedicalData(0, nameText, value);
        }
        public static void updateMedicalData(int traumaFormID, string nameText, string value)
        {
            if (doesMedicalDataExist(traumaFormID, nameText, value)) return;
            string sql = "EXEC [sp_InsertMedicalData] " + Convert.ToString(traumaFormID) + ", '" + nameText + "', NULL , '" + value + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            dh.executeSqlNonQuery(sql);
        }
        public static bool doesMedicalDataExist(int traumaFormID, string nameText, string value)
        {
            string categoryID = getCategoryIDFromNameText(nameText);
            string sql = "SELECT COUNT(*) FROM MedicalData WHERE TraumaFormID = " + Convert.ToString(traumaFormID);
            sql += " AND CategoryID = " + getCategoryIDFromNameText(nameText);
            sql += " AND ValueText = '" + value + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            int c = dh.executeSqlReturnInt(sql);
            return (c >= 1);
        }
        public static string getCategoryIDFromNameText(string nameText)
        {
            string sql = "SELECT CategoryID FROM NameResolution WHERE NameText = '" + nameText +"'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
    }
} 

 

DisplayTraumaForms.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DisplayTraumaForms.aspx.cs" Inherits="TraumaFlow.InputForms.DisplayTraumaForms" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Select Trauma Form to Edit</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Select Current Trauma Form to Edit</h1>
        <asp:Label ID="lblCurrent" runat="server" Text=""></asp:Label>
        <br />
        <asp:GridView ID="gvForms" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="id" DataSourceID="SqlDataSource1" 
            EnableModelValidation="True" ForeColor="#333333" GridLines="None" OnRowCommand="gvForms_OnRowCommand">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                    ReadOnly="True" SortExpression="id" />
                <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
                <asp:BoundField DataField="Arrival" HeaderText="Arrival" 
                    SortExpression="Arrival" />
                <asp:TemplateField HeaderText="Edit">
                <ItemTemplate>
                    <asp:LinkButton ID="lbEdit" runat="server" CommandName="EditPatient" CommandArgument='<%#Eval("id") %>'>Edit This Patient</asp:LinkButton> | 
                    <asp:LinkButton ID="lbData" runat="server" CommandName="EditData" CommandArgument='<%#Eval("id") %>'>Edit Form Data</asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="SELECT * FROM [TraumaForm]"></asp:SqlDataSource>
        <br />
        <br />
        <asp:HyperLink ID="HyperLink1" runat="server" 
            NavigateUrl="~/InputForms/CreateTraumaFlow.aspx">Create New Trauma Flow</asp:HyperLink>
    </div>
    </form>
</body>
</html>
 

 

DisplayTraumaForms.aspx.cs

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace TraumaFlow.InputForms
{
    public partial class DisplayTraumaForms : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            { lblCurrent.Text = "Current Trauma Form ID: " + Convert.ToString(Session["TraumaFormID"]); }
            catch
            { lblCurrent.Text = "No Current Trauma Form  "; }

        }

        protected void gvForms_OnRowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "EditPatient")
            {
                int TraumaFormID = Convert.ToInt32(e.CommandArgument);
                Session.Add("TraumaFormID", TraumaFormID);
                Response.Redirect("CreateTraumaFlow.aspx");
            }

            if (e.CommandName == "EditData")
            {
                int TraumaFormID = Convert.ToInt32(e.CommandArgument);
                Session.Add("TraumaFormID", TraumaFormID);
                Response.Redirect("TabbedEntryBasicInfo.aspx");
            }
        }
    }
}

 

CreateTraumaFlow.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CreateTraumaFlow.aspx.cs" Inherits="TraumaFlow.InputForms.CreateTraumaFlow" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Input Trauma Flow for New Form</title>
    <link rel="Stylesheet" href="/../styles.css" type="text/css" />
</head>
<body>
<h1 style="text-align:center">Enter Data for New Trauma Tracking</h1>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td>
        <asp:Label ID="Label1" runat="server" CssClass="inputLabel" Text="Patient Name:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbPatientName" CssClass="inputTextBox" runat="server" 
            Width="200"></asp:TextBox></td>
    <td>
        <asp:Label ID="Label2" runat="server" CssClass="inputLabel" Text="Time Of Arrival:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbArrivalTime" CssClass="inputTextBox" runat="server" Width="200"></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label3" runat="server" CssClass="inputLabel" Text="Age:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbAge" runat="server" CssClass="inputTextBox" Width="200" ></asp:TextBox></td>
    <td>
        <asp:Label ID="Label4" runat="server" CssClass="inputLabel" Text="Date:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbDate" runat="server" CssClass="inputTextBox"  Width="200"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label5" runat="server" CssClass="inputLabel" Text="Mode Of Arrival:" Width="150"></asp:Label></td>
    <td>
        <asp:DropDownList ID="ddlModeOfArrival" runat="server" 
            CssClass="inputTextBox" Width="200px" 
            DataSourceID="SqlDataSourceModeOfArrival" 
            DataTextField="DisplayText" 
            DataValueField="CodeID">
        </asp:DropDownList></td>
    <td>
        <asp:Label ID="Label6" runat="server" CssClass="inputLabel" Text="Evac Unit" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbEvacUnit" runat="server" CssClass="inputTextBox"  Width="200"></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label7" runat="server" CssClass="inputLabel" Text="Allergies" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbAllergies" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label8" runat="server" CssClass="inputLabel" Text="Meds" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbMeds" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label9" runat="server" CssClass="inputLabel" Text="PMH" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbPMH" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    </table>
        <center>
            <asp:Button ID="btnSubmit" runat="server" CssClass="submitButton" 
                Text="Submit" onclick="btnSubmit_Click" />                
             <asp:Button ID="btnUpdate" runat="server" CssClass="submitButton" 
                Text="Update" onclick="btnUpdate_Click" />
            <asp:SqlDataSource ID="SqlDataSourceModeOfArrival" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
                SelectCommand="SELECT Categories.id AS CategoryID, Codes.id AS CodeID, Codes.DisplayText FROM Categories INNER JOIN Codes ON Categories.id = Codes.CategoryID WHERE (Codes.CategoryID = 10)">
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
                InsertCommand="sp_CreateForm" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_OnInserted"
                UpdateCommand="sp_UpdateForm" UpdateCommandType="StoredProcedure">
                <InsertParameters>
                    <asp:ControlParameter ControlID="tbPatientName" Name="PatientName" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="TimeOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAge" Name="Age" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="DateOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Allergies" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Meds" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="ddlModeOfArrival" Name="ModeOfArrival" 
                        PropertyName="SelectedValue" Type="Int32" />
                    <asp:ControlParameter ControlID="tbEvacUnit" Name="EvacUnit" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbPMH" Name="PMH" PropertyName="Text" 
                        Type="String" />
                    <asp:Parameter Direction="Output" Name="TraumaFormID" Type="Int32" />
                </InsertParameters>
                 <UpdateParameters>
                    <asp:SessionParameter Name="TraumaFormID" Type="Int32" SessionField="TraumaFormID" 
                         ConvertEmptyStringToNull="False" />
                    <asp:ControlParameter ControlID="tbPatientName" Name="PatientName" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="TimeOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAge" Name="Age" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="tbDate" Name="DateOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Allergies" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbMeds" Name="Meds" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="ddlModeOfArrival" Name="ModeOfArrival" 
                        PropertyName="SelectedValue" Type="Int32" />
                    <asp:ControlParameter ControlID="tbEvacUnit" Name="EvacUnit" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbPMH" Name="PMH" PropertyName="Text" 
                        Type="String" />
                </UpdateParameters>                
            </asp:SqlDataSource>           
        </center>
    </div>
    </form>
</body>
</html>
 

 

CreateTraumaFlow.aspx.cs

 

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using TraumaFlow.Code;

namespace TraumaFlow.InputForms
{
    public partial class CreateTraumaFlow : System.Web.UI.Page
    {
        public int TraumaFormID()
        {
            if (Session["TraumaFormID"] == null)
                return 0;
            else
                return Convert.ToInt32(Session["TraumaFormID"]);
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (TraumaFormID() != 0)
                {
                    int id = TraumaFormID();
                    btnSubmit.Visible = false;
                    btnUpdate.Visible = true;
                    tbPatientName.Text = TraumaFlowHelper.getValue("PatientReportedName", id);
                    tbArrivalTime.Text = TraumaFlowHelper.getValue("TimeOfArrival", id);
                    tbAge.Text = TraumaFlowHelper.getValue("PatientCurrentAge", id);
                    tbDate.Text = TraumaFlowHelper.getValue("DateOfArrival", id);
                    tbAllergies.Text = TraumaFlowHelper.getValue("PatientReportedAllergies", id);
                    tbMeds.Text = TraumaFlowHelper.getValue("PatientReportedMedications", id);
                    tbEvacUnit.Text = TraumaFlowHelper.getValue("PatientTransportEvacUnit", id);
                    tbPMH.Text = TraumaFlowHelper.getValue("PatientPreviousMedicalHistory", id);
                    int codeID = TraumaFlowHelper.getCodeID("PatientModeOfArrival", id);
                    if (codeID != 0) ddlModeOfArrival.SelectedValue = Convert.ToString(codeID);
                }
                else
                {
                    btnSubmit.Visible = true;
                    btnUpdate.Visible = false;
                }
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlDataSource2.Insert();
            Response.Redirect("DisplayTraumaForms.aspx");
        }

        protected void SqlDataSource2_OnInserted(object sender, SqlDataSourceStatusEventArgs e)
        {
            System.Data.Common.DbCommand command = e.Command;
            Session.Add("TraumaFormID", command.Parameters["@TraumaFormID"].Value.ToString());            
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {            
            SqlDataSource2.Update();
            Response.Redirect("DisplayTraumaForms.aspx");
        }
    }
}

 

 

 

Additional Information

 

 

COP 4834 Lectures Page