How to call a stored procedure from web service?

Question

How to call a stored procedure from web service?

Answer

Create a source code schema:

namespace Terrasoft.Configuration.UsrTestStored
{
    using System;
    using System.Web;
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Linq;
    using System.Text;
    using System.Collections.Generic;
    using System.Collections.Concurrent;
    using System.Data;
    using Terrasoft.Common;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Factories;
    using Terrasoft.Core.Store;
    using Terrasoft.Nui;
 
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class UsrTestStored
    {
        [OperationContract]
        [WebInvoke(Method = "GET", RequestFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped,
        ResponseFormat = WebMessageFormat.Json)]
        public string GetTest(string inputParam)
        {
            var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
            StoredProcedure storedProcedure = new StoredProcedure(userConnection, "tsp_Test");
            storedProcedure.PackageName = userConnection.DBEngine.SystemPackageName;
            using (var dbExecutor = userConnection.EnsureDBConnection()) {
               try {
                   dbExecutor.CommandTimeout = 0;
                   dbExecutor.StartTransaction();
                   storedProcedure.Execute(dbExecutor);
                   dbExecutor.CommitTransaction();
                   return "OK";
               } catch {
                   dbExecutor.RollbackTransaction();
                   return "NOT OK";
               }
            }
        }
    }
}