Oracle快速批量导⼊数据
Oracle快速批量导⼊数据
Public Sub InsertBatchData(dt As DataTable)
Dim dtTime As DateTime = DateTime.Now
Dim conStr As String ="user id="⽤户名";password=“密码”;data source=“服务器”/orcl; Pooling=false;"
Dim conn As Oracle.ManagedDataAccess.Client.OracleConnection = New Oracle.ManagedDataAccess.Client.OracleConnection(conStr) Using(conn)
Dim command As Oracle.ManagedDataAccess.Client.OracleCommand = New Oracle.ManagedDataAccess.Client.OracleCommand()            conn.Open()
command.Connection = conn
'记录插⼊的条数
command.ArrayBindCount = dt.Rows.Count
'插⼊语句
command.CommandText = "INSERT INTO table_name(REGIST_NO,GUEST_NO,REGIST_TOP,BOOK,POINT)
values(:REGIST_NO,:GUEST_NO,:REGIST_TOP,:BOOK,:POINT)"
#Region "定义数组（同表中字段）须同数据库命名规范"
Dim REGIST_NO(dt.Rows.Count) As String
Dim GUEST_NO(dt.Rows.Count) As String
Dim REGIST_TOP(dt.Rows.Count) As String
Dim BOOK(dt.Rows.Count) As Decimal
#End Region
#Region "参数传递，须按照顺序⼀⼀对应，才可解析成功"
'1
Dim deptNoParam = New Client.OracleParameter("REGIST_NO", Client.OracleDbType.Varchar2)
deptNoParam.Direction = ParameterDirection.Input
deptNoParam.Value =REGIST_NO
command.Parameters.Add(deptNoParam)
'2
Dim deptNoParam2 = New Client.OracleParameter("GUEST_NO", Client.OracleDbType.Varchar2)
deptNoParam2.Direction = ParameterDirection.Input
deptNoParam2.Value =GUEST_NO
command.Parameters.Add(deptNoParam2)
'3
Dim deptNoParam3 = New Client.OracleParameter("REGIST_TOP", Client.OracleDbType.Varchar2)
deptNoParam3.Direction = ParameterDirection.Input
deptNoParam3.Value =REGIST_TOP
command.Parameters.Add(deptNoParam3)
'4
Dim deptNoParam4 = New Client.OracleParameter("BOOK", Client.OracleDbType.Decimal)
deptNoParam4.Direction = ParameterDirection.Input
deptNoParam4.Value =BOOK
command.Parameters.Add(deptNoParam4)
#End Region
#Region "将表格数据添加⾄数组中"
For i As Integer =0 To dt.Rows.Count -1
oracle decimal类型
REGIST_NO(i)= dt.Rows(i).Item("regist_no")
GUEST_NO(i)= dt.Rows(i).Item("guest_no")
REGIST_TOP(i)= dt.Rows(i).Item("regist_top")
BOOK(i)= dt.Rows(i).Item("book")
Next
#End Region
'这个调⽤将把参数数组传进SQL，同时写⼊数据库
command.ExecuteNonQuery()
End Using
代码实现较为简单，单较于传统意义上的Insert单句传输已经⾮常提⾼效率了，主要是通过以下⼏个步骤：
1.撰写带有参数的SQL语句
2.根据写⼊表的列列名和数据格式定义数组，数据格式转换如下表
数据格式对应表
4.循环遍历，将datatable中的数据转存⾄数组中
5.执⾏SQL语句
需注意的是：数组和Parameter的数据类型须和Oracle库中的表对应完全⼀致，且不能为空（⽬前只发现了这些问题，后续遇到待补充）
⽅式2
使⽤truncate清空全部数据主键⾃增长是从1开始(效率更⾼)
truncate table “表名字”

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。