processing DNS db via SQL with VS VB 2017

working on analyzing 156gb of DNS data... this is my first take on challenge.. 3 hours later... 

to run this quickly, just commented out the GetWebText function and watch your status. i tried a little threading pause to give my ISP a break..ps.. looking at BackgroundWorker component, who knows.. the SQL database bcp-in was more interesting..powershell hack to the rescue.

 

can't show you the DNS db, its confidential. just say 1.5 trillion records (probably 60% not applicable for the math I want/have/need to do for a research organization...  ;-) hehe.

 

// 122218 - arf - had to make a change to the random generator in r=, the previous did not work and this does.

 

 

Imports System.Data.SqlClient
'Imports System.Threading


Public Class Form1
    Inherits System.Windows.Forms.Form
    'Create ADO.NET objects.
    Private myConn As SqlConnection
    Private myCmd As SqlCommand
    Private myReader As SqlDataReader
    Private results As String
    Private maxDBrecords As Integer
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Private Sub BtnExit_Click(sender As Object, e As EventArgs) Handles BtnExit.Click
        Application.Exit()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListStatus.Items.Add("App loaded.")
        'Call ConnectDB()
    End Sub

    Private Sub ConnectDB(myquery, numparams)
        ListStatus.Items.Add("Konnecting 2 db.")
        'Create a Connection object.
        myConn = New SqlConnection("Initial Catalog=dbDNS;" &
                "Data Source=localhost;User ID=sa;Password=neverblanksomethingcomplicated")
        'Create a Command object.
        myCmd = myConn.CreateCommand
        'myCmd.CommandText = "SELECT max(row_id) FROM DNSRecords1"
        myCmd.CommandText = myquery

        ListStatus.Items.Add("Opening db.")
        'Open the connection.
        myConn.Open()

        results = ""
        myReader = myCmd.ExecuteReader()

        ListStatus.Items.Add("Reading db.")
        'Concatenate the query result into a string.
        Do While myReader.Read()
            Select Case numparams

                Case 0
                    'don't return anything, just execute query
                Case 1
                    results = results & myReader(0) & vbCrLf
                Case 2
                    results = results & myReader(0) & vbTab & myReader(1) & vbCrLf
                Case 3
                    results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbCrLf
                Case 4
                    results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbTab & myReader(3) & vbCrLf
                Case 5
                    results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbTab & myReader(3) & vbTab & myReader(4) & vbCrLf

                Case Else

            End Select
            If numparams > 1 Then
                ListResults.Items.Add(results)
            End If
            'results = ""
        Loop
        'Display results.
        'MsgBox(results)
        'ListResults.Items.Add(results)
        'Close the reader and the database connection.
        myReader.Close()
        ListStatus.Items.Add("Klozing db.")
        myConn.Close()

    End Sub

    Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
        If iStart.Text <> "" And iEnd.Text <> "" Or zMyQuery.Text <> "" Then
            Debug.Print("good. start and end or query")
            If zMyQuery.Text <> "" Then
                Call ConnectDB(zMyQuery.Text, 1)
            Else
                Dim s
                s = "select * from DNSRecords1 where row_id >= " & iStart.Text & " and row_id <= " & iEnd.Text
                Call ConnectDB(s, 5)
            End If
        Else
            ListResults.Items.Add("no good. nothing entered to find.")
            Debug.Print("no good. nothing entered to find.")
        End If

    End Sub

    Private Sub BtnClear_Click(sender As Object, e As EventArgs) Handles BtnClear.Click
        ListResults.Items.Clear()
    End Sub

    Private Sub BtnProcessDB_Click(sender As Object, e As EventArgs) Handles BtnProcessDB.Click

        'process the database
        Dim x, y, p, d
        x = 0
        y = 0
        p = 0
        d = ""
        'set the startdatetime
        LblStartDateTime.Text = Now()
        'first get the max record count
        Dim s
        s = "select max(row_id) from DNSRecords1"
        Call ConnectDB(s, 1)
        maxDBrecords = results
        Debug.Print("maxrecords=" & maxDBrecords)
        'connect to db
        'Create a Connection object.
        myConn = New SqlConnection("Initial Catalog=dbDNS;Data Source=localhost;User ID=sa;Password=neverblanksomethingcomplicated")
        'Create a Command object.
        'myCmd = myConn.CreateCommand
        'myCmd.CommandText = "SELECT max(row_id) FROM DNSRecords1"
        'myCmd.CommandText = myquery

        ListStatus.Items.Add("Opening db.")
        'Open the connection.
        myConn.Open()

        'results = ""
        'myReader = myCmd.ExecuteReader()

        ListStatus.Items.Add("Reading db.")
        'Concatenate the query result into a string.
        'Do While myReader.Read()
        'Select Case numparams

        'Case 0
        'don't return anything, just execute query
        'Case 1
        'results = results & myReader(0) & vbCrLf
        'Case 2
        'results = results & myReader(0) & vbTab & myReader(1) & vbCrLf
        'Case 3
        'results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbCrLf
        'Case 4
        'results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbTab & myReader(3) & vbCrLf
        'Case 5
        'results = results & myReader(0) & vbTab & myReader(1) & vbTab & myReader(2) & vbTab & myReader(3) & vbTab & myReader(4) & vbCrLf

        'Case Else

        'End Select
        'If numparams > 1 Then
        'ListResults.Items.Add(results)
        'End If
        'results = ""
        'Loop
        'Display results.
        'MsgBox(results)
        'ListResults.Items.Add(results)
        'Close the reader and the database connection.
        'myReader.Close()
        'ListStatus.Items.Add("Klozing db.")
        'myConn.Close()
        'second randomize the record to analyze

        Dim r
        Do Until ChkBoxStopProcessing.CheckState = CheckState.Checked
            Application.DoEvents()

            'r = CInt(Math.Ceiling(Rnd() * maxDBrecords) + 1)

            Static Generator As System.Random = New System.Random()
            r = Generator.Next(1, maxDBrecords)
            'as long as r is within bounds

            If r > 0 And r < maxDBrecords Then
                'Debug.Print("r = " & r)
                'get the record and see if it is worth analyzing
                's = "select * from DNSRecords1 where row_id = " & r
                'Call ConnectDB(s, 5)
                myCmd = myConn.CreateCommand
                s = "select domain_name from DNSRecords1 where substring(recordtype,9,5) = 'cname' and processed_datetime is null and row_id = " & r
                myCmd.CommandText = s
                myReader = myCmd.ExecuteReader()
                'Call ConnectDB(s, 1)
                Do While myReader.Read()
                    results = results & myReader(0) & vbCrLf
                    ListResults.Items.Add(x & vbTab & r & vbTab & results)
                    x = x + 1
                    LblMatches.Text = x.ToString
                    d = Mid(results, 9, Len(results) - 11)
                    Debug.Print("domainame = " & results & " - " & d)
                    Call GetWebText(r, d)
                Loop
                results = ""
                myReader.Close()
                LblTries.Text = y.ToString
                'x = x + 1
                y = y + 1
                If ChkBoxRandomPause.CheckState = CheckState.Checked Then
                    p = CInt(Math.Ceiling(Rnd() * 200) + 1)
                    LblPauseCount.Text = p.ToString
                    'Threading.Thread.Sleep(p)
                    'Sleep p
                End If
                If x Mod 20 = 0 Then
                    ListResults.Items.Clear()
                    ListStatus.Items.Clear()
                End If
            End If

        Loop

        ListStatus.Items.Add("Klozing db.")
        myConn.Close()
        'analyze and score the records
        'repeat in this loop, and pause (remember there are other workers involved)
    End Sub

    Private Sub BtnBetaTest_Click(sender As Object, e As EventArgs) Handles BtnBetaTest.Click
        Dim sourceString As String = New System.Net.WebClient().DownloadString("https://mountaincomputers.org")
        'Debug.Print(sourceString)
        RichTextBox1.Text = sourceString
    End Sub

    Private Sub GetWebText(r As Integer, v As String)
        Dim myConn2 As SqlConnection
        Dim myCmd2 As SqlCommand
        Dim s
        Try
            Dim sourceString As String = New System.Net.WebClient().DownloadString("http://" & v)
            myConn2 = New SqlConnection("Initial Catalog=dbDNS;Data Source=localhost;User ID=sa;Password=neverblanksomethingcomplicated")
            myConn2.Open()
            myCmd2 = myConn2.CreateCommand
            s = "update DNSRecords1 set processed_datetime = getdate() , homepagetext = '" & GetSQLText(sourceString) & "' where row_id = " & r
            myCmd2.CommandText = s
            myCmd2.ExecuteNonQuery()
            myConn2.Close()
            sourceString = ""
        Catch ex As Exception
            Debug.Print("error" & ex.Message & " trying to reach " & v)
        Finally
            'do nothing
        End Try

    End Sub

    Private Function GetSQLText(sText)
        Dim bEnd
        Dim i
        Dim iLast
        Dim sSQLText
        sSQLText = ""
        bEnd = False
        i = 1
        iLast = 1
        While Not bEnd
            i = InStr(iLast, sText, "'")
            If i > 0 Then
                sSQLText = sSQLText & Mid(sText, iLast, i - iLast) & "''"
                iLast = i + 1
            Else
                sSQLText = sSQLText & Mid(sText, iLast)
                bEnd = True
            End If
        End While
        GetSQLText = sSQLText
    End Function

End Class