TN Appsvr155 Working with input and output parameters when connecting to a SQL database from within Wonderware Application Server

Description

This article is an introduction into using input and output parameters to SQL Server stored procedures.

• Author: Joseph Hefner
• Published: 09/09/2015
• Applies to: Application Server 3.1 and above

Details

First, you will need to ensure that your input and output parameters are declared in your SQL Server stored procedure. A parameter that is declared as output can be used for either input or output. The screenshot below shows how each parameter can be declared.

Inside of an Application Server script you will create the connection string:

Dim SQLConnection AS System.Data.SqlClient.SqlConnection;
Dim SQLCommand AS System.Data.SqlClient.SqlCommand;
Dim SQLRdr AS System.Data.SqlClient.SqlDataReader;

Open and Connect to the database:

SQLConnection = new System.Data.SqlClient.SqlConnection("Your SQL Server Connection string goes here");
SQLConnection.Open();

The website below is a good source in determining what to use for the connection string above:
http://www.connectionstrings.com/

Tell which stored procedure will be used.

Dim sCmdText as string;
sCmdText = "StoredProcedureName" ; 

Invoke the stored procedure and pass the relevent parameters to it below

SQLCommand = new System.Data.SqlClient.SqlCommand(sCmdText, SQLConnection);
SQLCommand.CommandType = System.Data.CommandType.StoredProcedure;

Pass the parameters

SQLCommand.Parameters.Add("@OutputParameterName, System.Data.SqlDbType.Int);
SQLCommand.Parameters("@OutputParameterName").Direction = System.Data.ParameterDirection.Output ;

SQLCommand.Parameters.Add("InputParameterName", System.Data.SqlDbType.NVarChar,50);
SQLCommand.Parameters("InputParameterName").Value = "Value To Be Passed as Input Parameter" ;

Execute the stored procedure to reader

SQLRdr = SQLCommand.ExecuteReader(); 

Check for a result to the query

dim bOK As Boolean;
bOk = SQLRdr.HasRows;

Execute the SQL Reader

SQLRdr.Read();

Capture the value of the output parameter from the first record and assign it to the local variable

Dim ValueReadFromOutputParameter as Integer;
ValueReadFromOutputParameter = SQLRdr("@OutputParameterName");

Execute the SQL Command to the Reader

SQLRdr.Close();

Cleanup and dispose of command and connection memory

SQLCommand.Dispose();
SQLConnection.Dispose();