Archive

Posts Tagged ‘c#’

Workaround: Oracle, NHibernate, and CreateSQLQuery Not Working

January 17, 2012 Comments off

It’s difficult to sum this post up with a title. I started the morning adding (what I thought to be) a trivial feature to one of our shared repository libraries.

By the time I saw light at the end of the rabbit hole, I wasn’t sure what happened. This is the tale of my journey. All of the code is is guaranteed to work on my machine… usually. 😉

I’ve done this before–how hard could it be?

The full source code is available via a gist.

The initial need

A simple need really: take a complex query and trim it down to a model using NHibernate’s Session.CreateSQLQuery and Transformers.AliasToBean<T>.

The problems

 

So far, the only data provider I’ve had these problems with is Oracle’s ODP: Oracle.DataAccess. I’m not sure if the built-in System.Data.OracleClient.

 

Problem #1 – Why is EVERYTHING IN CAPS?

The first oddness I ran into seemed to be caused by the IPropertyAccessor returning the properties in ALL CAPS. When it tried to match the aliases in the array, [FIRSTNAME] != [FirstName]. Well, that’s annoying.

Workaround: Add an additional PropertyInfo[] and fetch the properties myself.

This method ignores the aliases parameter in TransformTuple and relies on a call in the constructor to populate the Transformer’s properties.

public OracleSQLAliasToBeanTransformer(Type resultClass)
{
    // [snip!]
    // this is also a PERSONAL preference to only return fields that have a valid setter.
    _fields = this._resultClass.GetProperties(Flags)
                   .Where(x => x.GetSetMethod() != null).ToArray();
}

Inside TransformTuple, I then call on _fields rather than the aliases constructor parameter.

var fieldNames = _fields.Select(x => x.Name).ToList();

// [big snip!]

setters = new ISetter[fieldNames.Count];
for (var i = 0; i < fieldNames.Count; i++)
{
    var fieldName = fieldNames[i];
    _setters[i] = _propertyAccessor.GetSetter(_resultClass, fieldName);
}

Problem solved. Everything is proper case.

Bold assumption: I’m guessing this is coming back in as upper case because Oracle, by default, stores and retrieves everything as upper case unless it’s surrounded by quotes (which has it’s own disadvantages).

Problem #2 – Why are my ints coming in as decimals and strings as char[]?

This one I’m taking a wild guess. I found a similar issue for Hibernate (Java daddy of NHibernate), but didn’t see a matching NHibernate issue. It seems that the types coming in are correct, but the tuple data types are wrong.

For example, if an object as a integer 0 value, it returns as 0M and implicitly converts to decimal.

Workaround: Use System.Convert.ChangeType(obj, T)

If I used this on every piece of code, I’d feel more guilty than I do; however, on edge cases where the standard AliasToBeanTransformer won’t work, I chalk it up to part of doing business with Oracle.

Inside the TransformTuple method, I iterate over the fields and recast each tuple member accordingly.  The only caveat is that I’m separating out enums and specifically casting them as int32. YMMV.


var fieldNames = _fields.Select(x => x.Name).ToList();
for (var i = 0; i < fieldNames.Count; i++)
{
    var fieldType = _fields[i].PropertyType;
    if (fieldType.IsEnum)
    {
        // It can't seem to handle enums, so convert them
	// to Int (so the enum will work)
	tuple[i] = Convert.ChangeType(tuple[i], TypeCode.Int32);
    }
    else
    {
        // set it to the actual field type on the property we're
	// filling.
	tuple[i] = Convert.ChangeType(tuple[i], fieldType);
    }
}

At this point, everything is recast to match the Type of the incoming property. When all is said and done, adding a bit of exception handling around this is recommended (though, I’m not sure when a non-expected error might pop here).

Problem solved. Our _setters[i].Set() can now populate our transformation and return it to the client.

Summary

Lessons learned? Like Mr. Clarkson usually discovers, when it sounds easy, it means you’ll usually end up on fire. Keep fire extinguishers handy at all times.

Is there another way to do this? Probably. I could probably create a throwaway DTO with all capital letters then use AutoMapper or such to map it to the properly-cased objects. That, honestly, seems more mindnumbing than this (though perhaps less voodoo).

Categories: .net 4.0, c# Tags: , , , ,

Dynamically Adding Sub Reports to an ActiveReport

