1 / 8

Database Encapsulation

Database Encapsulation. By: Devon Wright Sin-Min Lee Spring 2009. Lajenda Technolgies. Dental Practice Management IT work for dental practices Open Source Software: Open Dental C# Jordan Sparks was tired of paying $20,000 a year to use other software Topic:

gizela
Download Presentation

Database Encapsulation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Database Encapsulation By: Devon Wright Sin-Min Lee Spring 2009

  2. LajendaTechnolgies • Dental Practice Management • IT work for dental practices • Open Source Software: Open Dental • C# • Jordan Sparks was tired of paying $20,000 a year to use other software • Topic: • How to encapsulate classes around a database

  3. Patient Table Important Attributes: PatNum – Patient Number Lname – varchar Fname – varchar PatStatus - Inactive-0, patient-1, deceased-2, archieved-3, nonpatient-4 Birthdate - date Address – varchar Guarantor – PatNum who is responsible for account BalTotal – balance of account SQLyog Community Edition

  4. Get Single Patient • How to get a single patient from the patient table. Pass in the patient number. Return type is a single Patient. • Note: SubmitAndFill executes the command and returns an array of type Patient, only 1 patient will be returned • public static Patient GetPat(int patNum){ • if(patNum==0) { • return null; • } • String command="SELECT * FROM patient WHERE PatNum="+POut.PInt(patNum); • Patient[] patarray=SubmitAndFill(command); • return patarray[0]; }

  5. Update Patient Table After having the patient fill a form with the updated information, pass in the newly created Patient object and the old patient object. This style of coding will only update the attributes that are different from the old information, of course if nothing is changed then it just returns. • public static int Update(Patient pat, Patient CurOld) { • bool comma=false; • string c = "UPDATE patient SET "; • if(pat.LName!=CurOld.LName) { • c+="LName = '“ • +POut.PString(pat.LName)+"'"; • comma=true; • } • if(pat.FName!=CurOld.FName) { • if(comma) • c+=","; • c+="FName = '" • +POut.PString(pat.FName)+"'"; • comma=true; • } • if(pat.MiddleI!=CurOld.MiddleI) { • if(comma) • c+=","; • c+="MiddleI = '" • +POut.PString(pat.MiddleI)+"'"; • comma=true; • } .............for ever attribute in Patient table……………..

  6. Get Multiple Patients • public static Patient[] GetMultPats(int[] patNums){ • string strPatNums=""; • DataTable table; • if(patNums.Length>0){ • for(int i=0;i<patNums.Length;i++){ • if(i>0){ • strPatNums+="OR ";} • strPatNums+="PatNum='"+patNums[i].ToString()+"' "; } • string command="SELECT * FROM patient WHERE "+strPatNums; • table=General.GetTable(command); • } • else{ • table=new DataTable(); • } • Patient[] multPats=TableToList(table).ToArray(); • return multPats; • } • For each element in array patNums, add that patient number to the string, strPatNums. • Make a command string which holds the SELECT statement. • Concatenate strPatNums to the end of command. • Pass the string to the query executer then it will return a DataTable. • TableToList returns a collection of <Patient> • Inherited ToArray method is called.

  7. Get Birthday List • public static DataTable GetBirthdayList(DateTime dateFrom,DateTime dateTo){ • string command="SELECT LName,FName,Preferred,Address,Address2,City,State,Zip,Birthdate “+"FROM patient " • +"WHERE SUBSTRING(Birthdate,6,5) >= '"+dateFrom.ToString("MM-dd")+"' “+"AND SUBSTRING(Birthdate,6,5) <='"+dateTo.ToString("MM-dd")+"' “ • +"AND PatStatus=0 ORDER BY DATE_FORMAT(Birthdate,'%m/%d/%Y')"; • DataTable table=General.GetTable(command); • table.Columns.Add("Age"); • for(int i=0;i<table.Rows.Count;i++){ • table.Rows[i]["Age"]=DateToAge(PIn.PDate(table.Rows[i]["Birthdate"].ToString()),dateTo.AddDays(1)).ToString(); • } • return table; } • Create String with SELECT statement • Add WHERE statement from dateFrom to dateTo. • AND PatStatus=0 is only active patients • ORDER BY the DATE_FORMAT • Pass it through the query executer • Add the column Age • Calculate for each Patient’s age in the DataTable

  8. What have we learned? • Use static methods so it is public to all other classes • ANY query that has to retrieve or update information from that single table, then that method has to implemented and executed here. • Minimize query to only necessary attribute updates or selects. • Have another class to do the execution of the query, only prepare the string for execution • If multiple Objects have to be returned, use a generic type then cast to necessary type. • Questions…?

More Related