StreamReader - Losing Lines from File.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul Say (saywin_at_tpg.com.au)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 17:30:29 +1000

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

Relevant Pages

  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Auto Write Name and Merge across
    ... Dim Sheetname01 As String ... Dim WeekName01 As String ...
    (microsoft.public.excel.misc)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Help answer these 70-310 questions
    ... One argument is the string ... Dim output As New StringBuilder ... EmployeeLocations. ... You create a strongly named serviced component. ...
    (microsoft.public.cert.exam.mcsd)