General information

• External functions are used to call your own C# program code on the server side. The result of calling an external function is always a value expressed as a text string.

• External functions are used in cases where
  • it is not possible or appropriate to provide the required functionality via a server-side script,
  • it is necessary to call the server code from JavaScript, typically in the form of an AJAX request.

• The server function “ngef(string id, string arg0, string arg1, string arg2, …)” is used to call external functions
  • The mandatory parameter "id" specifies the external function identifier.
  • Other parameters are optional and are passed to the external function as the "string[] args" array in the same order in which they are listed when calling.

• External functions can only be used by administrators when designing applications. A regular user does not have direct access to their calls from the application user interface, and external functions are always launched only indirectly through applications designed by administrators.

• An error may occur during the execution of an external function or its execution may be interrupted. The system's behavior in such a case depends on the context from which the external function was called:
  • External functions run from a script terminate the execution of the script in the event of an error or interruption. The system returns to the editing form or the viewing page from which the script was called and displays an error message to the user. For the "OnBeforeSave" and "OnBeforeDelete" scripts, such an interruption means preventing the planned saving or deletion of the database record.
  • External functions run from "HTML" or "JavaScript" controls in the event of an error or interruption terminate the loading of the editing form or viewing page and display an error message to the user.
  • External functions run from other controls in the event of an error or interruption terminate only the loading of data into the given control. The error message is displayed directly in this control and the loading of other controls is not affected.

2. Library “ngef.dll”

• External functions are part of the “ngef.dll” library located in the “NETGenium\bin” directory. A clean installation of NET Genium has a default library “ngef.dll” in the “bin” directory, which is the result of compiling the default source code located in the “NETGenium\bin\ngef.cs” file.

using System;

namespace NETGenium
{
  public class ExternalFunctions
  {
    public static string ngef(string id, string[] args, bool test, DbCommand cmd, DbConnection conn)
    {
      if (test) return "";

      switch (id)
      {
        // case "MyFirstFunction": return MyFirstFunction();
        default: return conn == null ? "" : conn.ExternalFunctionNotFound(id, cmd);
      }
    }
  }
}

• The source code for external functions uses objects and methods from the “NETGeniumConnection.dll” library, which is stored in the “N:\NetGenium\Projekty\NetGenium\References” directory. “NETGeniumConnection.dll” is a library with basic functions for working with databases and file attachments.
• Modifications of the “ngef.dll” library can only be performed through a separate project in the “Visual Studio 2015” application and higher, resp. by programming the source code of this project, and then compiling the project into the library “ngef.dll”.
• A clean installation of NET Genium does not contain a project with source codes for external functions. Before you can program external functions, you must create a new library project in Visual Studio using the following steps, and add a reference to the “NETGeniumConnection.dll” library:
  • Start Visual Studio
  • From the menu on the main bar, select “File/New/Project…” (Ctrl+Shift+N)
    • Project type: Class Library (.NET Framework)
    • Project name: ngef
    • Location: optional project location
    • Solution: Create new solution
    • Place solution and project in the same directory: Yes
    • Create directory for solution: No (Visual Studio 2015)
    • Framework: .NET Framework 4.7.2
  • Right-click on the file “Class1.cs”, and select “Delete”
  • Right-click on the project name “ngef”, select “Add” / “Existing Item…” (Shift+Alt+A), and select the path to the file “NETGenium\bin\ngef.cs” on the computer disk
  • Right-click on “References”, select “Add Reference…”, and select the path to the file “NETGenium\bin\NETGeniumConnection.dll” on the computer disk
  • Right-click on “References” / “NETGeniumConnection”, select “Properties” (Alt+Enter), and set the “Copy Local” attribute to “False”
  • Choose “Debug” compilation mode
    • “Debug” mode generates “ngef.dll” and “ngef.pdb” files by default
    • Thanks to the “ngef.pdb” file, errors and interrupts in external functions are easily detected, because the “Stack Trace” of each error also includes the file name and the line number on which the interrupt occurred.
    • The “Release” mode is only recommended for the final version of the tuned source code in the “ngef.dll” library. By default, the “Release” mode only generates the “ngef.dll” file, which is sufficient for NET Genium, but when interrupted, finding the cause of the error is very complicated.
  • Compile the project – from the menu on the main bar select “Build” / “Build Solution” (Ctrl+Shift+B)
  • Copy the files “ngef.dll” and “ngef.pdb” from the directory “bin\Debug” to the directory “NETGenium\bin” if the library is compiled in “Debug” mode, or the file “ngef.dll” from the directory “bin\Release”) to the “NETGenium\bin” directory when compiling the “ngef.dll” library in “Release” mode. In the case of “Release” mode, it is important to delete the “ngef.pdb” file from the “NETGenium\bin” directory.
    • Uploading a new version of the “ngef.dll” library will always restart the web application, as will any change in the “NETGenium\bin” directory.
• The source code of external functions can be freely modified, but the following conventions in the “ngef.cs” file must not be changed:
  • Namespace NETGenium
  • public class ExternalFunctions
  • public static string ngef(string id, string[] args, bool test, DbCommand cmd, DbConnection conn)
  • if (test) return "" ;
  • default: return conn == null? "" : conn.ExternalFunctionNotFound(id, cmd);

3. Parameters for the “public static string ngef” function

• string id
  • An external function identifier that uniquely identifies a function or program code executed by calling the server function “ngef(id)”.
  • It is necessary to create a separate “case” for each identifier inside the “switch” command in the “public static string ngef” function guide.
• string[] args
  • External function parameters that are part of the server function call “ngef(id, arg0, arg1, arg2,…)” in the second and next position in the parameter list.
  • The “args” parameter list can have 0 elements if the call to the “ngef(id)” server function contains only the identifier of the external function without additional parameters.
• bool test
  • The logical value “test” determines whether the external function is called from the script designer using the “Run script” button.
  • The default source code for external functions includes an “if(test) return "" ;” statement on the first line of the “public static string ngef” function, which ensures that the external function from the script designer does not run unintentionally when debugging the script.
