/// <summary>
/// 查询教师和学生列表并将其分别展示在两个界面上
/// </summary>
public AdminMessage()
{
InitializeComponent();
WindowStartupLocation = WindowStartupLocation.CenterScreen;
//string n = MainWindow.UserCode;
//string UserNameTea = n.ToString();
//Stu,Tea分别为单个学生和教师的信息集合
MessageStu Stu = new MessageStu();
AdminMessageTea Tea = new AdminMessageTea();
#region 数据库连接
SqlConnection myConnection;
string connStr = "Server ='" + IPAddress.ip + "'; database =测试数据库; uid =sa; pwd=1738010002";
//string connStr = @"Server = LAPTOP-25MJ4H0M\SQLEXPRESS; database =教学系统; Trusted_Connection=SSPI";
myConnection = new SqlConnection(connStr);
try
{
myConnection.Open();
}
catch (Exception)
{
MessageBox.Show("连接失败");
}
#endregion
//查询所有的学生信息
SqlCommand cmd = new SqlCommand("select 学号,姓名,性别,班级,邮箱 from xsb", myConnection);
SqlDataReader sdr = cmd.ExecuteReader();
//依次读取并将其加入学生列表
while (sdr.Read())
{
Stu.UserCode = sdr[0].ToString();
Stu.UserName = sdr[1].ToString();
Stu.UserSex = sdr[2].ToString();
Stu.UserClass = sdr[3].ToString();
Stu.UserEmail = sdr[4].ToString();
InfoStuList.Add(Stu);
}
//绑定DG12 datagrid 的数据源为学生列表
DG12.AutoGenerateColumns = false;
DG12.ItemsSource = InfoStuList;
sdr.Close();
//查询所有的教师信息
SqlCommand cmd2 = new SqlCommand("select 职工号,姓名,性别,管理班级,邮箱 from jsb", myConnection);
SqlDataReader sdr2 = cmd2.ExecuteReader();
//依次读取并将其加入学生列表
while (sdr2.Read())
{
Tea.UserCode = sdr2[0].ToString();
Tea.UserName = sdr2[1].ToString();
Tea.UserSex = sdr2[2].ToString();
Tea.UserClass = sdr2[3].ToString();
Tea.UserEmail = sdr2[4].ToString();
InfoTeaList.Add(Tea);
}
//绑定DG13 datagrid 的数据源为教师列表
DG13.AutoGenerateColumns = false;
DG13.ItemsSource = InfoTeaList;
}
public MessageTea()
{
InitializeComponent();
WindowStartupLocation = WindowStartupLocation.CenterScreen;
//UserNameTea为教师的账户名
string n = MainWindow.UserCode;
string UserNameTea = n.ToString();
MessageStu Stu = new MessageStu();
//数据库连接
SqlConnection myConnection;
string connStr = "Server ='" + IPAddress.ip + "'; database =测试数据库; uid =sa; pwd=1738010002";
//string connStr = @"Server = LAPTOP-25MJ4H0M\SQLEXPRESS; database =教学系统; Trusted_Connection=SSPI";
myConnection = new SqlConnection(connStr);
try
{
myConnection.Open();
}
catch (Exception)
{
MessageBox.Show("连接失败");
}
//查询该教师管理的班级
SqlCommand cmd2 = new SqlCommand("select 管理班级 from jsb where 职工号 = '" + UserNameTea + "'", myConnection);
SqlDataReader sdr2 = cmd2.ExecuteReader();
sdr2.Read();
//TeaClass为该教师管理的班级
string TeaClass = sdr2[0].ToString();
sdr2.Close();
SqlCommand cmd = new SqlCommand("select 学号,姓名,性别,班级,邮箱 from xsb where 班级 = '" + TeaClass + "'", myConnection);
SqlDataReader sdr = cmd.ExecuteReader();
//循环读取查询到的信息
while (sdr.Read())
{
Stu.UserCode = sdr[0].ToString();
Stu.UserName = sdr[1].ToString();
Stu.UserSex = sdr[2].ToString();
Stu.UserClass = sdr[3].ToString();
Stu.UserEmail = sdr[4].ToString();
infoList.Add(Stu);
}
//绑定界面数据源
DG5.AutoGenerateColumns = false;
DG5.ItemsSource = infoList;
}
/// <summary>
/// 点击设置提醒
/// </summary>
private void Set_Click(object sender, RoutedEventArgs e)
{
//UserNameTea为教师账户名
string n = MainWindow.UserCode;
string UserNameTea = n.ToString();
string Send = "定时提醒";
infoList.Clear();
MessageStu Stu = new MessageStu();
//Reminders Stu = new Reminders();
//FunctionUserNameTea.Content = UserNameTea;
//数据库连接
SqlConnection myConnection;
//string connStr = @"Server = LAPTOP-25MJ4H0M\SQLEXPRESS; database =教学系统; Trusted_Connection=SSPI";
string connStr = "Server ='" + IPAddress.ip + "'; database =测试数据库; uid =sa; pwd=1738010002";
myConnection = new SqlConnection(connStr);
try
{
myConnection.Open();
}
catch
{
MessageBox.Show("连接失败");
}
//查询该教师管理的班级
SqlCommand cmd2 = new SqlCommand("select 管理班级 from jsb where 职工号 = '" + UserNameTea + "'", myConnection);
SqlDataReader sdr2 = cmd2.ExecuteReader();
sdr2.Read();
//Class为班级
string Class = sdr2[0].ToString();
sdr2.Close();
SqlCommand cmd3 = new SqlCommand("select 学号 from xsb where 班级 = '" + Class + "'", myConnection);
SqlDataReader sdr3 = cmd3.ExecuteReader();
//Stu.UserCode为学号
while (sdr3.Read())
{
Stu.UserCode = sdr3[0].ToString();
infoList.Add(Stu);
}
sdr3.Close();
for (int i = 0; i < infoList.Count; i++)
{
SqlConnection myConnection2;
//string connStr = @"Server = LAPTOP-25MJ4H0M\SQLEXPRESS; database =教学系统; Trusted_Connection=SSPI";
string connStr2 = "Server ='" + IPAddress.ip + "'; database =测试数据库; uid =sa; pwd=1738010002";
myConnection2 = new SqlConnection(connStr2);
try
{
myConnection2.Open();
}
catch
{
MessageBox.Show("连接失败");
}
string RemindContent2 = RemindContent.Text.ToString();
string RemindTime2 = Year.Text.ToString().Trim() + "." + Month.Text.ToString().Trim() + "." + Day.Text.ToString().Trim() + " " + Hour.Text.ToString().Trim() + ":" + Minute.Text.ToString().Trim() + ":00";
//将提醒上传到数据库
// RemindContent2为提醒的内容RemindTime2为提醒的时间
SqlCommand cmd4 = new SqlCommand("insert into xxb(发送方,接收方,消息内容,时间,状态) values ('" + Send.Trim() + "','" + infoList[i].UserCode.ToString().Trim() + "','" + RemindContent2.Trim() + "','" + RemindTime2 + "','" + 0 + "')", myConnection2);
cmd4.ExecuteNonQuery();
myConnection2.Close();
myConnection2.Dispose();
}
sdr3.Close();
myConnection.Close();
myConnection.Dispose();
MessageBox.Show("定时消息设置成功");
}
