Capture System.IO rows from incoming file and insert into table

Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.

1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL

Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split

Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)

If sIncomingfile <> "" And sOutputDirectory <> "" Then

Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)
f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read

Dim file As New IO.FileInfo(sIncomingfile)
Dim filefs As IO.FileStream = Nothing
If file.Exists Then
Try
filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1
Catch ex As Exception
SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")
Application.Exit()
End Try
End If

Dim reader As New IO.StreamReader(filefs)
Dim counter As Integer = 0

Dim CurrentFS As IO.FileStream
Dim CurrentWriter As IO.StreamWriter
Dim extension As String = IO.Path.GetExtension(file.FullName)

If extension = ".mnt" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If IsNumeric(Line.Substring(0, 1)) Then
Dim Parts() As String = Line.Split(" "c) ' split row into parts
If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End If
End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "mnt")

ElseIf extension = ".naf" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "naf")
End If
Else
'input file not valid
SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")
End If
End Sub

You don't need a console application to import the data into SQL Server if you are in SQL Server 2000 you need a DTS package and in SQL Server 2005 you need an Integration services package. The only important thing to note is SQL Server being a RDBMS(relational database management systems) sees a text file as having Null values so you import your data into a Temp table then do INSERT INTO your destination table. Try the link below for sample DTS and Integration services code. Hope this helps.

http://www.sqlis.com/

|||

Put this somewhere at the top:

Dim conn as new sqlconnection("Your connect string")
conn.open
Dim cmd1 as new sqlcommand("INSERT INTO ProcessedFiles(Filename) VALUES (@.Filename) SELECT SCOPE_@.IDENTITY()")

cmd1.parameters.add("@.Filename",sqldbtype.varchar)

dim cmd2 as new sqlcommand("INSERT INTO ProcessedLines(FileID,LineNum,LineData) VALUES (@.FileID,@.LineNum,@.LineData)",conn)

cmd2.parameters.add("@.FileID",sqldbtype.int32)

cmd2.parameters.add("@.LineNum",sqldbtype.int32)

cmd2.parameters.add("@.LineData",sqldbtype.varchar)

Then after you get the filename in your code:

cmd1.parameters("@.Filename").value={Your filename variable}

cmd2.parameters("@.FileID").value = cmd1.executescaler

Then after you read a line of data from the file:

cmd2.parameters.add("@.LineNum").value=counter

cmd2.parameters.add("@.LineData").value=line

cmd2.executenonquery

And at the end of your program:

conn.close

of course, this assumes you have a table named processedfiles that has a Filename column, as well as an identity field. I would put a ProcessedDate field in there too, that defaults to GetUTCDate(). And a table named ProcessedLines that has three columns (FileID,LineNum,LineData).

Is that what you were looking for?