Read Database Data

Use the OPCSystems.dll assembly to call GetDatabaseData to return a DataTable of values from a database table or view.

  • The GetDatabaseData function returns a DataTable of values from the local or remote service by obtaining database values where the service is running.
  • Returns blank DataTable if service is not reachable.
  • DBProvider is the database provider type to use.
  • DBServer is the database server to connect to. Not applicable for MS Access and Oracle.
  • TableOrView is the table name or view name to query the data from.
  • MSSQLWindowsAuthentication – when connecting with SQL Server use Windows Authentication. When false specify the DBUser and DBPassword for SQL user login.
  • DBUser is the user name for security authentication. Not applicable if using SQL Server and MSSQLWindowsAuthentication is set to true.
  • DBPassword is the password for security authentication. Not applicable if using SQL Server and MSSQLWindowsAuthentication is set to true.
  • FieldNames is a string array containing the field names to query from the table for view.
  • DataTypes is an array of field data types for the fields.
  • UseDates will enable using the StartDate and EndDate.
  • StartDate as the start date and time of history to retrieve.
  • EndDate as the end date and time of history to retrieve.
  • QueryString is the WHERE condition to append to the query. When blank it is not used.
  • NetworkNode is the name of the network node of the OPC Systems Service to connect to. Leave blank for localhost connection.
  • ErrorString will be set to Success when function is successful and an error message when in error.
  • RemoteSCADAHostingName is the name of the Live Data Cloud OPC Systems Service to connect to.
Article Contents
VB
C#

VB

        Private Sub ButtonGetDatabaseData_Click(sender As System.Object, e As System.EventArgs) Handles ButtonGetDatabaseData.Click
        Dim m_OPCSystemsComponent1 As New OPCSystems.OPCSystemsComponent

        Dim returnedDataTable As DataTable

        Dim localDBProvider As OPCSystems.OPCSystemsComponent.DBProviderTypes = OPCSystems.OPCSystemsComponent.DBProviderTypes.SQLServer
        Dim localDBServer As String = "OAS_SONY\SQLOAS"
        Dim database As String = "TestDB"
        Dim table As String = "TestCont"
        Dim useWindowsAuthentication As Boolean = True
        Dim user As String = ""
        Dim password As String = ""

        Dim FieldNames(3) As String
        FieldNames(0) = "Ramp_Value"
        FieldNames(1) = "Value01_Value"
        FieldNames(2) = "Value02_Value"
        FieldNames(3) = "Value03_Value"


        Dim FieldDataTypes(3) As OPCSystems.OPCSystemsComponent.FieldDataTypes
        FieldDataTypes(0) = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData
        FieldDataTypes(1) = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData
        FieldDataTypes(2) = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData
        FieldDataTypes(3) = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData

        Dim useStartAndEndDates As Boolean = True
        Dim dateTimeFieldName As String = "DateAndTime"
        Dim endDate As Date = Now
        Dim startDate As Date = endDate.AddYears(-1)

        Dim networkNode As String = "localhost"
        Dim errorString As String = ""

        returnedDataTable = m_OPCSystemsComponent1.GetDatabaseData(localDBProvider, localDBServer, database, table, useWindowsAuthentication, user, password, FieldNames, FieldDataTypes, useStartAndEndDates, dateTimeFieldName, startDate, endDate, "", networkNode, errorString)

        DataGridView1.DataSource = returnedDataTable
    End Sub

C#

 private void ButtonGetDatabaseData_Click(object sender, System.EventArgs e)
              {
			OPCSystems.OPCSystemsComponent m_OPCSystemsComponent1 = new OPCSystems.OPCSystemsComponent();

			DataTable returnedDataTable = null;

			OPCSystems.OPCSystemsComponent.DBProviderTypes localDBProvider = OPCSystems.OPCSystemsComponent.DBProviderTypes.SQLServer;
			string localDBServer = "OAS_SONY\\SQLOAS";
			string database = "TestDB";
			string table = "TestCont";
			bool useWindowsAuthentication = true;
			string user = "";
			string password = "";

			string[] FieldNames = new string[4];
			FieldNames[0] = "Ramp_Value";
			FieldNames[1] = "Value01_Value";
			FieldNames[2] = "Value02_Value";
			FieldNames[3] = "Value03_Value";


			OPCSystems.OPCSystemsComponent.FieldDataTypes[] FieldDataTypes = new OPCSystems.OPCSystemsComponent.FieldDataTypes[4];
			FieldDataTypes[0] = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData;
			FieldDataTypes[1] = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData;
			FieldDataTypes[2] = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData;
			FieldDataTypes[3] = OPCSystems.OPCSystemsComponent.FieldDataTypes.DoubleFloatData;

			bool useStartAndEndDates = true;
			string dateTimeFieldName = "DateAndTime";
			DateTime endDate = DateTime.Now;
			DateTime startDate = endDate.AddYears(-1);

			string networkNode = "";
			string errorString = "";

			returnedDataTable = m_OPCSystemsComponent1.GetDatabaseData(localDBProvider, localDBServer, database, table, useWindowsAuthentication, user, password, FieldNames, FieldDataTypes, useStartAndEndDates, dateTimeFieldName, startDate, endDate, "", networkNode, errorString);

			DataGridView1.DataSource = returnedDataTable;
              }