TN Appsvr142 How to query a SQL database and display in DataGrid View in Archestra Graphic

Description

This article from InSource shows how to query data from a SQL database and display in a DataGrid in an Archestra Graphic.

• Author: Mike Viteri
• Published: 1/2/2015
• Applies to: System Platform

How and When to Use this Guide

When you want to display data from a SQL database inside an datagridview Archestra Graphic

Instructions

You will need to make sure your datagridview is imported. If not available. Please refer to this technote.

1. You will need to create an Archestra Graphic Symbol. You will need to bring in the datagrid view control and create a button.
   

Click on the button and create a action script. You will use the following script to create a connection to your SQL database. 

Under sDBConnStr you will need to replace your Server, Database,UserID and Password to match your system.

For sSQL you will need to insert your query for data you want to showup in the datagrid view.

Make sure the datagridview you are using is "datagridview1" if not change the script below to make the datagridview you are using.

The complete script is pasted below in between the lines

--------------------------------------------------------------------------------------------------------------------------------------

Dim objDB As System.Data.SqlClient.SqlConnection;
Dim objCmd As System.Data.SqlClient.SqlCommand;
Dim objDR As System.Data.SqlClient.SqlDataReader;
Dim objTbl As System.Data.DataTable; 
Dim sDBConnStr As String;
Dim sSQL As String;
Dim bOk As Boolean;
 
sDBConnStr = "Server=MV20142;Database=Runtime;User Id=sa;Password=I$$Insource1234;"; 

'' Connect and open the database
objDB = New System.Data.SqlClient.SqlConnection(sDBConnStr);
objDB.Open(); 

sSQL = "SELECT [DateTime],[TagName],[Value] FROM [Runtime].[dbo].[v_AnalogHistory] Where TagName = 'systimesec'"; 

'' Invoke the SQL command 
objCmd = New System.Data.SqlClient.SqlCommand(sSQL, objDB); 

'' Retrieve the queried fields from the query into the reader
objDR = objCmd.ExecuteReader(); 

'' Is there a result to the query?
bOk = objDR.HasRows; 

If bOk <> True Then
LogMessage( "INFO: SQL Reader -:- No rows returned. [" + sSQL + "]");
Else

'' Instanciate & Load the results into a table object

objTbl = New System.Data.DataTable;
objTbl.Load(objDR);

'' Load the table into the grid
 DataGridView1.DataSource = objTbl; 

Endif;

DataGridView1.AutoResizeColumns();
 
'' Cleanup
objDR.Close();
objDB.Dispose();
objCmd.Dispose();
objTbl.Dispose();

3. You will need to embed your symbol that has the grid and button into an Intouch Window. Go to runtime. You should see your grid and button. when you click the button the data should be returned from the sql into the data grid.
   

--------------------------------------------------------------------------------------------------------------------------------------

Confirmation of Success

Data is displayed in the grid.