GetDsBySp Method
- Last UpdatedMar 17, 2026
- 3 minute read
The GetDsBySp() method allows calls to stored procedures in the MES database that return a DataSet.
To define the input parameters that will be passed, use the methods in the SpParameter class.
'Declaration
Public Shared Function GetDsBySp( _
ByVal spName As String, _
ByVal ParamArray parameters() As SpParameter _
) As DataSet
'Usage
Dim spName As String
Dim parameters() As SpParameter
Dim value As DataSet
value = DirectAccess.GetDsBySp(spName, parameters)
public static DataSet GetDsBySp(
string spName,
params SpParameter[] parameters
)
Parameters
- spName
- Required. A string that is the name of the stored procedure within the MES database to be called.
- parameters
Optional. If one or more parameters are required by the stored procedure, provide them as a list of Name/Value pairs:
- The Name should match the name of a parameter in the stored procedure being called without the "@" character.
- The Value is the value being passed in for the parameter.
Each parameter Name/Value pair should be supplied only once.
Return Value
Returns a DataSet that contains rows as specified by the stored procedure being called.
Return parameters will always be in lower case. For example, a parameter named @Output1 in the stored procedure will need to be referenced as output1 in the SPReturnValues.GetReturnValueAsX("output1") methods.
- The call will fail if the stored procedure is not a valid MES database stored procedure. So the call will fail if the stored procedure being called is a SQL System stored procedure or a stored procedure that is not in the MES database.
- The GetDsBySp() method does not handle session IDs. If a session ID is supplied, the middleware will discard it. The session ID is used to set the context for calls that modify the database. If you want to make a call that modifies the database, use ExecSp().
- When calling a custom stored procedure and a parameter is not included in this method call, the middleware will call the stored procedure with a null value for any missing parameter. The method does not automatically replace the null with the default value defined in the stored procedure. When using parameters for this method, all parameters must be supplied. Any missing parameters will be set to null by the MES middleware and will not use any defaults defined in the stored procedure. The stored procedure can be programmed to detect a null and then set a default value as is done in many of the MES stored procedures.
- For information about datetime and decimal separator requirements when passed in strings, see the overview topic Datetime and Decimal Format Requirements.
A stored procedure that takes no parameters could be called as follows in Visual Studio. If using System Platform scripting, the parameters list is required. Pass in a single parameter with any name/value and it will be ignored.
- DirectAccess.GetDsBySp("MySp");
A stored procedure that takes one parameter could be called as follows:
- DirectAccess.GetDsBySp("MySp", new SpParameter("param_name", param_value));
A stored procedure that takes two parameters could be called as follows:
- DirectAccess.GetDsBySp("MySp", new SpParameter("param_name1", param_value1), new SpParameter("param_name2", param_value2));
The first code example below is a complete example of using this method to call a custom stored procedure "sp_SA_ENT" that requires multiple parameters.
The second code example below is a simplified System Platform scripting example where the custom stored procedure does not require any parameters. Note that dummy values must be provided to the method.
GetDsBySp Example Call (C#)
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using FactMES.API.Core;
using Fact.Common;
namespace DirectAccessConsoleApp
{
class Program
{
static void Main(string[] args)
{
try
{
DateTime shiftStartLocal;
//valid call using Ent.Getall()
DataSet tempDs = FactMES.API.Core.Ent.GetAll(null, "Plant", null, null, null, null, 1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
int entId = (int)tempDs.Tables[0].Rows[0]["ent_id"];
shiftStartLocal = (DateTime)tempDs.Tables[0].Rows[0]["cur_shift_start_time_local"];
//valid call using GetDsBySp, functionally similar to the Ent.GetAll() call
DataSet actualDs = FactMES.API.Core.DirectAccess.GetDsBySp("sp_SA_Ent", new SpParameter("ent_name", "Plant"), new SpParameter("can_sched_shifts", true), new SpParameter("cur_shift_start_time_local", shiftStartLocal));
int entId = (int)actualDs.Tables[0].Rows[0]["ent_id"];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.Read();
}
}
}
GetDsBySp Example Call from System Platform Script
dim result as aaMES.Result;
dim SPParam[1] as aaMES.Core.aaSpParameter;
SPParam[1] = new aaMES.Core.aaSpParameter("test","test");
result = aaMES.Core.aaDirectAccess.GetDsBySp( "sp_I_Test", SPParam );
Datetime and Decimal Format Requirements