This is a solution to Assignment 4 of Trauma Flow System. COP 4834 Summer 2012 Assignment 4 - Forms that Update
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.
http://online1.daytonastate.edu/player2.php?id=56cb94cb34617aeadff1e79b53f38354
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 |
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); } } } |
<%@ 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> |
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"); } } } } |
<%@ 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> |
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"); } } } |