• DbCommand cmd
  • The “cmd” object specifies a database object of the “DbCommand” type, which is used to write data to the database.
  • External functions executed from a script using the “ngef” server function, together with other script commands, use a single database object “cmd”, which is used to write or delete data from the database in a single transaction of type “IsolationLevel.ReadCommitted”.
  • Database operations performed via the “cmd” object do not take effect in the database until the transaction is committed. This occurs automatically at the end of each successful script, or by calling the server function “COMMIT()”.
  • Any error or interruption during the execution of the external function or the script itself will ensure a “rollback” of all database operations performed via the “cmd” object.
  • The use of the “cmd” object is not appropriate in cases of bulk data imports, or in general in cases where data entry is not required within the transaction. Typical data changes through the “cmd” object contain at most units of write or delete commands.
  • Using the “cmd” object can cause a “deadlock” of the database. When working with an object, it is always important to load everything needed from the database first, and only then write it to the database. Deadlock arises in situations where the programmer first writes data to a database table and then tries to read from the same database table.
    • For a Firebird database, a “deadlock” occurs when trying to read from the database table to which the transaction was written. Thus, Firebird locks the entire database table to which it was written during the transaction.
    • For an MSSQL database, a “deadlock” occurs when trying to read from a row of the database table to which the transaction was written. Therefore, MSSQL locks the rows of the database table to which it was written during the transaction.
  • Whenever it is not necessary to write to the database within a single transaction together with a script, it is recommended to use your own object “cmd” – for example using the command “using (DbCommand cmd = new DbCommand(conn)) {}”.
  • An external function executed from a location other than the script has the “cmd” object set to “null”.
• DbConnection conn
  • The “conn” object specifies a database object of the “DbConnection” type, which represents a connection to the NET Genium database, and is used to read or write data to the database.

4. Debugging external functions in the console application

• The most convenient way to write external functions is to design a prototype of an external function in a console application, and then copy the tuned source code to the “ngef.dll” library project.
• Compiling and running a console application is very fast, and allows you to easily debug and trace either using “breakpoints” or by listing information to the console using the “Console.WriteLine()” command.
• When designing an external function, it is important to choose a new external function declaration so that the resulting source code can be easily transferred to the “ngef.dll” library project using “Ctrl+C” and “Ctrl+V”.

4.1. Creating a console application

• In the first step, you need to create a new console application project in Visual Studio using the following steps, and add a reference to the “NETGeniumConnection.dll” library:
  • Start Visual Studio
  • From the menu on the main bar, select “File / New / Project…” (Ctrl+Shift+N)
    • Framework: .NET Framework 4.7.2
    • Project Type: Console Application
    • Name: ConsoleApp1
    • Location: optional project location
    • Solution: Create new solution
    • Create directory for solution: No
    • Add to Source Control: No
  • Right-click on “References”, select “Add Reference…”, and select the path to the file “References\NETGeniumConnection.dll” on the computer disk
  • Choose “Debug” compilation mode
  • Start project – select “Debug” / “Start Debugging” (F5) from the menu on the main bar

4.2. NET Genium environment simulation

• In the second step, you must modify the default source code in the “Program.cs” file so that the console application simulates the NET Genium environment, which runs external functions by calling “public static string ngef” with the parameters “args”, “cmd” and “conn”.

using NETGenium;
using System;

namespace ConsoleApp1
{
  class Program
  {
    static void Main(string[] args)
    {
      NETGeniumConsole console = new NETGeniumConsole();

      using (DbConnection conn = new DbConnection(@"driver=firebird;datasource=localhost;user=SYSDBA;password=masterkey;database=C:\Firebird\netgenium.fdb;charset=WIN1250;collation=WIN_CZ"))
      // using (DbConnection conn = new DbConnection("server=(local);Trusted_Connection=true;database=netgenium"))
      using (DbCommand cmd = new DbCommand(conn))
      {
        conn.Open();

        conn.RootPath = "C:\\inetpub\\wwwroot\\netgenium";
        DateTime now = DateTime.Now;

        Console.WriteLine(MyFirstFunction(new string[] { "a", "b", "c" }, cmd, conn));

        Console.WriteLine();
        Console.WriteLine(conn.User.FormatTimeSpan(DateTime.Now - now));
      }

      console.Exit();
    }

    private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
    {
      return "Hello World! args: " + string.Join(";", args);
    }
  }
}

4.3. Writing the source code of an external function

• In the third step, it is then possible to write the source code of the external function “MyFirstFunction”.

5. Internal external functions called by NET Genium itself

• The following external functions are not called by a user script, but directly by the NET Genium framework.
• They are called automatically in precisely defined situations.

• External functions can return a string. If the return value is to be processed, it must start with the prefix: OK\r\n
• This prefix is followed by the actual content (HTML, JavaScript, CSS, color, value, file path, etc.). == 5.1. NETGenium.Campaigns.Image ==
• Triggered when the Campaigns/Image.aspx?guid page is displayed, which occurs when trying to read an email due to an image that refers to Image.aspx
  • html = html.Replace("[UNSUBSCRIBE]", "<a href=\"" + url + "/Unsubscribe.aspx?" + guid + "\">Unsubscribe</a> from these emails...") + "<img src=\"" + url + "/Image.aspx?" + guid + "\">";
• args = new string[] { queryString }

5.2. NETGenium.Campaigns.Unsubscribe

• Triggered when the Campaigns/Unsubscribe.aspx?guid page is displayed, which occurs when the user clicks on the link in the email
  • html = html.Replace("[UNSUBSCRIBE]", "<a href=\"" + url + "/Unsubscribe.aspx?" + guid + "\">Unsubscribe</a> from these emails...") + "<img src=\"" + url + "/Image.aspx?" + guid + "\">"
• args = new string[] { queryString }

5.3 NETGenium.Campaigns.View

