Check to see if Last Hour data was inserted into Database

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
I have a interesting question. I query data from a remote web server on a hourly basis. Every once in a while they disable the portal to do routine maintenace on the site, unfortunately i have no clue as to when they are going to do this.
Hence my problem.
What I need to do is have my application check to see if the last hour that was inserted into the database table is equal to the current hour minus 1 hour when it runs. If it is not equal to the current hour -1 then it needs to go back and download
the missing data automatically.
The hours go from 1 to 24 with midnight being the 24th hour as youll see in my code. My problem is I dont know how to code what Im trying to do.
Here is the current code:
<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; Private <span style="color:Blue; Sub HourDownload()
DeleteFileHour()
<span style="color:Blue; If OneHour(<span style="color:#A31515; "C:abcd.pfx", <span style="color:#A31515; "*******") = <span style="color:Blue; True <span style="color:Blue; Then
<span style="color:Blue; If validateOneHourxml() = <span style="color:Blue; True <span style="color:Blue; Then
<span style="color:Blue; If HourImport() = <span style="color:Blue; True <span style="color:Blue; Then
DeleteFileHour()
MyTimer.NextCheckAtMinute = 1
<span style="color:Blue; Exit <span style="color:Blue; Sub
<span style="color:Blue; Else <span style="color:Green; Import Failed
DeleteFileHour()
MyTimer.NextCheckAtMinute = 1 <span style="color:Green; Restart
<span style="color:Blue; Exit <span style="color:Blue; Sub
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; Else <span style="color:Green; Validate Failed
DeleteFileHour()
MyTimer.NextCheckAtMinute = Now.Minute + 1 <span style="color:Green; Restart
<span style="color:Blue; Exit <span style="color:Blue; Sub
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; Else <span style="color:Green; Download Failed
DeleteFileHour()
MyTimer.NextCheckAtMinute = Now.Minute + 1 <span style="color:Green; Restart
<span style="color:Blue; Exit <span style="color:Blue; Sub
<span style="color:Blue; End <span style="color:Blue; If

<span style="color:Blue; End <span style="color:Blue; Sub

<span style="color:Blue; Public <span style="color:Blue; Function OneHour(<span style="color:Blue; ByVal cert <span style="color:Blue; As <span style="color:Blue; String, <span style="color:Blue; ByVal pass <span style="color:Blue; As <span style="color:Blue; String)
<span style="color:Blue; Try
<span style="color:Blue; Dim Day <span style="color:Blue; As DateTime
<span style="color:Blue; If Today.IsDaylightSavingTime() <span style="color:Blue; Then
Day = DateTime.Now.AddHours(-1)
<span style="color:Blue; Else
Day = DateTime.Now()
<span style="color:Blue; End <span style="color:Blue; If

