Friday, September 28, 2007

InfoPath Common Trap and SQL Event

Common Trap
I had a past client reach out to me today for a question about He was using the SQL Event do some operations within an InfoPath process. He fell into one of the traps I discussed in my blog (Item 13 - Process and Activity Level XML and Multi-Destination Considerations). Basically remember if you were to do something like the following where you have an event (in this case a SQL event) after an InfoPath client event in the same activity you must use the activity XML field. Do not use the process XML field as it will not be updated until the succeeding rule for the activity is called.

I personally do not like how this code is hidden down inside of the succeeding rule. The code to move data from an activity to process level XML field conceptually has nothing to do with the success of an activity. There should be an OnActivityComplete event handler which should handle this. I digress…

A quick and dirty solution would be to do something like below if you want to use the process level XML fields. I would think this would be better solution as there could be multiple destination users who each have their own version of the XML. If there is only one slot, doing this you are guaranteed to use the XML of the destination user would finished the activity.

Now this may not be the best solution because maybe the code within the SQL Event handler could be very similar between SQL Events. You can refactor the code into something reusable if you wish. Point is I am not such a fan of using the SQL Event unless you are practicing a very rapid delivery environment. Reason is that all of the SQL statements are injected directly into .Net code as strings. If you want to be a minimalist and not create a large data layer just create store procedures and write a little code yourself to call them. You will have more maintainable code over the long run.

SQL 2005 Not Working with the Event
As well, he was running into some issues with SQL 2005 not working with the SQL Event Wizard although it would work with SQL 2000. I checked around and did not have an explanation on why that was occurring. Unfortunately I do not have an environment to test this out at the moment but I was not surprised there was a problem. The client said that if he took code from an event that he had generated with SQL 2000 the code would work with SQL 2005. I explained that is because the code is using underneath the hood of the SQL Event which is agnostic to the version of SQL. This leads me to believe that the SQL Event Wizard must be using something specific to SQL 2000.

No comments: