Get StartsWith value-CodePudding

I am trying to get the string value of the startsWth. Currently, its output is boolean. How do I display the string instead of True/False in my SQL database?

   Protected Sub Read(sender As Object, e As EventArgs)
    Dim lines As String() = File.ReadAllLines(Server.MapPath("~/File.txt"))
    For i As Integer = 0 To lines.Length - 1
        Dim file As String = Convert.ToString(lines(i))
        Dim Title As String = lines(i).StartsWith("123")
        Insert(Title)
    Next

End Sub


Public Sub Insert(Record As String)
    Using con As New SqlConnection(con)
        Dim query As String = ""
        Dim Title As String = Record.StartsWith("123")

        Using cmd As New SqlCommand("INSERT INTO Test (Title) VALUES (@Title)")
            cmd.Parameters.AddWithValue("@Title", Title.ToString())

            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

    End Using


End Sub

CodePudding user response：

Protected Sub Read(sender As Object, e As EventArgs)
    Dim lines As String() = File.ReadAllLines(Server.MapPath("~/File.txt"))
    For i As Integer = 0 To lines.Length - 1
        Dim Title As String = lines(i)
        If Title.StartsWith("123") Then
           Insert(Title)
        End
    Next
End Sub


Public Sub Insert(Title As String)
    Using con As New SqlConnection(con)    
        Using cmd As New SqlCommand("INSERT INTO Test (Title) VALUES (@Title)")
            cmd.Parameters.AddWithValue("@Title", Title)

            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

CodePudding user response：

If you want to create a list of matching lines, Linq is very good at that sort of thing.

Dim lines = File.ReadAllLines(Server.MapPath("~/File.txt"))
Dim matching = lines.Where(Function(line) line.StartsWith("123"))

This will create an enumerable sequence of lines that start with "123". If you want to do additional processing, you could chain a Select onto this, e.g.

Dim matching = lines.Where(Function(line) line.StartsWith("123")) _
    .Select(Function(item) PostProcess(item))

(or I think you could use AddressOf for a case like this where you just want to directly call a function, vs. doing simple processing inline)

CodePudding user response：

This should do it, and also make the code quite a bit faster and more memory efficient.

Protected Sub Read(sender As Object, e As EventArgs)
    Dim lines = File.ReadLines(Server.MapPath("~/File.txt")).
         Where(Function(line) line.StartsWith("123"))

    Insert(lines)
End Sub

Public Sub Insert(Titles As IEnumerable(Of String))
    Dim query As String = "INSERT INTO Test (Title) VALUES (@Title)"
    Using con As New SqlConnection(con), _
          cmd As New SqlCommand(query, con)

        ' Use the actual column type and length here
        cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 100)        

        con.Open()
        For Each title As String In Titles
            cmd.Parameters("@Title").Value = title
            cmd.ExecuteNonQuery()
        Next
    End Using
End Sub

Notice ReadLines() vs ReadAllLines(), which will save memory, and notice how the Insert() method now accepts the entire set, so we only need one command/connection in a much tighter loop.

But I also see a comment on another answer about needing more columns. In that case, you should also define a class to encapsulate the columns you need, with a property for each column. Then you add a Static/Shared method to the class to parse the data from a string input. This assumes each line in the file is a complete record:

Public Class MyData
    Public Property Title As String
    Public Property Field2 As DateTime
    ' ...
    Public Property FieldN As String
    ' Of course, use better names than this

    Public Shared Function Parse(input As String) As MyData
         Return New MyData() With {
              ' Use things like RegEx, String functions,
              ' or CSV/JSON parsers here to get the 
              ' fields you need from the string
         }
    End Function
End Class

Once you have this class, the earlier Read() and Insert() code changes like this:

Protected Sub Read(sender As Object, e As EventArgs)
    Dim records = File.ReadLines(Server.MapPath("~/File.txt")).
         Where(Function(line) line.StartsWith("123")).
         Select(Function(line) MyData.Parse(line))

    Insert(records)
End Sub

Public Sub Insert(Records As IEnumerable(Of MyData))
    Dim query As String = "INSERT INTO Test (Title, Field2, FieldN) VALUES (@Title, @Field2, @FieldN)"
    Using con As New SqlConnection(con), _
          cmd As New SqlCommand(query, con)

        ' Use the actual column type and length here
        cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 100)  
        cmd.Parameters.Add("@Field2", SqlDbType.DateTime)
        cmd.Parameters.Add("@FieldN", SqlDbType.NVarChar, 20)      

        con.Open()
        For Each record As MyData In Records
            cmd.Parameters("@Title").Value = record.Title
            cmd.Parameters("@Field2").Value = record.Field2
            cmd.Parameters("@FieldN").Value = record.FieldN

            cmd.ExecuteNonQuery()
        Next
    End Using
End Sub

If each line in the file is NOT a complete record, and records are spread over several lines, you'll need to change the Read() method to first know where the records start and stop, and be able to pass a complete record's worth of data at a time to your Parse() or Create() method.

It's also very likely the file is in a format for which there is an existing generic parser (like INI, JSON, XML, etc), and in this case always use the existing parser. An existing parser will tend to have fewer bugs (especially around edge cases). It will be less work for you to get working, even if it can be intimidating to get it set up at first. They will result in less code for you to write and maintain. And they will also tend to be much faster and more efficient.