Hi,
I am pasting the code to import data from Text/Excel files into Database(Oracle ) Table...
I think some one needs this .. they can use.. it...
=======================
Dim dsDB As New DataSet
Dim daDB As New OracleDataAdapter
Dim cmdDB As OracleCommand = New OracleCommand
Dim strRow As String
Dim sqlArr() As String 'Array which stores all queries of all records to be executed as a whole to database
Dim RetVal As Long = 0
'For Soucrec file
Dim DSSRC As New DataSet
Dim SourceFolder As String
Dim SRCConn As String
SourceFolder = G_SelectedFile.Substring(0, G_SelectedFile.LastIndexOf("\"))
If SourceFolder.Length = 2 Then 'if the file is there in c: or d: like that then we need to add to c:\ like that
SourceFolder = SourceFolder & "/"
End If
SRCConn = "MaxBufferSize=2048;FIL=excel 8.0;DSN=" & TBDSNName.Text.ToString.Trim() & ";PageTimeout=5;DefaultDir=C:\;DBQ=" & G_SelectedFile & ";DriverId=790"
Dim SRCDA As New OdbcDataAdapter("SELECT * FROM " & "[" & TxtSheet.Text & "$]", SRCConn)
Try
SRCDA.Fill(DSSRC, "Table")
Catch e As Exception
MessageBox.Show(e.Message.ToString(), G_MsgStr)
Exit Sub
End Try
'Destination DB Connections
If Cn.State <> ConnectionState.Open Then
If connect("internal", G_UID, G_Pwd, G_TnsName) = False Then
Exit Sub
End If
End If
cmdDB.CommandType = CommandType.Text
cmdDB.CommandText = "select * from " & CBOTNames.SelectedValue & " where 1>2" ' G_SelectedFile.Substring(G_SelectedFile.LastIndexO f("\") + 1).ToString().Trim()
cmdDB.Connection = Cn
daDB.SelectCommand = cmdDB
Try
daDB.Fill(dsDB, "Table")
daDB.AcceptChangesDuringFill = True
Catch ex As Exception
MessageBox.Show("Invalid or Unable to open the table" & ex.Message)
Exit Sub
End Try
dsDB.Merge(DSSRC, False, MissingSchemaAction.Ignore)
ReDim sqlArr(0) 'Initialises with 0 first
For RowiDx As Integer = 0 To dsDB.Tables(0).Rows.Count - 1
strRow = ""
For ColIdx As Integer = 0 To dsDB.Tables(0).Columns.Count - 1
If ((dsDB.Tables("Table").Rows(RowiDx)(ColIdx).GetTyp e.ToString() = "System.Decimal") Or (dsDB.Tables("Table").Rows(RowiDx)(ColIdx).GetType .ToString() = "System.Decimal")) Then
strRow = strRow & dsDB.Tables("Table").Rows(RowiDx)(ColIdx) & ", "
Else
strRow = strRow & "'" & dsDB.Tables("Table").Rows(RowiDx)(ColIdx) & "' , "
End If
Next
strRow = strRow.Remove(strRow.LastIndexOf(",") - 1, 2)
strRow = " insert into " & CBOTNames.SelectedValue & " values ( " & strRow & ")"
ReDim Preserve sqlArr(UBound(sqlArr) + 1)
sqlArr(UBound(sqlArr)) = strRow
Next
If UBound(sqlArr) > 0 Then RetVal = ExecuteDML(sqlArr)
If RetVal > 0 Then
MessageBox.Show("Data Has been transfered successfully", G_MsgStr)
Else
MessageBox.Show("Unable to Transfer " & G_SelectedFile & " records into " & CBOTNames.SelectedValue & "")
End If
End Sub
Public Function ExecuteDML(ByVal sql() As String) As Long
Dim RecordsAffected As Long
Dim DMlIdx As Long
ExecuteDML = 0
Dim cmd As New OracleCommand
Try
If Cn.State <> ConnectionState.Open Then
If connect("internal", G_UID, G_Pwd, G_TnsName) = False Then
Exit Function
End If
End If
cmd.Connection = Cn
TotSuccessRecords = 0
For DMlIdx = 1 To UBound(sql) 'starting of array is 1
TotSuccessRecords += 1
cmd.CommandText = sql(DMlIdx)
RecordsAffected = cmd.ExecuteNonQuery()
Next
ExecuteDML = RecordsAffected
Exit Function
Catch ex As Exception
cmd.Dispose()
End Try
End Function
--------------------------------
From: ramnaresh_t yadav
-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)
<Id>NJb2N1VLyEaEVamFsttr3A==</Id>
