public static List <SomerenModel.Activities> DB_getActivities()
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// make a list to store data from DB into it
List <SomerenModel.Activities> activityList = new List <SomerenModel.Activities>();
// sql query
string sqlQuery = "SELECT activity_id, activity_desc, numberofStudents, numberofSupervisors " +
"FROM Activities";
// execute the sql query
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader reader = command.ExecuteReader();
// read data from DB
while (reader.Read())
{
SomerenModel.Activities activities = new SomerenModel.Activities();
activities.setId((int)reader["activity_id"]);
activities.setActivity_desc((string)reader["activity_desc"]);
activities.setNumOfStudents((int)reader["numberofStudents"]);
activities.setNumOfSupervisors((int)reader["numberofSupervisors"]);
activityList.Add(activities);
}
// close all connections
reader.Close();
connection.Close();
return(activityList);
}
private void activityAddBtn_Click(object sender, EventArgs e)
{
SomerenModel.Activities one = new SomerenModel.Activities();
if (activityDescBox.Text == "" || activityStudBox.Text == "" || activitySuperBox.Text == "")
{
// show a message if a user did not enter a value
MessageBox.Show("Please enter a value for (Description, Number of Students, Number of Supervisors) " +
"fields before clicking on Add", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
// reading data from the textboxes
string desc = activityDescBox.Text;
int numberOfStudent = int.Parse(activityStudBox.Text);
int numberOfSupervisor = int.Parse(activitySuperBox.Text);
// passing data to the UI layer
SomerenUI.addActivity(desc, numberOfStudent, numberOfSupervisor);
}
// clearing data from textboxes
activityIdBox.Clear();
activityDescBox.Clear();
activityStudBox.Clear();
activitySuperBox.Clear();
}
public static void deleteActivity(int id)
{
// make a new variable of type SomerenModel.Activities in order to store data to it
SomerenModel.Activities newActivity = new SomerenModel.Activities();
// store data to the newActivity variable
newActivity.setId(id);
// passing data to the DB layer
SomerenDB.DB_deleteActivity(newActivity);
}
public static void uppdateActivity(int id, string desc, int numOfStudents, int numberOfSupervisor)
{
// make a new variable of type SomerenModel.Activities in order to store data to it
SomerenModel.Activities newActivity = new SomerenModel.Activities();
// store data to the newActivity variable
newActivity.setId(id);
newActivity.setActivity_desc(desc);
newActivity.setNumOfStudents(numOfStudents);
newActivity.setNumOfSupervisors(numberOfSupervisor);
// passing data to the DB layer
SomerenDB.DB_uppdateActivity(newActivity);
}
public static void DB_deleteActivity(SomerenModel.Activities newActivity)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for deleting data from DB
string sqlQuery = "DELETE FROM Activities WHERE activity_id = @activity_id";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@activity_id", newActivity.getId());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}
public static void DB_addActivity(SomerenModel.Activities newActivity)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for inserting new data to the DB
string sqlQuery = "INSERT INTO Activities(activity_desc, numberofStudents, numberofSupervisors) VALUES(@activity_desc, @numberofStudents, @numberofSupervisors)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@activity_desc", newActivity.getActivity_desc());
command.Parameters.AddWithValue("@numberofStudents", newActivity.getNumOfStudents());
command.Parameters.AddWithValue("@numberofSupervisors", newActivity.getNumOfSupervisors());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}
public static void addActivity(string desc, int numOfStudents, int numberOfSupervisor)
{
// make a list for retrieving data from it
List <SomerenModel.Activities> activities = new List <SomerenModel.Activities>();
// linking the list to the DB connection in order to get data from it
activities = SomerenDB.DB_getActivities();
// make a new variable of type SomerenModel.Activities in order to store data to it
SomerenModel.Activities newActivity = new SomerenModel.Activities();
// make a list to store avtivity description in it
List <string> activityDesc = new List <string>();
// add avtivity description to the activityDesc list
foreach (SomerenModel.Activities activity in activities)
{
activityDesc.Add(activity.getActivity_desc().ToLower());
}
// make a validation to know if the data entered is diffrenet from DB data
if (activityDesc.Contains(desc.ToLower()))
{
// show a message if the entered data exists in the DB
MessageBox.Show("You can not add the same activity twice, please enter another activity name!",
"Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//store data to the newActivity variable
newActivity.setActivity_desc(desc);
newActivity.setNumOfStudents(numOfStudents);
newActivity.setNumOfSupervisors(numberOfSupervisor);
// passing data to the DB layer
SomerenDB.DB_addActivity(newActivity);
// show a messagebox after a successfull addition
MessageBox.Show("You have successfully added a new activity!!\n" +
"Enter Refresh the list to see the changes", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void DB_uppdateActivity(SomerenModel.Activities newActivity)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for updating data of the DB
string sqlQuery = "UPDATE Activities SET activity_desc = @activity_desc, " +
"numberofStudents = @numberofStudents, numberofSupervisors= @numberofSupervisors " +
"WHERE activity_id = @activity_id ";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@activity_id", newActivity.getId());
command.Parameters.AddWithValue("@activity_desc", newActivity.getActivity_desc());
command.Parameters.AddWithValue("@numberofStudents", newActivity.getNumOfStudents());
command.Parameters.AddWithValue("@numberofSupervisors", newActivity.getNumOfSupervisors());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}