Infor CSI Custom Code: T-SQL vs .NET

I am hearing more and more talk about the stored procedures in Infor CSI eventually being ported over to Handcoded (.NET) methods (somebody feel free to correct me if I am wrong).

Also, the Development Best Practices document from the Mongoose Portal would prefer that custom code, if there is a need for it, should be done with Form Scripting (Client) or IDO Extension Class (App Server).

We currently have quite a bit of custom code in the form of stored procedures. I am curious if we should begin looking to port our custom code from T-SQL to .NET code (running on either the client or app server, instead of the db server).

And any new customizations that would require IDO Methods be written, seems like it should be a Handcoded method instead of a Stored Procedure method.

Does anybody have any thoughts or guidance on this subject.

BTW, we are currently running CSI 9.00.30 with hopes of migrating to 10 in the near future.

Find more posts tagged with

Comments

Stephen Cena

We're in the same boat as you are, only 9.01.01 (w 9.01.10 and/or 10 on the horizon). This is my opinion on it:

With the announcement of the change (.NET and Mongoose reporting) paired with Infor's cloud offering of CSI, we're going to be migrating everything over to IDO Ext Classes/FormScripts and Mongoose reporting. My understanding is if you remain on prem you can get away with stored procedures a little longer. My person take is: while Infor says an on prem solution will be provided you don't know when they may change their minds. I'm looking to treat our environment as if we were going to the cloud.

If you have a low-priority project that would require SSRS or an SP, use that as a trial run if you've never worked with IDO Extension Classes or Mongoose reporting. That way if the project stumbles, you're not in any great trouble.

As for where the code should live, I've been struggling with this one. Though there are advantages to each (it's been a while so please correct me if I'm wrong & I can't find the notes I took):

-Smart Client & Web Client: Form Scripts can access local system resources and make connections to external resources from the client PC.

-IDO Ext Class: Can access all server resources and make connections to external resources from the Utility Server. I've also noticed code here seems to perform faster.

My current mode of thinking is: if this functionality should be called from any Form, stick it in an Extension Class. If it's specific to a Form, stick it on the Form (I'm still not 100% sold on this though lol)

As for any tips/etc:

-Mongoose/CSI supports both VB.NET and C#. Infor appears to favor VB.NET (just look at any Infor FormScript). I'm a C# guy (though know both). I'd pick a language & standardize on it. In my case, all my Form Scripts/Ext Classes are in C#.

-If you don't have a version control system in place, get one set up. I opted for Subversion as Infor allows direct integration in CSI 9. NOTE: CSI integration will only work for the "sa" account unless you have a Developer License(s). I would have it set up at least for your coding in Visual Studio, but I would also recommend you turn on Form Control as well. It's definitely a policy/style change, so don't plan on just turning it on.

-I've been using the IDO Extrension Classes to add "missing" (read actually missing or I had no idea where it was in Mongoose) functionality to CSI. I've created library IDO's to hold specific functionality (printing, math, etc.). It helps to centralize code rather than copy/paste code everywhere.

So; that's my $0.02 before my morning coffee.

Legacy Contributor

Steve hits most of the points I would typically make. Another advantage of doing IDO Extension Class methods is that they become a part of your IDOs, and therefore will trigger AES events reliably. One issue with stored procedures is they can be called outside of the IDO system, and when that is the case, AES doesn't see what they do, and as a result, your IdoPostInsert and IdoPostUpdate events won't fire. Being able to use AES events reliably removes the need for SQL stored procedures, triggers and functions.

... I use Azure Dev Ops for my source control. I create a master SQL Server template project in Visual Studio, so I can add my own folder structure to add scripts to, and then add a class library project for my IDO extension classes. That way I keep all the code for a customer in one GIT repository. ...

Stephen Cena

I completely forgot that you can plug Visual Studio into SQL Server. I'm going to need to take a look at that to see if it will help versioning our SQL stuff. I'm currently trying to use Redgate to version our database but it's been very slow going (structure/code only, no data).

I did answer my own question (I think) about Fomr Control and any software repo: community.inforxtreme.com/.../35743

Stephen Cena

Oh; one other thing I was told whatever version of Visual Studio you are using, make sure your DLL's get compiled for .NET ver. 4.6/4.6.1. I remember someone saying that is the version that Mongoose will support right now.

Legacy Contributor

It's actually based on the .NET framework version of the Mongoose .dlls you are referencing against. The version is different for each major version of SyteLine/CSI. 8.03+ was 4.5 (I believe), 9.xx is 4.6.1, and 10.xx is 4.7.2. So for a major upgrade, you have to recompile your IDO extension classes against the newer version of the Mongoose .dlls and change the .NET version of the IDO extension class project. Another reason to keep them in one class library project so you only have to change it in one place.

BradleyBrown

Ditto on all the points raised thus far.

For source control, I moved from subversion to devops for both form control and source control. I have not regretted the move at all and it has been far easier to maintain than an on-prem CVS repo. Plus the task management in devops has been very helpful. You can also choose to use git repos with devops if you prefer not to use the native source control.

Correct that you need to compile your extension class code with the correct .Net version to go with your flavor of Mongoose dlls.

We also chose to go with one language, C#. In my opinion VB.net can be too ambiguous and has too many ways of describing the same thing and allows developers the ability to really get... creative... with the way they write code. Sometimes to the detriment of the project. Like PERL, in VB there's more than one way to do it.

Visual studio also allows you to tailor your coding style, save it and export/import that style across all of your developer's visual studio installs so your code is somewhat consistent.

For deciding which way to go when coding, keep in mind that form and global scripts fire from the client tier. Crunching large sets of data in the client tier will be far more time consuming than doing it in IEC code.

Global scripts can be used in situations where you have common code that needs to fire in the client tier,i.e. called from multiple forms.

Lastly, when you use SQL data tools with visual studio, you can do schema compares between databases. Pretty useful for scripting specific changes. You can also customize which portions of the databases are compared and what types of entities are scripted out.

Legacy Contributor

I agree with the comments above.

If you are looking to port your work to the cloud one day then in Configuration Manager > Configurations > Edit in the Code security section you need to check the boxes which set the WebClient scripts to run in partial trust, and the IDO Extension classes to run in partial trust (Smart client scripts can run without partial trust, they only run on the client machine).

This denies access to reflection and the local file system (amongst other things) which is not permitted in the multi-tenant cloud. It can be a pain to work around this at first especially if you have legacy code to port, or 3rd party libraries which rely on reflection. Starting development with the system locked down avoids surprises later.

There is a usability benefit to IDO methods (custom assembly) code, despite the fact that it can be more difficult to debug, it is possible to reference and use 3rd party DLLs which are also imported into the custom assemblies IDO, these are not accessible in form or global scripts.

Keeping business logic (validation/calculations/triggers) to the IDO/AES layer means it also works in the mid-tier so you get the same behaviour from API's as you do from forms. If you put all your logic/validation in forms and global scripts then the API can become a back door to creating invalid data making it more effort to consume them later.

Last but not least every IDO and its methods can be exposed via the REST API's.

if you create an IDO method (list or scalar) a user which is granted explicit access to the IDO can consume it from the REST API interface.

This could be of special interest to mobile app developers targeting the MT cloud. Using OAuth SSO through ION API gets you to the Mongoose (and other) REST API endpoints making it is easy to consume the business logic developed against an IDO in a connected app.

0712131256598360.doc

BradleyBrown

Excellent points on the partial trust, Lee.

And thanks for the note about reflection... wasn't aware that was one of the restrictions.

Stephen Cena

One trick I've used for debugging was to add a private helper method that just does string concatination. At the end of my IDO call, I return the string by ref & show the output in a Multiline Text field. In code, it's easy to do a global search for my method call to remove it.

dmitchell

Thanks everybody for all the thoughts, ideas, tips and tricks that have been shared. This is good stuff!

It is encouraging to hear what others are doing and how they are handling these type of situations.

We attempted to setup and use Form Control, but continued to run into issues (probably partly because of our current database setup/development workflow and also because we didn't get to spend much time before we had to have something in place). So we opted to just script/export everything out to Azure DevOps (well, still a TFS server at the moment) and use that for all of our source control. It requires a bit of discipline from the developers but works pretty nicely for us. Eventually, I'd like to be using the built in Form Control though.

I do like the idea of the business logic being at the IDO level. There have been several times that I have wanted to use AES or Rest API for a piece of logic, just to find out it is bypassing the whole IDO layer...super frustrating.

It sometimes feels that being on 9.00.30 hinders from utilizing a lot of the tools and best practices that are encouraged (ie, no Mongoose Reporting, a ton of business logic still in Stored Procedures, etc.), but there are definitely lots of opportunities where we can improve and move forward with our customizations. This discussion has provided the motivation for me to do just that!

Ford GTL.pdf

Legacy Contributor

I think I have shared these before but it is relevant to this discussion. There are a couple of youtube channels which are worth looking at.

YouTube InforMarketing10

This is an old youtube channel which the InforOS team have moved away from however it has a lot of material which despite being a bit dated it still relevant. The Mongoose101 video series is effectively ".NET coding in Mongoose 101" The How-To and developer demos are a mixed back of various tips and tricks which you can use some are coding, but the best are the ones which demonstrate how you can use low/no code and use the framework.

Youtube InforOS

This is the newer channel which the Mongoose Enablement team moved to when they became the InforOS enablement team, it has a lot of Mongoose content but is designed to demonstrate the entire InforOS ecosystem when it's fully integrated in the cloud, or on-premise.

Legacy Contributor

Link got mangled in the edit, this should be the correct one for the YouTube InforMarketing10 channel

www.youtube.com/.../InforMarketing10

Stephen Cena

Infor is definitely changing up where stuff happens, and I like what they are doing. For my future projects, I'm trying to follow this:

-SP's ----> IDO Extension Classes/Form Scripts

-SSRS ---> Mongoose Reporting

-DB Triggers --> AES

-External App Integration via REST* (can also be done at the IDO Ext Class level)

As for the toolset, you're absolutely correct. We're on 9.01.01 and there are still bugs in the toolset. For example, if you try to export an AES Event and ask it to include all referenced objects (variables, constants, etc.) it won't do it. "That is fixed in 9.01.10 and we won't be back porting it". If you're on 9.0X.XX, I would try to get to 9.01.10 (that's our plan at least). We're aiming for a stable, "cloud friendly" install prior to going to 10.

Stephen Cena

SLIGHT TANGENT: (Please correct me if I'm wrong). One thing I discovered with IDO Extension classes is you can give the "IDO Extension Class" permissions that you don't want the users to have.

Case in point: I had to write some label making code for our stock room. Our stock rooms are staffed dynamically so on any given day it could be a different person in the room. I wrote code that will dynamically change ReportOptions for a given user on the fly. However; I didn't want users to have permission to go into the ReportOptions Form. On the Group Permissions, you can select MIDDLEWARE. These permissions will apply to your IDO Extension Class while the Form ones apply to the end users.

Legacy Contributor

Censored...

Copyright © 2025 Infor. All rights reserved.