private void createEventButton_Click(object sender, EventArgs e)
{
int startHour = startTimePicker.Value.Hour, endHour = endTimePicker.Value.Hour;
string day = dayPickerListBox.Text, eventName = eventNameTextBox.Text, eventDescription = eventDescriptionRichTextBox.Text;
if (eventName == "" || eventDescription == "")
{
return;
}
DBconnect db = new DBconnect();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = db.getConnection();
cmd.CommandText = "INSERT into EVENT (EVENT_NAME,CREATOR_USERNAME,EVENT_START,EVENT_END,EVENT_DAY,EVENT_DESC) VALUES(@eventName,'johnny',@startHour,@endHour,@day,@eventDescription)";
cmd.Parameters.AddWithValue("eventName", eventName);
cmd.Parameters.AddWithValue("startHour", startHour);
cmd.Parameters.AddWithValue("endHour", endHour);
cmd.Parameters.AddWithValue("day", day);
cmd.Parameters.AddWithValue("eventDescription", eventDescription);
db.OpenConn();
if (db.ConnStatus())
{
cmd.ExecuteNonQuery();
}
this.Close();
}
private void associationEventsGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
DataSet ds;
DBconnect db = new DBconnect();
SqlCommand command = new SqlCommand();
db.OpenConn();
var dataIndexNo = associationEventsGrid.Rows[e.RowIndex].Index.ToString();
string cellValue1 = associationEventsGrid.Rows[e.RowIndex].Cells[0].Value.ToString();
string cellValue2 = associationEventsGrid.Rows[e.RowIndex].Cells[1].Value.ToString();
command.CommandType = CommandType.Text;
command.Connection = db.getConnection();
command.CommandText = "SELECT * FROM Event WHERE Event.EVENT_ID = @cellValue";
command.Parameters.AddWithValue("cellValue", cellValue1);
ds = db.generalCommand(command);
MessageBox.Show(
"\n ID: " +
ds.Tables[0].Rows[0].ItemArray[0].ToString() + "\n Name: " +
ds.Tables[0].Rows[0].ItemArray[1].ToString() + "\n Creator: " +
ds.Tables[0].Rows[0].ItemArray[2].ToString() + "\n Start: " +
ds.Tables[0].Rows[0].ItemArray[3].ToString() + "\n End: " +
ds.Tables[0].Rows[0].ItemArray[4].ToString() + "\n Day: " +
ds.Tables[0].Rows[0].ItemArray[5].ToString() + "\n Description: " +
ds.Tables[0].Rows[0].ItemArray[6].ToString());
}
private void watchAssociates_Click(object sender, EventArgs e)
{
////
//// Print all associates in system
////
DBconnect db = new DBconnect();
db.OpenConn();
//create data adapter
var dataAdapter = new SqlDataAdapter("SELECT * from Users WHERE ACCESSGROUP='Associate'", db.ConnectionStringGet());
var commandBuilder = new SqlCommandBuilder(dataAdapter);
ds = new DataSet();
//try to fill sataset with query result
try
{
dataAdapter.Fill(ds);
dataGrid.ReadOnly = true;
dataGrid.DataSource = Ds.Tables[0];
}
catch (Exception)
{
MessageBox.Show("Query not returned any data");
}
db.CloseConn(db.ConnStatus()); // close connection
}
private void sendRequest_button_Click(object sender, EventArgs e)
{
String messageBody = messageBox.Text;
if (messageBody.Length == 0)
{
MessageBox.Show("Can's send empty message");
return;
}
String secretaryName = secretaryChooseList.Text;
if (secretaryName.Length == 0)
{
MessageBox.Show("Not secretary choosen, can't assign to nobody");
return;
}
DBconnect db = new DBconnect();
try
{
db.executionQuery("insert into StudentRequests values('" + username + "', '" + messageBody + "','New request','" + secretaryName + "')");
MessageBox.Show("Request message : \n\n" + messageBody + "\n\nBeen save in system and assigned to Secretary :" + secretaryName);
}
catch { }
}
public IEnumerable <Customer_Data> Get()
{
context = HttpContext.RequestServices.GetService(typeof(DBconnect)) as DBconnect;
List <Customer_Data> list = context.GetData();
return(list);
}
private void watchStudents_Click(object sender, EventArgs e)
{
////
//// Print all students in system
////
DBconnect db = new DBconnect();
db.OpenConn();
//create data adapter
var dataAdapter = new SqlDataAdapter("select USers.ID,Users.username,Users.Pass,Users.UName,Users.SName,Student.Semester from Users join Student on Users.username = Student.username", db.ConnectionStringGet());
var commandBuilder = new SqlCommandBuilder(dataAdapter);
ds = new DataSet();
//try to fill sataset with query result
try
{
dataAdapter.Fill(ds);
dataGrid.ReadOnly = true;
dataGrid.DataSource = Ds.Tables[0];
}
catch (Exception)
{
MessageBox.Show("Query not returned any data");
}
db.CloseConn(db.ConnStatus()); // close connection
}
public DataTable QUERY(DB_class db)//对数据库查询
{
DataTable dt = null;
if (db.db != null && db.col_array != null && db.col_data != null && db.col != null)
{
dt = DBconnect.selectDB(db.db, db.col_array, db.col, db.col_data);
}
else if (db.db != null && db.col_data != null && db.col != null)
{
dt = DBconnect.selectDB(db.db, db.col, db.col_data);
}
else if (db.db != null && db.col_array != null)
{
dt = DBconnect.selectDB(db.db, db.col_array);
}
else if (db.db != null && db.condition != null)
{
dt = DBconnect.selectDB(db.db, db.condition);
}
else if (db.db != null)
{
dt = DBconnect.selectDB(db.db);
}
return(dt);
}
public bool ifUserIDinDatabase(int id)
{
bool status = false;
DBconnect db;
db = new DBconnect();
db.OpenConn();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = db.getConnection();
cmd.CommandText = "SELECT username FROM USERS WHERE ID = @ID";
cmd.Parameters.AddWithValue("ID", id);
DataSet ds = db.generalCommand(cmd);
db.CloseConn(db.ConnStatus());
try
{
string user = ds.Tables[0].Rows[0].ItemArray[0].ToString();
status = true;
}
catch (System.IndexOutOfRangeException)
{
status = false;
}
return(status);
}
private void ListCoursesClick(object sender, EventArgs e)
{
DBconnect db = new DBconnect();
db.OpenConn();
//create data adapter
var dataAdapter = new SqlDataAdapter("select * from Course", db.ConnectionStringGet());
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
//try to fill sataset with query result
try
{
dataAdapter.Fill(ds);
dataGrid.ReadOnly = true;
dataGrid.DataSource = ds.Tables[0];
allCoursesSet = ds;
}
catch (Exception)
{
MessageBox.Show("Query not returned any data");
}
db.CloseConn(db.ConnStatus()); // close connection
}
private void recieveAmountClick(object sender, EventArgs e)
{
DBconnect db = new DBconnect();
db.OpenConn();
//create data adapter, count amount of courses in system
var dataAdapter = new SqlDataAdapter("SELECT COUNT(COURSE_ID)FROM Course", db.ConnectionStringGet());
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
//try to fill dataset with query result
try
{
dataAdapter.Fill(ds);
coursesAmount = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[0].ToString());
//print result in textBox
textBox.Text += "There are total of " + coursesAmount.ToString() + " courses in system";
}
catch (Exception)
{
MessageBox.Show("There is problem with your querry, check it!");
}
db.CloseConn(db.ConnStatus()); // close connection
}
public Output Update(Output op)
{
Account act = Login.Get_user();
if (act != null && !string.IsNullOrEmpty(act.ID))
{
int ID = op.status;
string name = op.reg[0];
string strsql = "update Program set programName='" + name + "' where ID=" + ID;
int status = DBconnect.noQueryDB(strsql);
if (status == 1)
{
strsql = "select programName from Program where ID=" + ID;
name = SqlHelper.ExecuteScalar(DBconnect.get(), CommandType.Text, strsql).ToString();
Output output = new Output(1, name);
return(output);
}
else
{
Output output = new Output(0, "修改失败");
return(output);
}
}
else
{
Output output = new Output(-10, "请先登录");
return(output);
}
}
// GET api/Class/{ClassCode}
public Class Get(string id)
{
SqlConnection conn = DBconnect.Connect();
string query = "select * from Class where ClassCode = '" + id + "'";
Class output = new Class();
SqlCommand cmd;
SqlDataReader rdr;
try
{
conn.Open();
cmd = new SqlCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
output = (new Class(Convert.ToString(rdr.GetValue(0)), Convert.ToString(rdr.GetValue(1)), Convert.ToString(rdr.GetValue(2)), Convert.ToInt32(rdr.GetValue(3))));
}
}
catch (Exception e)
{
throw;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(output);
}
private void retreiveData()
{
DBconnect db = new DBconnect();
db.OpenConn();
//create data adapter
var dataAdapter = new SqlDataAdapter("select EVENT_ID, EVENT_NAME from Event", db.ConnectionStringGet());
var commandBuilder = new SqlCommandBuilder(dataAdapter);
var ds = new DataSet();
//try to fill dataset with query result
try
{
dataAdapter.Fill(ds);
associationEventsGrid.ReadOnly = true;
associationEventsGrid.DataSource = ds.Tables[0];
allEvents = ds;
}
catch (Exception)
{
MessageBox.Show("Query not returned any data");
}
db.CloseConn(db.ConnStatus());
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
command.CommandText = "SELECT * FROM Event";
}
public Output[] GetCalingProgram()
{
Account act = Login.Get_user();
List <Output> outputs = new List <Output>();
if (act != null && !string.IsNullOrEmpty(act.ID))
{
DataTable dt = DBconnect.selectDB("TempProgram", " where userID=" + act.ID);
if (dt == null || dt.Rows.Count == 0)
{
return(outputs.ToArray());
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["status"].ToString() == "0")
{
Output output = new Output(0, "正在计算");
outputs.Add(output);
}
else if (Convert.IsDBNull(dt.Rows[i]["program"]) || dt.Rows[i]["program"].ToString() == "" || dt.Rows[i]["program"].ToString().Length <= 10)
{
string name = dt.Rows[i]["programName"].ToString();
string strsql = "delete TempProgram where ID=" + dt.Rows[i]["ID"].ToString();
DBconnect.noQueryDB(strsql);
Output output = new Output(-2, name + " 方案无法计算出可行解，请更换算法重试");
output.reg = new string[1] {
dt.Rows[i]["ID"].ToString()
};
outputs.Add(output);
}
else
{
string ID = dt.Rows[i]["ID"].ToString();
string strsql = "delete TempProgram where ID=" + ID;
DBconnect.noQueryDB(strsql);
strsql = "insert into Program(userID,program,OAtype,programName) values(" + dt.Rows[0]["userID"].ToString() + ",'" + dt.Rows[0]["program"].ToString() + "'," + dt.Rows[0]["OAtype"].ToString() + ",'" + dt.Rows[0]["programName"].ToString() + "')";
DBconnect.noQueryDB(strsql);
strsql = "select ident_current('Program')";
int p_ID = Decimal.ToInt32(((Decimal)SqlHelper.ExecuteScalar(DBconnect.get(), CommandType.Text, strsql)));
Output output = new Output(p_ID, dt.Rows[0]["program"].ToString());
output.reg = new string[3] {
dt.Rows[0]["OAtype"].ToString(), dt.Rows[0]["programName"].ToString(), ID
};
outputs.Add(output);
}
}
return(outputs.ToArray());
}
}
else
{
Output output = new Output(-10, "请先登录");
outputs.Add(output);
return(outputs.ToArray());
}
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
connection = new DBconnect();
command.CommandText = "SELECT * FROM StudentRequests WHERE StatusOFRequest='New request'";
}
public IActionResult Put(int id, [FromBody] Customer_Data customer)
{
context = HttpContext.RequestServices.GetService(typeof(DBconnect)) as DBconnect;
if (context.Update(id, customer) == null)
{
return(BadRequest());
}
return(Ok());
}
public IActionResult Delete(int id)
{
context = HttpContext.RequestServices.GetService(typeof(DBconnect)) as DBconnect;
if (context.Delete(id) == -1)
{
return(BadRequest());
}
return(Ok());
}
private void Form_studentAddRequest_Load(object sender, EventArgs e)
{
DBconnect db = new DBconnect();
DataSet ds = db.LoadTableWithRule("users", "WHERE accessgroup='Secretary'");
secretaryChooseList.DataSource = ds.Tables[0];
secretaryChooseList.ValueMember = "Uname";
secretaryChooseList.DisplayMember = "Uname";
}
public IActionResult Post([FromBody] Customer_Data customer)
{
context = HttpContext.RequestServices.GetService(typeof(DBconnect)) as DBconnect;
if (context.Insert(customer) == null)
{
return(BadRequest());
}
return(Ok());
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
connection = new DBconnect();
command.CommandText = "SELECT Course.* FROM Course, Student WHERE Student.semester = Course.course_semester and Student.username = @username";
command.Parameters.AddWithValue("username", username);
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
connection = new DBconnect();
command.CommandText = "SELECT Course.* FROM Course, Schedules WHERE Course.course_id = Schedules.course_id and Schedules.student_un = @username";
command.Parameters.AddWithValue("username", username);
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
connection = new DBconnect();
command.CommandText = "SELECT Course.Course_ID, Course.Course_name, Course.teacher_id, Course.room_number, Course.course_day, Course.start_hour, Course.end_hour, Course.course_semester from Schedules, Course WHERE Schedules.Student_UN = @username and Schedules.Course_id = Course.course_id";
command.Parameters.AddWithValue("username", student.Username);
}
public Plot(DBconnect Database)
{
InTemperture = new List <DataPoint>();
HumLvl = new List <DataPoint>();
LightLvl = new List <DataPoint>();
WaterLvl = new List <DataPoint>();
SoilHumLvl = new List <DataPoint>();
LightStatus = new List <DataPoint>();
ConvertPointsTemp(Database);
}
private void Init()
{
connection = new DBconnect();
command = new SqlCommand();
command.CommandType = CommandType.Text;
command.Connection = connection.getConnection();
connection = new DBconnect();
command.CommandText = "SELECT * FROM Course where course_id != @id and course_name = @name order by course_id";
command.Parameters.AddWithValue("name", course.Name);
command.Parameters.AddWithValue("id", course.ID);
}
public bool setDBConnection(DBconnect db)
{
if (db != null)
{
this.db = db; return(true);
}
else
{
return(false);
}
}
public void checkCoursesUpdated()
{
Object[] searchParam = new Object[1];
searchParam[0] = "test";
DBconnect dbCon = new DBconnect();
dbCon.executionQuery("INSERT INTO Course VALUES(7357,'test',73,73,73,3,7,73,7,'test',7,0,0)");
courseSearchObject.Invoke("searchByName", searchParam);
Assert.AreEqual(1, resultsBox.Items.Count);
dbCon.executionQuery("DELETE FROM Course WHERE COURSE_NAME='test'");
}
private void removeAssoc_Click(object sender, EventArgs e)
{
DBconnect db = new DBconnect();
string idToRemove = this.textBox1.Text;
string commandString = "DELETE FROM Users WHERE Users.ID = " + idToRemove;
if (checkFieldText())
{
db.OpenConn();
db.executionQuery(commandString);
MessageBox.Show("User Deleted.\n");
}
}
private void removeEvent_Click_1(object sender, EventArgs e)
{
DBconnect db = new DBconnect();
string idToRemove = this.eventIdTextBox.Text;
string commandString = "DELETE FROM Event WHERE Event.EVENT_ID = " + idToRemove;
if (checkFieldText())
{
db.OpenConn();
db.executionQuery(commandString);
MessageBox.Show("Event Deleted.\n");
}
}
public void SecretaryDeniesRequest()
{
Form_secretaryStudentRequests secretaryForm = new Form_secretaryStudentRequests();
PrivateObject secretaryPrivateObject = new PrivateObject(secretaryForm);
DBconnect db = new DBconnect();
db.OpenConn();
//create new request
db.executionQuery("insert into studentrequests values('alex', 'unittest','R_Denied','isabele')");
DataSet ds = new DataSet();
ds = db.LoadTableByFreeQuerry("select * from studentrequests where MESSAGEBODY='unittest'");
Assert.IsNotNull(ds);
string requestID = ds.Tables[0].Rows[0][0].ToString();
string requestFromStrudent = ds.Tables[0].Rows[0][1].ToString();
string expectedRequestFromStrudent = "alex";
Assert.AreEqual(requestFromStrudent, expectedRequestFromStrudent);
string messagebody = ds.Tables[0].Rows[0][2].ToString();
string expectedMessageBody = "unittest";
Assert.AreEqual(messagebody, expectedMessageBody);
string requestStatus = ds.Tables[0].Rows[0][3].ToString();
string expectedRequestStatus = "R_Denied";
Assert.AreEqual(requestStatus, expectedRequestStatus);
String[] args = new String[2];
string expectedNewStatus = "R_Denied";
args[0] = "R_Denied";
args[1] = requestID;
secretaryPrivateObject.Invoke("updateRequest", args);
ds = db.LoadTableByFreeQuerry("select * from studentRequests where MESSAGEBODY='unittest' and ID=" + requestID);
Assert.IsNotNull(ds);
string newStatus = ds.Tables[0].Rows[0][3].ToString();
try { Assert.AreEqual(expectedNewStatus, newStatus); }
finally
{
db.executionQuery("delete from StudentRequests where MESSAGEBODY='unittest'"); //remove created request
db.CloseConn(db.ConnStatus());
}
}
