hi all
i write this code to load text file (undelimeted) to access database
my problem each record take from 1 to 2 second to loaded into datasource
Imports System
Imports System.Diagnostics
Imports System.Threading
Imports System.Transactions
Public Class Form1
Dim trd As Threading.Thread
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
trd.Abort()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim trd As New Thread(AddressOf loaddata)
Dim value As Boolean
value = trd.IsBackground
trd.IsBackground = value
trd.Start()
end sub
Private Sub loaddata()
Stat1DataSet.EnforceConstraints = False
Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\db\ult_icd9c.mdb")
conn.Open()
Dim tfp As New FileIO.TextFieldParser("x:\icd9.txt") 'replace "c:\datafile.txt" with the actual file name & path
'Dim tfp As New FileIO.TextFieldParser("C:\txtFilesFolder\tsticd9.txt") 'replace "c:\datafile.txt" with the actual file name & path
tfp.TextFieldType = FileIO.FieldType.FixedWidth
tfp.FieldWidths = New Integer() {6, 7, 29, 1, 10, 6, 7, 9, 9, 13, 15, 3, 4, 9, 2, -1} 'these integers should be a comma seperated list of all the field widths
tfp.HasFieldsEnclosedInQuotes = False 'set this property to true if the field values are enclosed in quotes
Dim cmd As New OleDb.OleDbCommand("Select Count(*) from Tsticd9", conn)
If cmd.ExecuteScalar > 1 Then
MsgBox("Tsticd9 not empty Rowcount of Tsticd9 = " & cmd.ExecuteScalar)
Me.Tsticd91TableAdapter1.DeleteQuery()
' End
End If
Dim j As Integer = 0
While Not tfp.EndOfData
Try
Dim vals() As String = tfp.ReadFields
Dim drnew As DataRow = Stat1DataSet.Tsticd91.NewRow
For i As Integer = 0 To Stat1DataSet.Tables(0).Columns.Count - 1
If vals(i).Trim.Length > 0 And Not vals(8) = "000000000" Then
drnew.Item(i) = vals(i)
End If
Next
Dim stopWatch As New Stopwatch()
stopWatch.Start()
Stat1DataSet.Tables(0).Rows.Add(drnew)
j = j + 1
Me.Tsticd91TableAdapter1.Update(Stat1DataSet.Tsticd91)
'Me.Tsticd91TableAdapter1.Update(Stat1DataSet.Tables(0))
stopWatch.Stop()
' Get the elapsed time as a TimeSpan value.
Dim ts As TimeSpan = stopWatch.Elapsed
' Format and display the TimeSpan value.
labelTime.Text = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", _
ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10)
Label1.Text = j
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
End While
MsgBox("Rowcount of Tsticd9 = " & Stat1DataSet.Tsticd91.Rows.Count)
conn.Close()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
trd.Abort()
End
End Sub
End Class