public string UpdEnrollmentTypes(List <EnrollmentType> data) { UpdatedEnrollmentTypes ui = new UpdatedEnrollmentTypes(); foreach (EnrollmentType i in data) { ui.Add(i); } string errMsg = ""; if (data.Count > 0) { using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspUpdEnrollmentTypes"; cmd.Parameters.Add("@EnrollmentTypes", SqlDbType.Structured).Value = ui; cmd.Parameters.Add("@ErrMsg", SqlDbType.VarChar, 512).Direction = ParameterDirection.InputOutput; conn.Open(); cmd.ExecuteNonQuery(); if (!Convert.IsDBNull(cmd.Parameters["@ErrMsg"].Value)) { return(cmd.Parameters["@ErrMsg"].Value.ToString()); } conn.Close(); } } } return(errMsg); }
public string ValidLogin(string username, byte[] hashedPW) { string errMsg = ""; using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspLogIn"; cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = username; cmd.Parameters.Add("@Password", SqlDbType.VarBinary, 64).Value = hashedPW; cmd.Parameters.Add("@IsValid", SqlDbType.Bit).Direction = ParameterDirection.Output; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); rdr.Read(); rdr.Close(); bool IsValid = Convert.ToBoolean(cmd.Parameters["@IsValid"].Value); if (!IsValid) { errMsg = "Wrong username or password!"; } } }; return(errMsg); }
public People getPersonChurch(short keyfield) { People p = new People(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetPersonChurch"; cmd.Parameters.Add("@keyfield", SqlDbType.SmallInt).Value = keyfield; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //tblAddresses.Adr1, tblAddresses.Adr2, tblAddresses.City, tblAddresses.St, tblAddresses.ZIP p.keyfield = DbUtilities.GetShort(rdr, "KeyField"); p.Church = rdr["Church"].ToString(); p.Denomination = rdr["Denomination"].ToString(); } } return(p); } }
public List <EnrollmentType> GetEnrollmentTypes() { List <EnrollmentType> l = new List <EnrollmentType>(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetEnrollmentTypes"; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { EnrollmentType i = new EnrollmentType(); i.KeyField = Convert.ToInt16(rdr["KeyField"].ToString()); i.Description = rdr["Description"].ToString(); if (rdr["DisplayOrder"].ToString().Length > 0) { i.DisplayOrder = Convert.ToInt16(rdr["DisplayOrder"].ToString()); } l.Add(i); } } return(l); } }
public List <LookUp> getPeople(short?personTypeFK) { List <LookUp> l = new List <LookUp>(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetPeople"; if (personTypeFK != null) { cmd.Parameters.Add("@personTypeFK", SqlDbType.SmallInt).Value = personTypeFK; } conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { LookUp p = new LookUp(); p.keyfield = Convert.ToInt16(rdr["KeyField"].ToString()); p.name = rdr["name"].ToString(); l.Add(p); } } return(l); } }
public List <Room> GetRooms() { List <Room> l = new List <Room>(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetRooms"; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Room i = new Room(); i.KeyField = Convert.ToInt16(rdr["KeyField"].ToString()); i.description = rdr["Description"].ToString(); if (rdr["MaxStudents"].ToString().Length > 0) { i.MaxStudents = Convert.ToInt32(rdr["MaxStudents"].ToString()); } l.Add(i); } } return(l); } }
public People getPersonGeneral(short keyfield) { People p = new People(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetPersonGeneral"; cmd.Parameters.Add("@keyfield", SqlDbType.SmallInt).Value = keyfield; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //tblAddresses.Adr1, tblAddresses.Adr2, tblAddresses.City, tblAddresses.St, tblAddresses.ZIP p.keyfield = DbUtilities.GetShort(rdr, "KeyField"); p.FirstName = rdr["FirstName"].ToString(); p.LastName = rdr["LastName"].ToString(); p.age = DbUtilities.GetByte(rdr, "age"); p.DOB = DbUtilities.GetNullableDateTime(rdr, "DOB"); p.Gender = rdr["Gender"].ToString(); p.SSN = rdr["SSN"].ToString(); p.PrimaryPhone = rdr["PrimaryPhone"].ToString(); p.SecondaryPhone = rdr["SecondaryPhone"].ToString(); } rdr.NextResult(); if (rdr.HasRows) { while (rdr.Read()) { Addresses a = new Addresses(); a.Adr1 = rdr["adr1"].ToString(); a.Adr2 = rdr["adr2"].ToString(); a.City = rdr["City"].ToString(); a.St = rdr["St"].ToString(); a.ZIP = rdr["ZIP"].ToString(); p.Addresses.Add(a); // professional way //p.Addresses.Add(new Addresses() //{ // Adr1 = rdr["adr1"].ToString(), // Adr2 = rdr["adr2"].ToString(), // City = rdr["city"].ToString(), // St = rdr["St"].ToString(), // ZIP = rdr["ZIP"].ToString() //}); } } } return(p); } }
public static List <T> GetList <T>(string storedProcNm, Action <SqlParameterCollection> setSqlParams, Func <SqlDataReader, List <T> > mapObj) where T : class { using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcNm; setSqlParams?.Invoke(cmd.Parameters); // Means if setSqlParams != null stops at . if null; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); return(rdr.HasRows ? mapObj(rdr) : null); } } }
public People getPersonHealth(short keyfield) { People p = new People(); using (SqlConnection conn = new SqlConnection(DBConnect.GetSchoolConnStr())) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "uspGetPersonHealth"; cmd.Parameters.Add("@keyfield", SqlDbType.SmallInt).Value = keyfield; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Allergies a = new Allergies(); a.Keyfield = DbUtilities.GetShort(rdr, "KeyField"); a.allergyDescription = rdr["Description"].ToString(); p.Allergies.Add(a); } rdr.NextResult(); if (rdr.HasRows) { while (rdr.Read()) { Insurances i = new Insurances(); i.keyfield = DbUtilities.GetShort(rdr, "keyfield"); i.InsuranceCo = rdr["InsuranceCo"].ToString(); i.PolicyNum = rdr["PolicyNum"].ToString(); i.SubscriberName = rdr["SubscriberName"].ToString(); i.SubscriberNum = rdr["SubscriberNum"].ToString(); i.SubscriberEmployer = rdr["SubscriberEmployer"].ToString(); i.SubscriberJob = rdr["SubscriberJob"].ToString(); i.SubscriberWorkPhone = rdr["SubscriberWorkPhone"].ToString(); p.Insurances.Add(i); } } rdr.NextResult(); if (rdr.HasRows) { while (rdr.Read()) { MedHist mh = new MedHist(); mh.keyfield = DbUtilities.GetShort(rdr, "KeyField"); mh.HealthCondition = DbUtilities.GetByte(rdr, "HealthCondition"); mh.ConditionExplanation = rdr["ConditionExplanation"].ToString(); mh.HealthTreatment = rdr["HealthTreatment"].ToString(); mh.HasAsthma = DbUtilities.GetBool(rdr, "HasAsthma"); mh.HasSinusitis = DbUtilities.GetBool(rdr, "HasSinusitis"); mh.HasBronchitis = DbUtilities.GetBool(rdr, "HasBronchitis"); mh.HasKidneyTrouble = DbUtilities.GetBool(rdr, "HasKidneyTrouble"); mh.HasHeartTrouble = DbUtilities.GetBool(rdr, "HasHeartTrouble"); mh.HasDiabetes = DbUtilities.GetBool(rdr, "HasDiabetes"); mh.HasDizziness = DbUtilities.GetBool(rdr, "HasDizziness"); mh.HasStomachUpset = DbUtilities.GetBool(rdr, "HasStomachUpset"); mh.HasHayFever = DbUtilities.GetBool(rdr, "HasHayFever"); mh.Explanation = rdr["Explanation"].ToString(); mh.PastOperationsOrIllnesses = rdr["PastOperationsOrIllnesses"].ToString(); mh.CurrentMeds = rdr["CurrentMeds"].ToString(); mh.SpecialDietOrNeeds = rdr["SpecialDietOrNeeds"].ToString(); mh.HadChickenPox = DbUtilities.GetBool(rdr, "HadChickenPox"); mh.HadMeasles = DbUtilities.GetBool(rdr, "HadMeasles"); mh.HadMumps = DbUtilities.GetBool(rdr, "HadMumps"); mh.HadWhoopingCough = DbUtilities.GetBool(rdr, "HadWhoopingCough"); mh.OtherChildhoodDiseases = rdr["OtherChildhoodDiseases"].ToString(); mh.DateOfTetanusShot = DbUtilities.GetDateTime(rdr, "DateOfTetanusShot"); mh.FamilyDoctor = rdr["FamilyDoctor"].ToString(); mh.FamilyDoctorPhone = rdr["FamilyDoctorPhone"].ToString(); p.MedHist.Add(mh); } } } return(p); } }