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