| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Want to get organized in 2022? Let Dokkio put your cloud files (Drive, Dropbox, and Slack and Gmail attachments) and documents (Google Docs, Sheets, and Notion) in order. Try Dokkio (from the makers of PBworks) for free. Available on the web, Mac, and Windows.

View
 

COP 4834 Summer 2012 Assignment 3 - Passing Data in Web Systems

Page history last edited by Dr. Ron Eaglin 9 years, 7 months ago

 Assignment 3 - Working with Data

 

Objectives

 

Create Forms to Collect Data in .NET

Work with a Database back-end

 

Assignment

 

We are now going to program the form. Everything in the application at this point is about collecting data that is needed based on the form or reporting data that is in the form in a usable format.

 

The first part of the form has the following data:

 

Data
Location of Data
XML Text
Patient Name TraumaForm - Name PatientReportedName
Time of Arrival
TraumaForm - Arrival (DateTime)
TimeOfArrival
Age
MedicalData - Category = Patient Age, ValueText
PatientCurrentAge
Date
TraumaForm - Arrival (DateTime)
DateOfArrival
Allergies
MedicalData - Category = Allergy, ValueText
PatientReportedAllergies
Meds
MedicalData - Category = Meds, ValueText
PatientReportedMedications
Mode Of Arrival

MedicalData - Category = Mode of Arrival, Codes = (Evac, Air One, Private Vehicle, Fire Flight, Other)

 

PatientModeOfArrival

 

 

Evac Unit MedicalData - Category = EvacUnit PatientTransportEvacUnit
PMH MedicalData - Category = Previous Medical History, ValueText PatientPreviousMedicalHistory

 

Please Note that you will need to use the entry capability of the previous form to enter each of the Data Fields as a Category. Mode Of Arrival will also need the corresponding codes entered.

 

CreateTraumaFlow.aspx - This will be a form that is an input form, that will allow you to input data through the form. You will create a form that allows for the input of this data. It will use the stored procedure below to accept the data.

 

You also want to have a positive feedback to show the data being entered. In this case we will use a View (also in code below). Using this View create a display that shows the data that was entered.

 

You are responsible for designing the form and performing the full data collection. The form and the display of the entered data should be available on your site.

 

Information

 

Here are the additional database tables for this assignment. All Medical Data are stored in a single table called MedicalData. The form table TraumaForm is really a shell table that contains an ID for all data to point to.

 

USE TraumaFlow
GO
CREATE TABLE TraumaForm (
 id INT PRIMARY KEY IDENTITY(1,1),
 name VARCHAR(200),
 Arrival DATETIME )
  
 CREATE TABLE MedicalData (
  id INT PRIMARY KEY IDENTITY(1,1),
  TraumaFormID INT NOT NULL,
  CategoryID INT NOT NULL,
  TakenDateTime DATETIME,
  CodeID INT,    
  ValueText VARCHAR(MAX))
  
 ALTER TABLE MedicalData ADD FOREIGN KEY (TraumaFormID)
   REFERENCES MedicalData(id)
 
 ALTER TABLE MedicalData ADD FOREIGN KEY (CategoryID)
   REFERENCES Categories(id)

 ALTER TABLE MedicalData ADD FOREIGN KEY (CodeID)
   REFERENCES Codes(id) 

 

Because our system relies very heavily on integer ID's to keep track of information types, we will also need to have a way to resolve this is a more human readable form. To do this we MAP names to the integer ID's to Actual Names. We then use a function to resolve the ID. Here is to learning a few more database tricks.

 

USE TraumaFlow
GO

CREATE TABLE NameResolution (
  XMLNameText VARCHAR(200),
  NameSpaceID INT,
  CategoryID INT,
  CodeID INT )

 CREATE FUNCTION func_GetNamespaceID (
    @NamespaceText VARCHAR(200))
RETURNS INT
AS
BEGIN
    
    DECLARE @ResultVar INT

    SET @ResultVar = (SELECT TOP 1 NamespaceID 
            FROM NameResolution
            WHERE UPPER(NameText) = UPPER(@NamespaceText))
    
    RETURN ISNULL(@ResultVar, 0)
END
GO

USE TraumaFlow 
GO

CREATE FUNCTION func_GetCategoryID (
    @CategoryText VARCHAR(200))
RETURNS INT
AS
BEGIN
    
    DECLARE @ResultVar INT

    SET @ResultVar = (SELECT TOP 1 CategoryID 
            FROM NameResolution
            WHERE UPPER(NameText) = UPPER(@CategoryText))
    
    RETURN ISNULL(@ResultVar,0)
END
GO

 

I also altered the sp_InsertCategory to ensure there were no duplicates. Here is the ALTER PROCEDURE and also the insertions using this for this assignment.

ALTER PROCEDURE [dbo].[sp_InsertCategory] (
   @DisplayText varchar(200)
   )
