Using ODBC from CLR

Help Contents

Introduction

You can access data in Fernhill SCADA CLR programs in two different ways:

Using the .NET Framework Data Provider directly, or
Using the .NET Framework Data Provider for ODBC with the Fernhill SCADA ODBC Driver

This article discusses the latter: Using the .NET Framework Data Provider for ODBC with the Fernhill SCADA ODBC Driver.

The general structure of a program is:

Use the OdbcConnection class to establish a connection to Fernhill SCADA
Use the OdbcCommand class to create an SQL command to execute on Fernhill SCADA

Establishing a connection

You can use the OdbcConnection object to establish an ODBC connection to Fernhill SCADA:

Imports System.Data.Odbc

Dim connectionString As String

' This connection string indicates Fernhill SCADA running on the local computer
' You could also use a DSN
connectionString = "Driver={Fernhill SCADA};Server=localhost"

Using connection As New OdbcConnection(connectionString)

    ' Open the connection to Fernhill SCADA...
    connection.Open()

    ' Do something with the connection
End Using

Executing Commands

The OdbcCommand class is used to execute SQL queries. SELECT query commands can be executed using the ExecuteReader method that returns an OdbcDataReader. UPDATE commands can be executed using the ExecuteNonQuery method.

SELECT Query Without Parameters

Sub ReadUsingDataReader(connection As OdbcConnection)
    ' Create the command
    ' In this case select FullName and Value fields from all analog tags
    Dim command As OdbcCommand = _
            New OdbcCommand("SELECT FullName,Value From TagDataPointAnalog", connection)

    ' Execute the command
    Dim reader As OdbcDataReader = command.ExecuteReader()

    ' Check we have at least 1 row
    If reader.HasRows() Then

        ' Output the data to the console
        Do While reader.Read()
            Console.WriteLine("{0} = {1}", reader.GetString(0), reader.GetDouble(1))
        Loop
    Else
        Console.WriteLine("No rows found")
    End If

End Sub

SELECT Query With Parameters

Sub ReadUsingDataReader(connection As OdbcConnection)
    ' Create the command
    Dim command As OdbcCommand = _
            New OdbcCommand("SELECT Value From TagDataPointAnalog WHERE FullName = ?", connection)

    ' For Fernhill SCADA need to prepare the command before setting any parameters
    command.Prepare()

    ' Set the parameters
    command.Parameters.Add("FullName", OdbcType.VarChar).Value = "Main.RandomValues.Level"

    ' Execute the command
    Dim reader As OdbcDataReader = command.ExecuteReader()

    ' Read the first row and if found print the value
    If reader.Read() Then
        Console.WriteLine("Value = {0}", reader.GetDouble(0))
    Else
        Console.WriteLine("Tag not found")
    End If
End Sub

UPDATE Command with Parameters

Sub UpdateAnalogTag(connection As OdbcConnection, tagName As String, value As Double)
    ' Create the command
    Dim command As OdbcCommand = _
            New OdbcCommand("UPDATE InternalAnalog SET Value = ? WHERE FullName = ?", connection)

    ' For Fernhill SCADA need to prepare the command before setting any parameters
    command.Prepare()

    ' Set the parameters
    command.Parameters.Add("Value", OdbcType.Double).Value = value
    command.Parameters.Add("FullName", OdbcType.VarChar).Value = tagName

    ' Execute the command
    command.ExecuteNonQuery()
End Sub

Further Information

Fernhill SCADA Structured Query Language (SQL)

To learn about the structured query language (SQL) supported by Fernhill SCADA.

Setting up an ODBC DSN

To learn about configuring ODBC data Source Names.

Using ODBC Connection Strings to Connect to Fernhill SCADA

To learn about the keywords Fernhill SCADA supports in ODBC connection strings.

Fernhill SCADA ODBC Driver

To learn how the Fernhill SCADA ODBC Driver can provide data to other applications.

Fernhill SCADA .NET Framework API

For information on the Fernhill SCADA .NET Framework API.

Glossary

For the meaning of terms used in Fernhill SCADA.