/// <summary> /// 更新一条数据 /// </summary> public void Update(LearnSite.Model.StudentsExcel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update StudentsExcel set "); strSql.Append("Snum=@Snum,"); strSql.Append("Syear=@Syear,"); strSql.Append("Sgrade=@Sgrade,"); strSql.Append("Sclass=@Sclass,"); strSql.Append("Sname=@Sname,"); strSql.Append("Spwd=@Spwd,"); strSql.Append("Sex=@Sex,"); strSql.Append("Saddress=@Saddress,"); strSql.Append("Sphone=@Sphone,"); strSql.Append("Sparents=@Sparents,"); strSql.Append("Sheadtheacher=@Sheadtheacher,"); strSql.Append("Sscore=@Sscore,"); strSql.Append("Squiz=@Squiz,"); strSql.Append("Sattitude=@Sattitude,"); strSql.Append("Sape=@Sape"); strSql.Append(" where Sid=@Sid "); SqlParameter[] parameters = { new SqlParameter("@Sid", SqlDbType.Int, 4), new SqlParameter("@Snum", SqlDbType.NVarChar, 50), new SqlParameter("@Syear", SqlDbType.Int, 4), new SqlParameter("@Sgrade", SqlDbType.Int, 4), new SqlParameter("@Sclass", SqlDbType.Int, 4), new SqlParameter("@Sname", SqlDbType.NVarChar, 50), new SqlParameter("@Spwd", SqlDbType.NVarChar, 50), new SqlParameter("@Sex", SqlDbType.NVarChar, 2), new SqlParameter("@Saddress", SqlDbType.NVarChar, 200), new SqlParameter("@Sphone", SqlDbType.NVarChar, 50), new SqlParameter("@Sparents", SqlDbType.NVarChar, 50), new SqlParameter("@Sheadtheacher", SqlDbType.NVarChar, 50), new SqlParameter("@Sscore", SqlDbType.Int, 4), new SqlParameter("@Squiz", SqlDbType.Int, 4), new SqlParameter("@Sattitude", SqlDbType.Int, 4), new SqlParameter("@Sape", SqlDbType.NVarChar, 1) }; parameters[0].Value = model.Sid; parameters[1].Value = model.Snum; parameters[2].Value = model.Syear; parameters[3].Value = model.Sgrade; parameters[4].Value = model.Sclass; parameters[5].Value = model.Sname; parameters[6].Value = model.Spwd; parameters[7].Value = model.Sex; parameters[8].Value = model.Saddress; parameters[9].Value = model.Sphone; parameters[10].Value = model.Sparents; parameters[11].Value = model.Sheadtheacher; parameters[12].Value = model.Sscore; parameters[13].Value = model.Squiz; parameters[14].Value = model.Sattitude; parameters[15].Value = model.Sape; DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(LearnSite.Model.StudentsExcel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into StudentsExcel("); strSql.Append("Snum,Syear,Sgrade,Sclass,Sname,Spwd,Sex,Saddress,Sphone,Sparents,Sheadtheacher,Sscore,Squiz,Sattitude,Sape)"); strSql.Append(" values ("); strSql.Append("@Snum,@Syear,@Sgrade,@Sclass,@Sname,@Spwd,@Sex,@Saddress,@Sphone,@Sparents,@Sheadtheacher,@Sscore,@Squiz,@Sattitude,@Sape)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@Snum", SqlDbType.NVarChar, 50), new SqlParameter("@Syear", SqlDbType.Int, 4), new SqlParameter("@Sgrade", SqlDbType.Int, 4), new SqlParameter("@Sclass", SqlDbType.Int, 4), new SqlParameter("@Sname", SqlDbType.NVarChar, 50), new SqlParameter("@Spwd", SqlDbType.NVarChar, 50), new SqlParameter("@Sex", SqlDbType.NVarChar, 2), new SqlParameter("@Saddress", SqlDbType.NVarChar, 200), new SqlParameter("@Sphone", SqlDbType.NVarChar, 50), new SqlParameter("@Sparents", SqlDbType.NVarChar, 50), new SqlParameter("@Sheadtheacher", SqlDbType.NVarChar, 50), new SqlParameter("@Sscore", SqlDbType.Int, 4), new SqlParameter("@Squiz", SqlDbType.Int, 4), new SqlParameter("@Sattitude", SqlDbType.Int, 4), new SqlParameter("@Sape", SqlDbType.NVarChar, 1) }; parameters[0].Value = model.Snum; parameters[1].Value = model.Syear; parameters[2].Value = model.Sgrade; parameters[3].Value = model.Sclass; parameters[4].Value = model.Sname; parameters[5].Value = model.Spwd; parameters[6].Value = model.Sex; parameters[7].Value = model.Saddress; parameters[8].Value = model.Sphone; parameters[9].Value = model.Sparents; parameters[10].Value = model.Sheadtheacher; parameters[11].Value = model.Sscore; parameters[12].Value = model.Squiz; parameters[13].Value = model.Sattitude; parameters[14].Value = model.Sape; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(1); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 得到一个对象实体 /// </summary> public LearnSite.Model.StudentsExcel GetModel(int Sid) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 Sid,Snum,Syear,Sgrade,Sclass,Sname,Spwd,Sex,Saddress,Sphone,Sparents,Sheadtheacher,Sscore,Squiz,Sattitude,Sape from StudentsExcel "); strSql.Append(" where Sid=@Sid "); SqlParameter[] parameters = { new SqlParameter("@Sid", SqlDbType.Int, 4) }; parameters[0].Value = Sid; LearnSite.Model.StudentsExcel model = new LearnSite.Model.StudentsExcel(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["Sid"].ToString() != "") { model.Sid = int.Parse(ds.Tables[0].Rows[0]["Sid"].ToString()); } model.Snum = ds.Tables[0].Rows[0]["Snum"].ToString(); if (ds.Tables[0].Rows[0]["Syear"].ToString() != "") { model.Syear = int.Parse(ds.Tables[0].Rows[0]["Syear"].ToString()); } if (ds.Tables[0].Rows[0]["Sgrade"].ToString() != "") { model.Sgrade = int.Parse(ds.Tables[0].Rows[0]["Sgrade"].ToString()); } if (ds.Tables[0].Rows[0]["Sclass"].ToString() != "") { model.Sclass = int.Parse(ds.Tables[0].Rows[0]["Sclass"].ToString()); } model.Sname = ds.Tables[0].Rows[0]["Sname"].ToString(); model.Spwd = ds.Tables[0].Rows[0]["Spwd"].ToString(); model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString(); model.Saddress = ds.Tables[0].Rows[0]["Saddress"].ToString(); model.Sphone = ds.Tables[0].Rows[0]["Sphone"].ToString(); model.Sparents = ds.Tables[0].Rows[0]["Sparents"].ToString(); model.Sheadtheacher = ds.Tables[0].Rows[0]["Sheadtheacher"].ToString(); if (ds.Tables[0].Rows[0]["Sscore"].ToString() != "") { model.Sscore = int.Parse(ds.Tables[0].Rows[0]["Sscore"].ToString()); } if (ds.Tables[0].Rows[0]["Squiz"].ToString() != "") { model.Squiz = int.Parse(ds.Tables[0].Rows[0]["Squiz"].ToString()); } if (ds.Tables[0].Rows[0]["Sattitude"].ToString() != "") { model.Sattitude = int.Parse(ds.Tables[0].Rows[0]["Sattitude"].ToString()); } model.Sape = ds.Tables[0].Rows[0]["Sape"].ToString(); return(model); } else { return(null); } }
/// <summary> /// 将DataSet导入到临时表StudentsExcel中 /// </summary> /// <param name="upexcelpath"></param> public static string DataSettoStudentsExcel(DataSet ds) { int count = ds.Tables[0].Rows.Count; int columnscount = ds.Tables[0].Columns.Count; string msg = ""; int isright = 0; string[] strColumn = { "学号", "入学年度", "年级", "班级", "姓名", "密码", "性别", "家庭住址", "联系电话", "家长姓名", "班主任" }; for (int k = 0; k < columnscount; k++) { string strname = ds.Tables[0].Columns[k].ColumnName; foreach (string str in strColumn) { if (strname == str) isright++; } } if (isright == strColumn.Length) { if (count > 0) { int wrong = 0; LearnSite.Model.StudentsExcel stu = new LearnSite.Model.StudentsExcel(); LearnSite.BLL.StudentsExcel stubll = new LearnSite.BLL.StudentsExcel(); for (int i = 0; i < count; i++) { string strnum = ds.Tables[0].Rows[i]["学号"].ToString().Trim(); if (LearnSite.Common.WordProcess.IsNum(strnum)) { int getnum = int.Parse(strnum); stu.Snum = getnum.ToString(); stu.Syear = int.Parse(ds.Tables[0].Rows[i]["入学年度"].ToString()); stu.Sgrade = int.Parse(ds.Tables[0].Rows[i]["年级"].ToString()); stu.Sclass = int.Parse(ds.Tables[0].Rows[i]["班级"].ToString()); stu.Sname = ds.Tables[0].Rows[i]["姓名"].ToString(); stu.Spwd = ds.Tables[0].Rows[i]["密码"].ToString(); stu.Sex = ds.Tables[0].Rows[i]["性别"].ToString(); stu.Saddress = ds.Tables[0].Rows[i]["家庭住址"].ToString(); stu.Sphone = ds.Tables[0].Rows[i]["联系电话"].ToString(); stu.Sparents = ds.Tables[0].Rows[i]["家长姓名"].ToString(); stu.Sheadtheacher = ds.Tables[0].Rows[i]["班主任"].ToString(); stubll.AddFromExcelDs(stu); } else { wrong++; } } int right = count - wrong; msg = "成功导入" + right + "条数据!"; if (wrong > 0) msg = msg + " 学号错误共" + wrong + "条数据"; } else { msg = "无数据!"; } } else { msg = "Excel数据格式不正确,请参考样式"; } return msg; }
/// <summary> /// �õ�һ������ʵ�� /// </summary> public LearnSite.Model.StudentsExcel GetModel(int Sid) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 Sid,Snum,Syear,Sgrade,Sclass,Sname,Spwd,Sex,Saddress,Sphone,Sparents,Sheadtheacher,Sscore,Squiz,Sattitude,Sape from StudentsExcel "); strSql.Append(" where Sid=@Sid "); SqlParameter[] parameters = { new SqlParameter("@Sid", SqlDbType.Int,4)}; parameters[0].Value = Sid; LearnSite.Model.StudentsExcel model = new LearnSite.Model.StudentsExcel(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["Sid"].ToString() != "") { model.Sid = int.Parse(ds.Tables[0].Rows[0]["Sid"].ToString()); } model.Snum = ds.Tables[0].Rows[0]["Snum"].ToString(); if (ds.Tables[0].Rows[0]["Syear"].ToString() != "") { model.Syear = int.Parse(ds.Tables[0].Rows[0]["Syear"].ToString()); } if (ds.Tables[0].Rows[0]["Sgrade"].ToString() != "") { model.Sgrade = int.Parse(ds.Tables[0].Rows[0]["Sgrade"].ToString()); } if (ds.Tables[0].Rows[0]["Sclass"].ToString() != "") { model.Sclass = int.Parse(ds.Tables[0].Rows[0]["Sclass"].ToString()); } model.Sname = ds.Tables[0].Rows[0]["Sname"].ToString(); model.Spwd = ds.Tables[0].Rows[0]["Spwd"].ToString(); model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString(); model.Saddress = ds.Tables[0].Rows[0]["Saddress"].ToString(); model.Sphone = ds.Tables[0].Rows[0]["Sphone"].ToString(); model.Sparents = ds.Tables[0].Rows[0]["Sparents"].ToString(); model.Sheadtheacher = ds.Tables[0].Rows[0]["Sheadtheacher"].ToString(); if (ds.Tables[0].Rows[0]["Sscore"].ToString() != "") { model.Sscore = int.Parse(ds.Tables[0].Rows[0]["Sscore"].ToString()); } if (ds.Tables[0].Rows[0]["Squiz"].ToString() != "") { model.Squiz = int.Parse(ds.Tables[0].Rows[0]["Squiz"].ToString()); } if (ds.Tables[0].Rows[0]["Sattitude"].ToString() != "") { model.Sattitude = int.Parse(ds.Tables[0].Rows[0]["Sattitude"].ToString()); } model.Sape = ds.Tables[0].Rows[0]["Sape"].ToString(); return model; } else { return null; } }
/// <summary> /// 将DataSet导入到临时表StudentsExcel中 /// </summary> /// <param name="upexcelpath"></param> public static string DataSettoStudentsExcel(DataSet ds, bool pinying) { string msg = ""; if (ds != null) { int count = ds.Tables[0].Rows.Count; if (count > 0) { int columnscount = ds.Tables[0].Columns.Count; int isright = 0; string[] strColumn = { "学号", "入学年度", "年级", "班级", "姓名", "密码", "性别", "家庭住址", "联系电话", "家长姓名", "班主任" }; for (int k = 0; k < columnscount; k++) { string strname = ds.Tables[0].Columns[k].ColumnName; foreach (string str in strColumn) { if (strname == str) { isright++; } } } if (isright == strColumn.Length) { int wrong = 0; LearnSite.Model.StudentsExcel stu = new LearnSite.Model.StudentsExcel(); LearnSite.BLL.StudentsExcel stubll = new LearnSite.BLL.StudentsExcel(); for (int i = 0; i < count; i++) { string strnum = ds.Tables[0].Rows[i]["学号"].ToString().Trim(); if (LearnSite.Common.WordProcess.IsNum(strnum)) { string stryear = ds.Tables[0].Rows[i]["入学年度"].ToString(); string strgrade = ds.Tables[0].Rows[i]["年级"].ToString(); string strclass = ds.Tables[0].Rows[i]["班级"].ToString(); stu.Snum = strnum; stu.Syear = int.Parse(stryear); stu.Sgrade = int.Parse(strgrade); stu.Sclass = int.Parse(strclass); stu.Sname = ds.Tables[0].Rows[i]["姓名"].ToString().Replace(" ", "").Trim();//去空格,以防提交的作品名称找不到路径 if (pinying) { string spellname = LearnSite.Common.Gbk2Spell.Chinese.FirstLetter(stu.Sname);//取姓名的拼音缩写为密码 if (LearnSite.Common.WordProcess.IsEnNum(spellname)) { stu.Spwd = spellname;//如果缩写为字母或数字则采用 } else { stu.Spwd = ds.Tables[0].Rows[i]["密码"].ToString();//否则用原密码 } } else { stu.Spwd = ds.Tables[0].Rows[i]["密码"].ToString(); } stu.Sex = ds.Tables[0].Rows[i]["性别"].ToString(); stu.Saddress = ds.Tables[0].Rows[i]["家庭住址"].ToString(); stu.Sphone = ds.Tables[0].Rows[i]["联系电话"].ToString(); stu.Sparents = ds.Tables[0].Rows[i]["家长姓名"].ToString(); stu.Sheadtheacher = ds.Tables[0].Rows[i]["班主任"].ToString(); stubll.AddFromExcelDs(stu); } else { wrong++; } } int right = count - wrong; msg = "成功获取" + right + "条数据到临时表!<br/>请点下一步导入数据,即可将数据导入数据库!"; if (wrong > 0) { msg = msg + " 学号错误(非数字格式)共" + wrong + "条数据"; } } else { msg = "Excel数据格式不正确,请参考学生导入模板"; } } else { msg = "无数据!"; } } else { msg = "没有选择Excel上传或不是标准的Excel格式,<br/>请用Excel打开再保存试试!"; } return(msg); }