April 5, 2011 Comments off

I’m currently working on a project where I needed to iterate through a group of users and plug in a little sub report that contains some demographic information and a Code38 barcode.

One sub report is easy–add the control to the page, set the .Report property and away we go; however, adding multiple sub reports dynamically and getting the spacing right proved to be a bit challenging.

Warning: It “works on my machine”.

To add controls to your report dynamically, you must use the _ReportStart event of your report.

To address the spacing issue, let’s start out by specifying our ‘base’ top and the height of our sub report.

const float height = 0.605f;
var currentTop = 7.250f;

In my case, I want my sub reports to start at about 7.25″ and be ~0.605″ in height.

The actual creation of the sub report placeholder is fairly standard–new it up and assign a few properties. I’ll get into the looping a bit later.

var subReport = new SubReport
{
    CloseBorder = false,
    Height = height,
    Left = 0F,
    Width = 7.5F,
    Top = currentTop,
    Name = person.DisplayName + "_SubReport",
    ReportName = person.DisplayName + "_SubReport",
};

Notice how I’ve set the Top property to be our currentTop variable. Keep that in mind.

The next step is to new up our actual sub report object.  My sub report has two properties on it, each for a data item.  I could pass it along as an object, but it seems a bit overkill for two string properties. After the report object is assigned to our new sub report container, add the container to our details section.

var spReport = new _PersonAssignment()
{
    Name = person.DisplayName,
    EmployeeId = person.EmployeeId
};

subReport.Report = spReport;
this.Sections["detail"].Controls.Add(subReport);

Because we’ve assigned it a left, width, and top, our sub report will be added where expected.

The final piece is incrementing the ‘top’ to accommodate for the height of the last sub report.

currentTop += height;

Easy.  Now the next sub report will start at 7.250″ + 0.605″ or 7.855″.  Keep in mind that the 0.605″ includes a bit of whitespace, if you need additional whitespace, pad the height number.

The full _ReportStart event looks like:

const float height = 0.605f;
var currentTop = 7.250f;
foreach (var person in Model.People)
{
    var subReport = new SubReport
    {
        CloseBorder = false,
        Height = height,
        Left = 0F,
        Width = 7.5F,
        Top = currentTop,
        Name = person.DisplayName + "_SubReport",
        ReportName = person.DisplayName + "_SubReport",
      };

    var spReport = new _PersonAssignment()
    {
        Name = person.DisplayName,
        EmployeeId = person.EmployeeId
    };

    subReport.Report = spReport;
    this.Sections["detail"].Controls.Add(subReport);
    currentTop += height;
}

Bingo.

Example of Dynamic Sub Reports

Categories: .net 3.5, c# Tags: , ,

Crazy Way To Set Display Order From an Enum

I’m sure someone has a better way to do this and I’m all ears; however, this seems to ‘work’ and performs well enough (YAGNI fully applied).

We have an Enum of our different “school levels”–elementary, middle, high schools, special schools, etc.  Since the Enum’s values correspond to the data values in our student system, their display/alphabetical orders do not match the common order (e.g. High School = 4, Middle School = 5).

I came up with a simple attribute that’s added to the Enum to “specify” display order and it’s worked like a champ for the past year or so.

[Description("Elementary")]
[DisplayOrder(1)]
Elementary = 2,

[Description("Middle")]
[DisplayOrder(2)]
Middle = 5,

Not at all fancy.

However useful it is at looping and providing context in code, what about using it for ordering OTHER “level” information from another source?  So far, I haven’t found a clean way.

The “Solution”?

Use the index of our already-ordered Enum list.  In this instance, our model’s SchoolLevel property matches the Description of the Enum.

            var levelsInOrder = Enum<level>.ToList();
            // AutoMapper mappings, etc.

            model.Data = data
                .OrderBy(x => levelsInOrder
                    .FindIndex(z => z.Description() == x.SchoolLevel));

Is it perfect? No, but it works. I could probably even refactor the Enum list and FindIndex call out for a bit more clarity.

        model.Data = data.OrderBy(x => x.GetDisplayOrder(x.SchoolLevel));

        ...

        private int GetDisplayOrder(string level)
        {
            return Enum<level>.ToList().FindIndex(x => x.Description() == level);
        }

Is there a better way?

Categories: .net 3.0, c#, LINQ Tags: , ,