Useful string manipulations in VB.NET

From the perspective of the developer, one of the biggest selling points of XF OneStream is the coding power and flexibility that comes with VB.NET. In this post I wanted to share a couple useful tricks when working with strings in OneStream applications.

Text.StringBuilder

First, any complicated string that is being put together from different pieces will benefit from the use of Text.StringBuilder class. Not only it results in a more readable code, but it also saves memory in case of intense string manipulations, especially inside loops. Here’s the explanation from Microsoft:

The String object is immutable. Every time you use one of the methods in the System.String class, you create a new string object in memory, which requires a new allocation of space for that new object. In situations where you need to perform repeated modifications to a string, the overhead associated with creating a new Stringobject can be costly. The System.Text.StringBuilder class can be used when you want to modify a string without creating a new object. For example, using the StringBuilder class can boost performance when concatenating many strings together in a loop.

https://docs.microsoft.com/en-us/dotnet/standard/base-types/stringbuilder

It is widely used in OneStream built-in code, so examples are abundant. The one below is from the out-of-the-box Connector PLP_PeoplePlanningData rule (comes with People Planning solution found on XF Marketplace):

Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

1. A StringBuilder object named “sql” is instantiated
2. Strings are appended to sql object
3. Line breaks might be appended too, which might be useful when displaying parametrized queries on runtime.
4. Finally, ToString method is used to retrieve the resulting string.

String.Split

Another commonly employed manipulation with strings will be parsing with delimiter:

1. wfTime here is workflow time, such as “2019M4”
2. Split method (with delimiter M) results in the array {2019, 4}. Thus year is the position 0 of the array, and period number is the position 1 of the array.

String.Join

The reverse operation, that is to join strings and delimit them with the specified character, comes in handy just as often:

1. Join concatenates all values from the current data row (the code is looping through a data table) into one string delimited with commas. ItemArray method applied to the current row conveniently yields all row values as the array of string.

IEnumerable.Last

The above method requires array of strings as input, meaning the developer has to organize strings to be delimited into an array first, meaning in most cases looping through some other collections.

For example, a common task would be to concatenate member names based on a member filter. GetMembersUsingFilter method yields collection of MemberInfo objects, and not of member names, i.e. strings. Member name is rather a property of MemberInfo. Since looping through this collection (to retrieve name strings) is required anyway, it makes sense to put the resulting list together directly inside the loop, bypassing building of the array, as shown below. The challenge here is to escape “empty spot” in the beginning, or at the end of the resulting string because of appending the delimiter – you either need to drop the delimiter at the beginning or at the end of the result:

1. New StringBuilder object is instantiated to capture the delimited string. As this resulting string will be eventually used inside the WHERE clause of a SQL statement, each member name must be enclosed with single quotes. So the very first character is a single quote
2. Looping through the collection of MemberInfo items retrieved at Line 832
3. The trick to drop the delimiter at the end of the line: if the current item is the last item in the collection – then don’t add the “full delimiter”, but rather one closing single qoute.