前面已经介绍过如何向数据库的一张表中批量导入数据,详情见博客《项目经验---简单三层使用DataTable向数据库表批量导入数据---向SqlServer一张表中导入数据》;本文主要介绍如何向SqlServer的多张表中批量导入数据。
如今有这样一个需求,需要批量导入建筑信息,依据数据库表结构设计,批量导入建筑信息,需要向数据库相应的三张表都写入数据。
看一下数据库表结构设计:
在建筑表(TB_Building)中,BuildingID是主键,在建筑类型表(TB_BuildingType)中BuildingTypeID是主键,建筑表与建筑类型之间的关系通过第三张表(建筑类型关系表《TBR_BuildingTypeLink》)来维护,建筑与建筑类型关系表保证了数据的唯一性!
导入建筑信息,需要同时导入建筑与建筑类型的关系,还需要导入建筑与校区的关系(哪一个校区的建筑)。
下面借助《批量导入建筑信息》的实例讲解批量向数据库多张表导入数据的方法。
1.界面设计
观看一下批量导入建筑信息的界面设计:
2.框架结构
此项目的实现,我依然采用简单的三层,看一下项目框架
3.批量向数据库多张表导入数据的实现
下面逐层介绍向数据库导入数据各层的代码实现:
3.1 SqlHelper数据库助手类中添加向数据库表导入数据的方法(SqlHelper.cs)
#region 批量导入DataTable
/// <summary>批量导入DataTable
/// 批量导入DataTable
/// </summary>
/// <param name="dt">DataTable数据表</param>
/// <param name="tableName">表名</param>
/// <param name="dtColum">数据列集合</param>
/// <return>Boolean值:true成功,false失败</return>
public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)
{
//打开数据库
GetConn();
try
{
//声明SqlBulkCopy ,using释放非托管资源
using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn))
{
//一次批量的插入的数据量
sqlBC.BatchSize = 1000;
//超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 60;
//設定 NotifyAfter 属性,以便在每插入10000 条数据时,呼叫相应事件。
//sqlBC.NotifyAfter = 10000;
// sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
//设置要批量写入的表
sqlBC.DestinationTableName = tableName;
//自定义的datatable和数据库的字段进行对应
//sqlBC.ColumnMappings.Add("id", "tel");
//sqlBC.ColumnMappings.Add("name", "neirong");
for (int i = 0; i < dtColum.Count; i++)
{
sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());
}
//批量写入
sqlBC.WriteToServer(dt);
}
// conn.Dispose();
//GetConn();
return true;
}
catch
{
return false;
}
finally
{
//关闭数据库
sqlConn.Close();
}
}
#endregion
3.2
DAL层批量导入数据的代码(BuildingDAL.cs)
#region 批量添加建筑信息 /// <summary> /// 建筑管理:批量添加建筑信息: /// 1.向建筑表添加建筑基本信息 /// 2.向建筑与建筑类型关联表添加建筑与建筑类型的关联信息 /// 3.向建筑与校区关联表添加建筑与校区的关联信息 /// </summary> /// <summary> /// 批量导入建筑信息 /// </summary> /// <param name="ds">DataSet-ds;</param> public Boolean ImportBuilding(DataSet ds) { //定义布尔型标记变量,记录建筑是否添加成功 //添加建筑信息 Boolean flagAddBuilding; //添加建筑与建筑类型的关系 Boolean flagAddBuildingType; //添加建筑与校区的关系 Boolean flagAddBuildingCampus; //执行隐式事务:报错--此操作对该状态的事务无效 的错误于是去掉了事务 //using (TransactionScope scope = new TransactionScope()) //{ //调用sqlHelper的"批量导入datatable表"的方法 flagAddBuilding = sqlHelper.InsertTable(ds.Tables["dt_AddBuilding"], "TB_Building", ds.Tables["dt_AddBuilding"].Columns); //调用sqlHelper的"批量导入datatable表"的方法 flagAddBuildingType = sqlHelper.InsertTable(ds.Tables["dt_AddBuildingType"], "TBR_BuildingTypeLink", ds.Tables["dt_AddBuildingType"].Columns); //调用sqlHelper的"批量导入datatable表"的方法 flagAddBuildingCampus = sqlHelper.InsertTable(ds.Tables["dt_AddBuildingCampus"], "TBR_BuildingCampusLink", ds.Tables["dt_AddBuildingCampus"].Columns); //} //返回结果 return (flagAddBuilding && flagAddBuildingType && flagAddBuildingCampus); } #endregion
3.3
BLL层批量导入数据的代码(BuildingBLL.cs)
#region 批量导入建筑信息 /// <summary> /// 批量导入建筑信息 /// </summary> /// <param name="ds">DataSet</param> /// <returns>是否导入成功:true成功,false失败</returns> public Boolean ImportBuiding(DataSet ds) { return buildingDAL.ImportBuilding(ds); } #endregion
3.4 界面层构造DataSet数据,向BLL层传递
这里我依然采用从界面上传Excel,然后从Excel获取输入然后存入DataSet的各DataTable表中。
3.4.1 界面层HTML代码(AddBuilding.aspx)
<div class="block"> <div class="h"> <span class="icon-sprite icon-list"></span> <h3>批量导入建筑</h3> <div class="bar"> <a class="btn-lit" href="javascript:history.go('<% =returnCount %>')"><span>返回</span></a> </div> </div> <div class="tl corner"></div> <div class="tr corner"></div> <div class="bl corner"></div> <div class="br corner"></div> <div class="cnt-wp"> <div class="cnt form"> <%--<form method="post" enctype="multipart/form-data" action="">--%> <table class="data-form" cellspacing="0" cellpadding="0"> <tr><td> <asp:Label ID="Label1" runat="server" Text="提示:请导入扩展名为.xls的文件【工作薄名:Sheet1;表头(如下):】"></asp:Label> </td> </tr> <tr><td> <img src="../uploadFileImage/importBuilding.jpg" alt=""/></td> </tr> <tr> <asp:FileUpload ID="fupImportBuilding" runat="server" /> <asp:Button ID="btnImportBuilding" runat="server" Text="批量导入" OnClick="btnImportBuilding_Click" /> </tr> </table> <%-- </form>--%> </div> </div> </div>
3.4.2 从界面传入Excel,调用BLL层Excel转换成DataTable的方法(AddBuilding.aspx.cs)
#region 批量导入建筑信息 protected void btnImportBuilding_Click(object sender,EventArgs e) { //建筑业务逻辑层 BuildingBLL buildingBLL = new BuildingBLL(); //BLL层把Excel转化为datatable类 CreateExcelDataBLL createExcelData = new CreateExcelDataBLL(); //获取上传文件地址 string url = fupImportBuilding.PostedFile.FileName.ToString(); if (url == "") { //数据源为空,弹出提示:请选择Excel文件! Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择Excel文件!');</script>"); return; } string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名 DataTable dtAllBuildingCampusType; //在系统中建文件夹up,并将excel文件另存 this.fupImportBuilding.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中 //获得文件路径 string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation; //把上传的Excel转换为datatable dtAllBuildingCampusType = createExcelData.CreateExcelDataSource(strpath); DataSet dsBuildingCampusType = new DataSet("ds_BuilingCampusType"); //创建一个名为ds_BuilingCampusType的DataSet //手动创建的新数据表-建筑数据表 DataTable dtAddBuilding = new DataTable("dt_AddBuilding"); //创建一个名为dt_AddBuilding的DataTalbe //为dt_AddBuilding表内建立Column(表头),添加数据列:建筑ID、建筑代码、建筑名、是否可用、备注、操作类型、操作内容、操作原因、操作人、操作时间 dtAddBuilding.Columns.Add(new DataColumn("BuildingID", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("BuildingCode", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("BuildingName", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("IsAvailable", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("Descriptions",typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("ActionType", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("ActionContent", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("ActionReason", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("Operator", typeof(string))); dtAddBuilding.Columns.Add(new DataColumn("SetDatetime", typeof(DateTime))); //手动创建的新数据表-建筑、建筑类型关系数据表 DataTable dtAddBuildingType = new DataTable("dt_AddBuildingType"); //创建一个名为dt_AddBuildingType的DataTalbe //为dt_AddBuildingType表内建立Column(表头),添加数据列:建筑ID、建筑类型ID、是否可用 dtAddBuildingType.Columns.Add(new DataColumn("BuildingID", typeof(string))); dtAddBuildingType.Columns.Add(new DataColumn("BuildingTypeID", typeof(string))); dtAddBuildingType.Columns.Add(new DataColumn("IsAvailable", typeof(string))); //手动创建的新数据表-建筑、校区关系数据表 DataTable dtAddBuildingCampus = new DataTable("dt_AddBuildingCampus"); //创建一个名为dt_AddBuildingCampus的DataTalbe //为dt_AddBuildingCampus表内建立Column(表头),添加数据列:建筑ID、校区ID、是否可用 dtAddBuildingCampus.Columns.Add(new DataColumn("BuildingID", typeof(string))); dtAddBuildingCampus.Columns.Add(new DataColumn("CampusID", typeof(string))); dtAddBuildingCampus.Columns.Add(new DataColumn("IsAvailable", typeof(string))); //从上传的Excel转换为的datatable表中取出数据,分别放入建筑信息表、建筑与建筑类型关系表、建筑与校区关系表 for (int intRow = 0; intRow < dtAllBuildingCampusType.Rows.Count; intRow++) { //建筑ID string strBuildingID = Guid.NewGuid().ToString(); //建筑代码 string strBuildingCode= dtAllBuildingCampusType.Rows[intRow]["建筑代码"].ToString(); //建筑名 string strBuildingName = dtAllBuildingCampusType.Rows[intRow]["建筑名"].ToString(); //建筑备注 string strBuildingDescription=dtAllBuildingCampusType.Rows[intRow]["备注"].ToString(); //建筑类型代码 string strBuildingTypeCode = dtAllBuildingCampusType.Rows[intRow]["建筑类型代码"].ToString(); //校区代码 string strCampusCode=dtAllBuildingCampusType.Rows[intRow]["校区代码"].ToString(); //根据建筑类型代码获取建筑类型ID BuildingTypeEntity enBuildingType = new BuildingTypeEntity(); //建筑类型代码 enBuildingType.BuildingTypeCode = strBuildingTypeCode; //建筑业务逻辑层,按建筑类型代码查询建筑类型ID DataTable dtBuildingType = new BuildingBLL().QueryBuildingTypeByCode(enBuildingType); //建筑类型ID为 string strBuildingTypeID = dtBuildingType.Rows[0]["BuildingTypeID"].ToString(); //根据校区代码获取校区ID CampusEntity enCampus = new CampusEntity(); //校区代码 enCampus.CampusCode = strCampusCode; //建筑业务逻辑层,按校区代码查询校区ID DataTable dtCampus = new BuildingBLL().QueryCampusByCode(enCampus); //校区ID string strCampusID=dtCampus.Rows[0]["CampusID"].ToString(); //添加建筑信息表的新行 DataRow drAddBuilding = dtAddBuilding.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow,然后不用new直接的用创建的DataTable下面的NewRow方法。 //建筑信息表对应的各列值 drAddBuilding["BuildingID"]=strBuildingID; drAddBuilding["BuildingCode"] = strBuildingCode; drAddBuilding["BuildingName"] = strBuildingName; drAddBuilding["IsAvailable"] = "是"; drAddBuilding["Descriptions"] = strBuildingDescription; drAddBuilding["ActionType"] = null; drAddBuilding["ActionContent"] = null; drAddBuilding["ActionReason"] = null; drAddBuilding["Operator"] = null; drAddBuilding["SetDatetime"] = DateTime.Now; //当前日期时间 dtAddBuilding.Rows.Add(drAddBuilding); //将一整条数据写入表中 //添加建筑与建筑类型关系信息表的新行 DataRow drAddBuildingType = dtAddBuildingType.NewRow(); //建筑与建筑类型关系信息表对应的各列值 drAddBuildingType["BuildingID"] = strBuildingID; drAddBuildingType["BuildingTypeID"] = strBuildingTypeID; drAddBuildingType["IsAvailable"] = "是"; dtAddBuildingType.Rows.Add(drAddBuildingType); //将一整条数据写入表中 //添加建筑与校区关系信息表的新行 DataRow drAddBuildingCampus = dtAddBuildingCampus.NewRow(); //建筑与校区关系信息表对应的各列值 drAddBuildingCampus["BuildingID"] = strBuildingID; drAddBuildingCampus["CampusID"] = strCampusID; drAddBuildingCampus["IsAvailable"] = "是"; dtAddBuildingCampus.Rows.Add(drAddBuildingCampus); //将一整条数据写入表中 } //将各表加入DataSet中:建筑信息、建筑与建筑类型关系信息、建筑与校区关系信息 dsBuildingCampusType.Tables.Add(dtAddBuilding); dsBuildingCampusType.Tables.Add(dtAddBuildingType); dsBuildingCampusType.Tables.Add(dtAddBuildingCampus); //将DataSet中数据表导入数据库 Boolean flagImportBuilding = buildingBLL.ImportBuiding(dsBuildingCampusType); if (true==flagImportBuilding) { //导入成功,弹出提示 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('建筑信息导入成功!');</script>"); } else { //导入失败,弹出提示 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('建筑信息导入失败!');</script>"); } } #endregion
3.4.3 BLL层Excel转换成DataTable的类(MgrCreateExcelData.cs)
/******************************************************************************* *文 件:CreateExcelDataBLL.cs *作 者:mzj *所属小组:评教小组 *文件说明:基础系统-把excel转化为datatable *创建日期:2013年1月23日9:43:16 *修改作者: *修改日期: *修改描述: *版 本 号:V1.0 *版本号变更记录: ********************************************************************************/ using System; using System.Collections.Generic; using System.Linq; using System.Text; //引用各命名空间 using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; namespace TeachSystem.BLL.CreateExcelDataBLL { public class CreateExcelDataBLL { //构造方法 public CreateExcelDataBLL() { } /// <summary> /// 传入excel路径,转换为datatable /// </summary> /// <param name="url"></param> /// <returns></returns> public DataTable CreateExcelDataSource(string url) { //定义一个DataTable数据表 DataTable dt = null; //获得excel数据 string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; //从Excel表的Sheet1单元格获取数据 string strSql = "select * from [Sheet1$]"; OleDbConnection oleConn = new OleDbConnection(connetionStr); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr); try { //把Excel数据填充给DataTable dt = new DataTable(); oleAdapter.Fill(dt); //返回数据表 return dt; } catch (Exception ex) { throw ex; } finally { oleAdapter.Dispose(); oleConn.Close(); oleConn.Dispose(); //删除上传的Excel文件(因为该文件的存在会占用多余的网站空间) if (File.Exists(url)) { File.Delete(url); } } } } }
至此批量向数据库多张表导入数据的功能已完成了,对想数据库表导入的数据的合法性还需要自己通过代码进进行判断!