private async void getscore() { if (scorelist.ItemsSource != readcore) { StatisticsDetailViewModel sd = (StatisticsDetailViewModel)this.DataContext; string sql = $"select * from Score where stulisturl='{sd.DetailCourse.StudentListUrl}'"; readcore.Clear(); await Task.Run(() => { OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath); if (reader != null) { while (reader.Read()) { Score a = new Score(); a.StuNum = reader["stunum"].ToString(); a.StuName = reader["stuname"].ToString(); a.Attendance = Convert.ToDecimal(reader["attendance"].ToString()); a.Homework = Convert.ToDecimal(reader["homework"].ToString()); a.Addition = Convert.ToDecimal(reader["addition"].ToString()); a.Exam = Convert.ToDecimal(reader["exam"].ToString()); a.Final = Convert.ToDecimal(reader["final"].ToString()); this.Dispatcher.Invoke(() => { readcore.Add(a); }); } reader.Close(); AccessDBHelper.CloseConnectDB(); this.Dispatcher.Invoke(() => { this.scorelist.ItemsSource = readcore; if (readcore != null && readcore.Count != 0) this.ediscore.Content = "保存成绩"; }); } }); } }
bool get(string teachernum) { string filename = String.Format("{0}.mdb", teachernum);//以教师号命名数据库文件 FileInfo dbfile = new FileInfo(filename); string filePath = new DirectoryInfo(".").FullName + "\\" + filename; App.Databasefilepath = filePath; if (dbfile.Exists) { //考勤表 AccessDBHelper.CreateTable("create table Attendance (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),coursetime text(50), arrivestate text(1),stulisturl text(50))", filePath); //作业表 AccessDBHelper.CreateTable("create table Homework (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),score text(10),hcount text(5),stulisturl text(50))", filePath); //成绩表 // AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),coursenum text(10), attendance text(5), homework text(5),addition text(5), exam text(5),final text(5))", filePath); AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),stulisturl text(50), attendance text(10), homework text(10),addition text(10), exam text(10),final text(10))", filePath); //作业内容表 AccessDBHelper.CreateTable("create table Record (id autoincrement primary key, stulisturl text(50), hcount text(5),publishtime text(50),content text(200))", filePath); return true; } else { List<ClassDetail> classtable = TableHelp.GetClassTable(teachernum); if (classtable == null) { File.Delete(App.Databasefilepath); return false; } //创建数据库 AccessDBHelper.CreateDB(filePath); //AccessDBHelper.ConnectDB(filePath); //创建数据表 AccessDBHelper.CreateTable("create table classtable (id autoincrement primary key, coursenum TEXT(10), coursename TEXT(100), classroom TEXT(100), lastweeks TEXT(100), classtype text(50) ,subject TEXT(80), stuclassnum TEXT(100), stulisturl TEXT(50),courseday TEXT(10),coursetime TEXT(10))", filePath); string itempattentb = "insert into classtable (coursenum, coursename, classroom, lastweeks , classtype ,subject, stuclassnum, stulisturl,courseday,coursetime) values"; foreach (var item in classtable) { string LastWeeks = ""; string StuClassNum = ""; for (int i = 0; i < item.LastWeeks.Length; i++) { //if (item.LastWeeks[i] == -1) //{ // LastWeeks = "单周"; // break; //} //if (item.LastWeeks[i] == -2) //{ // LastWeeks = "双周"; // break; //} if (i != 0) LastWeeks += ","; LastWeeks += item.LastWeeks[i]; } for (int i = 0; i < item.StuClassNum.Length; i++) { if (i != 0) StuClassNum += ","; StuClassNum += item.StuClassNum[i]; } string insert = String.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", item.CourseNum, item.CourseName, item.Classroom, LastWeeks, item.ClassType, item.Subject, StuClassNum, item.StudentListUrl, item.CourseDay, item.CourseTime); string sql = itempattentb + insert; AccessDBHelper.CreateTable(sql, filePath); } //学生名单表们 var stuurls = from url in classtable where url.StudentListUrl != null select url.StudentListUrl; //url一样的删掉,获得这课的学生名单号。 List<string> disurls = stuurls.ToList(); disurls = disurls.Distinct().ToList(); for (int i = 0; i < disurls.Count; i++) { //实验课学生名单获取 if (disurls[i] != null) { List<TeachClassStu> stu = TableHelp.GetJxbStuList(disurls[i]); string tablename = disurls[i]; string[] SQLTransaction = new string[stu.Count]; string sql = "create table " + tablename + " (stunum TEXT(50) primary key, stuname TEXT(100),sex TEXT(4),subject TEXT(100),classnum TEXT(100),classstate TEXT(50),classtype TEXT(50),num autoincrement)"; //创建教学班表 AccessDBHelper.CreateTable(sql, filePath); string itempatten = "insert into " + tablename + " (stunum, stuname, sex, subject, classnum, classstate, classtype) values "; for (int trans = 0; trans < SQLTransaction.Length; trans++) { string insert = String.Format(itempatten + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", stu[trans].StuNum, stu[trans].StuName, stu[trans].Sex, stu[trans].Subject, stu[trans].ClassNum, stu[trans].ClassState, stu[trans].ClassType); SQLTransaction[trans] = insert; } AccessDBHelper.Transaction(SQLTransaction, filePath); } //理论课学生名单获取 //else if (disurls[i].Substring(20).StartsWith("A") || disurls[i].Substring(20).StartsWith("R")) //{ // List<TeachClassStuA> stu = TableHelp.GetJxbStuList(disurls[i]) as List<TeachClassStuA>; // string tablename = disurls[i].Substring(20); // string[] SQLTransaction = new string[stu.Count]; // string sql = "create table " + tablename + " (num Integer,subject TEXT(100),stunum TEXT(100),stuname TEXT(100),sex TEXT(100),classnum TEXT(100),inrollyear TEXT(4),classstate TEXT(100))"; // AccessDBHelper.CreateTable(sql, filePath); // string itempatten = "insert into " + tablename + " (num, subject, stunum, stuname, sex, classnum,inrollyear,classstate) values "; // for (int trans = 0; trans < SQLTransaction.Length; trans++) // { // string insert = String.Format(itempatten + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", stu[trans].Num, stu[trans].Subject, stu[trans].StudentNum, stu[trans].Name, stu[trans].Sex, stu[trans].ClassNum, stu[trans].Year, stu[trans].ClassState); // SQLTransaction[trans] = insert; // } // AccessDBHelper.Transaction(SQLTransaction, filePath); //} //超出我的预料啊 else { return false; } } //建表 //考勤表 AccessDBHelper.CreateTable("create table Attendance (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),coursetime text(50), arrivestate text(1),stulisturl text(50))", filePath); //作业表 AccessDBHelper.CreateTable("create table Homework (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),score text(10),hcount text(5),stulisturl text(50))", filePath); //成绩表 // AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),coursenum text(10), attendance text(5), homework text(5),addition text(5), exam text(5),final text(5))", filePath); AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),stulisturl text(50), attendance text(10), homework text(10),addition text(10), exam text(10),final text(10))", filePath); //作业内容表 AccessDBHelper.CreateTable("create table Record (id autoincrement primary key, stulisturl text(50), hcount text(5),publishtime text(50),content text(200))", filePath); List<Score> readcore = new List<Score>(); //为成绩表添加内容 for (int i = 0; i < disurls.Count; i++) { string sql = $"select stuname,stunum,classnum from {disurls[i]}"; OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath); if (reader != null) { while (reader.Read()) { Score a = new Score(); a.StuNum = reader["stunum"].ToString(); a.StuName = reader["stuname"].ToString(); a.Homework = 0; a.Attendance = 0; a.Addition = 0; a.Exam = 0; a.Final = 0; readcore.Add(a); } reader.Close(); AccessDBHelper.CloseConnectDB(); string[] SQLTransaction = new string[readcore.Count]; string itempatten = $"insert into Score (stuname,stunum,stulisturl,attendance,homework,addition,exam,final) values "; for (int j = 0; j < SQLTransaction.Length; j++) { string insert = itempatten + $"('{readcore[j].StuName}','{readcore[j].StuNum}','{disurls[i]}','{readcore[j].Attendance}','{readcore[j].Homework}','{readcore[j].Addition}','{readcore[j].Exam}','{readcore[j].Final}')"; SQLTransaction[j] = insert; } AccessDBHelper.Transaction(SQLTransaction, App.Databasefilepath); readcore.Clear(); } } return true; } }