excel导入数据到sqlserver
2021/6/3 19:22:04
本文主要是介绍excel导入数据到sqlserver，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
一，ADO.net的方式
1、读取excel数据到dataset
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
2、将数据写入到数据库
protected void Button2_Click(object sender, EventArgs e)
{
string filename = FileUpload1.FileName;
string savePath = Server.MapPath(("~/fujian/") + filename);
FileUpload1.SaveAs(savePath);
DataSet ds = ExcelSqlConnection(savePath, filename);
//GridView1.DataSource = ds;
//GridView1.DataBind();
DataRow[] dr = ds.Tables[0].Select();
for (int i = 0; i < dr.Length; i++)
{
SqlConnection myconn = myconnect();
myconn.Open();
string title = dr[i]["名称"].ToString();
string huanxianweizhi = dr[i]["环线位置"].ToString();
string quyu = dr[i]["区域"].ToString();
string sqlstr1 = "select * from dbo.test where name='" + quyu + "'"; //区域中间表转换
SqlCommand myCmd1 = new SqlCommand(sqlstr1, myconn);
SqlDataAdapter mydata = new SqlDataAdapter(myCmd1);
DataSet my = new DataSet();
mydata.Fill(my);
GridView1.DataSource = my;
GridView1.DataBind();
string quyu1 = my.Tables[0].Rows[0][0].ToString(); ;
//SqlDataAdapter adapt = new SqlDataAdapter(sqlstr1, myconn);
DataSet ds1 = new DataSet();
string zuoluo = dr[i]["座落"].ToString();
string yongtu = dr[i]["用途"].ToString();
string chengjiaotaoshu = dr[i]["成交套数"].ToString();
string jianzhumianji = dr[i]["建筑面积"].ToString();
string chengjiaozongjia = dr[i]["成交总价"].ToString();
string dangrijunjia = dr[i]["当日均价"].ToString();
string chengjiaoriqi = dr[i]["成交日期"].ToString();
string qitashuoming = dr[i]["其他说明"].ToString();
string bankuai = dr[i]["板块"].ToString();
//SqlConnection myconn = myconnect();
//myconn.Open();
string sqlstr = "insert into dbo.youweishuju(名称,环线位置,区域,座落,用途,成交套数,建筑面积,成交总价,当日均价,成交日期,其他说明,板块)values('" + title + "','" + huanxianweizhi + "','" + quyu1 + "','" + zuoluo + "','" + yongtu + "','" + chengjiaotaoshu + "','"+ jianzhumianji +"','"+ chengjiaozongjia +"','"+ dangrijunjia +"','"+ chengjiaoriqi +"','"+ qitashuoming +"','"+ bankuai +"')";
//string sqlstr = "insert into dbo.youweishuju(名称)values('" + title + "')";
SqlCommand myCmd = new SqlCommand(sqlstr, myconn);
myCmd.ExecuteNonQuery();
myconn.Close();
}
}
二，linq的方式
1、读取excel数据并且验证
protected void UpLoad()
{
this.GvReport.DataSource = null;
this.GvReport.DataBind();
var fileName = string.Empty;
try
{
if (this.FileUpload1.PostedFile != null && !string.IsNullOrEmpty(this.FileUpload1.PostedFile.FileName))
{
if (!".xls,.xlsx".Contains(Path.GetExtension(this.FileUpload1.PostedFile.FileName)))
{
Response.Write("<script>alert('上传的文件不是EXCEL文件！请重新上传！');</script>");
}
else
{
var connectionString = string.Empty;
fileName = Path.GetTempFileName();
this.FileUpload1.PostedFile.SaveAs(fileName);
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended properties='Excel 12.0;HDR=yes;IMEX=1'";
var sql = "Select * from [Sheet1$]";
var dataAdapter = new OleDbDataAdapter(sql, connectionString);
var ds = new DataSet();
dataAdapter.Fill(ds, "ExcelInfo");
var dataTable = ds.Tables["ExcelInfo"];
var tb = new List<OrganizationBasis>();
foreach (DataRow dr in dataTable.Rows)
{
var query = new OrganizationBasis
{
StudentCode = dr["学号"].ToString().Trim(),
StudentName = dr["姓名"].ToString().Trim(),
SubjectName = dr["科目名称"].ToString().Trim(),//
CourseName = dr["模块名称"].ToString().Trim(),//
OrganizationNo = dr["班级编号"].ToString(),
OrganizationName = dr["班级名称"].ToString(),
SeatNo = dr["座位号"].ToString(),
ClassTypeName = dr["班级类型"].ToString(),
TeacherName = dr["任课教师"].ToString().Trim(),
RoomName = dr["教室"].ToString(),
WeekName = dr["星期"].ToString(),
PeriodName = dr["节次"].ToString().Trim(),
Remark = string.Empty,
Status = true
};
tb.Add(query);
}
if (tb.Count() == decimal.Zero)
{
ShowMessage("EXCLE内容为空，请核对并修改后再重新上传!");
return;
}
using (var db = new SMSPModel.SMSPEntities())
{
var studentBasis = (from p in db.tbStudentBasis
where p.RecordStatus == decimal.One && p.StudentStatus == decimal.One
select p).ToList();
var teacherBasis = (from p in db.tbTeacherBasis
where p.RecordStatus == decimal.One
select p).ToList();
var gradBasis = (from p in db.tbGradeBasis
where p.RecordStatus == decimal.One
select p).ToList();
var classTypeBasis = (from p in db.tbClassType
where p.RecordStatus == decimal.One
select p).ToList();
var orgStudent = (from p in db.tbOrganizationStudent
where p.RecordStatus == decimal.One && p.tbStudentBasis.RecordStatus == decimal.One
&& p.tbStudentBasis.StudentStatus == decimal.One && p.tbOrganizationBasis.RecordStatus == decimal.One
&& p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
&& p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
select p).ToList();
var orgTeacher = (from p in db.tbOrganizationTeacher
where p.RecordStatus == decimal.One && p.tbTeacherBasis.RecordStatus == decimal.One
&& p.tbOrganizationBasis.RecordStatus == decimal.One && p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
&& p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
select p).ToList();
var orgMaster = (from p in db.tbOrganizationMaster
where p.RecordStatus == decimal.One && p.tbTeacherBasis.RecordStatus == decimal.One
&& p.tbOrganizationBasis.RecordStatus == decimal.One && p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
&& p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
select p).ToList();
var orgBasis = (from p in db.tbOrganizationBasis
where p.RecordStatus == decimal.One && p.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbYear.ID == this.DrplstYear1.YearId && p.tbGradeBasis.ID == this.DrplstGrade1.GradeId
select p).ToList();
var jobBasis = (from p in db.tbTeacherJob
where p.RecordStatus == decimal.One
select p).ToList();
var courseBasis = (from p in db.tbCourseBasis
where p.RecordStatus == decimal.One
select p).ToList();
var YearId = (from i in db.tbYear
where i.ID == (from p in db.tbYear
where p.RecordStatus == decimal.One && p.ID == this.DrplstYear1.YearId
select p.ParentID).FirstOrDefault()
select i.ParentID).FirstOrDefault();
var classBasis = (from p in db.tbClassBasis
where p.RecordStatus == decimal.One && p.tbYear.ID == YearId
select p).ToList();
var roomBasis = (from p in db.tbBuildRoom
where p.RecordStatus == decimal.One
select p).ToList();
var weekBasis = (from p in db.tbWeekBasis
where p.RecordStatus == decimal.One
select p).ToList();
var periodBasis = (from p in db.tbPeriodBasis
where p.RecordStatus == decimal.One
select p).ToList();
foreach (var t in tb)
{
if (!string.IsNullOrEmpty(t.StudentCode))
{
var tt = tb.Where(d => d.StudentCode == t.StudentCode && d.OrganizationName == t.OrganizationName && d.CourseName == t.CourseName).Count();
if (tt > decimal.One)
{
t.Remark = t.Remark + "该学号重复（" + tb.Where(d => d.StudentCode == t.StudentCode && d.OrganizationName == t.OrganizationName && d.CourseName == t.CourseName).Count() + "次);";
}
}
if (!string.IsNullOrEmpty(t.StudentCode))
{
var studentCode = (from p in studentBasis
where p.StudentCode == t.StudentCode
select p).FirstOrDefault();
if (studentCode == null)
{
t.Remark = t.Remark + "该学号不存在;";
}
}
if (!string.IsNullOrEmpty(t.StudentName))
{
var studentName = (from p in studentBasis
where p.StudentCode == t.StudentCode && p.StudentName == t.StudentName
select p).FirstOrDefault();
if (studentName == null)
{
t.Remark = t.Remark + "该学生学号与姓名不一致;";
}
}
if (!string.IsNullOrEmpty(t.TeacherName))
{
var teacher = (from p in teacherBasis
where p.TeacherName == t.TeacherName
select p).FirstOrDefault();
if (teacher == null)
{
t.Remark = t.Remark + "该教师名称不存在;";
}
}
if (!string.IsNullOrEmpty(t.ClassTypeName))
{
var classType = (from p in classTypeBasis
where p.TypeName == t.ClassTypeName
select p).FirstOrDefault();
if (classType == null)
{
t.Remark = t.Remark + "班级类型格式不正确;";
}
}
if (!string.IsNullOrEmpty(t.RoomName))
{
var room = (from p in roomBasis
where p.RoomName == t.RoomName
select p).FirstOrDefault();
if (room == null)
{
t.Remark = t.Remark + "教室信息不存在;";
}
}
if (!string.IsNullOrEmpty(t.SubjectName))
{
var subject = (from p in db.tbSubjectBasis
where p.SubjectName == t.SubjectName
select p).FirstOrDefault();
if (subject == null)
{
t.Remark += "科目信息不存在;";
}
}
if (!string.IsNullOrEmpty(t.CourseName))
{
var course = (from p in db.tbCourseBasis
where p.CourseName == t.CourseName
select p).ToList();
if (course.Count == decimal.Zero)
{
t.Remark += "模块信息不存在;";
}
if (course.Where(c => c.tbSubjectBasis.SubjectName == t.SubjectName).Count() == decimal.Zero)
{
t.Remark += "在【" + t.SubjectName + "】科目中没有该模块信息;";
}
}
if (!string.IsNullOrEmpty(t.StudentCode) && string.IsNullOrEmpty(t.StudentName))
{
t.Remark = t.Remark + "姓名为必填字段;";
}
if (string.IsNullOrEmpty(t.StudentCode) && !string.IsNullOrEmpty(t.StudentName))
{
t.Remark = t.Remark + "学号为必填字段;";
}
}
var t0 = tb.Where(c => c.Remark != string.Empty);
if (t0.Count() == decimal.Zero)
{
this.Save(db, studentBasis, teacherBasis, classTypeBasis, gradBasis, orgStudent, orgTeacher, orgMaster, orgBasis, classBasis, courseBasis, jobBasis, roomBasis, weekBasis, periodBasis, tb);
}
else
{
ShowMessage("EXCLE内容有错，请核对并修改后再重新上传!");
this.GvReport.DataSource = t0;
this.GvReport.DataBind();
}
}
}
}
}
catch (Exception e)
{
if (e.Message.Equals("外部表不是预期的格式。"))
{
var reader = XmlReader.Create(fileName);
var dt = new DataTable();
var row = 0;
var isColumn = false;
var col = 0;
while (reader.Read())
{
if (reader.Name == "Row" && reader.NodeType == XmlNodeType.Element)
{
row = row + 1;
col = 0;
if (row > 1)
{
var dr = dt.NewRow();
dt.Rows.Add(dr);
}
}
if (reader.Name == "Cell" && reader.NodeType == XmlNodeType.Element)
{
isColumn = true;
if (reader.HasAttributes && reader.GetAttribute("ss:Index") != null)
{
col = ConvertToInt(reader.GetAttribute("ss:Index"));
}
else
{
col = col + 1;
}
}
if (reader.NodeType == XmlNodeType.Text)
{
if (isColumn)
{
if (row == 1)
{
dt.Columns.Add(reader.Value);
isColumn = false;
}
else
{
dt.Rows[dt.Rows.Count - 1][col - 1] = reader.Value;
}
}
}
if (reader.Name == "Row" && reader.NodeType == XmlNodeType.EndElement && row == 1)
{
var dc = new DataColumn
{
DataType = Type.GetType("System.Boolean"),
DefaultValue = true,
ColumnName = "Status"
};
dt.Columns.Add(dc);
dt.Columns.Add("Remark");
}
if (reader.Name == "Table" && reader.NodeType == XmlNodeType.EndElement)
{
break;
}
}
reader.Close();
dt.AcceptChanges();
for (var i = 0; i < dt.Columns.Count; i++)
{
switch (dt.Columns[i].ColumnName)
{
case "学号":
dt.Columns[i].ColumnName = "StudentCode";
break;
case "姓名":
dt.Columns[i].ColumnName = "StudentName";
break;
case "科目名称"://
dt.Columns[i].ColumnName = "SubjectName";
break;
case "模块名称"://
dt.Columns[i].ColumnName = "CourseName";
break;
case "班级编号":
dt.Columns[i].ColumnName = "OrganizationNo";
break;
case "班级名称":
dt.Columns[i].ColumnName = "OrganizationName";
break;
case "座位号":
dt.Columns[i].ColumnName = "SeatNo";
break;
case "班级类型":
dt.Columns[i].ColumnName = "ClassTypeName";
break;
case "任课教师":
dt.Columns[i].ColumnName = "TeacherName";
break;
case "教室":
dt.Columns[i].ColumnName = "RoomName";
break;
case "星期":
dt.Columns[i].ColumnName = "WeekName";
break;
case "节次":
dt.Columns[i].ColumnName = "PeriodName";
break;
default:
break;
}
}
var list = new List<OrganizationBasis>();
var rows = dt.Select(string.Empty);
using (var db = new SMSPModel.SMSPEntities())
{
var studentBasis = (from p in db.tbStudentBasis
where p.RecordStatus == decimal.One && p.StudentStatus == decimal.One
select p).ToList();
var teacherBasis = (from p in db.tbTeacherBasis
where p.RecordStatus == decimal.One
select p).ToList();
var gradBasis = (from p in db.tbGradeBasis
where p.RecordStatus == decimal.One
select p).ToList();
var classTypeBasis = (from p in db.tbClassType
where p.RecordStatus == decimal.One
select p).ToList();
var orgStudent = (from p in db.tbOrganizationStudent
where p.RecordStatus == decimal.One && p.tbStudentBasis.RecordStatus == decimal.One && p.tbStudentBasis.StudentStatus == decimal.One
&& p.tbOrganizationBasis.RecordStatus == decimal.One && p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
select p).ToList();
var orgTeacher = (from p in db.tbOrganizationTeacher
where p.RecordStatus == decimal.One && p.tbTeacherBasis.RecordStatus == decimal.One
&& p.tbOrganizationBasis.RecordStatus == decimal.One && p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
select p).ToList();
var orgMaster = (from p in db.tbOrganizationMaster
where p.RecordStatus == decimal.One && p.tbTeacherBasis.RecordStatus == decimal.One
&& p.tbOrganizationBasis.RecordStatus == decimal.One && p.tbOrganizationBasis.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbOrganizationBasis.tbYear.ID == this.DrplstYear1.YearId
select p).ToList();
var orgBasis = (from p in db.tbOrganizationBasis
where p.RecordStatus == decimal.One && p.tbGradeBasis.ID == this.DrplstGrade1.GradeId
&& p.tbYear.ID == this.DrplstYear1.YearId
select p).ToList();
var jobBasis = (from p in db.tbTeacherJob
where p.RecordStatus == decimal.One
select p).ToList();
var courseBasis = (from p in db.tbCourseBasis
where p.RecordStatus == decimal.One
select p).ToList();
var classBasis = (from p in db.tbClassBasis
where p.RecordStatus == decimal.One
select p).ToList();
var roomBasis = (from p in db.tbBuildRoom
where p.RecordStatus == decimal.One
select p).ToList();
var weekBasis = (from p in db.tbWeekBasis
where p.RecordStatus == decimal.One
select p).ToList();
var periodBasis = (from p in db.tbPeriodBasis
where p.RecordStatus == decimal.One
select p).ToList();
foreach (var dr in rows)
{
var model = new OrganizationBasis
{
OrganizationName = dr["OrganizationName"].ToString(),
OrganizationNo = dr["OrganizationNo"].ToString(),
SeatNo = dr["SeatNo"].ToString(),
StudentCode = dr["StudentCode"].ToString(),
StudentName = dr["StudentName"].ToString(),
ClassTypeName = dr["ClassTypeName"].ToString(),
TeacherName = dr["TeacherName"].ToString(),
Remark = dr["Remark"].ToString(),
RoomName = dr["RoomName"].ToString(),
Status = (bool)dr["Status"],
SubjectName = dr["SubjectName"].ToString(),//
CourseName = dr["CourseName"].ToString(),//
//PeriodName =dr["PeriodName"].ToString(),
//WeekName=dr["WeekName"].ToString()
};
list.Add(model);
if (!string.IsNullOrEmpty(model.StudentCode))
{
var studentCode = (from p in studentBasis
where p.StudentCode == model.StudentCode
select p).FirstOrDefault();
if (studentCode == null)
{
model.Remark = model.Remark + "该学号不存在;";
}
}
if (!string.IsNullOrEmpty(model.StudentName))
{
var studentName = (from p in studentBasis
where p.StudentCode == model.StudentCode && p.StudentName == model.StudentName
select p).FirstOrDefault();
if (studentName == null)
{
model.Remark = model.Remark + "该学生学号与姓名不一致;";
}
}
if (!string.IsNullOrEmpty(model.TeacherName))
{
var teacher = (from p in teacherBasis
where p.TeacherName == model.TeacherName
select p).FirstOrDefault();
if (teacher == null)
{
model.Remark = model.Remark + "该教师名称不存在;";
}
}
if (!string.IsNullOrEmpty(model.ClassTypeName))
{
var classType = (from p in classTypeBasis
where p.TypeName == model.ClassTypeName
select p).FirstOrDefault();
if (classType == null)
{
model.Remark = model.Remark + "班级类型格式不正确;";
}
}
if (!string.IsNullOrEmpty(model.RoomName))
{
var room = (from p in roomBasis
where p.RoomName == model.RoomName
select p).FirstOrDefault();
if (room == null)
{
model.Remark = model.Remark + "教室信息不存在;";
}
}
if (!string.IsNullOrEmpty(model.SubjectName))
{
var subject = (from p in db.tbSubjectBasis
where p.SubjectName == model.SubjectName
select p).FirstOrDefault();
if (subject == null)
{
model.Remark += "科目信息不存在;";
}
}
if (!string.IsNullOrEmpty(model.CourseName))
{
var course = (from p in db.tbCourseBasis
where p.CourseName == model.CourseName
select p).ToList();
if (course.Count == decimal.Zero)
{
model.Remark += "模块信息不存在;";
}
if (course.Where(c => c.tbSubjectBasis.SubjectName == model.SubjectName).Count() == decimal.Zero)
{
model.Remark += "在【" + model.SubjectName + "】科目中没有该模块信息;";
}
}
if (!string.IsNullOrEmpty(model.StudentCode) && string.IsNullOrEmpty(model.StudentName))
{
model.Remark = model.Remark + "姓名为必填字段;";
}
if (string.IsNullOrEmpty(model.StudentCode) && !string.IsNullOrEmpty(model.StudentName))
{
model.Remark = model.Remark + "学号为必填字段;";
}
}
if (list.Count == decimal.Zero)
{
ShowMessage("EXCLE内容为空，请核对并修改后再重新上传!");
return;
}
foreach (var i in list)
{
if (!string.IsNullOrEmpty(i.StudentCode))
{
var tt = list.Where(d => d.StudentCode == i.StudentCode && d.OrganizationName == i.OrganizationName && d.CourseName == i.CourseName).Count();
if (tt > decimal.One)
{
i.Remark = i.Remark + "该学号重复（" + list.Where(d => d.StudentCode == i.StudentCode && d.OrganizationName == i.OrganizationName && d.CourseName == i.CourseName).Count() + "次);";
}
}
}
var tb = list.Where(c => c.Remark != string.Empty);
if (tb.Count() == decimal.Zero)
{
this.Save(db, studentBasis, teacherBasis, classTypeBasis, gradBasis, orgStudent, orgTeacher, orgMaster, orgBasis, classBasis, courseBasis, jobBasis, roomBasis, weekBasis, periodBasis, list);
}
else
{
ShowMessage("EXCLE内容有错，请核对并修改后再重新上传!");
this.GvReport.DataSource = tb;
this.GvReport.DataBind();
}
}
}
else
{
ShowMessage("您上传Excel文件表头结构或表头名称与系统不匹配，请核查后再次操作！（参考信息：" + e.Message + "）");
}
}
}
2，将数据写到数据库
protected void Save(SMSPModel.SMSPEntities db, List<SMSPModel.tbStudentBasis> studentBasis, List<SMSPModel.tbTeacherBasis> teacherBasis, List<SMSPModel.tbClassType> classTypeBasis, List<SMSPModel.tbGradeBasis> gradBasis, List<SMSPModel.tbOrganizationStudent> orgStudent, List<SMSPModel.tbOrganizationTeacher> orgTeacher, List<SMSPModel.tbOrganizationMaster> orgMaster, List<SMSPModel.tbOrganizationBasis> orgBasis, List<SMSPModel.tbClassBasis> classBasis, List<SMSPModel.tbCourseBasis> courseBasis, List<SMSPModel.tbTeacherJob> jobBasis, List<SMSPModel.tbBuildRoom> roomBasis, List<SMSPModel.tbWeekBasis> weekBasis, List<SMSPModel.tbPeriodBasis> periodBasis, List<OrganizationBasis> tb)
{
var arrangeCourseBasis = (from p in db.tbArrangeCourseBasis
where p.RecordStatus == decimal.One && p.ID == GuidDefault
select p).FirstOrDefault();
var year = (from p in db.tbYear
where p.RecordStatus == decimal.One && p.ID == this.DrplstYear1.YearId
select p).FirstOrDefault();
var grad = (from p in gradBasis
where p.ID == this.DrplstGrade1.GradeId
select p).FirstOrDefault();
var course = (from p in courseBasis
//where p.ID == this.DrplstCourse1.CourseId ----KEN's Mark
select p).FirstOrDefault();
var oldOrgStudent = (from p in orgStudent
select p).ToList();
var myOrg = (from p in tb.Where(d => !string.IsNullOrEmpty(d.OrganizationName))
select new
{
p.OrganizationName,
p.OrganizationNo,
p.ClassTypeName,
p.RoomName,
p.CourseName,//
p.SubjectName//
}).Distinct().ToList();
var Defaultclass = db.tbClassBasis.Where(d => d.RecordStatus == decimal.One && d.ID == GuidDefault).FirstOrDefault();
foreach (var c in myOrg)
{
var org = new SMSPModel.tbOrganizationBasis
{
ID = Guid.NewGuid(),
OrganizationNo = ConvertToDecimal(c.OrganizationNo),
OrganizationName = c.OrganizationName,
tbYear = year,
tbGradeBasis = grad,
tbCourseBasis = courseBasis.Where(o => o.CourseName == c.CourseName).FirstOrDefault(), //course,
tbBuildRoom = roomBasis.Where(d => d.RoomName == c.RoomName).FirstOrDefault() ?? roomBasis.Where(d => d.ID == GuidDefault).FirstOrDefault(),
tbClassBasis = Defaultclass,
tbTeacherJob = jobBasis.Where(d => d.ID == GuidDefault).FirstOrDefault(),
tbClassType = classTypeBasis.Where(d => d.TypeName == c.ClassTypeName).FirstOrDefault(),
tbArrangeCourseBasis = arrangeCourseBasis,
RecordStatus = decimal.One,
UpdateTime = DateTime.Now
};
db.AddTotbOrganizationBasis(org);
}
db.SaveChanges();
var newOrg = (from p in db.tbOrganizationBasis
where p.RecordStatus == decimal.One
&& p.tbYear.ID == this.DrplstYear1.YearId
select p).ToList();
foreach (var t in tb)
{
if (!string.IsNullOrEmpty(t.StudentCode) && !string.IsNullOrEmpty(t.StudentName))
{
var stu = new SMSPModel.tbOrganizationStudent
{
ID = Guid.NewGuid(),
tbOrganizationBasis = newOrg.Where(d => d.OrganizationName == t.OrganizationName && d.tbCourseBasis.CourseName == t.CourseName && d.tbCourseBasis.tbSubjectBasis.SubjectName == t.SubjectName).FirstOrDefault(),
SeatNo = t.SeatNo == null ? decimal.Zero : ConvertToDecimal(t.SeatNo),
tbStudentBasis = studentBasis.Where(d => d.StudentCode == t.StudentCode).FirstOrDefault(),
RecordStatus = decimal.One,
UpdateTime = DateTime.Now
};
db.AddTotbOrganizationStudent(stu);
}
}
var myTeacher = (from p in tb.Where(d => !string.IsNullOrEmpty(d.TeacherName))
select new
{
p.TeacherName,
p.OrganizationName
}).Distinct().ToList();
foreach (var mt in myTeacher)
{
var tea = new SMSPModel.tbOrganizationTeacher
{
ID = Guid.NewGuid(),
tbOrganizationBasis = newOrg.Where(d => d.OrganizationName == mt.OrganizationName).FirstOrDefault(),
tbTeacherBasis = teacherBasis.Where(d => d.TeacherName == mt.TeacherName).FirstOrDefault(),
RecordStatus = decimal.One,
UpdateTime = DateTime.Now
};
db.AddTotbOrganizationTeacher(tea);
}
var myArrangeCourse = (from p in tb.Where(d => !string.IsNullOrEmpty(d.WeekName) && !string.IsNullOrEmpty(d.PeriodName))
select new
{
p.OrganizationName,
p.WeekName,
p.PeriodName
}).Distinct().ToList();
foreach (var t in myArrangeCourse)
{
var te = new SMSPModel.tbArrangeCourseResult
{
ID = Guid.NewGuid(),
tbOrganizationBasis = newOrg.Where(d => d.OrganizationName == t.OrganizationName).FirstOrDefault(),
tbWeekBasis = weekBasis.Where(d => d.WeekName == t.WeekName).FirstOrDefault(),
tbPeriodBasis = periodBasis.Where(d => d.PeriodName == t.PeriodName).FirstOrDefault(),
RecordStatus = decimal.One,
UpdateTime = DateTime.Now
};
db.AddTotbArrangeCourseResult(te);
}
db.SaveChanges();
ShowMessage("教学班导入成功！");
}
这篇关于excel导入数据到sqlserver的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解，规避错误)
