Skip to main content
InSource Solutions

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

insource logo large.jpg

 

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.

 

Capture2.JPG

 

 

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();