StreamReader - Losing Lines from File.

  • Thread starter Thread starter Paul Say
  • Start date Start date
P

Paul Say

I am using the following code to load a T-SQL Script File during the
instalation of an application. The File for example
CreateDatabaseObjects.Sql is as embedded resource within a installation
custom action. The Problem I am having is that I am unable to read lines
that contain only a single Bracket ')' or '(' these lines appear empty. I
this normal? Is there somthing I should so that these lines can be read. an
examply of what I am trying to read sample A and what gets read sample B is
listed, as well as the source code I am using to greate the streamReader
etc.

Paul




Sample A (What the file contains)

Insert Table A
(
UserName,
User Id,
) Values (
'Sad Programmer',
1,
)

Sample B (What I Get, Of cause when I try to ececute this against the
database it throws an error)

Insert Table A
UserName,
User Id,
) Values (
'Sad Programmer',
1,


Sample Code

Protected Sub ExecuteScript(ByVal strDBName As String, ByVal ScriptFile As
String)

Dim scriptString As StreamReader = GetSqlScript(ScriptFile)
Dim strCommand As String = String.Empty
Dim strLine As String = String.Empty

strLine = scriptString.ReadLine()

While Not strLine Is Nothing
If Trim(strLine) = "GO" Then
ExecuteSql(strDBName, strCommand)
strCommand = ""
Else
If Len(Trim(strLine)) > 1 Then
If Len(strCommand) > 1 Then
strCommand = strCommand & vbCrLf
End If

strCommand = strCommand & strLine
End If
End If
strLine = scriptString.ReadLine()
End While
scriptString.Close()
End Sub

Private Function GetSqlScript(ByVal Name As String) As StreamReader
Try
' Gets the current assembly.
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()

' Resources are named using a fully qualified name.
Dim strm As Stream =
Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name)

' Reads the contents of the embedded file.
Dim reader As StreamReader = New StreamReader(strm)

Return reader

Catch ex As Exception
MsgBox("In GetSQL: " & ex.Message)
Throw ex
End Try
End Function



Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)

Try
Command.ExecuteNonQuery()
Finally
' Finally, blocks are a great way to ensure that the connection is
always closed.
Command.Connection.Close()
End Try
End Sub
 
Hi Paul,

A StreamReader uses UTF-8 by default. Try specifying another encoding.

Dim reader As StreamReader = New StreamReader(strm, Encoding.ASCII)
Dim reader As StreamReader = New StreamReader(strm, Encoding.Default)
 
Paul Say said:
I am using the following code to load a T-SQL Script File during the
instalation of an application. The File for example
CreateDatabaseObjects.Sql is as embedded resource within a installation
custom action. The Problem I am having is that I am unable to read lines
that contain only a single Bracket ')' or '(' these lines appear empty. I
this normal? Is there somthing I should so that these lines can be read. an
examply of what I am trying to read sample A and what gets read sample B is
listed, as well as the source code I am using to greate the streamReader
etc.
Click to expand...

Your problem is this line:

If Len(Trim(strLine)) > 1 Then

That's going to be false for a line containing only a bracket, and so
you're effectively ignoring it.
 
If Len(Trim(strLine)) > 1 Then

should be

If Len(Trim(strLine)) > 1 Then

or better (.NET version)

If (strLine.Trim().Length > 0) Then
(OR)
'Will never be less than 0, so just test for empty lines
If (strLine.Trim().Length <> 0) Then

' (' after Trim() = 1, so you are not going to run that line. If you
change to 0, you are better off.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Paul Say said:
I am using the following code to load a T-SQL Script File during the
instalation of an application. The File for example
CreateDatabaseObjects.Sql is as embedded resource within a installation
custom action. The Problem I am having is that I am unable to read lines
that contain only a single Bracket ')' or '(' these lines appear empty. I
this normal? Is there somthing I should so that these lines can be read. an
examply of what I am trying to read sample A and what gets read sample B is
listed, as well as the source code I am using to greate the streamReader
etc.

Paul




Sample A (What the file contains)

Insert Table A
(
UserName,
User Id,
) Values (
'Sad Programmer',
1,
)

Sample B (What I Get, Of cause when I try to ececute this against the
database it throws an error)

Insert Table A
UserName,
User Id,
) Values (
'Sad Programmer',
1,


Sample Code

Protected Sub ExecuteScript(ByVal strDBName As String, ByVal ScriptFile As
String)

Dim scriptString As StreamReader = GetSqlScript(ScriptFile)
Dim strCommand As String = String.Empty
Dim strLine As String = String.Empty

strLine = scriptString.ReadLine()

While Not strLine Is Nothing
If Trim(strLine) = "GO" Then
ExecuteSql(strDBName, strCommand)
strCommand = ""
Else
If Len(Trim(strLine)) > 1 Then
If Len(strCommand) > 1 Then
strCommand = strCommand & vbCrLf
End If

strCommand = strCommand & strLine
End If
End If
strLine = scriptString.ReadLine()
End While
scriptString.Close()
End Sub

Private Function GetSqlScript(ByVal Name As String) As StreamReader
Try
' Gets the current assembly.
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()

' Resources are named using a fully qualified name.
Dim strm As Stream =
Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name)

' Reads the contents of the embedded file.
Dim reader As StreamReader = New StreamReader(strm)

Return reader

Catch ex As Exception
MsgBox("In GetSQL: " & ex.Message)
Throw ex
End Try
End Function



Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)

Try
Command.ExecuteNonQuery()
Finally
' Finally, blocks are a great way to ensure that the connection is
always closed.
Command.Connection.Close()
End Try
End Sub
Click to expand...
 
Back
Top