DATABASE ENCAPSULATIONBy: 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: How to encapsulate classes around a

database

PATIENT TABLEImportant Attributes:PatNum – Patient NumberLname – varcharFname – varcharPatStatus - Inactive-0, patient-1, deceased-2, archieved-3, nonpatient-4Birthdate - dateAddress – varcharGuarantor – PatNum who is responsible for accountBalTotal – 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 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……………..

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.

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