//*************************************************** //학생 생성 result 1 이상 성공 0 실패 //*************************************************** public int CreateStudent(StudentAdminViewModel Data, string Admin) { string sql = "insert into SASU_STD values" + "(@num,@resno,@name,@sex,@dept,@phone,@email,@password,now(),'A',@admin); "; int result = 0; try { connectionOpen(); cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@num", Data.StudentNumber); cmd.Parameters.AddWithValue("@resno", Data.StudentResNumber); cmd.Parameters.AddWithValue("@name", Data.StudentName); cmd.Parameters.AddWithValue("@sex", Data.StudentSex); cmd.Parameters.AddWithValue("@dept", Data.StudentDept); cmd.Parameters.AddWithValue("@phone", Data.StudentPhone); cmd.Parameters.AddWithValue("@email", Data.StudentEmail); cmd.Parameters.AddWithValue("@password", SHA512(Data.StudentResNumber1)); cmd.Parameters.AddWithValue("@admin", Admin); cmd.ExecuteNonQuery(); result = 1; } catch (Exception e) { Console.WriteLine(e.ToString()); result = 0; } finally { cmd = null; conn.Close(); } return(result); }
//public SnackbarMessageQueue(TimeSpan messageDuration) //********************************************* //엑셀파일 읽기 //********************************************* private void ExcelRead(string FilePath) { Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook workbook = app.Workbooks.Open(FilePath, 0, true, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1); string str; int rCnt = 0; // 열 갯수 int cCnt = 0; // 행 갯수 Excel.Range range = worksheet.UsedRange; System.Array myvalues = (System.Array)range.Cells.Value2; //string[] strArray = ConvertToStringArray(myvalues); myViewModel.Clear(); for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++) { StudentAdminViewModel Data = new StudentAdminViewModel { StudentCode = "A", StudentNumber = (range.Cells[rCnt, 1] as Excel.Range).Value2.ToString(), StudentResNumber1 = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString(), StudentResNumber2 = (range.Cells[rCnt, 3] as Excel.Range).Value2.ToString(), StudentName = (range.Cells[rCnt, 4] as Excel.Range).Value2.ToString(), StudentSex = (range.Cells[rCnt, 5] as Excel.Range).Value2.ToString(), StudentDept = (range.Cells[rCnt, 6] as Excel.Range).Value2.ToString(), StudentPhone = (range.Cells[rCnt, 7] as Excel.Range).Value2.ToString(), StudentEmail = (range.Cells[rCnt, 8] as Excel.Range).Value2.ToString(), StudentPassword = (range.Cells[rCnt, 2] as Excel.Range).Value2.ToString() }; myViewModel.Insert(myViewModel.Count, Data); } notifier.ShowSuccess("엑셀파일을 성공적으로 불러왔습니다"); }
//*************************************************** //학생 조회 (검색) //*************************************************** public void SearchStudent(BindingList <StudentAdminViewModel> myViewModel, string StuNo) { if (StuNo.Length < 3) { return; } String sql = "Select * from sasu_std where stu_stuNo like '%" + StuNo + "%'"; try { connectionOpen(); cmd = new MySqlCommand(sql, conn); MySqlDataReader reader = cmd.ExecuteReader(); myViewModel.Clear(); while (reader.Read()) { //stu_stuno, stu_resno, stu_name, stu_sex, stu_dept, stu_phone, stu_email, stu_password, datasys1, datasys2, datasys3 string ResNo = reader["stu_resno"].ToString(); StudentAdminViewModel Data = new StudentAdminViewModel { StudentCode = "S", StudentNumber = reader["stu_stuno"].ToString(), StudentResNumber1 = ResNo.Substring(0, 6), StudentResNumber2 = ResNo.Substring(6), StudentEmail = reader["stu_email"].ToString(), StudentName = reader["stu_name"].ToString(), StudentPassword = reader["stu_password"].ToString(), StudentPhone = reader["stu_phone"].ToString(), StudentSex = reader["stu_sex"].ToString(), StudentDept = reader["stu_dept"].ToString(), }; myViewModel.Insert(myViewModel.Count, Data); } } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { cmd = null; conn.Close(); } }
//*************************************************** //학생 업데이트 result 1 이상 성공 0 실패 //*************************************************** public int UpdateStudent(StudentAdminViewModel Data, string admin) { int result = 0; string sql = "update SASU_STD set " + "stu_resno = @num, " + "stu_name = @name, " + "stu_resno = @resno " + "stu_sex = @sex, " + "stu_dept = @dept, " + "stu_phone = @phone " + "stu_email =@email ," + "stu_password = @password " + "datasys1 = now(), " + "datasys2= 'U', " + "datasys3 = @admin " + "where stu_stuno = @num"; try { connectionOpen(); cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@num", Data.StudentNumber); cmd.Parameters.AddWithValue("@resno", Data.StudentResNumber); cmd.Parameters.AddWithValue("@name", Data.StudentName); cmd.Parameters.AddWithValue("@sex", Data.StudentSex); cmd.Parameters.AddWithValue("@dept", Data.StudentDept); cmd.Parameters.AddWithValue("@phone", Data.StudentPhone); cmd.Parameters.AddWithValue("@email", Data.StudentEmail); cmd.Parameters.AddWithValue("@password", SHA512(Data.StudentPassword)); cmd.Parameters.AddWithValue("@admin", admin); cmd.ExecuteNonQuery(); result = 1; }catch (Exception e) { Console.WriteLine(e.ToString()); result = 0; } finally { cmd = null; conn.Close(); } return(result); }
//*************************************************** //학생 삭제 result 1 이상 성공 0 실패 //*************************************************** public int DeleteStudent(StudentAdminViewModel Data) { int result = 0; string sql = "delete from SASU_STD WHERE stu_stuno =@num;"; try { connectionOpen(); cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@num", Data.StudentNumber); cmd.ExecuteNonQuery(); result = 1; }catch (Exception e) { Console.WriteLine(e.ToString()); result = 0; } finally { cmd = null; conn.Close(); } return(result); }
//********************************************* //확인 버튼 클릭 //********************************************* private void CheckButton_Click(object sender, RoutedEventArgs e) { DefaultState(); //입력 StudentAdminViewModel data = (StudentAdminViewModel)StudentInfo.DataContext; if (Btn_state == 0) { StudentAdminViewModel temp = new StudentAdminViewModel { StudentCode = "A", StudentNumber = data.StudentNumber, StudentName = data.StudentName, StudentResNumber1 = data.StudentResNumber1, StudentResNumber2 = data.StudentResNumber2, StudentSex = data.StudentSex, StudentDept = data.StudentDept, StudentPassword = data.StudentPassword, StudentEmail = data.StudentEmail, StudentPhone = data.StudentPhone, }; myViewModel.Insert(myViewModel.Count, temp); } //업데이트 if (Btn_state == 1) { if (myViewModel[DG1.SelectedIndex].StudentCode == "A") { } else { int cnt = 0; if (TempData[0] != data.StudentNumber) { cnt++; } if (TempData[1] == data.StudentName) { cnt++; } if (TempData[2] == data.StudentPhone) { cnt++; } if (TempData[3] == data.StudentPassword) { cnt++; } if (TempData[4] == data.StudentDept) { cnt++; } if (TempData[5] == data.StudentSex) { cnt++; } if (TempData[6] == data.StudentEmail) { cnt++; } if (TempData[7] == data.StudentResNumber1) { cnt++; } if (TempData[8] == data.StudentResNumber2) { cnt++; } if (cnt > 0) { data.StudentCode = "U"; } } } if (Btn_state == 2) { IList items = DG1.SelectedItems; if (items.Count > 1) { List <int> indexArray = new List <int>(); foreach (StudentAdminViewModel item in items) { for (int i = 0; i < myViewModel.Count; i++) { if (myViewModel[i].StudentNumber == item.StudentNumber) { if (item.StudentCode == "A") { indexArray.Add(i); } else if (item.StudentCode == "D") { myViewModel[i].StudentCode = myViewModel[i].StudentDivision; } else { myViewModel[i].StudentDivision = myViewModel[i].StudentCode; myViewModel[i].StudentCode = "D"; } } } } for (int i = indexArray.Count - 1; i >= 0; i--) { myViewModel.RemoveAt(indexArray[i]); } } else if (items.Count == 1) { StudentAdminViewModel DeleteData = myViewModel[DG1.SelectedIndex]; if (DeleteData.StudentCode == "A") { myViewModel.Remove(DeleteData); } else if (DeleteData.StudentCode == "D") { DeleteData.StudentCode = DeleteData.StudentDivision; } else { DeleteData.StudentDivision = DeleteData.StudentCode; DeleteData.StudentCode = "D"; } } else { MessageBox.Show("선택이 안되었네요"); } } resetText(); }