I'm creating a small program to read from an SQL server, loading result into an array, pivoting the array to my liking and mailing it out to selected members of a group.
It's going to be run once a day with Windows schedule.
I have the connection, query and mail sorted, but not the datareader to array bit. Anyone know how it's done (line 26)?
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Module Module1
Sub Main()
'Connection to SQL
Dim con As New SqlConnection
con.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=db;integrated security=SSIP;Persist Security Info=False;Trusted_Connection=Yes"
con.Open()
'Query SQL
Dim cmd As New SqlCommand("SELECT DISTINCT REG,K.NAME,L.LOCATION,L.LOCATION_NAME,A.INSTRUCTION, " & _
"SUM(CASE SAMPLE WHEN 'POS' THEN 1 WHEN 'OK' THEN 1 ELSE 0 END) AS Pos,COUNT(CODE) as 'Sum' " & _
"FROM PT_SAMPLE P " & _
"INNER JOIN PT_SAMPLE_PARTS PP ON P.SAMPLE = PP.SAMPLE " & _
"INNER JOIN KT_CUSTOMER K ON P.CUSTOMER = K.CUSTOMER " & _
"INNER JOIN LT_LOCATION L ON L.LOCATION = P.LOCATION " & _
"INNER JOIN AT_CODE A ON A.CODE = PP.CODE " & _
"WHERE DATE LIKE '20111104%' AND CODE <> 'NA' AND CODE NOT LIKE 'PREVIOUS%' AND P.ZT <> 'W' " & _
"GROUP BY REG,K.NAME,L.LOCATIONL,L.LOCATION_NAME,CODE,A.INSTRUCTION", con)
Dim reader As SqlDataReader = cmd.ExecuteReader()
con.Close()
'Convert to array
?
'Mail setup
Dim mMailMessage As New MailMessage()
mMailMessage.From = New MailAddress("Sender")
mMailMessage.To.Add(New MailAddress("Reciever"))
mMailMessage.Subject = "Subject"
'CSS setup for presentation of array (work in progress)
Dim Body As String
Body = "<!DOCTYPE html PUBLIC " & Chr(34) & "-//W3C//DTD XHTML 1.0 Transitional//EN" & Chr(34) & " " & Chr(34) & "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" & Chr(34) & ">" & _
"<html><head><style type=" & Chr(34) & "text/css" & Chr(34) & ">table{border-collapse:collapse;} table, td, th {border:1px solid black;}</style></head><body><table>" & _
"<tr><th>Name</th><th>Name</th></tr><tr><td>Peter</td><td>Griffin</td></tr><tr><td>Lois</td><td>Griffin</td>" & _
"</tr></table>" & _
"<p><b>Note:</b> If a !DOCTYPE is not specified, the border-collapse property can produce unexpected results" & _
"in IE8 and earlier versions.</p></body></html>"
mMailMessage.Body = Body
mMailMessage.IsBodyHtml = True
mMailMessage.Priority = MailPriority.Normal
Dim mSmtpClient As New SmtpClient()
mSmtpClient.Send(mMailMessage)
End Sub
End Module