Hey guys does anyone know how to read in a large sql query from a file into vb.net?
I tried using a stream reader to read in the contents of the sql query but I'm losing all of the CRLF's and that's causing the ExecuteDataReader to fail.
I need to be able to preserve all of the CRLF's that take place in the query I guess...
That seems to do the same thing. CRLF's are not included in the string.
One Idea I had was maybe to do a line read and then append a CRLF at the end of each of those in a string builder ??? Hoping there is an easier way to do this though with less code.
Rajkumar Gs
That idea should work.
I have no computer right now to figure out your current issue.
Raj
romieb69
ASKER
Well that was close... but not quite there yet. Doing something like this certainly worked as far as preserving the CRLF's:
Dim strContents As New StringBuilder
Dim objReader As StreamReader
objReader = New StreamReader(FullPath)
Try
Do While objReader.Peek() >= 0
strContents.Append(objReader.ReadLine())
strContents.Append(vbCrLf)
Loop
....
When you look at strContents.ToString you can see that all the carriage returns and line feeds are in place... but when I pass it into : sqlCmd.CommandText = sqlStr.ToString
the value of sqlCmd.CommandText no longer has the crlf's ... it's all one long string with no CRLF delimeters.
Humm so this seems to be an issue with Data.SqlClient.SqlCommand ... I wonder what would happen with SqlTransaction??
I'm not sure if your issue & solution was the "GO" keyword/ExecuteNonQuery, or the CRLF thing, but just wanted to point out that File.ReadAllText leaves the CRLF's in tact, as they appear in the file.
If you're actually not seeing them after File.ReadAllText, could be that 1) When you hove the mouse over a string variable in Visual Studio while debugging it doesn't show the CRLF's, but they're there - or 2) the file you're reading from has a UNIX-style format, with newline's at the end of each line instead of Windows' carriage return/linefeed pair.
Probably neither here nor there anyway, just felt compelled to espouse my two cents. ;)
One Idea I had was maybe to do a line read and then append a CRLF at the end of each of those in a string builder ??? Hoping there is an easier way to do this though with less code.