Slide 1Slide 2Slide 3Project FeaturesSlide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26CS 8630 Database Administration, Ajay Sitaram, David Odaibo Web Technologies Front End And DBMS With ASP.NET Ajay Krithi Sitaram, David OdaiboCS8630 Database AdminstrationMED ASSIST Next Generation Hospital Management SystemCS 8630 Database Administration, Ajay Sitaram, David Odaibo MED ASSIST• 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 loggingCS 8630 Database Administration, Ajay Sitaram, David Odaibo ASP. Net OverviewASP. Net OverviewProject Features•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.CS 8630 Database Administration, Ajay Sitaram, David Odaibo Project FeaturesCS 8630 Database Administration, Ajay Sitaram, David Odaibo ER Diagram ER DiagramCS 8630 Database Administration, Ajay Sitaram, David Odaibo CRUD Matrix CRUD MatrixCS 8630 Database Administration, Ajay Sitaram, David Odaibo Gantt ChartCS 8630 Database Administration, Ajay Sitaram, David Odaibo Use Case DiagramCS 8630 Database Administration, Ajay Sitaram, David OdaiboForm Name Form DescriptionLogin Form Form to login to system Application Interface Form Interface of application Permission Form User level permission to modules in systemPatient Information Form View and edit patient informationPrescription Form View and edit required prescription to patientsMedication Form View and edit the required medication.Room assignment Form View and edit the current information of a roomSearch Form View and edit search form for searching any informationSample report Form View and edit report of selected moduleList Form List all entitiesUser Form Display information about the user Form DescriptionCS 8630 Database Administration, Ajay Sitaram, David Odaibo Database Permission TableCS 8630 Database Administration, Ajay Sitaram, David Odaibo Patient TableCS 8630 Database Administration, Dr. GuimaraesData Access ASP.NET• 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 DataTableCS 8630 Database Administration, Dr. GuimaraesLogin Form• Login Form Validates UsersCS 8630 Database Administration, Dr. GuimaraesUsers Table• 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 objectCS 8630 Database Administration, Dr. Guimaraesprotected 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(); } }}Login Button Event HandlerCS 8630 Database Administration, Dr. GuimaraesNavigation Drop Down Lists(Create, View, Edit and Delete Records) (List managed entities) ( Search for specific records) (view reports) ( Administration)CS 8630 Database Administration, Dr. GuimaraesStaff FormStaff Form – View and Edit details about a staff member.CS 8630 Database Administration, Dr. GuimaraesBillable Item FormBillable Item Form – Create, View and Edit billable Item details. Save Button – Insert or Update ?CS 8630 Database Administration, Dr. GuimaraesAssociate Table Lookups• 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 typesCS 8630 Database Administration, Dr. GuimaraesListing recordsEntities are listed in data grid, specific record can be click to view detailsCS 8630 Database Administration, Dr. GuimaraesSearching for records• Search for specific records by specifying criteriaCS 8630 Database Administration, Dr. GuimaraesSecurity• Administrator can assign Read/Edit privileges to users based on roles• Settings are stored in permission tableCS 8630 Database Administration, Dr. GuimaraesPermission Table• Administration panel stores all security settings in permission table• All forms consult permission table to see if users role has permission to read or editCS 8630 Database Administration, Dr. GuimaraesReports• Crystal Report used in asp. Net web form• Uses Report Viewer web controlCS 8630 Database Administration, Dr. GuimaraesConclusionImprovements 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 layerTechnologies Used include• ASP.NET ( Active