How to read multiple files from a folder?

[JenHu]

Hi experts,

I wrote a function which retrieves a file in the folder, the file path
is :
Dim sr As New StreamReader(strFilepath & ReturnFileName)

What if I have more than 1 file_name in EPay_Batch_Table Where
File_Status=2?

How to read multiple files and 1 file at a time, through a loop?

Thanks.


Public Function ProcessRET(ByRef strFilepath As String)
Dim EpayConnection As New SqlConnection
Dim GPConnection As New SqlConnection
Dim cmdBatchName As New SqlCommand
Dim dtrBatchName As SqlDataReader
Dim strRetFileName As String
EpayConnection.ConnectionString = strEpayDBConn
With cmdBatchName
.Connection = EpayConnection
.CommandText = "SELECT File_Name FROM EPay_Batch_Table
Where File_Status=2"
End With
EpayConnection.Open()
Try
dtrBatchName = cmdBatchName.ExecuteReader()
dtrBatchName.Read()
strRetFileName = dtrBatchName("File_Name")
Catch ex As Exception
MsgBox("All file status <> 2, No return file",
MsgBoxStyle.Information)
End Try
Dim ReturnFileName As String = strRetFileName.Replace(".in",
".RET")
EpayConnection.Close()
Dim flRetBatch As File
Dim stRetBatch As FileStream
Dim smRetBatch As StreamReader
Dim sr As New StreamReader(strFilepath & ReturnFileName)
'Open the text file into a stream reader
Try
............................
Catch
End Try

End Function

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Cor Ligthert]

JenHu,

I have seen your question more, however in my opinion are you confusing us
with so much of the sample.

You are reading from a database a file name, however what is the important
part in showing that, does that not work, or does the reading of the file
not work?

Maybe you can explain this a little bit more compact?

Cor

 

[JenHu]

Cor,

The code I am showing is that I am able to read one file and
process the content, however, I need help to read more than one file
in the folder.

In the code I posted, I can retrieve only 1 file (where file_Status=2
in Batch_Table) and process the data (I finished the part for the
data processing), and I will update the File_Status to 3 in
Batch_table for file retrieved.

But, what if I have more than 1 file_name in EPay_Batch_Table Where
File_Status=2? I want to look for if there is any other file and
process it, until there is no file_status=2 in Batch_Table.

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Cor Ligthert]

JenHu,

I think I understand what you want, and I can do it with a datatreader
however this sample is easier to make from your code. And see it as pseudo,
because I did type it in this message so there can be typos or other errors.
When you want this for more file status than you should use an
command.parameter, however than you should suply that. However it can be
that I still don't understand you.

