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