<span style="color:Blue; Dim Day1 <span style="color:Blue; As <span style="color:Blue; String
Day1 = Format(Day, <span style="color:#A31515; "yyyy-MM-dd")

<span style="color:Blue; Dim Hour <span style="color:Blue; As <span style="color:Blue; String = Microsoft.VisualBasic.Format(Day, <span style="color:#A31515; "HH")
<span style="color:Blue; If Hour = 0 <span style="color:Blue; Then
Hour = 24
Day1 = Microsoft.VisualBasic.Format(Today.AddDays(-1), <span style="color:#A31515; "yyyy-MM-dd")
<span style="color:Blue; End <span style="color:Blue; If
<span style="color:Blue; Dim BUFFERLENGTH <span style="color:Blue; As <span style="color:Blue; Integer = 4096
<span style="color:Blue; Dim buffer(BUFFERLENGTH) <span style="color:Blue; As <span style="color:Blue; Byte
<span style="color:Blue; Dim size <span style="color:Blue; As <span style="color:Blue; Integer = 0
<span style="color:Blue; Dim bytesRead <span style="color:Blue; As <span style="color:Blue; Integer = 0
<span style="color:Blue; Dim sData <span style="color:Blue; As <span style="color:Blue; String

sData = <span style="color:#A31515; "<?xml version=""1.0"" encoding=""utf-8""?>" _
& <span style="color:#A31515; "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/" " _
& <span style="color:#A31515; "<soap:Body>" _
& <span style="color:#A31515; "<QueryRequest xmlns=""http://markets.midwestiso.org/dart/xml" " _
& <span style="color:#A31515; "<QueryRealTimeIntegratedLMP day=" + Day1 + <span style="color:#A31515; ">" _
& <span style="color:#A31515; "<LocationName>ABC</LocationName>" _
& <span style="color:#A31515; "<Hour>" + Hour + <span style="color:#A31515; "</Hour>" _
& <span style="color:#A31515; "</QueryRealTimeIntegratedLMP>" _
& <span style="color:#A31515; "</QueryRequest>" _
& <span style="color:#A31515; "</soap:Body>" _
& <span style="color:#A31515; "</soap:Envelope>"

<span style="color:Blue; Dim Request <span style="color:Blue; As HttpWebRequest = <span style="color:Blue; DirectCast(WebRequest.Create(<span style="color:#A31515; "https://markets.abc.com/dart/xml/query"), HttpWebRequest)
Request.ClientCertificates.Add(<span style="color:Blue; New X509Certificate(cert, pass))
Request.Method = <span style="color:#A31515; "POST"
<span style="color:Green; convert data to a byte array
<span style="color:Blue; Dim bArray <span style="color:Blue; As <span style="color:Blue; Byte() = Encoding.ASCII.GetBytes(sData)
<span style="color:Green; set content type
Request.ContentType = <span style="color:#A31515; "text/xml"
Request.Headers.Add(<span style="color:#A31515; "SOAPAction", <span style="color:#A31515; "/dart/xml/query")
<span style="color:Green; Set the ContentLength property of the WebRequest.
Request.ContentLength = bArray.Length
<span style="color:Green; Get the request stream.
<span style="color:Blue; Dim dataStream <span style="color:Blue; As Stream = Request.GetRequestStream
<span style="color:Blue; Dim cPostResponse <span style="color:Blue; As <span style="color:Blue; String
<span style="color:Blue; Try
<span style="color:Green; Write the data to the request stream.
dataStream.Write(bArray, 0, bArray.Length)
<span style="color:Blue; Catch ex <span style="color:Blue; As Exception
cPostResponse = <span style="color:#A31515; "Error Sending Data: " & ex.Message
<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Green; Close the Stream object.
dataStream.Close()
<span style="color:Blue; Try
<span style="color:Green; Get the response.
<span style="color:Blue; Dim oResponse <span style="color:Blue; As WebResponse = Request.GetResponse()
dataStream = oResponse.GetResponseStream()
<span style="color:Blue; Dim contents <span style="color:Blue; As Stream = oResponse.GetResponseStream()
<span style="color:Blue; Dim fileApndStrm <span style="color:Blue; As <span style="color:Blue; New FileStream(<span style="color:#A31515; "C:1Hour.xml", FileMode.OpenOrCreate, FileAccess.Write)
<span style="color:Blue; While size <> -1
size = dataStream.Read(buffer, 0, BUFFERLENGTH)
<span style="color:Blue; If size = 0 <span style="color:Blue; Then <span style="color:Blue; Exit <span style="color:Blue; While
bytesRead += size
<span style="color:Blue; Me.Text = bytesRead & <span style="color:#A31515; " bytes / " & FormatNumber((bytesRead / 4096), 2) & <span style="color:#A31515; " kb"
<span style="color:Green; Show progress bar / status
<span style="color:Blue; If size < BUFFERLENGTH <span style="color:Blue; Then
<span style="color:Blue; Dim buff(size) <span style="color:Blue; As <span style="color:Blue; Byte
Array.Copy(buffer, buff, size)
fileApndStrm.Write(buff, 0, size)
buff = <span style="color:Blue; Nothing
<span style="color:Blue; Else
fileApndStrm.Write(buffer, 0, size)
<span style="color:Blue; End <span style="color:Blue; If
Array.Clear(buffer, 0, size)
<span style="color:Blue; End <span style="color:Blue; While
Console.WriteLine(sData)
fileApndStrm.Close()
dataStream.Close()
buffer = <span style="color:Blue; Nothing
<span style="color:Blue; Catch ex <span style="color:Blue; As Exception
cPostResponse = <span style="color:#A31515; "Error Getting Response: " & ex.Message
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; End <span style="color:Blue; Try
dataStream.Close()
<span style="color:Blue; Catch ex <span style="color:Blue; As NullReferenceException
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Blue; Return HourCheck
<span style="color:Blue; End <span style="color:Blue; Function

<span style="color:Blue; Private <span style="color:Blue; Function validateOneHourxml() <span style="color:Blue; As <span style="color:Blue; Boolean
<span style="color:Blue; Dim xmlFileName <span style="color:Blue; As <span style="color:Blue; String = <span style="color:#A31515; "C:1Hour.xml"
<span style="color:Blue; Dim xmlSchemaName <span style="color:Blue; As <span style="color:Blue; String = <span style="color:#A31515; "v31.xsd"
<span style="color:Blue; Using myFile <span style="color:Blue; As <span style="color:Blue; New FileStream(xmlFileName, FileMode.Open, FileAccess.Read, FileShare.None)
<span style="color:Blue; Dim xDoc <span style="color:Blue; As <span style="color:Blue; New Xml.XmlDocument()
<span style="color:Blue; If myFile.Length = 0 <span style="color:Blue; Then
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; End <span style="color:Blue; If
xDoc.Load(myFile)
xDoc.Schemas.Add(GetSchema(xmlSchemaName))
<span style="color:Blue; Try
xDoc.Validate(schemaValidation)
<span style="color:Blue; Return <span style="color:Blue; True
<span style="color:Blue; Catch ex <span style="color:Blue; As XmlSchemaValidationException
Console.Write(ex.ToString)
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; Catch ex <span style="color:Blue; As XmlSchemaException
Console.Write(ex.ToString)
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; Catch ex <span style="color:Blue; As Exception
Console.Write(ex.ToString)
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; End <span style="color:Blue; Function

<span style="color:Blue; Private <span style="color:Blue; Function GetSchema(<span style="color:Blue; ByVal filePath <span style="color:Blue; As <span style="color:Blue; String) <span style="color:Blue; As XmlSchema

<span style="color:Blue; Dim schema <span style="color:Blue; As XmlSchema
<span style="color:Blue; Using s <span style="color:Blue; As <span style="color:Blue; New System.IO.FileStream(filePath, FileMode.Open)
<span style="color:Blue; Using reader <span style="color:Blue; As <span style="color:Blue; New StreamReader(s)
schema = XmlSchema.Read(reader, <span style="color:Blue; Nothing)
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; End <span style="color:Blue; Using
<span style="color:Blue; Return schema
<span style="color:Blue; End <span style="color:Blue; Function

<span style="color:Blue; Private <span style="color:Blue; Sub ValidationHandler(<span style="color:Blue; ByVal sender <span style="color:Blue; As <span style="color:Blue; Object, <span style="color:Blue; ByVal e <span style="color:Blue; As System.Xml.Schema.ValidationEventArgs)
<span style="color:Blue; Throw e.Exception
<span style="color:Blue; End <span style="color:Blue; Sub


<span style="color:Blue; Private <span style="color:Blue; Function HourImport()
<span style="color:Blue; Try
<span style="color:Blue; Dim FileName <span style="color:Blue; As <span style="color:Blue; New <span style="color:Blue; String(<span style="color:#A31515; "C:1Hour.xml")
<span style="color:Blue; Dim cmd <span style="color:Blue; As <span style="color:Blue; New SqlCommand
<span style="color:Blue; Dim doc <span style="color:Blue; As <span style="color:Blue; New XmlDocument
doc.Load(FileName)
<span style="color:Blue; Dim queryResult <span style="color:Blue; As <span style="color:Blue; Integer
<span style="color:Blue; Dim Day <span style="color:Blue; As <span style="color:Blue; String = doc.GetElementsByTagName(<span style="color:#A31515; "RealTimeIntegrated").Item(0).Attributes(<span style="color:#A31515; "day").InnerText
<span style="color:Blue; Dim x <span style="color:Blue; As <span style="color:Blue; String = doc.GetElementsByTagName(<span style="color:#A31515; "NodeHourly").Item(0).Attributes(<span style="color:#A31515; "hour").InnerText
<span style="color:Blue; Dim PricingNodeHourlyNodes <span style="color:Blue; As XmlNodeList = doc.GetElementsByTagName(<span style="color:#A31515; "NodeHourly")
<span style="color:Blue; Dim HourSQL <span style="color:Blue; As <span style="color:Blue; String = <span style="color:#A31515; ""
conn.Open()
<span style="color:Green; Check to see if record already exists in Database table
cmd = <span style="color:Blue; New SqlCommand(<span style="color:#A31515; "SELECT COUNT(*) as numRows FROM Hour_RT WHERE Date = @Day and hour = @Hour", conn)
cmd.Parameters.AddWithValue(<span style="color:#A31515; "@Day", Day)
cmd.Parameters.AddWithValue(<span style="color:#A31515; "@Hour", x)
queryResult = cmd.ExecuteScalar()
conn.Close()
<span style="color:Green; If check returns a 0 then record does not exist so it will be entered into table.
<span style="color:Blue; If queryResult = 0 <span style="color:Blue; Then
<span style="color:Blue; For <span style="color:Blue; Each node <span style="color:Blue; As XmlNode <span style="color:Blue; In NodeHourlyNodes
HourSQL &= <span style="color:#A31515; "Insert into Hour_RT (Date, Hour, Price) " & _
<span style="color:#A31515; "Values (" & Day & <span style="color:#A31515; ", " & node.Attributes(<span style="color:#A31515; "hour").InnerText & <span style="color:#A31515; "," & node.Item(<span style="color:#A31515; "LMP").InnerText & <span style="color:#A31515; ")"
<span style="color:Blue; Next
<span style="color:Blue; With cmd
.Connection = conn
.CommandType = CommandType.Text
.CommandText = (HourSQL)
<span style="color:Blue; End <span style="color:Blue; With
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
<span style="color:Blue; Else
<span style="color:Blue; Return <span style="color:Blue; False
<span style="color:Blue; End <span style="color:Blue; If

<span style="color:Blue; Catch SQLExp <span style="color:Blue; As SqlException

<span style="color:Blue; End <span style="color:Blue; Try
<span style="color:Blue; Return HourDuplicateCheck
<span style="color:Blue; End <span style="color:Blue; Function
[/code]
Any assistance would be greatly welcomed.

View the full article
 
Back
Top