Case Study – Trauma Flow Database
Shown here are assignments and lectures that use the Trauma Flow Database. This is a full case study of a complete web system developed during the Summer 2012 offering of COP4834.
Database Procedure to Update the Trauma Form
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 Database Procedure used to Insert Data
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 C# File for TraumaFowHelper.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 – code file
<%@ 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>Associated Code-behind DisplayTraumaForms.aspx
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");
}
}
}
}