protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)//按行读取
{
//在导入数据库操作之前需要验证数据库与之是否匹配
string info = "select * from paperApply where id='" + dtExcel.Rows[i][5].ToString() + "'";
DB db=new DB();
DataSet ds = db.Select(info, db.DBconn());
try
{
string title=ds.Tables[0].Rows[0][3].ToString();
string kind=ds.Tables[0].Rows[0][4].ToString();
string teacher_ID = ds.Tables[0].Rows[0][1].ToString();
string teacher_Name = ds.Tables[0].Rows[0][20].ToString();
if (title.Equals(dtExcel.Rows[i][6].ToString()) && kind.Equals(dtExcel.Rows[i][7].ToString()) && teacher_ID.Equals(dtExcel.Rows[i][3].ToString()) && teacher_Name.Equals(dtExcel.Rows[i][4].ToString()))
{
}else{//验证不通过
for (int j= 0; j<i; j++)//按行读取
{
string del="delete from select_Result where student_ID='"+dtExcel.Rows[i][1].ToString()+"'";
db.Delete(del,db.DBconn());
}//将已经插入的数据删除掉
Response.Write("<script language='javascript'>alert('读取的Excel数据中教师" + dtExcel.Rows[i][3].ToString() + dtExcel.Rows[i][4].ToString().Trim() + "所出的论文编号为" + dtExcel.Rows[i][5].ToString() + "的论文题目为" + dtExcel.Rows[i][6].ToString() + "，性质为" + dtExcel.Rows[i][7].ToString() + "的数据与数据库中的数据不一致，导入失败！（在数据库中教师" + teacher_ID + teacher_Name.Trim() + "出的该编号的题目：" + title + "，性质为" + kind + "）');location='finalresults_informationInput.aspx'</script>");
break;
}
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into select_Result(class,student_ID,student_Name,teacher_ID,teacher_Name,title,grade,paperid,kind) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString().Trim() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][6].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][7].ToString() + "')";
bool result = ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能学号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
catch
{
Response.Write("<script language='javascript'>alert('读取数据库失败。');</script>");
}
finally
{
ds.Clear();
}
}
}
protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++) //按行读取
{
//在导入数据库操作之前需要验证数据库与之是否匹配
string info = "select * from paperApply where id='" + dtExcel.Rows[i][5].ToString() + "'";
DB db = new DB();
DataSet ds = db.Select(info, db.DBconn());
try
{
string title = ds.Tables[0].Rows[0][3].ToString();
string kind = ds.Tables[0].Rows[0][4].ToString();
string teacher_ID = ds.Tables[0].Rows[0][1].ToString();
string teacher_Name = ds.Tables[0].Rows[0][20].ToString();
if (title.Equals(dtExcel.Rows[i][6].ToString()) && kind.Equals(dtExcel.Rows[i][7].ToString()) && teacher_ID.Equals(dtExcel.Rows[i][3].ToString()) && teacher_Name.Equals(dtExcel.Rows[i][4].ToString()))
{
}
else //验证不通过
{
for (int j = 0; j < i; j++) //按行读取
{
string del = "delete from select_Result where student_ID='" + dtExcel.Rows[i][1].ToString() + "'";
db.Delete(del, db.DBconn());
} //将已经插入的数据删除掉
Response.Write("<script language='javascript'>alert('读取的Excel数据中教师" + dtExcel.Rows[i][3].ToString() + dtExcel.Rows[i][4].ToString().Trim() + "所出的论文编号为" + dtExcel.Rows[i][5].ToString() + "的论文题目为" + dtExcel.Rows[i][6].ToString() + "，性质为" + dtExcel.Rows[i][7].ToString() + "的数据与数据库中的数据不一致，导入失败！（在数据库中教师" + teacher_ID + teacher_Name.Trim() + "出的该编号的题目：" + title + "，性质为" + kind + "）');location='finalresults_informationInput.aspx'</script>");
break;
}
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into select_Result(class,student_ID,student_Name,teacher_ID,teacher_Name,title,grade,paperid,kind) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString().Trim() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][6].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][7].ToString() + "')";
bool result = ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能学号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
catch
{
Response.Write("<script language='javascript'>alert('读取数据库失败。');</script>");
}
finally
{
ds.Clear();
}
}
}
protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
md5Manger md5 = new md5Manger();
try
{
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)//按行读取
{
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into teacher(department,teacher_ID,name,sex,age,birthday,zhicheng,position,Email,password,role) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][6].ToString() +
"','" + dtExcel.Rows[i][7].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + md5.Md5Encrypt( dtExcel.Rows[i][5].ToString() )+ "','" + "教师" + "')";
bool result = ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能教师号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
}
catch (SqlException e)
{
Panel2.Visible = false;
this.Label2.Visible = true;
this.Label2.Text = "导入数据库失败！" + e.Message;
}
}
protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
try
{
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)//按行读取
{
//密码加密
md5Manger md5 =new md5Manger();
string pwd = md5.Md5Encrypt(dtExcel.Rows[i][4].ToString());
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into student(class,student_ID,name,sex,birthday,birthplace,entranceDate,nation,specialty,zyfx,college,IDcard,grade,phonenumber,Email,password,role) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][6].ToString() +
"','" + dtExcel.Rows[i][7].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + dtExcel.Rows[i][9].ToString() +
"','" + dtExcel.Rows[i][10].ToString() + "','" +dtExcel.Rows[i][11].ToString() + "','" + dtExcel.Rows[i][12].ToString() +
"','" + dtExcel.Rows[i][13].ToString() + "','" + dtExcel.Rows[i][14].ToString() + "','" + pwd + "','" + "学生" + "')";
bool result=ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能学号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
}
catch
{
}
}
protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
try
{
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)//按行读取
{
//密码加密
md5Manger md5 = new md5Manger();
string pwd = md5.Md5Encrypt(dtExcel.Rows[i][4].ToString());
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into student(class,student_ID,name,sex,birthday,birthplace,entranceDate,nation,specialty,zyfx,college,IDcard,grade,phonenumber,Email,password,role) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][6].ToString() +
"','" + dtExcel.Rows[i][7].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + dtExcel.Rows[i][9].ToString() +
"','" + dtExcel.Rows[i][10].ToString() + "','" + dtExcel.Rows[i][11].ToString() + "','" + dtExcel.Rows[i][12].ToString() +
"','" + dtExcel.Rows[i][13].ToString() + "','" + dtExcel.Rows[i][14].ToString() + "','" + pwd + "','" + "学生" + "')";
bool result = ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能学号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
}
catch
{
}
}
protected void DataTableToDB(string _strExcelFileName)//插入操作
{
EXCELtoSQL ets = new EXCELtoSQL();
md5Manger md5 = new md5Manger();
try
{
DataTable dtExcel = ets.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)//按行读取
{
// Response.Write("<script language='javascript'>alert('读取的数据为" + dtExcel.Rows[i][0].ToString() + "');</script>");
string insert = "insert into teacher(department,teacher_ID,name,sex,age,birthday,zhicheng,position,Email,password,role) values('" + dtExcel.Rows[i][0].ToString() +
"','" + dtExcel.Rows[i][1].ToString() + "','" + dtExcel.Rows[i][2].ToString() + "','" + dtExcel.Rows[i][3].ToString() +
"','" + dtExcel.Rows[i][4].ToString() + "','" + dtExcel.Rows[i][5].ToString() + "','" + dtExcel.Rows[i][6].ToString() +
"','" + dtExcel.Rows[i][7].ToString() + "','" + dtExcel.Rows[i][8].ToString() + "','" + md5.Md5Encrypt(dtExcel.Rows[i][5].ToString()) + "','" + "教师" + "')";
bool result = ets.InsertDataToSQL(insert);
if (result)
{
this.Label2.Text = "导入数据库成功！";
this.GridView1.Visible = false;
}
else
{
this.Label2.Text = "导入数据库失败！可能教师号字段重复。";
}
Panel2.Visible = false;
this.Label2.Visible = true;
}
}
catch (SqlException e)
{
Panel2.Visible = false;
this.Label2.Visible = true;
this.Label2.Text = "导入数据库失败！" + e.Message;
}
}
