I built an app to create a night export of data. I am new to programming (basically I have found vb.net to be more powerful than SSIS) and I was looking for tips in improving my basic program.

'Declared columns from SQL Script
        Dim Servicenumber As Integer
        Dim pilotarea As String 'This value has if statement wrapped around because of DBNULL
        Dim propertydatamap As String
        Dim City As String
        Dim State As String
        Dim Zip As Integer
        Dim Streetnumber As String
        Dim StreetName As String
        Dim unitnumber As String 'This value has if statement wrapped around because of DBNULL
        Dim viitemname As String
        Dim truckid As Integer
        Dim curbdatetime As DateTime
        Dim curblatitude As String 'This value has if statement wrapped around because of DBNULL
        Dim curblongitude As String 'This value has if statement wrapped around because of DBNULL
        Dim Observation1 As String 'This value has if statement wrapped around because of DBNULL
        Dim Observation2 As String 'This value has if statement wrapped around because of DBNULL
        Dim Observation3 As String 'This value has if statement wrapped around because of DBNULL
        Dim Observation4 As String 'This value has if statement wrapped around because of DBNULL
        Dim Observation5 As String 'This value has if statement wrapped around because of DBNULL

        'combination of declared Columns
        Dim TotalCol As String

        'Date Format
        Dim D As String
        Dim M As String
        Dim Y As String


        'Set Date Values
        D = DateTime.Now.Day
        M = Format(Month(Now), "00")
        Y = DateTime.Now.Year

        'Write Lakeland Text File
        Dim oWriter As System.IO.StreamWriter
        oWriter = System.IO.File.CreateText("c:\ObservationDump_" & Y & M & D & ".txt")

        'Build Connection String to Lakeland DB
        Dim connstring As String = "Server=10%;Database=%;User ID=%;Password=%;Trusted_Connection=False;"
        Dim conn As New SqlConnection(connstring)

        Dim selectSQL As String = "select Servicenumber,pilotarea,propertydatamap,upper(city),state, " & _
                                   "zip,streetnumber,Upper(streetname),unitnumber,viitemname, " & _
                                   "truckid,curbdatetime,curblatitude,curblongitude,Observation1, " & _
                                    "Observation2, Observation3, Observation4,observation5 " & _
                                    "from latipping a inner join lalocation b on a.locationid = b.locationid " & _
                                    "left join laitem c on a.itemid = c.itemid " & _
                                    "where (tipdatetime > getdate() - 1)" & _
                                   "and Observation1 <> '' " & _
                                   "order by streetname,cast(streetnumber as int)"

        'create selection command
        Dim cmd As New SqlCommand(selectSQL, conn)
        'set cmd property
        cmd.CommandType = CommandType.Text
        'open database
        conn.Open()
        'create a new reader and execute the reader
        Dim myreader As SqlDataReader = cmd.ExecuteReader
        'read information from the database and give the values to the arguments(column_1, and column_2)
        While myreader.Read
            Servicenumber = myreader.GetInt32(0)

            If myreader.IsDBNull(1) Then
                pilotarea = "NULL"
            Else
                pilotarea = myreader.GetString(1)
            End If

            If myreader.IsDBNull(2) Then
                propertydatamap = "NULL"
            Else
                propertydatamap = myreader.GetString(2)
            End If
            City = myreader.GetString(3)
            State = myreader.GetString(4)
            Zip = myreader.GetString(5)
            Streetnumber = myreader.GetString(6)
            StreetName = myreader.GetString(7)
            If myreader.IsDBNull(8) Then
                unitnumber = "NULL"
            Else
                unitnumber = myreader.GetString(8)
            End If

            viitemname = myreader.GetString(9)
            truckid = myreader.GetString(10)
            curbdatetime = myreader.GetDateTime(11)

            If myreader.IsDBNull(12) Then
                curblatitude = "NULL"
            Else
                curblatitude = myreader.GetString(12)
            End If
            If myreader.IsDBNull(13) Then
                curblongitude = "NULL"
            Else
                curblongitude = myreader.GetString(13)
            End If

            If myreader.IsDBNull(14) Then
                Observation1 = "NULL"
            Else
                Observation1 = myreader.GetString(14)
            End If
            If myreader.IsDBNull(15) Then
                Observation2 = "NULL"
            Else
                Observation2 = myreader.GetString(15)
            End If

            If myreader.IsDBNull(16) Then
                Observation3 = "NULL"
            Else
                Observation3 = myreader.GetString(16)
            End If
            If myreader.IsDBNull(17) Then
                Observation4 = "NULL"
            Else
                Observation4 = myreader.GetString(17)
            End If
            If myreader.IsDBNull(18) Then
                Observation5 = "NULL"
            Else
                Observation5 = myreader.GetString(18)
            End If

            'start to build the the output file

            TotalCol = Servicenumber & "|" & pilotarea & "|" & propertydatamap & "|" & _
                       City & "|" & State & "|" & Zip & "|" & _
                       Streetnumber & "|" & StreetName & "|" & unitnumber & "|" & _
                       viitemname & "|" & truckid & "|" & curbdatetime & "|" & _
                       curblatitude & "|" & curblongitude & "|" & Observation1 & "|" & _
                       Observation2 & "|" & Observation3 & "|" & Observation4 & "|" & Observation5
            oWriter.WriteLine(TotalCol)
        End While
        'close file and Database Connection
        oWriter.Close()
        conn.Close()
    End Sub

