CS 8630 Database Administration, Ajay Sitaram, David Odaibo

1
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
Web Technologies Front End And DBMS
With ASP.NET Ajay Krithi Sitaram, David
Odaibo CS8630 Database Adminstration
2
MED ASSIST
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
MED ASSIST
Next Generation Hospital Management System
3
ASP. Net Overview
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
ASP. Net Overview

• ASP. Net is a part of .Net framework that allows
  for the dynamic creation of web pages over HTTP.
• Flexible language options like C ,Vb.Net and
  Jscript.net.
• Full access functionality to .Net framework which
  includes Database access, XML and performance
  monitoring and logging

4
Project Features
Project Features
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo

• Security to application with login.
• Security to Forms through Permissions.
• Security to lists with permission.
• Security to reports
• AJAX for form validation and lists.
• Easy to use web interface.
• Relationship mapping in forms.

5
ER Diagram
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
ER Diagram
6
CRUD Matrix
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
CRUD Matrix
7
Gantt Chart
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
8
Use Case Diagram
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
9
Form Description
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
10
Database Permission Table
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
11
Patient Table
CS 8630 Database Administration, Ajay Sitaram,
David Odaibo
12
Data Access ASP.NET
CS 8630 Database Administration, Dr. Guimaraes

• ADO. Net (ActiveX Data Objects) is a library
  of objects available in the .Net framework to
  work with databases.
• SqlConnection Used to connect to the database
• SqlCommand Used as a wrapper for SQL
  statements or Stored Procedure calls
• SqlDataAdapter Used to hold select, insert,
  update, delete, commands that are used to
  populate a Dataset and update the Database
• SqlDataReader Used to quickly read records
  from the database. It maintains an open
  connection to the database
• DataSet Filled by a SqlDataAdapter and
  contains a set of Datatables and relationships
  between the tables
• DataTable A container for rows/records read
  from the database
• DataRow A row in a DataTable

13
Login Form
CS 8630 Database Administration, Dr. Guimaraes

• Login Form Validates Users

14
Users Table
CS 8630 Database Administration, Dr. Guimaraes

• Stores information about valid system users
• User Roles determine user privileges
• Queried by login form to authenticate users
• User Role is stored in the session object

15
Login Button Event Handler
CS 8630 Database Administration, Dr. Guimaraes

• protected void Button1_Click(object sender,
  EventArgs e)
• SqlConnection conn new
  SqlConnection("server(local)databasePatientMana
  gementuiddavidpwdpass")
• SqlCommand myCommand
• SqlDataReader myDataReader
• String Name_String usernameLogin.Text
• String Pass_String userpassLogin.Text
• if (Name_String.Length gt 0
  Pass_String.Length gt 0)
• try
• conn.Open()
• String queryS "select UID,
  UserName,UserNameFirst, UserNameLast, UserRole,
  Password from Users where UserName "
  "'"Name_String"'"
• myCommand new
  SqlCommand(queryS, conn)
• myDataReader myCommand.ExecuteRe
  ader()
• if(myDataReader.HasRows)

16
Navigation Drop Down Lists
CS 8630 Database Administration, Dr. Guimaraes
(Create, View, Edit and Delete Records) (List
managed entities) ( Search for specific records)
(view reports) ( Administration)
17
Staff Form
CS 8630 Database Administration, Dr. Guimaraes
Staff Form View and Edit details about a staff
member.
18
Billable Item Form
CS 8630 Database Administration, Dr. Guimaraes
Billable Item Form Create, View and Edit
billable Item details. Save Button Insert or
Update ?
19
Associate Table Lookups
CS 8630 Database Administration, Dr. Guimaraes

• Ajax and remote callbacks are used to suggest
  entities
• Select Column that will be used with Like clause
  in SQL statement
• Popup Grid lists suggested entities as user
  types

20
Listing records
CS 8630 Database Administration, Dr. Guimaraes
Entities are listed in data grid, specific record
can be click to view details
21
Searching for records
CS 8630 Database Administration, Dr. Guimaraes

• Search for specific records by specifying
  criteria

22
Security
CS 8630 Database Administration, Dr. Guimaraes

• Administrator can assign Read/Edit privileges to
  users based on roles
• Settings are stored in permission table

23
Permission Table
CS 8630 Database Administration, Dr. Guimaraes

• Administration panel stores all security
  settings in permission table
• All forms consult permission table to see if
  users role has permission to read or edit

24
Reports
CS 8630 Database Administration, Dr. Guimaraes

• Crystal Report used in asp. Net web form
• Uses Report Viewer web control

25
Conclusion
CS 8630 Database Administration, Dr. Guimaraes
Improvements to be made

• Use more parameterized stored procedures to
  prevent SQL injection attacks
• Create a data access layer and move database
  access code out of the code behind page class,
  use entity objects between code behind class and
  data access layer

Technologies Used include

• ASP.NET ( Active Server Pages) Web tier
• ADO.NET ( Active x Data Objects) Middle Tier
  database access
• SQL server 2000
• Crystal Reports
• Ajax ( Asynchronous JavaScript and XML)
  callbacks to web server.

26
Questions
CS 8630 Database Administration, Dr. Guimaraes

Questions