Aug 12, 2022

POS: Execute stored procedure

This blog post will show you some ways to execute stored procedures from POS

POS: Execute stored procedure

POS: Execute stored procedure

This blog post will show you some ways to execute stored procedures from POS using standard API: SqlStoredProcedure and DBUtil from DataAccess DLL.
Let’s imagine we need to execute stored procedure [dbo].[TESTPROCEDURENAME] with 2 input parameters: param1 and param2.
A. Execute Stored procedure that returns data rows
In this case stored procedure returns the set of records.
using LSRetailPosis.DataAccess;
using LSRetailPosis.Settings;
SqlStoredProcedure sqlStoredProcedure = new SqlStoredProcedure( "[dbo].[TESTPROCEDURENAME]" );
sqlStoredProcedure.Add( "@param1", param1Value );
sqlStoredProcedure.Add(" @param2", param2Value );
var dbUtil = new DBUtil(ApplicationSettings .Database .LocalConnection);
using (DataTable dataTable = this.DBUtil .ExecuteStoredProcedureWithData( sqlStoredProcedure ) )

{
   foreach (DataRow dataRow in dataTable.Rows)
   {
       //process data returned by procedure
   }
}

B. Execute Stored procedure that returns scalar value
In this case stored procedure returns single (scalar) value: number, string, date, etc.
using LSRetailPosis.DataAccess;
using LSRetailPosis.Settings;
SqlStoredProcedure sqlStoredProcedure = new SqlStoredProcedure( "[dbo] .[ TESTPROCEDURENAME ]" );
sqlStoredProcedure.Add( "@param1", param1Value );
sqlStoredProcedure.Add( "@param2", param2Value );
var dbUtil = new DBUtil( ApplicationSettings .Database .LocalConnection );
var result = this .DBUtil .ExecuteStoredProcedure WithDataScalar( sqlStoredProcedure ) );
//now you need to convert result to appropriate type ( bool, string, int, etc. )