• Triggered when the Campaigns/View.aspx?guid page is displayed, which occurs when the user clicks on the link in the email
  • html = html.Replace("[BROWSER]", "<a href=\"" + url + "/View.aspx?" + guid + "\">View in web browser...</a>");
• args = new string[] { queryString }

5.4. NETGenium.Copyright

• Runs in the navigator (on the Apps.aspx or App.aspx page) every time the navigator is displayed, and allows you to replace the copyright with any html code
• The function must return html, e.g. OK\r\nhtml

5.5. NETGenium.CSS

• Runs before saving the CSS file to disk
• args = new string[] { value, browser.ToString() }
• The function must return the changed CSS, the string must start with OK\r\n

5.6. NETGenium.DataGrid2Excel

• Runs after exporting data from a datagrid so that the exported file can be opened and modified
  • Regular datagrid – only for "xlsx" format
  • Statistical datagrid – only allows "xlsx" format without the option to select
• args = new string[] { excel.path, "Q" + qb.ID, qb.FormInfo.ID.ToString(), qb.date1, qb.date2 }

==5.7. NETGenium.DataTable ==

• Triggered when data is loaded from the database
• https://www.netgenium.com/en/support/manuals/administrator-guide/database-query-designer

5.8. NETGenium.Download

• Runs when the Download.aspx page is viewed
• args = new string[] { id.ToString(), filename, path }
• The function can return the resulting path to a file on disk that will be passed to the user for download, the string must start with OK\r\n

5.9. NETGenium.Elearning.Test.ERROR

• Triggered after failing a test
• args = new string[] { test["id"].ToString(), sb_ok.ToString(), sb_ko.ToString(), sb_db.ToString() }
  • test["id"].ToString() – Test ID
  • sb_ok.ToString() - a semicolon-separated list of IDs of correctly answered questions
  • sb_ko.ToString() - semicolon-separated list of IDs of incorrectly answered questions
  • sb_db.ToString() - HTML test including questions and answers, which is stored in the database table ng_eltestvysl in the column ng_test0
• The function can return html, the string must start with OK\r\n

5.10. NETGenium.Elearning.Test.OK

• It will start after successfully passing the test
• args = new string[] { test["id"].ToString(), sb_ok.ToString(), sb_ko.ToString(), sb_db.ToString() }
  • test["id"].ToString() – Test ID
  • sb_ok.ToString() - a semicolon-separated list of IDs of correctly answered questions
  • sb_ko.ToString() - semicolon-separated list of IDs of incorrectly answered questions
  • sb_db.ToString() - HTML test including questions and answers, which is stored in the database table ng_eltestvysl in the column ng_test0
• The function can return html, the string must start with OK\r\n == 5.11. NETGenium.ExcelPassword ==
• Runs before encrypting an Excel file
• args = new string[] { }
• The function must return the password for encrypting the Excel file, the string must start with OK\r\n

5.12. NETGenium.Import

• Runs when the Import.aspx page is displayed
• args = new string[] { }
• The function can return html, which is inserted into the response
• If the function returns nothing, an empty string is placed in the response

5.13. NETGenium.ImportXml

• Runs when the ImportXml.aspx page is displayed
• args = new string[] { }
• The function can return html that is inserted into the response
• If the function returns nothing, an empty string is stored in the response

5.14. NETGenium.LockRecord

• Triggered when a database record is locked
• args = new string[] { form.ToString(), id.ToString() }

5.15. NETGenium.Log

• Runs when logging to disk
• args = new string[] { filenameWithoutExtension, message, logLevel.ToString(), loginname }

5.16. NETGenium.LoginCode

• Runs before each user login with username and password
• The function must return an authentication code, which the user must then fill in as part of two-factor authentication – the string must start with OK\r\n
• The function must send this code to the user either via email or SMS

5.17. NETGenium.Logout

• Runs after user logs out
• args = new string[] { }
• A function can return javascript, e.g. OK\r\nalert('The user just logged out...'); - so the string must start OK\r\n

5.18. NETGenium.Menu

• Launches in the navigator (on the page Apps.aspx nebo App.aspx) every time the navigator is displayed, and allows you to insert any html code at the beginning of the page
• The function must return html, e.g. OK\r\nhtml

5.19. NETGenium.Menu1

• Runs in an edit form, on a view page, or in portlets
• args = new string[] { }
• The function must return html, which is inserted at the beginning of the page, e.g. OK\r\nhtml

5.20 NETGenium.Menu2

• Runs in an edit form, on a view page, or in portlets
• args = new string[] { }
• The function must return html, which is inserted at the end of the page, e.g. OK\r\nhtml

5.2. NETGenium.OnAfterLogin

• Runs after successful user login
• Allows you to run javascript in the client browser using return "OK\r\n" followed by the javascript code itself e.g. return "OK\r\nalert('Welcome...');"

5.22. NETGenium.OnAfterPrint

• It is run immediately after printing to the print template, just before sending the file to the client station, so that the file can be modified using an external function.
• conn.PrintingProcess.File - path to the file to be printed to
• conn.PrintingProcess.FileName - the name of the file that will be offered to the user for download - can be changed
• conn.PrintingProcess.Template - path to print template
• conn.PrintingProcess.Form - Form ID from which printing was initiated
• conn.PrintingProcess.ViewPage - ID of the view page from which the print was initiated

5.23. NETGenium.OnBeforeLogin

