业务中经常会遇到批量导入数据库的操作,今天总结一下方法.
本文利用三层的架构的思想。将整个业务分成三部分,分别是界面,业务和数据处理。
先介绍第一种方法:
即先将Excel表格的内容转化到DataTable中,然后再将DataTable导入到SqlServer数据库.
下面看一个小demo
界面上用到的控件是FileUpload和Button
protected void btnImport_Click(object sender, EventArgs e)
{
string path;//定义文件保存的路径
// 检查FileUpload是否有指定文件
if (fupImport.PostedFile == null || fupImport.PostedFile.FileName == "")
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;
}
//System.IO.Path.GetExtension获得文件的扩展名
string fileExt = System.IO.Path.GetExtension(fupImport.FileName).ToString().ToLower();
if (fileExt == ".xls" || fileExt == ".xlsx")
{
//给文件设置路径
path = "TempPlace/" + this.fupImport.FileName.ToString().Trim();
//将虚拟路径转化为物理路径
path = Server.MapPath(path);
fupImport.SaveAs(path);
//调用b层的方法把Excel写入到数据库
if (new BLL().ExcelToDataTable(path))
{
//成功导入到sql
Response.Write("<script>alert('导入成功')</script>");
}
}
else
{
//当选择的不是Excel文件时,返回
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;
}
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
else
{
Response.Write("该Excel不存在");
}
}
逻辑层的主要任务是将界面传过来的Excel转化到DataTable中,并将DataTable传给Dal层处理.因为读取Excel的操作不是针对的Sql,而是用的OLEDB,所以放到了逻辑层.
/// <summary>
/// 将坐在路径的Excel中的内容写入到DataTable中
/// </summary>
/// <param name="strPath">需要导入的Excel的路径</param>
/// <returns>返回true 或false</returns>
public bool ExcelToDataTable(string strPath)
{
DataTable DT = new DataTable();
//设置数据连接
// HDR = "YES",第一行是标题 ,则第一行不写如数据库;
//HDR = "NO" 第一行是数据 ,则第一行写入数据库; 这个选择可以由界面当作参数传下来.
OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + strPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES'");
//打开连接
objConn.Open();
try
{
//获取 Excel 的表名,默认值是sheet1
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
sheetName = schemaTable.Rows[0][2].ToString().Trim();
//Sql语句
//string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
//string strExcel = "select * from [" + sheetName + "]";
string strExcel = "select * from [Sheet1$]";
//定义存放的数据表
DataSet ds = new DataSet();
//连接数据源
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, objConn);
//将Excel表中内容填入ds中
adapter.Fill(ds, sheetName);
//调用d层方法
return new DAL().BatchInsertData("user_Info", ds.Tables[sheetName]);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//关闭连接
objConn.Close();
objConn.Dispose();
}
}
D层,具体处理,将得到的DataTable批量导入到Sql中
/// <summary>
/// 将传过来的DataTable导入到Sql
/// </summary>
/// <param name="strTableName">要导入的sql数据库表名</param>
/// <param name="dt">待导入的数据表</param>
/// <returns></returns>
public bool BatchInsertData(string strTableName, DataTable dt)
{
bool blnResult;
//初始化连接
string strConn = "server=192.168.*.*;database=Student;uid=sa;pwd=123";
SqlConnection objConn = new SqlConnection(strConn);
objConn.Open();
//数据批量导入sqlserver,创建实例
System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(objConn);
//目标数据库表名
sqlbulk.DestinationTableName = strTableName;
try
{
//数据集字段索引与数据库字段索引映射
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlbulk.ColumnMappings.Add(i, i);
}
//导入
sqlbulk.WriteToServer(dt);
//导入成功
blnResult = true;
}
catch (Exception)
{
blnResult = false;
throw;
}
finally {
//关闭连接
sqlbulk.Close();
objConn.Close();
}
return blnResult;
}
这样通过两次调用,Excel就可以顺利导入数据库了,这种方法必须是数据库字段>=Excel表的字段,而且导入时是默认从左到右字段一一对应,如果不需要导入和数据库相等的字段,且字段不连续,可以用数组进行字段的控制.
在数据库进行映射时的代码如下,其他代码相同,不再赘述.
int[] intSqlFeild = { 0, 7 };
for (int i = 0; i < dt.Columns.Count; i++)
{
//建立映射
sqlbulk.ColumnMappings.Add(i , intSqlFeild[i]);
}
通过这种方式可以将Excel表的数据和数据库灵活绑定.但相对来说代码较多.
ps:由于本文着重实现功能,这个demo的复用性可能不好,如果工程中很多表,很多数据库中需要用到,可以进行重构,将关于表和数据库的内容提取出来,写成要传的参数或者配置文件.B层和D层的方法也可以更细的拆分.