客户需要将一个具有2W多条数据的Excel表格中的数据导入到Oracle数据库的A表中,开始采用的是利用Oledb直接将数据读入到DataTable中,然后通过拼接InserInto语句来插入到数据库表A中.然后做好以后,发现经常提示Invalidate Character错误.但是将拼接好的SQL拿到PLSQL中执行,一切完好.最后没办法,为了缩小错误范围,开始利用begin end分段来做.具体方式为:
每隔1000条数据,加上形如 Begin ...这里是1000条数据... End;Commit; 的标记
具体后台代码如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151 |
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往上递增的,出现问题的时候,也会提示哪次插入出现错误了,很方便.
Excel大数据量分段导入到Oracle,布布扣,bubuko.com
原文：http://www.cnblogs.com/shangshen/p/3592229.html