• Triggered in the following situations:
  • Login with username and password: args = new string[] { "", loginname, password }
    • The external function is run just before attempting to authenticate the user (by finding their username in the "susers" table in the "loginname" column), and
    • allows you to prevent user login and possibly run javascript in the client browser using return "OK\r\n", which can be followed by the javascript code itself, e.g. return "OK\r\nalert('Sorry...');", or
    • allows you to influence user authorization – the function can ensure external user authorization in LDAP, Azure Active Directory, etc., and reset the list of their user groups based on the result.
  • Automatic login thanks to Active Directory (WindowsAuthentication) and LoginByIdentity.txt: args = new string[] { account }
    • The external function is run just before the user logs in after successful authentication, and
    • allows you to influence user authorization – the function can ensure external user authorization in LDAP, etc., and reset the list of their user groups based on the result.
  • Automatic login thanks to Azure Active Directory and LoginByMicrosoft.aspx: args = new string[] { email }
    • The external function is run after the user has successfully authenticated on the Microsoft website, and just before attempting to find their email address in the "susers" table in the "email" column, and
    • allows you to prevent a user from logging in by deleting their user account in the "susers" table, or
    • allows you to influence user authorization – the function can ensure external authorization of a user in Azure Active Directory, etc., and reset the list of his user groups based on the result.

• A typical reason for modifying the external function NETGenium.OnBeforeLogin is to authorize a user in an external application that uses a user role system (permitted user activities in the information system):
  • LDAP – the list of user roles is determined by calling LDAP web services
  • Azure Active Directory – the list of user roles is determined by calling the Microsoft Graph API web services, or the list of user roles is part of the login token, which NET Genium stores in the session object "microsoft_access_token" when authenticating the user
• The equivalent of user roles in an external application are our user groups
  • A user can be a member of none, one, or more user groups at the same time
  • A user role in NET Genius is defined as a user group with the "Role" box checked - the string "(R)" is automatically placed at the beginning of the user group name, followed by the user group name.
  • A user role in an external application determines the scope of permitted user activities
  • A user role in an external application cannot be linked 1:1 to our user group because
    • the user role system in the external application does not use a rights system for controls, applications, etc. (the external application probably does not even use a hierarchy such as controls, forms, applications, etc.),
    • the list of user roles in the external application is not sufficient for us to correctly determine the list of all other user groups that must be set up for the user along with the "Role" user groups,
    • we use standardized applications such as ERP, etc., or we create tailor-made applications for the customer at a stage when we do not have a final list of user roles from them.

• To correctly set up user groups in NET Genius based on user roles in an external application, we use permission groups
  • Permission group is not equivalent to a user role in an external application
  • A permission group defines a set of user groups in which a user is a member
  • Permission group is an attribute of a user, a user can only be assigned one permission group (this attribute is a foreign key and can be filtered by it)
  • The authorization group is evaluated automatically when a user is saved by comparing the list of user groups of the given user with the list of authorization groups
  • The permission group contains the "Member of" field - this field is the equivalent of the users role in an external application
  • By filling in "Member of" for at least one permission group, we say that each time a user logs in, the list of their user groups according to user roles in the external application should be replaced. From the list of user roles, it is determined which permission groups correspond to these roles, and from these permission groups, a list of all user groups is compiled, which are then set for the given user. If the resulting list of user groups has at least one element, the user has permissions; if the resulting list is empty, the user does not have permissions.
    • If the user has permissions, the list of user groups defined in NET Genium will be replaced by the equivalent of user roles
    • If the user does not have permission, the list of user groups in NET Genium will be deleted
  • If "Member of" is not filled in for any permission group, authorization in the external application is only used to determine whether the user has permission to log in or not.
    • If the user has permissions, he will be left with a list of user groups defined in NET Genium
    • If the user does not have permission, the list of user groups in NET Genium will be deleted
  • The user role does not correspond meaningfully to either the user group (in ERP we use the "Role" checkbox, which distinguishes the type of user group), or the authorization group, which defines the list of user groups, and appears in the user form as a foreign key. From our point of view, this is a user role in an external application, the list of which is limited for us, and insufficient for us to be able to correctly edit our applications according to it. The user role is a semantically separate entity for which a new table could exist, however, it is unnecessary at the moment - at the moment we are only interested in the string ID of the user role, which we fill in the authorization group form, and due to the 1:1 binding and uniqueness of the user role ID, this is not filled anywhere other than in one authorization group.

5.24. NETGenium.OpenForm

• Runs in the editing form immediately after opening a database connection
• args = new string[] { ds.Form.ID.ToString() }
• The function is the only place where it is possible to set the value conn.Page.UserGroupIds - a list of user group IDs that the user has temporarily assigned to work in the editing form

5.25. NETGenium.OpenPortlets

• Runs in portlets immediately after opening a database connection
• args = new string[] { appgroup.ToString() }

5.26. NETGenium.OpenViewPage

• Runs on the preview page immediately after opening a database connection
• args = new string[] { viewpage.ID.ToString() }

5.27. NETGenium.PageProcessingTime

• Runs in the edit form and on the preview page just before sending the HTML to the user
• args = new string[] { (int)MathExt.Round(time.TotalMilliseconds).ToString() }

5.28. NETGenium.Payment

• Triggered when the Payment.aspx page is displayed
• args = new string[] { }
• The function can return html, the string must start with OK\r\n

==5.29. NETGenium.PaymentNotification ==

• Triggered when the PaymentNotification.aspx page is displayed
• args = new string[] { }
• The function can return html, the string must start with OK\r\n

5.30. NETGenium.PlannerCellColor

• Triggered when a cell is displayed in the planner
• args = new string[] { viewfield.ToString(), "0", date1.ToString(), date2.ToString(), resource, StartDate.ToString(), EndDate.ToString() };
• args = new string[] { viewfield.ToString(), "1", date1.ToString(), date2.ToString(), resource, date1.Date.ToString(), date1.Date.ToString() };
• The function must return the cell color in R,G,B format, the string must start with OK\r\n

5.31. NETGenium.PlannerCellColorWeekend

• Triggered when a cell is displayed in the planner
• args = new string[] { }
• The function must return the cell color on the weekend in R,G,B format, the string must start with OK\r\n

5.32. NETGenium.PlannerCellHtml

