Structural change in an Asp.Net MVC application backed by sprocs

Question

Hello fellow developers.

First of all I apologize beforehand for the wall of text that follows, but after a day going crazy on this, I need to call for help.

I've stumbled across a problem I cannot seem to solve. I'll try to describe the scenario in the best possible way.

Task at hand: in an existing Asp.Net Mvc application, create a lookup table for an integer field, and use the textual value from the lookup in the editing view. When saving, we must first check if the lookup already has a corresponding text value for the same Root ID. If there is, use that. Otherwise, create it and then use it.

The structure:

The data model is a graph of objects where we have the root object, a collection of level A child objects, and every level A child object has a collection of level B child objects, so something like this:

• Root (with fields)
  • Level A child (with fields) x n
    • Level B child (with fields) x n

The field we have to handle is on the LevelB objects.

There is a single Mvc view that handles the whole data. For collection objects, all fields are named like levelA1levelB1MyField, levelA1levelB2MyField, etc so every single field has unique name during the post. When the post happens, all values are read through a formCollection parameter which has average 120/130 keys. The keys are isolated by splitting them and looping on the numerical part of the names, values are read and parsed to the expected types and assigned to the object graph.

The datalayer part backing the object graph is all stored procedures, and all the mapping (both object to sproc and sproc to object) is hand written. There's a single stored procedure for the read part, which gets multiple datasets, and the method calling it reads the datasets and creates the object graph.

For the saving, there are multiple sprocs, mainly a "CreateRoot" and "UpdateRoot". When the code has to perform such tasks, the following happens:

• For create scenario, "CreateRoot" is called, then the sprocs "CreateLevelA" and "CreateLevelB" are called in loop for each element in the graph;
• For update scenario, "UpdateRoot" is called, which internally deletes all "LevelA" and "LevelB" items, then the code recreates them calling the aforementioned sprocs in loop.

Last useful piece of information is that the "business objects graph" is used directly as a viewmodel in the view, instead of being mapped to a plain "html friendly" viewmodel. This is maybe what is causing me the most trouble.

So now the textbox on the view handles an "integer" field. That field must now accept a string. The field on LevelB must remain an integer, only with a lookup table (with FK of course) and the text field from the lookup must be used.

The approaches I tried with no success:

• My first thought was to change the datatype on the property MyField from integer to string on the object, then change the sprocs accordingly and handle the join at sproc level: I'd have a consistent object for my view, and the read/write sprocs could translate from string to integer and viceversa, but I can't do that because the join keys to retrieve the integer when writing are part of the Root item (as I stated in the first lines of this wall of text), which I don't know in the CreateLevelB sproc, and changing the whole chain of calls to pass those parameters would have a huge impact on the rest of the application, so no good.
• My next try was to keep things "as they are" and call some "translation methods": when reading, pass the integer to the view, and there call the translation method to display the text value. When saving, use the posted text to retrieve the integer. The save part would work, I'd have all the parameters I need, but for the read part, I'd have to instantiate the "data access layer" and call its method at View level, and there's no need to explain why that is a very bad choice, so I ruled this out too.

Now I'm out of options (or ideas anyway). Any suggestion to solve this is very welcome, and also if something is not clear enough just point it out and I will edit my post with more accurate information.

Thanks.

Answer 1

This is not a real answer but you could rip out all sprocs and use the updating facilities of an OR mapper. This will resolve all the layering issues. You just update data how you see fit and submit at the end.

I guess this would also make the questions around "should I use an int or a string" go away.

Edit: After reading your comment I thought of the following: Do not implement alternative 1. You rather want to sacrifice code quality in the view than in the data storage model. The last one is more important and more centrally used.

I would not be too concerned with messing up the view by calling the DAL from it or the like. Changes in a view are localized and do not mess up the application's architecture. They just degrade the view.

Maybe you could create a view model in your controller and do the translations between DAL-model and view model? Or is that pattern not allowed?