//A method that takes a list of doctors from the database and returns it
public static List <ClassOffice> NotSpecifiedOffice()
{
string querry = "" +
"select office.Office_id, office.Office_number " +
"from tbl_Office as office " +
"FULL OUTER JOIN tbl_Doctor as doc " +
"ON office.Office_id = doc.Office_id " +
"where office.Office_id is null " +
"or doc.Office_id is null";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
List <ClassOffice> offlist = new List <ClassOffice>();
while (dr.Read())
{
ClassOffice off = new ClassOffice();
off.OfficeId = dr.GetInt32("Office_id");
off.OfficeNumber = dr.GetInt16("Office_number");
offlist.Add(off);
}
sqlCon.Close();
return(offlist);
}
public static void UpdateOffice(ClassOffice office)
{
string querry = "USE [db_Clinic] UPDATE tbl_Office SET [Office_number]=@OfficeNumber WHERE Office_id=@Office_id";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
sqlCommand.Parameters.AddWithValue("@Office_id", office.OfficeId);
sqlCommand.Parameters.AddWithValue("@OfficeNumber", office.OfficeNumber);
SqlDataReader dr = sqlCommand.ExecuteReader();
sqlCon.Close();
}
public static void AddNewOffice(ClassOffice office)
{
//Add office staff
string querryp1 = "USE[db_Clinic] INSERT INTO[dbo].[tbl_Office]([Office_number]) ";
string querryp2 = "VALUES(@Office_number)";
string querry = querryp1 + querryp2;
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
sqlCommand.Parameters.AddWithValue("@Office_number", office.OfficeNumber);
SqlDataReader dr = sqlCommand.ExecuteReader();
sqlCon.Close();
}
//Method that gets offices from database and returns list of offices
public static List <ClassOffice> OfficeList()
{
string querry = "SELECT [Office_id],[Office_number] FROM [db_Clinic].[dbo].[tbl_Office]";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
List <ClassOffice> officeList = new List <ClassOffice>();
while (dr.Read())
{
ClassOffice office = new ClassOffice();
office.OfficeId = dr.GetInt32("Office_id");
office.OfficeNumber = dr.GetInt16("Office_number");
officeList.Add(office);
}
sqlCon.Close();
return(officeList);
}
