Transform XML Strings

This one is a quickie. It applies XML transformations where the source and destination are both strings.

string xml = txtXml.Text;
string xslString = txtTranstring.Text;
string transformedXml = string.Empty;

XslCompiledTransform xsl = new XslCompiledTransform();
xsl.Load(new XmlTextReader(new StringReader(xslString)));
using (var stringWriter = new StringWriter())
{
    using (var xmlWriter = XmlWriter.Create(stringWriter))
        xsl.Transform(new XmlTextReader(new StringReader(xml)), xmlWriter);

    transformedXml = stringWriter.ToString();
}

txtResult.Text = transformedXml;

OrderBy Extension for Sorting by Multiple Column Names

I often use the OrderBy extension (https://msdn.microsoft.com/en-us/library/bb534966(v=vs.110).aspx) to sort results using Linq method syntax. However as of late I have found it necessary to sort by multiple fields by using the field names. While it hasn’t been something critical I have been seeking a way to do this. Today I was able to make my own extension that can take the following types of parameters.

IEnumerable<Employees> sortedResult = employees.OrderBy("department, division asc, lastname desc");

If you try it out and find any issues let me know so I can update the code. While this works on IEnumerable (as it is what I was working with) I will work on an IQueriable version.

using System;
using System.Collections.Generic;
using System.Linq;

namespace MyProject
{
    public static class Extensions
    {
        private enum OrderByType : Int16
        {
            Asc = 0,
            Desc = 1
        }

        public static IEnumerable<T> OrderBy<T>(this IEnumerable<T> enumerable, string sortString)
        {
            if (string.IsNullOrWhiteSpace(sortString))
                throw new MissingMemberException("Sortstring missing or empty.");

            T obj = enumerable.FirstOrDefault();
            string[] orderBy, orderBys = sortString.Split(',');
            string fieldname = string.Empty;
            OrderByType direction;

            IOrderedEnumerable<T> result = null;
            for (int i = 0; i < orderBys.Length; i++)
            {
                if (string.IsNullOrWhiteSpace(orderBys[i]))
                    throw new MissingMemberException("Invalid sortstring.");

                orderBy = orderBys[i].Split(' ').Where(s => !string.IsNullOrWhiteSpace(s)).ToArray(); // 0 = fieldname, 1 = direction
                if (1 <= orderBy.Length) fieldname = orderBy[0];
                Enum.TryParse((2 == orderBy.Length ? orderBy[1] : string.Empty), true, out direction);

                var propertyInfo = obj.GetType().GetProperty(fieldname, System.Reflection.BindingFlags.IgnoreCase   |
                                                                        System.Reflection.BindingFlags.Public       |
                                                                        System.Reflection.BindingFlags.Instance);

                if (0 < i)
                {
                    if (direction.Equals(OrderByType.Asc))
                        result = result.ThenBy(f => propertyInfo.GetValue(f, null));
                    else if (direction.Equals(OrderByType.Desc))
                        result = result.ThenByDescending(f => propertyInfo.GetValue(f, null));
                }
                else if (0 == i)
                {
                    if (direction.Equals(OrderByType.Asc))
                        result = enumerable.OrderBy(f => propertyInfo.GetValue(f, null));
                    else if (direction.Equals(OrderByType.Desc))
                        result = enumerable.OrderByDescending(f => propertyInfo.GetValue(f, null));
                }
            }

            return result;
        }
    }
}

Print Debug Messages

It’s really helpful when developing macros in VBA to get some sort of feedback from the code as to what is going on behind the scenes. Doing this has helped me get rid of superfluous reiterations.

1. Show the Immediate window

immediate_window1

2. Add descriptive messages to the code

add message for immediate window

3. See the debug messages as they are encountered

read immediate window

Executing an ISeries Stored Procedure from .NET with ODBC

Below is an example of how one might execute a stored procedure on the ISeries from a C# application. I understand that there are certainly other, much shorter ways to achieve this.

using (OdbcConnection DbConnection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MyISeres"].ConnectionString))
using (OdbcCommand DbCommand = new OdbcCommand())
{
    DbCommand.Connection = DbConnection;
    DbConnection.Open();
    DbCommand.CommandText = "call MYLIB.MYPROC(?, ?)";

    OdbcParameter p1= new OdbcParameter();
    p1.DbType = System.Data.DbType.String;
    p1.ParameterName = "MyParm1";
    p1.Size = 50;
    p1.Value = "some value";
    p1.Direction = System.Data.ParameterDirection.Input;
    DbCommand.Parameters.Add(p1);

    OdbcParameter p2= new OdbcParameter();
    p2.DbType = System.Data.DbType.String;
    p2.ParameterName = "MyParm2";
    p2.Size = 50;
    p2.Value = "some other value";
    p2.Direction = System.Data.ParameterDirection.Input;
    DbCommand.Parameters.Add(p2);

    using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
    {
        GridView1.DataSource = DbReader;
        GridView1.DataBind();
    } 
}  

Dot or Slash Separator On AS400 JDBC Resource

Recently I had to debug a Java web application and being new to the platform there were quite a few things I was unfamiliar with. One of those things was how to tell an AS400 resource whether it’s libraries and procedures were going to be separated by a slash or a dot. The application I was working on used slashes and apparently my connection’s default was dots. At first I changed all the calls but I always knew there had to be another way.

Here is how I specified what kind of naming the application should use on the server.xml file.

<Resource
  name="MyDataSource"
  auth="Container"
  type="javax.sql.DataSource"
  password="mypass"
  driverClassName="com.ibm.as400.access.AS400JDBCDriver"
  maxIdle="5"
  maxWait="5000"
  username="myuser" 
  url="jdbc:as400://AS400URL.COM; libraries= mylib, yourlib, theirlib; naming=system; prompt=false"
  maxActive="10"/>

Note the naming on the URL property.
“System” causes the connection to interpret the slash character as a separator.

naming=system

“Sql” causes the connection to interpret the dot character as a separator.

naming=sql

Copy Column Widths

I ran into this code when trying to copy some rows from one sheet to another in Excel through VBA. The widths would always display at the destination spreadsheet’s default column width. This code helps, all you need to pass it is the source and target ranges as parameters.

Code

Private Sub copyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
    With SourceRange
        For c = 1 To .Columns.Count
            TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
        Next c
    End With
End Sub

Example call

copyColumnWidths Sheets("MySheet").Range("A1:AA500"), Sheets("YourSheet").Range("A1:AA500")

Read Word Document Using NetOffice 1.60

I needed to look through a Word document for an application running the .NET 2.0 framework. Here is how I did it using NetOffice 1.60 which can be downloaded via NuGet in Visual Studio.

Imports Word = NetOffice.WordApi
Imports NetOffice.WordApi.Enums

Module Module1

    Sub Main()
        readWordDoc()
    End Sub

    Private Sub readWordDoc()

        ' start word and turn off msg boxes
        Dim wordApplication As New Word.Application
        wordApplication.DisplayAlerts = WdAlertLevel.wdAlertsNone

        ' open document as read only
        Dim myDocument As Word.Document
        myDocument = wordApplication.Documents.Open(System.IO.Path.GetFullPath("MyDoc.docx"), False, True)

        ' get document's content
        Dim content As String
        content = myDocument.Content.Text

        ' close doc
        wordApplication.Quit()
        wordApplication.Dispose()

        ' output content 
        Console.WriteLine(content)
        Console.ReadKey()

    End Sub

End Module