/// <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);
}