• Triggered when a cell is displayed in the planner
• args = new string[] { viewfield.ToString(), "0", date1.ToString(), date2.ToString(), resource, StartDate.ToString(), EndDate.ToString() };
• args = new string[] { viewfield.ToString(), "1", date1.ToString(), date2.ToString(), resource, date1.Date.ToString(), date1.Date.ToString() };
• The function must return html, the string must start with OK\r\n

5.33. NETGenium.RegisterExternalFunction

• It is triggered when the ngef.aspx page is displayed in a situation where the external function is not authorized for the currently logged in user (the ngef.aspx call was not placed in an HTML or JavaScript control, for example)
• This situation is, for example, directly calling the ngef.aspx page in a web browser
• The function can return OK\r\n, which allows the execution of the external function
• Calling this function is an alternative to the list of anonymous external functions, which is defined in the NET Genium settings

5.34. NETGenium.SendMessage

• Triggered after sending an email message from the "New Email Message" form
• The paths to file attachments on the server disk are transferred to args, so it is possible to, for example, pack these files into a ZIP and attach them as an attachment to NG, etc.

5.35. NETGenium.SortOrder

• After setting the order, the record is launched either in the datagrid or tree
• args = new string[] { "Q" + qb.ID, qb.Form.ID.ToString() };

5.36. NETGenium.TimeTableCellColor

• Triggered when a cell in the timetable is displayed in the Gantt chart
• args = new string[] { viewfield.ToString(), viewtype.ToString(), date1.ToString(), date2.ToString(), resource, mindate.ToString(), maxdate.ToString() };
• The function must return the cell color in R,G,B format, the string must start with OK\r\n

5.37. NETGenium.Translate

• Runs when the Translate.aspx page is displayed
• args = new string[] { sourcelanguage, targetlanguage, sourcevalue, format }
• The function can return html, the string must start with OK\r\n

5.38. NETGenium.TreePicker

• see TREEPICKER function

5.39. NETGenium.UnlockRecord

• Triggered when a database record is unlocked
• args = new string[] { form.ToString(), id.ToString() }

5.40. NETGenium.Url

• Runs before sending an email message from a script
• args = new string[] { user.DatabaseSettings.UrlEmails.Length != 0 ? user.DatabaseSettings.UrlEmails : user.DatabaseSettings.Url }
• The function can return the changed address, e.g. OK\r\nhttp://localhost/netgenium

6. Reading data from the database

6.1. Retrieve records from an SQL query into a DataTable object

using NETGenium;
using System;
using System.Data;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DataTable data = Data.Get("SELECT * FROM sholiday WHERE date_ > " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  Console.WriteLine(conn.User.FormatDataTableText(data));

  foreach (DataRow row in data.Rows)
  {
    // Console.WriteLine(row["id"]);
  }

  return "";
}

6.2. Retrieve records from an SQL query into a DbRow object

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DbRow row = new DbRow("SELECT * FROM sholiday WHERE date_ > " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (row.Read())
  {
    // Console.WriteLine(row["id"]);
    Console.WriteLine(row.Report());
  }

  return "";
}

6.3. Value parsing

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DbRow row = new DbRow("SELECT * FROM sholiday WHERE date_ > " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (row.Read())
  {
    int id = (int)row["id"];
    Console.WriteLine("id: " + id);

    int pid = Parser.ToInt32(row["pid"]);
    Console.WriteLine("pid: " + pid);

    double _pid = Parser.ToDouble(row["pid"]);
    Console.WriteLine("pid: " + _pid);

    string name = row["name"].ToString();
    Console.WriteLine("name: " + name);

    DateTime date = Parser.ToDateTime(row["date_"]);
    Console.WriteLine("date: " + conn.User.FormatDateTime(date));
  }

  return "";
}

6.4. Indexing of retrieved records by primary key

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DataTable data = Data.Get("SELECT * FROM sholiday", conn);
  Dictionary<int, DataRow> dictionary = Data.DictionaryInt32(data);

  int id = 1;
  if (dictionary.ContainsKey(id))
  {
    DataRow row = dictionary[id];
    Console.WriteLine(row["id"]);
  }

  return "";
}

6.5. Indexing of retrieved nested records according to the foreign key “pid”

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DataTable data = Data.Get("SELECT * FROM sholiday", conn);
  Dictionary<int, List<DataRow>> dictionary = Data.DictionaryInt32(data, "pid", true);

  int pid = 0;
  if (dictionary.ContainsKey(pid))
  {
    List<DataRow> rows = dictionary[pid];
    Console.WriteLine(rows.Count + " rows");
  }

  return "";
}

7. Writing data to the database

7.1. INSERT INTO – create a new record in the database

7.1.1. DataSaver – create one record

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DataSaver ds = new DataSaver("sholiday", 0, cmd);
  ds.Add("name", "Nový rok");
  ds.Add("date_", new DateTime(DateTime.Today.Year, 1, 1));
  ds.Execute();

  Console.WriteLine(ds.Report());

  return "";
}

7.1.2. DataSaver – create one record by copying another record

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = 1;

  DbRow row = new DbRow("SELECT * FROM sholiday WHERE id = " + id, conn);
  if (row.Read())
  {
    // row["ng_zadanokym"] = conn.User.LoginName;
    // row["ng_zadanokdy"] = DateTime.Now;
    // row["ng_zmenenokym"] = DBNull.Value;
    // row["ng_zmenenokdy"] = DBNull.Value;

    DataSaver ds = new DataSaver("sholiday", 0, cmd);
    ds.Add(row);
    ds.Execute();

    Console.WriteLine(ds.Report());
  }

  return "";
}

7.1.3. DataSaverSynchro – creation of one record, including creation of history record and ensuring record synchronization

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  DataSaverSynchro ds = new DataSaverSynchro("sholiday", 0, conn);
  ds.Add("name", "Nový rok");
  ds.Add("date_", new DateTime(DateTime.Today.Year, 1, 1));
  ds.Save(cmd);

  Console.WriteLine(ds.Report());

  return "";
}

7.2. UPDATE – editing an existing record in the database

