POS: Execute stored procedure

[Total: 0 Average: 0/5]

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.

sing 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
    }
}

sing 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.ExecuteStoredProcedureWithDataScalar(sqlStoredProcedure));

//now you need to convert result to appropriate type (bool, string, int, 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.ExecuteStoredProcedureWithDataScalar(sqlStoredProcedure));

//now you need to convert result to appropriate type (bool, string, int, etc.)

AX2012 Retail POS development SQL Stored procedure

POS: Execute stored procedure