[RESOLVED] is streamreader necessary?

[Pasvorto]

I am looking at Visual Basic 2008 Step By Step. I need to read a flat file and insert the records into my SQL database. My code so far:

            'NEXT, ADD RECORDS TO PAYROLLMASTER
            FileOpen(1, "z:\PR\PAYROLLMASTER.csv", OpenMode.Input)
            Do Until EOF(1)
            Input #1, EMPLOYEEID, LASTNAME, FIRSTNAME, MIDDLENAME, SSN
                '=============================================
                Dim cnxn2 As SqlClient.SqlConnection
                cnxn2 = New SqlClient.SqlConnection(cnxnstring)
                cnxn2.Open()
                Dim sqlInsert As New SqlClient.SqlCommand
                sqlInsert.CommandText = "INSERT INTO PAYROLLMASTER ([EMPLOYEEID],[LASTNAME],[FIRSTNAME],[MIDDLENAME],[SSN]) "
                sqlInsert.CommandText += "VALUES(@employeeid,@lastname,@firstname,@middlename,@ssn)"
                sqlInsert.Parameters.AddWithValue("@employeeid", EMPLOYEEID)
                sqlInsert.Parameters.AddWithValue("@lastname", LASTNAME)
                sqlInsert.Parameters.AddWithValue("@firstname", FIRSTNAME)
                sqlInsert.Parameters.AddWithValue("@middlename", MIDDLENAME)
                sqlInsert.Parameters.AddWithValue("@ssn", SSN)
                sqlInsert.ExecuteNonQuery()
                sqlInsert.Dispose()
                cnxn2.Close()
                cnxn2.Dispose()
                '=============
            Loop
            FileClose(1)

The INPUT line needs to be fixed. From what I can see, I need to move it all to a single field and use a split to parse it, correct?

As I look at the entries on this site, all the code seems to use stream readers. Why would the book not do that? Am I missing something?

[Shaggy Hiker]

How old is the book?

There are certainly a variety of ways to read text files. Streamreader is one of them, but not the only one. What you are using looks a lot like the VB6 method for reading files, though I would have to add that it has been so many years that I am no longer certain of that. The other options are probably going to be somewhat better, since you get away from those strange file tokens (the 1), but each requires a slight learning curve, albeit a very slight curve, especially since you can find lots of examples here.

[Pasvorto]

I changed to code to use a streamreader

            'NEXT, ADD RECORDS TO PAYROLLMASTER
            Dim filetoparse As String
            Dim strreader As StreamReader
            strreader = New StreamReader("z:\PR\PAYROLLMASTER.csv")
            filetoparse = strreader.ReadLine
            '=============================================
            Dim cnxn2 As SqlClient.SqlConnection
            cnxn2 = New SqlClient.SqlConnection(cnxnstring)
            cnxn2.Open()
            Dim sqlInsert As New SqlClient.SqlCommand
            sqlInsert.CommandText = "INSERT INTO PAYROLLMASTER ([EMPLOYEEID],[LASTNAME],[FIRSTNAME],[MIDDLENAME],[SSN]) "
            sqlInsert.CommandText += "VALUES(@employeeid,@lastname,@firstname,@middlename,@ssn)"
            sqlInsert.Parameters.AddWithValue("@employeeid", EMPLOYEEID)
            sqlInsert.Parameters.AddWithValue("@lastname", LASTNAME)
            sqlInsert.Parameters.AddWithValue("@firstname", FIRSTNAME)
            sqlInsert.Parameters.AddWithValue("@middlename", MIDDLENAME)
            sqlInsert.Parameters.AddWithValue("@ssn", SSN)
            sqlInsert.ExecuteNonQuery()
            sqlInsert.Dispose()
            cnxn2.Close()
            cnxn2.Dispose()
            strreader.Close()
            '=============

It is a commas separated file. I created 'filetoparse' as the receptacle for the reading. I guess I still need a WHILE statement (peek<>-1?), but I can find that on line.

[Pasvorto]

Is this close? There is still too much to do to try and do a build right now.

            'NEXT, ADD RECORDS TO PAYROLLMASTER
            Dim filetoparse As String
            Dim strreader As StreamReader
            strreader = New StreamReader("z:\PR\PAYROLLMASTER.csv")
            filetoparse = strreader.ReadLine
            Dim cnxn2 As SqlClient.SqlConnection
            cnxn2 = New SqlClient.SqlConnection(cnxnstring)
            cnxn2.Open()
            Dim sqlInsert As New SqlClient.SqlCommand
            While strreader.Peek <> -1
                Dim fieldarray() As String = Split(filetoparse)
                '=============================================
                sqlInsert.CommandText = "INSERT INTO PAYROLLMASTER ([EMPLOYEEID],[LASTNAME],[FIRSTNAME],[MIDDLENAME],[SSN]) "
                sqlInsert.CommandText += "VALUES(@employeeid,@lastname,@firstname,@middlename,@ssn)"
                sqlInsert.Parameters.AddWithValue("@employeeid", fieldarray(1))
                sqlInsert.Parameters.AddWithValue("@lastname", fieldarray(2))
                sqlInsert.Parameters.AddWithValue("@firstname", fieldarray(3))
                sqlInsert.Parameters.AddWithValue("@middlename", fieldarray(4))
                sqlInsert.Parameters.AddWithValue("@ssn", fieldarray(5))
                sqlInsert.ExecuteNonQuery()
            End While
            sqlInsert.Dispose()
            cnxn2.Close()
            cnxn2.Dispose()
            strreader.Close()
            '=============

[Pasvorto]

Final version. I think I have it.

            '------------------------------------
            'NEXT, ADD RECORDS TO PAYROLLMASTER
            '------------------------------------
            '
            Dim filetoparse As String
            Dim strreader As StreamReader
            strreader = New StreamReader("z:\PR\PAYROLLMASTER.csv")
            filetoparse = strreader.ReadLine
            Dim cnxn2 As SqlClient.SqlConnection
            cnxn2 = New SqlClient.SqlConnection(cnxnstring)
            cnxn2.Open()
            Dim sqlInsert As New SqlClient.SqlCommand
            While strreader.Peek <> -1
                Dim fieldarray() As String = filetoparse.Split(",")
                '
                sqlInsert.CommandText = "INSERT INTO PAYROLLMASTER ([EMPLOYEEID],[LASTNAME],[FIRSTNAME],[MIDDLENAME],[SSN]) "
                sqlInsert.CommandText += "VALUES(@employeeid,@lastname,@firstname,@middlename,@ssn)"
                sqlInsert.Parameters.AddWithValue("@employeeid", fieldarray(0))
                sqlInsert.Parameters.AddWithValue("@lastname", fieldarray(1))
                sqlInsert.Parameters.AddWithValue("@firstname", fieldarray(2))
                sqlInsert.Parameters.AddWithValue("@middlename", fieldarray(3))
                sqlInsert.Parameters.AddWithValue("@ssn", fieldarray(4))
                sqlInsert.ExecuteNonQuery()
            End While
            sqlInsert.Dispose()
            cnxn2.Close()
            cnxn2.Dispose()
            strreader.Close()
            '=============

[vb_ftw]

u can use while not filetoparse is nothing
.
.
end while

u can use the substring and lastindexof to split the string on a comma

[Shaggy Hiker]

You could also read all the lines into an array of strings with a single line, and loop through that array of strings. There is no advantage to doing it that way as far as I know, but it is an option.