7.2.1. DataSaver – editing a record whose ID is retrieved from the database

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = Data.ExecuteScalar2("SELECT id FROM sholiday WHERE name = " + conn.Format("Nový rok") + " AND date_ = " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (id != 0)
  {
    DataSaver ds = new DataSaver("sholiday", id, cmd);
    ds.Add("name", "Nový rok – TEST");
    ds.Execute();

    Console.WriteLine(ds.Report());
  }

  return "";
}

7.2.2. DataSaver – editing a record whose ID is stored in a variable; if the record does not exist, it will be created

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = 1;

  DataSaver ds = new DataSaver("sholiday", id, true, cmd);
  ds.Add("name", "Nový rok – TEST");
  ds.Execute();

  Console.WriteLine(ds.Report());

  return "";
}

7.2.3. DataSaverSynchro – editing a record whose ID is retrieved from the database, including creating a history record and ensuring record synchronization

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = Data.ExecuteScalar2("SELECT id FROM sholiday WHERE name = " + conn.Format("Nový rok") + " AND date_ = " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (id != 0)
  {
    DataSaverSynchro ds = new DataSaverSynchro("sholiday", id, conn);
    ds.Add("name", "Nový rok – TEST");
    ds.Save(cmd);

    Console.WriteLine(ds.Report());
  }

  return "";
}

7.2.4. DataSaverSynchro – editing a record whose ID is stored in a variable, including creating a history record and ensuring record synchronization; if the record does not exist, it will be created

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = 1;

  DataSaverSynchro ds = new DataSaverSynchro("sholiday", id, true, conn);
  ds.Add("name", "Nový rok – TEST");
  ds.Save(cmd);

  Console.WriteLine(ds.Report());

  return "";
}

7.3. Data synchronization of two database tables according to a single key

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  string key = "ng_osobnicislo";

  DataTable data1 = Data.Get("SELECT * FROM ng_data1", conn), data2 = Data.Get("SELECT * FROM ng_data2", conn);
  Dictionary<int, DataRow> dictionary = Data.DictionaryInt32(data1);

  foreach (DataRow row2 in data2.Rows)
  {
    DataRow row1 = Data.DataRow(dictionary, Parser.ToInt32(row2[key]));
    if (row1 == null)
    {
      DataSaver ds = new DataSaver("ng_data1", 0, cmd);

      for (int i = 1; i < data2.Columns.Count; i++)
      {
        ds.Add(data2.Columns[i].ColumnName, row2[data2.Columns[i].ColumnName]);
      }

      ds.Execute();
    }
    else
    {
      DataSaver ds = new DataSaver("ng_data1", (int)row1["id"], cmd);
      for (int i = 1; i < data2.Columns.Count; i++)
        if (row1[data2.Columns[i].ColumnName].ToString() != row2[data2.Columns[i].ColumnName].ToString())
        {
          ds.Add(data2.Columns[i].ColumnName, row2[data2.Columns[i].ColumnName]);
        }

      if (!ds.Empty)
      {
        ds.Execute();
      }
    }
  }

  return "";
}

8. Deleting data from the database

8.1. DELETE FROM – delete a record from the database

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = Data.ExecuteScalar2("SELECT id FROM sholiday WHERE name = " + conn.Format("Nový rok – TEST") + " AND date_ = " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (id != 0)
  {
    cmd.CommandText = "DELETE FROM sholiday WHERE id = " + id;
    cmd.ExecuteNonQuery();
  }

  return "";
}

8.2. DataSaverSynchro – deleting a record from the database, including creating a history record and ensuring record synchronization

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = Data.ExecuteScalar2("SELECT id FROM sholiday WHERE name = " + conn.Format("Nový rok – TEST") + " AND date_ = " + conn.Format(new DateTime(DateTime.Today.Year, 1, 1)), conn);
  if (id != 0)
  {
    DataSaverSynchro ds = new DataSaverSynchro("sholiday", id, conn);
    ds.Delete(cmd);
  }

  return "";
}

9. Edit forms

9.1. Find out the ID of the currently open edit form

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  string dbname = conn.FormData.Table.TableName;
  int form = Parser.ToInt32(Config.QueryString("form"));

  return "";
}

9.2. Retrieve the value of a control on an edit form

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  string name = conn["name"].ToString();

  return "";
}

9.3. Save the value to a control in an edit form

using NETGenium;
using System;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  conn["name"] = "Nový rok – TEST";

  return "";
}

9.4. Ajax call from javascript

9.4.1. JavaScript

var p0 = 'ěščřžýáíé', p1 = 'ĚŠČŘŽÝÁÍÉ';
loadUrl('ngef.aspx?MyFirstFunction,' + urlEncode(p0) + ',' + urlEncode(p1), 'ajaxResponse', 'test', 'p0=' + urlEncode(p0) + '&p1=' + urlEncode(p1));

function ajaxResponse(html)
{
  alert(html);
}

9.4.2. External functions

case "MyFirstFunction": MyFirstFunction(args, conn); return "";

using NETGenium;
using System;
	
private static void MyFirstFunction(string[] args, DbConnection conn)
{
  StringBuilder sb = new StringBuilder();
  sb.Append("MyFirstFunction REPORT");
  sb.Append(" | GET args: ");
  sb.Append(string.Join(", ", args));
  sb.Append(" | POST args: ");
  sb.Append(conn.Page.Request.Form["p0"]);
  sb.Append(", ");
  sb.Append(conn.Page.Request.Form["p1"]);
  Html.FlushAjaxContent(sb.ToString(), conn);
}

9.5. Simulation of opening a specific record in an edit form from a console application and reading the value of a control

using NETGenium;
using System;
using System.Data;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  int id = 1;

  DataTable data = Data.Get("SELECT * FROM sholiday WHERE id = " + id, conn);
  if (data.Rows.Count != 0)
  {
    conn.Register(data.Rows[0]);
  }

  string name = conn["name"].ToString();
  Console.WriteLine(name); 

  return "";
}

