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