Excel大数据量分段导入到Oracle
客户需要将一个具有2W多条数据的Excel表格中的数据导入到Oracle数据库的A表中,开始采用的是利用Oledb直接将数据读入到DataTable中,然后通过拼接InserInto语句来插入到数据库表A中.然后做好以后,发现经常提示Invalidate Character错误.但是将拼接好的SQL拿到PLSQL中执行,一切完好.最后没办法,为了缩小错误范围,开始利用begin end分段来做.具体方式为:
每隔1000条数据,加上形如 Begin ...这里是1000条数据... End;Commit; 的标记
具体后台代码如下:
using System;
using System.Data;
using System.Data.OracleClient;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using RedGlovePermission.Lib;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Web.UI;
public partial class YQKH_YQKH_OACL : BasePageWithLog,ICallbackEventHandler
{
private BusinessLogicLayer.ICommonBLL _CommonBLL;
public BusinessLogicLayer.ICommonBLL CommonBLL
{
get
{
return _CommonBLL;
}
set
{
_CommonBLL = value;
}
}
public static string result = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
this.DBGridConfig1.openURL = "../CommData/GetDataByParameter.aspx?sqlKey=t_yqkh_oa_mx"; //加载需要进行导入的表中的数据
}
protected void toExcel_Click(object sender, EventArgs e)
{
if (UploadExcel.HasFile) //如果存在上传文件
{
string filename = UploadExcel.PostedFile.FileName;
String strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", filename);//serverpath是Excel表格路径
using (OleDbConnection Excel_conn = new OleDbConnection(strConnectionString)) //连接Excel,和连接MSSQL数据库的方式类似
{
Excel_conn.Open(); //打开连接
System.Data.DataTable dtExcelSchema = Excel_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//建立连接Excel的数据表
string SheetName = "";
SheetName = dtExcelSchema.Rows[3]["TABLE_NAME"].ToString();//取需要的工作表的名称
System.Data.DataTable dt = new System.Data.DataTable();
try
{
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format(" select * from [{0}] ", SheetName); //取工作表中的所有数据
myCommand = new OleDbDataAdapter(strExcel, Excel_conn);
myCommand.Fill(dt);
}
catch (Exception ex){}
using (OracleConnection oraconn = new OracleConnection(ConfigurationManager.AppSettings["SQLString"].ToString())) //打开oracle数据库
{
oraconn.Open();
IList<StringBuilder> _list = ExecInsertIntoOracleFromExcel(dt); //list中保存的是多个需要提交的事物段
foreach (StringBuilder sb in _list) //循环,按照段来执行
{
OracleCommand oracmd = new OracleCommand(sb.ToString(), oraconn);
oracmd.ExecuteNonQuery();
}
//Response.Write("导入成功!");
ClientScript.RegisterClientScriptBlock(Page.GetType(), "alertSuccess", "<script>alert('导入成功!')</script>");
this.DBGridConfig1.openURL = "../CommData/GetDataByParameter.aspx?sqlKey=t_yqkh_oa_mx";
}
}
}
}
public int iCount = 0;
private IList<StringBuilder> ExecInsertIntoOracleFromExcel(System.Data.DataTable dt)
{
IList<StringBuilder> iList = new List<StringBuilder>();
try
{
this._CommonBLL.ExecuteQuery("delete from db_zgfz.t_yqkh_oa");
}
catch(Exception ex)
{
Page.ClientScript.RegisterClientScriptBlock(Page.GetType(),"alertFail","<script>删除数据失败!</script>");
}
if (dt != null)
{
if (dt.Rows.Count > 0)
{
StringBuilder sql = new StringBuilder();
sql.Append("begin ");
foreach (DataRow dr in dt.Rows)
{
iCount++;
sql.Append(" insert into db_zgfz.t_yqkh_oa values('" + dr["受理号"].ToString() + "','" +
dr["企业名称"].ToString() + "','" +
dr["事项名称"].ToString() + "','" +
dr["事项所属类别"].ToString() + "','" +
dr["事项ID"].ToString() + "','" +
dr["工作环节"].ToString() + "','" +
dr["部门"].ToString() + "','" +
dr["流经部门开始日期"].ToString() + "','" +
dr["流经部门结束日期"].ToString() + "'); ");
if (iCount % 1000 == 0) //每隔1000条数据,当做一段来执行,缩小出错范围
{
sql.Append(" commit; end; ");
iList.Add(sql);
sql = new StringBuilder();
sql.Append("begin ");
}
else if (dt.Rows.Count == iCount)
{
sql.Append(" commit; end; ");
iList.Add(sql);
}
}
}
}
return iList ;
}
#region ICallbackEventHandler 成员
public string GetCallbackResult() //主要显示当前插入了多少条数据
{
return "当前共导入:"+result+"条数据!";
}
public void RaiseCallbackEvent(string eventArgument)
{
string sql = "SELECT COUNT(*) FROM t_yqkh_oa";
System.Data.DataTable dtt = this._CommonBLL.ADONETHelper.GetDataTableBySQL(CommandType.Text, sql);
if (dtt != null)
{
if (dtt.Rows.Count > 0)
{
result = dtt.Rows[0][0].ToString();
}
}
}
#endregion
}
前台代码如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="YQKH_OACL.aspx.cs" Inherits="YQKH_YQKH_OACL" %>
<%@ Register Src="~/inc/DBGridConfig.ascx" TagName="DBGridConfig" TagPrefix="uc1" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>外部数据导入</title>
<script language="javascript" src="../XmlDataSet/javascript/System.js"></script>
<script language="javascript" src="../XmlDataSet/javascript/ajax.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/DataSet.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/Control.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/DBControl.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/DBNavigator.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/DBPageNavigator.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/DBGrid.js"></script>
<script language="JavaScript" src="../XmlDataSet/javascript/ToolBar.js"></script>
<script language="javascript" src="../XmlDataSet/javascript/DBSearchToolBar.js"></script>
<script language="javascript" src="../javascripts/Collection.js"></script>
<script language="javascript" type="text/javascript" src="../javascripts/validator.js"></script>
<link href="../css/dtree.css" rel="stylesheet" type="text/css" />
<link href="../css/tables.css" rel="stylesheet" type="text/css" />
<link href="../css/main.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
var jcl_ResourceURL = "../XmlDataSet/javascript/images/";
function rServer(arg,context)
{
document.getElementById("info").innerHTML=arg;
}
function raiseCallBack(arg, context) {
<%=ClientScript.GetCallbackEventReference(this,"arg","rServer","context") %>;
}
window.onload=function(){
raiseCallBack();
}
setInterval('raiseCallBack()',2000);
</script>
</head>
<body>
<form id="form1" runat="server">
<table><tr><td></td><td>
<table border="2" cellpadding="0" cellspacing="0" width="90%" align="center" style="border-collapse:collapse; text-align:center; ">
<thead>
<th align="center" colspan="4" style="font-size:large;">外部数据导入</th>
</thead>
<tbody>
<tr>
<td style="font-size:medium;" align="left"> </td>
<td style="font-size:medium;" align="left"> </td>
<td>
<asp:FileUpload ID="UploadExcel" runat="server" CssClass="inputButton" Width="300px" />
<asp:Button ID="toExcel" runat="server" Text="导入数据" CssClass="inputButton" onclick="toExcel_Click" />
</td>
<td id="info">
</td>
</tr>
<tr><td colspan="4"><hr /></td></tr>
<tr>
<td colspan="4">
<uc1:DBGridConfig ID="DBGridConfig1" runat="server" configKey="t_yqkh_oa_mx" isCanEdit="false" postURL="../CommData/UpdateData.aspx" />
</td>
</tr>
</tbody>
</table>
</td></tr></table>
</form>
</body>
</html>
这样,当插入的时候,不断的Select count(*) from A 语句,就会发现数据时呈1000往上递增的,出现问题的时候,也会提示哪次插入出现错误了,很方便.