I would put the observations in a class then feed the constructor of the class an IDataReader.
The constructor would then parse the fields of the database into the variables in the class -- keeping your "loader" really minimal.

If this is the only program that will ever use that data in that manner, it might not be that big of a deal, but if you want a lot of reuse out of it, solidify that load into something that is more testable and solid.

Also, for the sake of efficiency, you can output the date format from a DateTime without having additional variables.

Edited 4 Years Ago by thines01: clarity

Also for the strings that are to be marked as "NULL" when they are DBNull, I would make a small function and put it in the class and pass those specific elements through it.

I would make the output string a ToString() override so when you're writing to a file (in a completely separate action), all you have to do is call the object with the .ToString()

p-Code

fileOut.WriteLine(myObserv.ToString())
Private Function NullString(ByVal s As String)
      If (IsDBNull(s)) Then
         Return "NULL"
      End If

      Return s
   End Function

   'Constructor
   Public Sub New(ByRef rdr As IDataReader)
      Servicenumber = rdr.GetInt32(0)
      pilotarea = NullString(rdr.GetString(1))
      propertydatamap = NullString(rdr.GetString(2))
      City = rdr.GetString(3)
      State = rdr.GetString(4)
      Zip = rdr.GetString(5)
      Streetnumber = rdr.GetString(6)
      StreetName = rdr.GetString(7)
      unitnumber = NullString(rdr.GetString(8))
      viitemname = rdr.GetString(9)
      truckid = rdr.GetString(10)
      curbdatetime = rdr.GetDateTime(11)
      curblatitude = NullString(rdr.GetString(12))
      curblongitude = NullString(rdr.GetString(13))
      Observation1 = NullString(rdr.GetString(14))
      Observation2 = NullString(rdr.GetString(15))
      Observation3 = NullString(rdr.GetString(16))
      Observation4 = NullString(rdr.GetString(17))
      Observation5 = NullString(rdr.GetString(18))
   End Sub

   ' and the output becomes just a ToString() override
   Public Overrides Function ToString() As String
      Return Servicenumber & "|" & pilotarea & "|" & propertydatamap & "|" & _
         City & "|" & State & "|" & Zip & "|" & _
         Streetnumber & "|" & StreetName & "|" & unitnumber & "|" & _
         viitemname & "|" & truckid & "|" & curbdatetime & "|" & _
         curblatitude & "|" & curblongitude & "|" & Observation1 & "|" & _
         Observation2 & "|" & Observation3 & "|" & Observation4 & "|" & Observation5
   End Function
This article has been dead for over six months. Start a new discussion instead.