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);database=PatientManagement;uid=david;pwd=pass"); SqlCommand myCommand; SqlDataReader myDataReader; String Name_String = usernameLogin.Text; String Pass_String = userpassLogin.Text; if (Name_String.Length > 0 && Pass_String.Length > 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.ExecuteReader(); if(myDataReader.HasRows) { // if the password returned from the database matches that provided by the user // Save Users Name and Role in the session object // Redirect to the main application Interface // else // Alert the user that the username of password is invalid } } catch (SqlException SqEX) { messageLabel.Text = “Exception Occurred”;} finally { conn.Close(); } myDataReader.Close(); } } }

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