janos.nagy

December 2009 Previous month Next month

if you read theThe SP_TransactionNotification Stored Procedure Stored Procedure  you understood the advantage of procedure: "you can do some validation in SAP Business One and prevent the user from doing an action if the validation condition fails."

Probably Everybody can have the question:
Is It possible to execute custom code via SP_TransactionNotification procedure?

Basically the answer is Yes.

Let's investigate the following scenario: Company sales process requires to send delivery notification email to specific customers at time when delivery note has been sucessfully issued.

We can solve this problem via DI API Messages Object using .NET code. There is more elegant another way: Sending email without Addons using the same .NET code from SQL Server.

Solution 

Write a .NET Class Based DLL which send an email to an address given by parameters. The DLL is using DI API to send an email over SBO mailer service.

I have used C# and Mesasges Object of DI API to send an email.

 

Sending message via DI API using Messages Object - without GUI

Create a New Windows Class Library Project  in  C#, give name SimpleDeliveryNotification and include the SAP Business One DI API 2007 as a reference.

Import namespaces required to MS SQL 2005 CLR code

Write a Class which can connect to SAP B1 company and send message.

Important notes:
- SAP B1 UserName and Password should provided for access to Company (single sign on is not working here)
- UseTrusted propety can be used (we are inside SQL - the code is running on sql server directly)
- For future flexible usage: Queries can be used to determine Company Name  and Server name for connection parameters of DI API Company Object.

Compile the Class, and Copy the SimpleDeliveryNotification.dll into anywhere on the MS SQL 2005 server.

MS SQL 2005 Settings

Enable CLR on MS SQL Server, connect by Management Studio, and open a new Query. There enter the following commands.

Register the DLL for MS SQL server by CREATE ASSEMBLY command from your copied (compiled path)

Create a Stored Procedure for Executing your managed code (DLL). Procedure name: SP_Send_Delivery_Notification and this will be called when sending the email message.

You can test the Stored procedure by Calling it from MS SQL Query Analizer or from an SAP B1 Query.

The SP_TransactionNotification code

When a delivery note has been issued, the SP_TransactionNotification is executed. In the following example the numatcard field holds the Customer Order Number. E-Mail field from Customers master data can used for populating the email address where the Simple Delivery Notification should be sent.

Conclusion

The CLR comined with SP_TransactionNotification stored procedure is a flexible, fast and elegant possiblity to handle background processes like sending notifications, creating automatic post operations using DI API .

Sample code can be downloaded from here

Background: CLR

The Common Language Runtime (CLR) is a core component of Microsoft's .NET initiative. It is Microsoft's implementation of the Common Language Infrastructure (CLI) standard, which defines an execution environment for program code. Developers using the CLR write code in a language such as C# or VB.NET. At compile time, a .NET compiler converts such code into CIL code. At runtime, the CLR's just-in-time compiler converts the CIL code into code native to the operating system.
Alternatively, the CIL code can be compiled to native code in a separate step prior to runtime. This speeds up all later runs of the software as the CIL-to-native
compilation is no longer necessary."

definition source: Wikipedia 

In this blog I would like to demonstrate feature of ActiveX control can be used from SDK.

Most common problem for SAP B1 developers who would like create secure entry of passwords:  how can make a password box or textbox with masked with * characters.

Information for MS Office users: Microsoft Forms 2.0 library contains ActiveX objects. For distribution of Forms 2.0 library please see article KB224305 at Microsoft website.

1. Add the Microsoft Forms 2.0 Library to Your Visual Studio Project

In a Microsoft Visual Studio
a) Click on Project/Add Reference Menu
b) Select COM tab, and find and select the Microsoft Forms 2.0 Library from the list.

c) Click on OK button to close the Add references window

2. Declaration of Global Object for ActiveX Password Box

As a next step you should declare a Global object inside your addon class for ActiveX Password Textbox. (local object is not working based on my tests).
The Microsoft Forms 2.0 Library assembly namespace is Microsoft.Vbe.Interop.Forms.
 

3. Adding the ActiveX control to your form

At creation of a Form, you can use "Forms.TextBox.1" as the ActiveX ClassId and should assign the previously created Global Object to the object of the ActiveX control.
Also set the property PasswordChar to * , which will mask the entered text.



You can use the oTextBox Global Object to manage the password value.

Actions