Solved

Streamreader in vb.net

Posted on 2009-04-06
6
1,269 Views
Last Modified: 2013-11-27
Hi,
Can anybody help me converting this code in vb.net
Public TempSQLPath As String
  Public SQLUnit As Integer
 Public TempSQLPath As String
Public Sub GetSQLFile()
        Dim Line As String
        Dim Datetime As String
        FileClose()
        SQLUnit = FreeFile()
        Datetime = Now.ToString("YYYYMMDDhhmmss") '(VB6.Format(Now, "YYYYMMDDhhmmss")
        TempSQLPath = TempDir & "\TempSQL" & Datetime & ".sql"
    
        If Dir(TempSQLPath) <> "" Then
            Kill(TempSQLPath)
        End If
        FileOpen(SQLUnit, TempSQLPath, OpenMode.Output)
        PrintLine(SQLUnit, SQLBox.Text & Environment.NewLine) 'redo the SQL file to get rid of RTF data
        FileClose()
        SQLUnit = FreeFile()
        FileOpen(SQLUnit, TempSQLPath, OpenMode.Input)
        Seek(SQLUnit, 1)
               Do Until EOF(SQLUnit)
            Line = LineInput(SQLUnit)
            NumLines += 1
        Loop
        Seek(SQLUnit, 1)
          End Sub

Open in new window

0
Comment
Question by:RIAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:djon2003
ID: 24077693
Here is my ReadFile function..

There is only one variable set outside this function (it is called AppPath... for the application data path).. so you could remove it if you want..

This function returns an array of strings or an array with one line starting with "ERROR:" + description.
This could be changed by Throw new Exception("MYERROR")

It is currently reading with UTF8 encoding, but this could also be changed.
Public Function ReadFile(ByVal PathFromApp As String, Optional ByVal FileMask() As Boolean = Nothing, Optional ByVal ReturnWhiteLine As Boolean = True, Optional ByVal UseAppBase As Boolean = True) As Array
        Dim Init, Accept As Boolean
        Dim LineCounter As Integer
        Dim Line, CurrentFile As String
        Dim Lines As New ArrayList()
 
        If UseAppBase Then
            CurrentFile = AppPath & Bar(AppPath) & PathFromApp
        Else
            CurrentFile = PathFromApp
        End If
        LineCounter = 1
        Init = False
 
        Line = ""
        If IO.File.Exists(CurrentFile) = False Then
            Lines.Add("ERROR:NOFILE")
            Return Lines.ToArray(Line.GetType)
        End If
 
        Dim MyFile As IO.FileStream
        Try
            MyFile = IO.File.Open(CurrentFile, IO.FileMode.Open, IO.FileAccess.Read)
            Dim MyFileReader As New IO.StreamReader(MyFile, System.Text.Encoding.UTF8)
 
            Line = MyFileReader.ReadLine()
            Do While (Not Line Is Nothing)
                Accept = False
                If FileMask Is Nothing OrElse FileMask.Length = 0 Then
                    Accept = True
                Else
                    If FileMask.GetUpperBound(0) < LineCounter Then
                        If FileMask(0) = True Then Accept = True
                    Else
                        Accept = FileMask(LineCounter)
                    End If
                End If
                If ReturnWhiteLine = False And Line = "" Then Accept = False
                If Line <> "" And Line.Length > 1 Then If Line.Substring(0, 2) = "#§" Then Accept = False
                If Accept = True Then
                    Init = True
                    Lines.Add(Line)
                End If
 
                LineCounter += 1
                Line = MyFileReader.ReadLine()
            Loop
        Catch
            Lines.Clear()
            Lines.Add("ERROR:FILECANTBEOPENED")
        Finally
            If Not MyFile Is Nothing Then
                MyFile.Close()
            End If
        End Try
 
        If Init = False Then
            Lines.Add("ERROR:FILEEMPTY")
        End If
 
        Line = ""
        Return Lines.ToArray(Line.GetType)
    End Function

Open in new window

0
 

Author Comment

by:RIAS
ID: 24077987
Hi,
Cheers for the code but if you convert my code to vb.net it will be of great help
0
 
LVL 4

Expert Comment

by:bhermer
ID: 24078140
try this, though looking at your code, I am not sure what your doing. I am making this presumption

1) you create a temp file name
2) you delete it if it exists
3) you append the new line chr to your sql text box (on your form??)
4) you write that out to the temp file
5) close the new file
6) re-open it and then read the line count


if that is what your doing then this code will work, though it isnt the best way of doing it
Public TempSQLPath As String
  Public SQLUnit As Integer
 Public TempSQLPath As String
Public Sub GetSQLFile()
        Dim Line As String
        Dim Datetime As String
 
        Datetime = Now.ToString("YYYYMMDDhhmmss") '(VB6.Format(Now, "YYYYMMDDhhmmss")
        TempSQLPath = TempDir & "\TempSQL" & Datetime & ".sql"
 
        If Dir(TempSQLPath) <> "" Then
            Kill(TempSQLPath)
        End If
 
        Dim sw As New IO.StreamWriter(TempSQLPath)
        sw.Write(SQLBox.Text & Environment.NewLine)
        sw.Close()
 
        Dim sr As New IO.StreamReader(TempSQLPath)
 
        Do Until sr.EndOfStream
            sr.ReadLine()
            NumLines += 1
        Loop
 
        sr.Close()
    End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RIAS
