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. )