vb中读取大数据量文件到DB中（oracle）

1:利用oracle的load

2:oracle10g以上版本：Oracle.DataAccess

3:Data.OleDb.OleDbDataAdapter

1利用oracle的load

        Dim strBu As String = "sqlldr jjh/jjh@jjh control=g:load.ctl"

        Dim psi As ProcessStartInfo = New ProcessStartInfo("cmd.exe")
        psi.UseShellExecute = False
        psi.RedirectStandardOutput = True
        psi.RedirectStandardInput = True
        psi.RedirectStandardError = False
        psi.CreateNoWindow = True
        psi.WorkingDirectory = "g:"
        Dim process1 As Process = Process.Start(psi)

        process1.StandardInput.WriteLine(strBu)
        process1.StandardInput.WriteLine("exit")

        Dim output As String
        Dim strErr As String
        Try
            output = process1.StandardOutput.ReadToEnd()
            strErr = process1.StandardError.ReadToEnd()
        Catch ex As Exception
        Finally
            If Not process1.HasExited Then
                process1.Close()
            End If

        End Try

  load.ctl:

OPTIONS (ERRORS=1000000000,ROWS=64,BINDSIZE=204800)
LOAD DATA CHARACTERSET UTF8
INFILE "G: estLoader.csv"
BADFILE "g:ad.txt"
TRUNCATE
INTO TABLE test5
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """
TRAILING NULLCOLS (COL1,COL2, COL3 SEQUENCE(MAX, 1))

2oracle10g以上版本：Oracle.DataAccess

        Dim vn_insert As Integer = 100000
        Dim txt_col1(vn_insert) As String
        Dim txt_col2(vn_insert) As String
        Dim txt_col3(vn_insert) As String
        Dim cmd As New Oracle.DataAccess.Client.OracleCommand

        Dim par(3) As Oracle.DataAccess.Client.OracleParameter

        Dim cnn As New Oracle.DataAccess.Client.OracleConnection("Data Source=kEIRI02_172.19.71.65;User Id=cpas;Password=cpas; ")
        "Dim cnn As New Oracle.DataAccess.Client.OracleConnection("Data Source=JJH;User Id=JJH;Password=JJH; ")

        Dim strSql As String

        cnn.Open()
        cmd = cnn.CreateCommand()
        cmd.ArrayBindCount = vn_insert

        Dim j As Integer
        For j = 0 To 10000

            txt_col1(j) = "col1__" & j
            txt_col2(j) = "col2__" & j
            txt_col3(j) = "col3__" & j
        Next

        Try
            cmd.Parameters.Add("1", Oracle.DataAccess.Client.OracleDbType.Varchar2, txt_col1, ParameterDirection.Input)
            cmd.Parameters.Add("2", Oracle.DataAccess.Client.OracleDbType.Varchar2, txt_col2, ParameterDirection.Input)
            cmd.Parameters.Add("3", Oracle.DataAccess.Client.OracleDbType.Varchar2, txt_col3, ParameterDirection.Input)

            strSql = "Insert   into   test5(col1,col2,col3)   values(:1,:2,:3) "
            cmd.CommandText = strSql
            cmd.ExecuteNonQuery()

            MessageBox.Show(strSql)
        Catch ex As Exception
            ex.GetBaseException()
        End Try

        Me.Close()

3Data.OleDb.OleDbDataAdapter

         Dim vards2 As New DataSet
        Dim vn_insert As Integer = 100000
        Dim cnn As New System.Data.OleDb.OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=kEIRI02_172.19.71.65;User Id=cpas;Password=cpas")
        Dim cmd As New System.Data.OleDb.OleDbCommand("select col1, col2, col3 from test5", cnn)
        Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
        da.FillSchema(vards2, SchemaType.Mapped)

        Dim i As Integer
        For i = 0 To vn_insert
            vards2.Tables(0).Rows.Add(New Object() {"col1", "col2", "col3"})
        Next

        Dim cb As New System.Data.OleDb.OleDbCommandBuilder(da)
        cnn.Open()
        da.Update(vards2)

        cnn.Close()

总结：感觉能用1,2方法最好，速度很快