ID: 24078173
Cheers for the post but what you think is the best way then?Can you please point
0
 

Author Comment

by:RIAS
ID: 24078492
HI bhermer:,
Cheers mate it worked perfectly.Can you help with the code below especially what happens to EOF
    Public Sub Get_SQL_Line(ByRef SQL As String)
        Dim EOFile As Boolean
        Dim CreateStatement As Boolean
        Dim CommentBlock As Boolean
        Dim StoredProcedure As Boolean
        Try
 
              If NextLine = String.Empty AndAlso Not EOF(SQLUnit) Then
                NextLine = LineInput(SQLUnit)
                LineNo += 1
            End If
            Do
 
                CurrLine = NextLine
                If EOF(SQLUnit) Then
                     If NextLine = String.Empty Then Exit Sub
                    If NextLine = vbNewLine Then Exit Sub
                    If NumLines = LineNo Then EOFile = True
                    NextLine = String.Empty
                Else
                    NextLine = LineInput(SQLUnit)
                    LineNo += 1
 
 
                End If
                'Create statement block
                If Trim(UCase(Left(CurrLine, 16))) = "CREATE PROCEDURE" OrElse Trim(UCase(Left(CurrLine, 14))) = "CREATE TRIGGER" OrElse Trim(UCase(Left(CurrLine, 11))) = "CREATE VIEW" Then
                    CreateStatement = True
                End If
                If Trim(UCase(Left(CurrLine, 15))) = "ALTER PROCEDURE" OrElse Trim(UCase(Left(CurrLine, 7))) = "DECLARE" Then
                    StoredProcedure = True
                End If
                If Trim(UCase(Left(CurrLine, 2))) = "GO" Then
                    StoredProcedure = False
                End If
                'Comment block
 
                If Left(Trim(CurrLine), 2) = "/*" OrElse Right(Trim(CurrLine), 2) = "*/" Then
                    If Not StoredProcedure Then
                        CommentBlock = True 'Note that SQL Server/Oracle scripts file may have comments formatted as /*  comment */
                    End If
                End If
                If CommentBlock Then
 
                    If CurrLine.IndexOf("*/") > 0 Then
 
                        CurrLine = Trim(Right(CurrLine, Len(CurrLine) - (InStr(CurrLine, "*/") + 1)))
                        CommentBlock = False
                    End If
                End If
                If Not CommentBlock Then
                    If Right(CurrLine, 1) = ";" Then
                        SQL = CurrLine
                    ElseIf NextLine.Trim = ";" Then
                        SQL = CurrLine
                        NextLine = String.Empty
                    ElseIf (NextLine.Trim = "" AndAlso Not StoredProcedure) Then
                        SQL = CurrLine
                        NextLine = String.Empty
                    ElseIf (NextLine.Trim).ToUpper = "GO" Then
                        SQL = CurrLine
                        NextLine = String.Empty
                    ElseIf (Not CreateStatement AndAlso CheckNextLine(CurrLine, NextLine) AndAlso Not StoredProcedure) Then
                        SQL = CurrLine
                    ElseIf Not EOFile Then
                        If CurrLine <> "" Then
                            arrNextLine.Add(CurrLine)
                        End If
                        If NextLine <> "" Then
                            arrNextLine.Add(NextLine)
                        End If
                        NextLine = CurrLine & vbNewLine & NextLine
                        Get_SQL_Line(SQL)
                    Else
                        SQL = CurrLine
                    End If
                End If
                If (arrNextLine.Count = 0 AndAlso CurrLine <> "") Then
                    arrNextLine.Add(CurrLine)
                End If
                SQL = CleanSQL(SQL)
                If EOFile Then Exit Sub
               Loop While SQL = String.Empty
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

Open in new window

0
 
LVL 4

Accepted Solution

by:
bhermer earned 250 total points
ID: 24084844
sr.EndOfStream will act the same as EOF, you can get a tru or false from it.

so If EOFile Then Exit Sub can be

if sr.EndOfStream then Exit Sub


  If EOF(SQLUnit) Then
                     If NextLine = String.Empty Then Exit Sub
                    If NextLine = vbNewLine Then Exit Sub
                    If NumLines = LineNo Then EOFile = True
                    NextLine = String.Empty
                Else
                    NextLine = LineInput(SQLUnit)
                    LineNo += 1
 
 
                End If

would be

  If sr.EndOfStream Then
                     If NextLine = String.Empty Then Exit Sub
                    If NextLine = vbNewLine Then Exit Sub
                    If NumLines = LineNo Then EOFile = True
                    NextLine = String.Empty
                Else
                    NextLine = LineInput(SQLUnit)
                    LineNo += 1
 
 
                End If


where you have LineInput(SQLUnit) you would use sr.readline


Also, where you are using Left() this has been depreciated in NET (though they still work in vb.net in cirtain circumstances) you should be using  MyString.SubString(iStart,iLength)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question