发表于:2002-08-16 21:58:00
1楼
Public Class Form1
Inherits System.Windows.Forms.Form
-ADO.NET objects for accessing MS SQL Server
Dim sqlConn As New SqlConnection("server=localhost;database=myDB;uid=sa")
Dim sqlReader As SqlDataReader
Dim sqlCmd As New SqlCommand()
Private Sub printf(ByVal s As String)
lstPrintf.Items.Add(s)
lstPrintf.SetSelected(lstPrintf.Items.Count - 1, True)
If lstPrintf.Items.Count > 500 Then lstPrintf.Items.Clear()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Static first_activation As Boolean = True
Dim s As String
-Change mouse to hourglass
Cursor.Current = Cursors.WaitCursor
-Only go through this code once
If first_activation Then
-Set up and open a connection to SQL Server
sqlConn.Open()
- The first thing well try is a select from the VACOMLOG table
- If the select throws an exception, we assume the VACOMLOG table
- has not yet been created
sqlCmd.CommandText = "SELECT * FROM VACOMLOG WHERE ID=1"
sqlCmd.Connection = sqlConn
Try
printf("Checking database to see if VACOMLOG table exists...")
sqlReader = sqlCmd.ExecuteReader()
sqlReader.Close()
Catch
-We got to this code because the read atempt above failes.
printf("Couldnt find the VACOMLOG table, creating new VACOMLOG...")
s = "CREATE TABLE VACOMLOG "
s = s & "(id int identity not null,"
s = s & "timestamp smalldatetime not null,"
s = s & "unit varchar(50) not null,"
s = s & "pointname varchar(200) not null,"
s = s & "value varchar(50),"
s = s & "location varchar(50),"
s = s & "sourceAddr varchar(10),"
s = s & "status varchar(20) )"
sqlCmd.CommandText = s
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
End Try
first_activation = False
End If
At this point we know the SQL table exists and we can connect to it.
printf("Database connection and table verified....")
Create a iLON100 object based on the i.LON100s WSDL file
Dim ilon As New WebReference1.iLON100()
Specify the IP location of the i.LON 100 we want to hit
s = ilon.Url
ilon.Url = s.Replace("localhost", "10.5.250.59")
Dim ret As String
Dim data As String
-Setup the input parameter required to read the first datalog
data = "0"
- Execute the SOAP call. (XML snippet is returned)
printf("Uploading i.LON100 datalog...")
ret = ilon.DataLoggerRead(data)
printf("Parsing results...")
Dim xmlDoc As New System.Xml.XmlDocument()
Dim xmlNav As System.Xml.XPath.XPathNavigator
Dim dataValues As System.Xml.XPath.XPathNodeIterator
Dim timestamps As System.Xml.XPath.XPathNodeIterator
Dim pointNames As System.Xml.XPath.XPathNodeIterator
Dim locations As System.Xml.XPath.XPathNodeIterator
Dim sourceAddresses As System.Xml.XPath.XPathNodeIterator
Dim units As System.Xml.XPath.XPathNodeIterator
Dim statuses As System.Xml.XPath.XPathNodeIterator
Parse the XML snippet
xmlDoc.LoadXml(ret)
xmlNav = xmlDoc.CreateNavigator()
Create iterators for the values we care about
dataValues = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTvalue")
timestamps = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTlogTime")
pointNames = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTpointName")
locations = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTlocation")
sourceAddresses = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTlogSourceAddress")
units = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTunit")
statuses = xmlNav.Select("/iLONDataLogger/Log/Element/UCPTpointStatus")
printf("Writing to SQL...")
- Walk through the datalog creating table rows in SQL as we go
While (dataValues.MoveNext())
timestamps.MoveNext()
pointNames.MoveNext()
locations.MoveNext()
sourceAddresses.MoveNext()
units.MoveNext()
statuses.MoveNext()
s = "insert VACOMLOG (timestamp, unit, pointname, value, location, sourceAddr, status) "
s = s & " values(" & timestamps.Current.Value() &
s = s & units.Current.Value() & "
s = s & pointNames.Current.Value() &
s = s & dataValues.Current.Value() &
s = s & locations.Current.Value() &
s = s & sourceAddresses.Current.Value() &
s = s & statuses.Current.Value() & ")"
sqlCmd.CommandText = s
sqlCmd.ExecuteNonQuery()
printf(dataValues.Current.Value() & " " & timestamps.Current.Value())
End While
printf("Done")
Cursor.Current = Cursors.Default
End Sub
End Class