\\\
Public Function ProcessRET(strEpayDBConn) as string()
Dim Conn As New SqlConnection(strEpayDBConn)
Dim ad As New SqlAdapter("SELECT File_Name FROM EPay_Batch_Table
Where File_Status=2")
dim dt as new datatable
try
conn.open
da(dt)
catch ex as exception
messagebox.show ex
finally
conn.dispose
end try
dim mytextarray(dt.rows.count)
for i as integer = 0 to dt.rows.count -1
dim dr as datarow = dt.rows(i)
Dim flRetBatch As File
if file.exist(dr(0).tostring then
try
Dim sr As New StreamReader(dr(0).tostring)
mytextarray(i) = sr.readtoend
catch ex as exception
messagebox.show(ex.tostring)
finally
sr.close
end try
end if
next
return mytextarray()
/////

However I hope this is in the right direction.

Cor

 

[JenHu]

Hi expert,

Thanks Cor. I thought about using datareader this morning, so I
wrote the following code by using datareader, by using
While dtrBatchName.Read().....End While

However, I got error: Invalid attempt to read when reader is closed on

"While dtrBatchName.Read() " statement while it's attempting to read
the 2nd filename.

How can I make sure the datareader is opened in this while loop?

Public Function ProcessRET(ByRef strFilepath As String)
Dim BatchNum As Int32
Dim sEmpID As String
Dim EFNum As Int64
Dim nErrorCode As Int32
Dim EpayConnection As New SqlConnection
Dim GPConnection As New SqlConnection
Dim cmdBatchName As New SqlCommand
Dim dtrBatchName As SqlDataReader
Dim strRetFileName As String
EpayConnection.ConnectionString = strEpayDBConn
With cmdBatchName
.Connection = EpayConnection
.CommandText = "SELECT File_Name FROM EPay_Batch_Table
Where File_Status=2"
End With
EpayConnection.Open()
dtrBatchName = cmdBatchName.ExecuteReader()
While dtrBatchName.Read()
Try
strRetFileName = dtrBatchName("File_Name")
Catch ex As Exception
MsgBox("No return file while all file status <>
2", MsgBoxStyle.Information)
End Try
Dim ReturnFileName As String =
strRetFileName.Replace(".in", ".RET")
EpayConnection.Close()
Dim flRetBatch As File
Dim stRetBatch As FileStream
Dim smRetBatch As StreamReader
'Open the text file into a stream reader
Dim sr As New StreamReader(strFilepath &
ReturnFileName)
Dim sRetLine As String
Dim sBatchNum As String
Dim sRecordType As String
Dim sEfundNum As String
Try
Dim lines() As String
Dim contents As String
Dim lineNum As Integer = 0
sRetLine = sr.ReadLine
'Return file heading record
If Left$(sRetLine, 1) = "H" Then
sBatchNum = (Mid$(sRetLine, 73, 13))
BatchNum = Convert.ToInt32(sBatchNum)
End If
While sr.Peek > -1
sRetLine = sr.ReadLine
'Return file enrollment/maintenance detail
records
If Left$(sRetLine, 1).ToString = "D" Or
Left$(sRetLine, 1).ToString = "R" Then
sEmpID = LTrim(Mid$(sRetLine, 2, 10))
EpayConnection.Open()
'Read lines and update transaction table
Select Case Left$(sRetLine, 1).ToString
Case "D"
Try
sEfundNum = (Mid$(sRetLine, 42,
13))
EFNum =
Convert.ToInt64(sEfundNum)
Dim strSQL As String
strSQL = "UPDATE
Epay_Transaction_Table SET EAcct_Num = " & EFNum & "
,Status_Ind=3, Enroll_Ind=3 , Error_Code = NULL, Error_Description =
NULL where EmployID='" & sEmpID & "' and Batch_Num= " &
BatchNum
Dim objComd = New
SqlCommand(strSQL, EpayConnection)
objComd.ExecuteNonquery()
EpayConnection.Close()
Catch err As Exception
MsgBox(err.Message)
End
End Try
Case "R"
Try
nErrorCode =
Convert.ToInt32(Mid$(sRetLine, 197, 3))
Dim strSQL As String
strSQL = "UPDATE
Epay_Transaction_Table SET Enroll_Ind=2, Status_Ind=2, Error_Code="
& nErrorCode & ", Error_Description=(SELECT Error_Description
FROM EPay_Error_Table WHERE error_Code=" & nErrorCode & ")
where EmployID='" & sEmpID & "' and Batch_Num=" &
BatchNum
Dim objComd = New
SqlCommand(strSQL, EpayConnection)
objComd.ExecuteNonquery()
EpayConnection.Close()
Catch err As Exception
MsgBox(err.Message)
End
End Try
End Select
End If
'Return file funding detail records
If Left$(sRetLine, 1) = "F" Then
End If
'Return file Trailer record, compare total return
records with total transaction records in the table
If Left$(sRetLine, 1) = "T" Then
Dim TotAcctOpened As Int32
Dim TotAcctRejected As Int32
Dim TotRetRecords As Int32
TotAcctOpened = Convert.ToInt32(Mid$(sRetLine,
2, 5))
TotAcctRejected =
Convert.ToInt32(Mid$(sRetLine, 7, 5))
TotRetRecords = TotAcctOpened +
TotAcctRejected
EpayConnection.Open()
Dim cmdTotRecord As New SqlCommand
Dim dtrTotRecord As SqlDataReader
With cmdTotRecord
.Connection = EpayConnection
.CommandText = "SELECT COUNT(*)As
TotBatchRecord FROM Epay_Transaction_Table WHERE Batch_Num =" &
BatchNum
End With
dtrTotRecord = cmdTotRecord.ExecuteReader()
dtrTotRecord.Read()
Dim strTotRecord As Int32
strTotRecord = dtrTotRecord("TotBatchRecord")
If TotRetRecords <> strTotRecord Then
MsgBox("Return file total records does not
match total records in database")
End If
EpayConnection.Close()
End If
End While
Catch err As Exception
MsgBox("File retrieve fail", err.Message)
End Try
End While

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

 

[Cor Ligthert]

Jenhu,

It is again a lot of code so not easy to understand however your exact
question I have showed in my previous answer. I explain it to you.

for i as integer = 0 to dt.rows.count -1
this is your while loop where I as told use the datatable, because it
compacts the problem so I forget that
dim dr as datarow = dt.rows(i)

if file.exist(path) then
' with this I prevent an error in a catch block when the file not exist
there should be an else in this to give an error, that I did not write in
this sample.
try
Dim sr As New StreamReader(dr(0).tostring)
'although it exist, can it be in use already so I set it inside the try
block
mytextarray(i) = sr.readtoend
'this is for you that while loop with the peek.
catch ex as exception
messagebox.show(ex.tostring)
finally
sr.close
'and this should be the answer on your question. Finally is for ever done,
even when the try block is not completed and it goes to a catch or even when
there is a return inside the try catch block. This close is forever done
with the exception when there was an me.close executed and the program is
stopped an "end" or when the power goes down.
end try
end if
next

I hope this give some idea's

Cor