//2)This is connected to the Case List form. It pulls the names from the Persons table that are all listed as an Individual person type. It returns the both the //first and last name. It then loads it into a list box on the form public List<string> RetrieveNameList() { var names = new List<string>(); IndivAtRisk indiv = new IndivAtRisk(); sqlConnection1 = InitializeConnectionString(); using (sqlConnection1) { string data = "Select Last_Name, First_Name from Persons where Persons.Person_Type = 'INDIVIDUAL'"; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(data, sqlConnection1); sqlConnection1.Open(); using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { indiv.lastName = read["Last_Name"].ToString(); indiv.firstName = read["First_Name"].ToString(); string fullName = indiv.firstName.ToString() + " " + indiv.lastName.ToString(); names.Add(fullName); } } } sqlConnection1.Close(); return names; }
//constructor with parameters public Case( //string caseID, IndivAtRisk indiv, CareGiver careGiver, PersonOfInterest personOfInterest, SocialWorker socialWorker, CaseHistory caseHistory) { // caseID = _caseID; indiv = _indiv; careGiver = _careGiver; personOfInterest = _personOfInterest; socialWorker = _socialWorker; caseHistory = _caseHistory; }
//3)Once a case is chosen, this will load the data from the Persons table and data from the IndividualAtRisk table and loads it into the Individual tab on the //Case View form. public IndivAtRisk RetrieveIndivData(string lastName2) { var indiv = new IndivAtRisk(); sqlConnection1 = InitializeConnectionString(); using (sqlConnection1) { string data = "Select * from Persons, IndividualAtRisk where Last_Name ='" + lastName2 + "' AND IndividualAtRisk.PersonId = Persons.PersonId"; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(data, sqlConnection1); sqlConnection1.Open(); using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { indiv.personId = read["PersonId"].ToString(); indiv.indivID = read["IndivId"].ToString(); indiv.lastName = read["Last_Name"].ToString(); indiv.firstName = read["First_Name"].ToString(); indiv.gender = read["Gender"].ToString(); indiv.race = read["Race"].ToString(); indiv.dob = read["DOB"].ToString(); indiv.ssn = read["SSN"].ToString(); indiv.milDependent = read["Military_Dependent"].ToString(); indiv.churchConnection = read["Church_Connections"].ToString(); indiv.streetAddress = read["Street_Address"].ToString(); indiv.apartment = read["Apartment_Number"].ToString(); indiv.city = read["City"].ToString(); indiv.state = read["State"].ToString(); indiv.zip = read["Zip_Code"].ToString(); indiv.email = read["Email"].ToString(); indiv.homePhone = read["Telephone_Home"].ToString(); indiv.mobilePhone = read["Telephone_Mobile"].ToString(); indiv.workPhone = read["Telephone_Work"].ToString(); indiv.type = read["Person_Type"].ToString(); indiv.SchoolName = read["School_Name"].ToString(); indiv.TeacherName = read["Teachers_Name"].ToString(); indiv.SchoolTelephone = read["SchoolResource_Number"].ToString(); indiv.abuse = read["Abuse"].ToString(); indiv.Neglect = read["Neglect"].ToString(); //indiv.personID = read["PersonId"].ToString(); } } } sqlConnection1.Close(); return indiv; }
//10)This will pull the person id for the individual so it can create the individual id in the Individual at Risk table. public IndivAtRisk PersonId() { var indiv = new IndivAtRisk(); sqlConnection1 = InitializeConnectionString(); using (sqlConnection1) { string data = "Select PersonId, Last_Value(PersonId) Over (Partition By PersonId Order By PersonId) from Persons"; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(data, sqlConnection1); sqlConnection1.Open(); using (SqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { indiv.personId = read["PersonId"].ToString(); } } } sqlConnection1.Close(); return indiv; }