9.6. Capture events in the edit form

9.6.1. Open the edit form

// case "NETGenium.OnAfterOpen": OnAfterOpen(args, conn); return "";

using NETGenium;
using System;

private static void OnAfterOpen(string[] args, DbConnection conn)
{
  int form = Parser.ToInt32(args[0]);
}

9.6.2. Save the record

// case "NETGenium.OnAfterSave": OnAfterSave(args, conn); return "";

using NETGenium;
using System;

private static void OnAfterSave(string[] args, DbConnection conn)
{
  int form = Parser.ToInt32(args[0]), id = (int)conn["id"];
}

9.6.3. Deleting a record

// case "NETGenium.OnAfterDelete": OnAfterDelete(args, conn); return "";

using NETGenium;
using System;

private static void OnAfterDelete(string[] args, DbConnection conn)
{
  int form = Parser.ToInt32(args[0]), id = (int)conn["id"];
}

10. View tables

10.1. Execute an external function with “ngef2” from the lookup table

10.1.1. Edits in NET Genium

• Create a new textbox in the “User” form
  • Name: “Test”
  • Check “Read Only”
  • Check “Hidden field”
  • Default value: ngef2(ngef2test)
  • Check “Fill in default value every time the edit form is opened”
• Create a new preview page with a datagrid that will only display the “Test” column

10.1.2. Edits in external function

// case "ngef2test": return ngef2test(args, conn);

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static string ngef2test(string[] args, DbConnection conn)
{
  int id = Parser.ToInt32(args[args.Length – 1]);

  string key = "ngef2test";
  Dictionary<int, DataRow> dictionary;

  if (!conn.Container2.ContainsKey(key))
  {
    DataTable data = Data.Get("SELECT id, loginname FROM susers", conn);
    dictionary = Data.DictionaryInt32(data);
    conn.Container2.Add(key, dictionary);
  }
  else
  {
    dictionary = (Dictionary<int, DataRow>)conn.Container2[key];
  }

  if (dictionary.ContainsKey(id))
  {
    return dictionary[id]["id"] + ": " + dictionary[id]["loginname"].ToString();
  }

  return id.ToString();
}

10.2. Filling in the values in the view table

10.2.1. Edits in NET Genium

• Create a new textbox in the “User” form
  • Name: “Test”
• Find out the textbox ID: for example “7125”
• Create a new preview page with a datagrid that will only display the “Test” column
  • Check “ngef(NETGenium.DataTable)” in the data source on the “Other” tab
  • Copy the source code sample to the cliboard under the check box:
    • if (args[0] == "Q987" && args[1] == "1")
    • {
    • DataTable data = (DataTable)conn.Container2[args [0]];
    • int form = Parser.ToInt32(args [1]);
    • }

10.2.2. Edits in external function – version 1 for a small number of records

// case "NETGenium.DataTable": QueryBuilder(args, conn); return "";

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static void QueryBuilder(string[] args, DbConnection conn)
{
  if (args[0] == "Q987" && args[1] == "1")
  {
    DataTable data = (DataTable)conn.Container2[args[0]];
    int form = Parser.ToInt32(args[1]);

    string key = "c7125";
    if (data.Columns.Contains(key) && data.Rows.Count != 0)
    {
      List<int> ids = new List<int>();
 
      foreach (DataRow row in data.Rows)
      {
        int id = (int)row["id"];
        if (id != 0)
        {
          ids.Add(id);
        }
      }
 
      DataTable data2 = Data.Get("SELECT id, loginname FROM susers WHERE id IN (" +
ParserSql.Ids(ids.ToArray()) + ")", conn);
      Dictionary<int, DataRow> dictionary = Data.DictionaryInt32(data2);
 
      foreach (DataRow row in data.Rows)
      {
        int id = (int)row["id"];
        if (dictionary.ContainsKey(id))
        {
          row[key] = dictionary[id]["id"] + ": " +
            dictionary[id]["loginname"].ToString();
        }
        else
        {
          row[key] = dictionary[id]["id"].ToString();
        }
      } 
    }
  }
}

10.2.3. Edits in external function – version 2 for a large number of records

// case "NETGenium.DataTable": QueryBuilder(args, conn); return "";

using NETGenium;
using System;
using System.Collections.Generic;
using System.Data;

private static void QueryBuilder(string[] args, DbConnection conn)
{
  if (args[0] == "Q987" && args[1] == "1")
  {
    DataTable data = (DataTable)conn.Container2[args[0]];
    int form = Parser.ToInt32(args[1]);

    string key = "c7125";
    if (data.Columns.Contains(key) && data.Rows.Count != 0)
    {
      Dictionary<int, DataRow> dictionary = new Dictionary<int, DataRow>();
      List<int> ids = new List<int>();

      foreach (DataRow row in data.Rows)
      {
        int id = (int)row["id"];
        if (id != 0 && !dictionary.ContainsKey(id))
        {
          dictionary.Add(id, row);
          ids.Add(id);
        }
      }

      if (ids.Count != 0)
        foreach (string group in ParserSql.IdsInGroupsOf100(ids.ToArray()))
        {
          DbCommand cmd = new DbCommand("SELECT id, loginname FROM susers WHERE id IN (" + group + ")", conn);
          DbDataReader dr = cmd.ExecuteReader();
          while (dr.Read())
          {
            int id = (int)dr["id"];
            if (dictionary.ContainsKey(id))
            {
              dictionary[id][key] = id + ": " + dr["loginname"].ToString();
            }
          }

          dr.Close();
          cmd.Dispose();
        }
    }
  }
}

10.3. Export data from a statistical look-up table

// case "NETGenium.DataGrid2Excel": DataGrid2Excel(args, conn); return "";

using NETGenium;
using System;

private static void DataGrid2Excel(string[] args, DbConnection conn)
{
  string path = args[0], query = args[1];
  int form = Parser.ToInt32(args[2]);
  DateTime date1 = Parser.ToDateTime(args[3]);
  DateTime date2 = Parser.ToDateTime(args[4]);
}