AS
BEGIN
  
  DECLARE @N INT
  SELECT @N = COUNT(*)    FROM Categories 
                        WHERE NamespaceID = 1
                        AND DisplayText = @DisplayText
                        
IF @N = 0                        
  BEGIN
  INSERT INTO Categories
    (NamespaceID, DisplayText)
  VALUES
    (1, @DisplayText)
  END  
END 

GO
EXEC sp_InsertCategory 'Patient Name'
EXEC sp_InsertCategory 'Time Of Arrival'
EXEC sp_InsertCategory 'Age'
EXEC sp_InsertCategory 'Date'
EXEC sp_InsertCategory 'Allergies'
EXEC sp_InsertCategory 'Meds'
EXEC sp_InsertCategory 'Mode Of Arrival'
EXEC sp_InsertCategory 'Evac Unit'
EXEC sp_InsertCategory 'PMH'

 

 

Last we have the complexity of Name Resolution. To handle this we are using an XML string to uniquely identify each piece of data. This XML name is stored in the table NameResolution. Using the Stored procedure sp_InsertNameResolution you will have to (1) create a form to allow the entry of the corresponding NameText that are specified in the upper part of the assignment.  This will be a form where you pick a category from a drop down list and then enter the NameText into a text box. You will have to enter all the data for this assignment.

 

USE TraumaFlow
GO
CREATE PROCEDURE sp_InsertNameResolution (
  @CategoryID INT,
  @NameText VARCHAR(200))
AS
BEGIN
DECLARE @N INT
SELECT @N = COUNT(*) FROM Nameresolution
                    WHERE NamespaceID = 1
                    AND (CategoryID = @CategoryID OR NameText = @NameText)
IF (@N = 0)
 BEGIN                    
 
 INSERT INTO NameResolution
  (NameText, NamespaceID, CategoryID)
 VALUES
  (@NameText, 1, @CategoryID) 
 
 END
END 
GO

CREATE VIEW view_CategoryXMLMap
AS
SELECT 
 Categories.id AS 'Category ID',
 Categories.DisplayText AS 'Category Name',
 NameResolution.NameText AS 'XML Text'
FROM
 Categories, NameResolution 
WHERE
 NameResolution.CategoryID = Categories.id 

GO

 

Here is a screen capture from the page I created to do this.

 

 

Here is the form that is the submission for this assignment.

 

Note: You will need to create the form above using the stored procedure sp_InsertNameResolution and enter the values as shown in the grid in the screen capture for the system to work. If you misspell one of the XML names you will need to delete the entry and enter it correctly (or modify it to be correct).

 

 

The Form above will be built on the stored procedure sp_CreateForm which is below. This stored procedure relies on sp_InsertMedicalData which is also in the script below.


CREATE 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

-- Does 
DECLARE @N INT
SELECT @N = COUNT(*) FROM MedicalData
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID

IF (@N = 0)
BEGIN
 INSERT INTO MedicalData
  (TraumaFormID, CategoryID, TakenDateTime, CodeID, ValueText)
 VALUES
  (@TraumaFormID, @CategoryID, GETDATE(), @CodeID, @ValueText)
END

IF (@N = 1)
BEGIN
UPDATE MedicalData
 SET   CodeID = @CodeID,
       ValueText = @ValueText     
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID
END 
END
GO 

CREATE PROCEDURE [dbo].[sp_CreateForm] (
  @PatientName VARCHAR(200) = 'John Doe',
  @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
  
DECLARE @TraumaFormID INT
INSERT INTO TraumaForm
 (name, Arrival)
VALUES
 (@PatientName, GETDATE())  

SET @TraumaFormID = @@IDENTITY

-- 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  
GO

 

 

 

 

 

Estimated Completion Time

 

You should allot 2-10 hours to complete this assignment.

 

Supporting Lectures 

 

There are quite a few lectures that will support this assignment. I have included a link here to the full page of all Lectures. As I receive questions on the assignment I will link to specific lectures or also create additional lectures.

 

COP 4834 Lectures Page

 

Questions and Answers

 

Question: I have been working through Assignment 3, and I got as far as creating the form to map the XML. I am a bit at a loss, however, how to use the stored procedure in order to create the final form, and make it functional. As far as I can tell, sp_InsertNameResolution does not write to the locations of Medical Data and Trauma Form. How does it all link together? Are we meant to write our own procedure? Or did I miss something in your notes?

 

Answer: There is something I did forget to mention. If you look at the figure above "Map Categories to XML Names" you'll see that there is some data entered (I used the form to enter the data). The reason I did not put this all in a script is the the id's would change based on the order you entered the categories - if you enter them with your own form, that will not be a problem. For the system to work correctly - you will need to enter the XML names as shown in the image above. Once you do this it should all work. You do not need to write any stored procedure.

 

 

 

External Resources

 

Any web resources to help with the assignment here

 

Grading Criteria

 

Grading criteria

 

Comments (0)

You don't have permission to comment on this page.