Database Encapsulation

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

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

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

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]; }

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……………..

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.

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

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…?

Database Encapsulation