11. File attachments

11.1. Creating a file attachment

using NETGenium;
using System;
using System.IO;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  string temp = Path.GetTempFileName();
  Files.Write(temp, "abc");

  int file = Attachment.Add("test.txt", temp, conn);
  File.Delete(temp);
  Console.WriteLine(file);

  return "";
}

11.2. Load the contents of a file attachment

using NETGenium;
using System;
using System.IO;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  string temp = Path.GetTempFileName();
  Files.Write(temp, "abc");

  int file = Attachment.Add("test.txt", temp, conn);
  File.Delete(temp);
  Console.WriteLine(file);

  string path = Attachment.FilePath(file, conn);
  if (File.Exists(path))
  {
    string s = File.ReadAllText(path);
    Console.WriteLine(s);
  }

  return "";
}

11.3. Prevent downloading of a file attachment – exchange of content for an empty file

// case "NETGenium.Download": return Download(args, conn);

using NETGenium;
using System;

private static string Download(string[] args, DbConnection conn)
{
  int id = Parser.ToInt32(args[0]);
  string filename = args[1], path = args[2];

  L.N("Download: " + conn.User.LoginName + "; " + id + "; " + filename + "; " + path);

  path = conn.RootPath + "Images\\1x1.gif";
  return "OK\r\n" + path;
}

12. E-mails

12.1. Sending an e-mail message

using NETGenium;
using System;
using System.Net.Mail;

private static string SendMessage(string[] args, DbConnection conn)
{
  string html = NETGenium.Email.Message.Container("<b>Hello</b>");

  MailMessage message = new MailMessage();
  message.From = new MailAddress("@");
  message.To.Add(new MailAddress("@"));
  message.Subject = "";
  message.AlternateViews.Add(Config.CreateTextAlternateView(Html.ToText(html)));
  message.AlternateViews.Add(Config.CreateHtmlAlternateView(html, conn));

  Config.SendMessage(message, conn);
}

12.2. Capture the event of sending an e-mail message via the “New e-mail” form, and skip saving the message to the sent ones

// case "NETGenium.SendMessage": return SendMessage(args, conn);

using NETGenium;
using System;

private static string SendMessage(string[] args, DbConnection conn)
{
  MailMessage message = null;
  bool save = false;

  foreach (object co in conn.Container)
    if (co is MailMessage)
    {
      message = (MailMessage)co;
    }
    else if (co is bool)
    {
      save = (bool)co;
    }

  return "skipsave";
}

13. Printing to printing templates

13.1. Capture a print event in a print template, and change the contents or name of the printed file

// case "NETGenium.OnAfterPrint": Print(conn); return "";

using NETGenium;
using System;

private static void Print(DbConnection conn)
{
  if (conn.PrintingProcess.Template == "Test.pdf")
  {
    string path = conn.PrintingProcess.FilePath;
    Files.Write(path, "abc");
    conn.PrintingProcess.FileName = "abc.txt";
  }
}

13.2. Change the password for locking Excel print templates

// case "NETGenium.ExcelPassword": return ExcelPassword();

using NETGenium;
using System;

private static string ExcelPassword()
{
  return "OK\r\n" + Guid.NewGuid().ToString();
}

14. User login

14.1. Capture the “OnBeforeLogin” event immediately before the user automatically logs on through Active Directory

// case "NETGenium.OnBeforeLogin": return OnBeforeLogin(args, conn);
	
using NETGenium;
using System;

private static void OnBeforeLogin(string[] args, DbConnection conn)
{
  string account = args[0];
}

14.2. Prevent users from logging on

// case "NETGenium.Login": return Login(args, conn);

using NETGenium;
using System;

private static string Login(DbConnection conn)
{
  if (HttpContext.Current.Request.UserHostAddress == "127.0.0.1")
  {
    return "";
  }

  HttpContext.Current.Session.Abandon();

  return "OK\r\nalert('Invalid login.'); if (opener != null) window.close(); else top.location = 'Default.aspx';";
}

15. Other

15.1. Logging to disk in the “Logs” directory

using NETGenium;
using System;
using System.IO;

private static string MyFirstFunction(string[] args, DbCommand cmd, DbConnection conn)
{
  try
  {
    throw new NullReferenceException("args");
  }
  catch (Exception ex)
  {
    L.E("MyFirstFunction", ex);
    // L.LogError("MyFirstFunction", ex);
    // L.Error("MyFirstFunction", ex);

    L.W("MyFirstFunction", ex);
    // L.LogWarning("MyFirstFunction", ex);
    // L.Warning("MyFirstFunction", ex);

    L.N("MyFirstFunction", ex);
    // L.LogNotice("MyFirstFunction", ex);
    // L.Notice("MyFirstFunction", ex);
  }

  return "";
}

15.2. Capture a database record lock event

// case "NETGenium.LockRecord": LockRecord(); return "";
// case "NETGenium.UnlockRecord": UnlockRecord(); return "";

using NETGenium;
using System;

private static void LockRecord(string[] args, DbConnection conn)
{
  int form = Parser.ToInt32(args[0]);
  long id = Parser.ToInt64(args[1]);
}

private static void UnlockRecord(string[] args, DbConnection conn)
{
  int form = Parser.ToInt32(args[0]);
  long id = Parser.ToInt64(args[1]);
}

15.3. Change the content of the text “Copyright”

// case "NETGenium.Copyright": return Copyright(conn);

using NETGenium;
using System;

private static string Copyright(DbConnection conn)
{
  return "OK\r\n © NetGenium " + DateTime.Now.Year;
}

15.4. Additional CSS style adjustments when saving the skin

// case "NETGenium.CSS": return CSS(args);

using NETGenium;
using System;

private static string CSS(string[] args)
{
  string value = args[0], browser = args[1];
  return "OK\r\n" + value + ".teststyle { color: red; }";
}