DevExpress XPO: Expensive SQL Query Upon XPCollection.EndInit() Call

For .NET projects I do like to use the controls by DevExpress. Additionally DevExpress XPO is very useful as database abstraction layer.

But in a current project I came across a very strange issue:

for a few forms (curiously not for all) a very expensive SQL query is sent to the database backend while the form is loading. It was basically a SQL query that loads all (and I mean really all) data rows from the database which is obviously not a very good idea. But as I wrote that was not the case for all forms: when loading the other forms only the current data object (the current row in the table) was loaded as expected.

Of course, I’ve first contacted the always very fast and helpful DevExpress support. They researched what causes the data request and determined that the XPCollection.Count property is requested by the CurrencyManager after the Control.UpdateBindings method call. Because that’s something inside the .NET Framework they can’t do anything about it unfortunately.

They suggested to not set the XPCollection at design time, but this would result in losing the possibility to build the forms in the Windows Forms Designer. That would have been very bad, because I do have a lot of big forms in this project and I really don’t like to build them all in code.

But I found another way – I will not call that solution, just a work-around – to be still able to use the designer:

Therefore I simple wrote two batch files (using a command line replacement tool called FART), one that runs before the build starts and makes sure no XPCollection is initialized in the InitializeComponent method (by just commenting the lines of code) and a second one that runs after the build finished and reverts these changes.

Here is the content of the pre-build batch file:

%1Tools\fart.exe -r -C "%1*.designer.cs" "this.xpKern = new DevExpress.Xpo.XPCollection(this.components);" "\x2f\x2fthis.xpKern = new DevExpress.Xpo.XPCollection(this.components);"
%1Tools\fart.exe -r -C "%1*.designer.cs" "((System.ComponentModel.ISupportInitialize)(this.xpKern)).BeginInit();" "\x2f\x2f((System.ComponentModel.ISupportInitialize)(this.xpKern)).BeginInit();"
%1Tools\fart.exe -r -C "%1*.designer.cs" "this.xpKern.ObjectType = typeof(Administration.Daten.Kern);" "\x2f\x2fthis.xpKern.ObjectType = typeof(Administration.Daten.Kern);"
%1Tools\fart.exe -r -C "%1*.designer.cs" "this.xpKern.Session = this.unitOfWork;" "\x2f\x2fthis.xpKern.Session = this.unitOfWork;"
%1Tools\fart.exe -r -C "%1*.designer.cs" "((System.ComponentModel.ISupportInitialize)(this.xpKern)).EndInit();" "\x2f\x2f((System.ComponentModel.ISupportInitialize)(this.xpKern)).EndInit();"

@exit 0

And the post-build one that reverts the changes:

%1Tools\fart.exe -r -C "%1*.designer.cs" "\x2f\x2fthis.xpKern = new DevExpress.Xpo.XPCollection(this.components);" "this.xpKern = new DevExpress.Xpo.XPCollection(this.components);"
%1Tools\fart.exe -r -C "%1*.designer.cs" "\x2f\x2f((System.ComponentModel.ISupportInitialize)(this.xpKern)).BeginInit();" "((System.ComponentModel.ISupportInitialize)(this.xpKern)).BeginInit();"
%1Tools\fart.exe -r -C "%1*.designer.cs" "\x2f\x2fthis.xpKern.ObjectType = typeof(Administration.Daten.Kern);" "this.xpKern.ObjectType = typeof(Administration.Daten.Kern);"
%1Tools\fart.exe -r -C "%1*.designer.cs" "\x2f\x2fthis.xpKern.Session = this.unitOfWork;" "this.xpKern.Session = this.unitOfWork;"
%1Tools\fart.exe -r -C "%1*.designer.cs" "\x2f\x2f((System.ComponentModel.ISupportInitialize)(this.xpKern)).EndInit();" "((System.ComponentModel.ISupportInitialize)(this.xpKern)).EndInit();"

@exit 0

The @exit 0 at the end of them is important as otherwise code 2 is returned (for whatever reason) and the Visual Studio build system will complain about it every build.

Now you just need to call these batch files in the Visual Studio build events with

$(ProjectDir)Tools\PreBuild.bat $(ProjectDir)

respectively:

$(ProjectDir)Tools\PostBuild.bat $(ProjectDir)

I know that this is not a very good “solution”, but the only idea I had that I can still use the form designer while avoiding these expensive SQL queries.

Did you ever have the same issue and maybe even a better solution for it? I’m looking forward to any other suggestion on this topic!

This post is also available in Deutsch.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>