Download
1 / 7
70 likes | 234 Views
Lajenda Technolgies. Dental Practice ManagementIT work for dental practicesOpen Source Software: Open DentalC
E N D
1. Database Encapsulation By: Devon Wright
Sin-Min Lee
Spring 2009
2. 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
3. Patient Table
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 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 Patients 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
?
