I have devised a way to save tons of code by calling stored procs with a single web service method, which takes a DataSet parameter, containing a DataTable with the name of the stored procedure and column names matching the parameter names, and row(0) contents matching the values to be passed. I have further simplified matters by making all parameters varchar, so a call from my client looks like:
Dim paramVals As String() = { myString1, myString2 }
Dim ds As DataSet = oneWsMethod("storedProcName", "param1,param2", paramVals)
...
Dim params As String() = { this1String, thisOtherstring, thirdString }
ds = oneWsMethod("anotherProc", "p1,p2,p3", params, usingTransaction:=True)
My oneWsMethod creates the DataSet's DataTable from the list of params and string array, then calls the web service. The web method returns exceptions in a table that's handled in oneWsMethod. I've been told this works like a Strategy pattern, but I couldn't see the connection. It feels kind of like a hack, but it saves miles of code, and lets me enhance the client code without any changes to the web service.
Is this a sound practice Can someone please offer critique Thanks.
critique requested on thin Data Access Layer
PoorSQLGuy
These are good points. I'm moving some business logic out of the client and into stored procedures.
I think I can recast the issue as wrapping an extra layer onto the SqlHelper used in the Data Access Layer, so clients can talk directly to the stored procs. I'm concerned about the SOA tenets -- explicit boundaries and autonomous services -- so I wonder whether it's legitimate to maintain that the stored procs present the boundaries and autonomous services, while the web service just provides a way to interface with them.
Tamirro
Hi Kit,
that way you may be paying a performance penalty, as you have to parse your "p1,p2,p3" inside oneWsMethod each time, although that doesn't prevent you to collect the benefits of your code line reduction. Actually you can benchmark both alternatives: yours and some other which doesn't consider the code reduction. Just in order to quantify how much are you paying in execution time for that gain in development time. Be realistic, don't meassure just one invocation: create a job which launches 100, 1000 invocations to oneWsMethod(...) and compare the END TO END elapsed time (from 1st call to last one)
Eventually another way to address your problem could be with some sort of code generator, for instance with code snippets. That way you can shortcut your syntax, which will later expanded in the necessary code lines and compiled to CLI. That way nothing will be parsed in execution time so the performance penalty won't exist. But, again, try this just if you discover that the gap between your implementation and a regular is really considerable
(If you consider this answer useful for the purposes you opened the thread, pls tag it as USEFUL. Thanks!)
anderskj1
Good question. I had one case where I needed to pass multiple records, and found a DataSet very handy, so I thought it would make a great general-purpose, one-size-fits-all pattern. But I've never since had a need for it, so I now prefer passing string arrays:
Dim paramNames As String() = {"@p1", "@p2"}
Dim paramValues As String() = {"myValue", "anotherValue"}
ds = oneWsMethod("anotherProc", paramNames, paramValues, usingTransaction:=True)
Perhaps JSON would be lighter/faster, but I suspect I'd get more speed by switching from web services (on SOAP) to .NET remoting.
gray-82
Now that the thin layer may be required to be used by other applications, we are actually considering using Remoting to make the layer available. we considered using Web Services, but definately using remoting and a binary formatter is way faster than using soap. Now that the WCF is out, maybe you should consider using it.
Just remeber that string manipulation has a severe performance hit on any process, so parsing parameters like that is very expensive.
Erik Pedersen
Hello Diego
Although you are right when talking about performance penalty when manipulating strings, but since we are discussing a web service topic, I guess by definition we are talking about parsing XML strings, as well as serializing/deserializing data to/from text format. So an extra small parsing "p1, p2, p3" won't be a big deal. Please correct me if I am wrong.
Rick Strahl
Depends on your specifc situation but having the business logic in the clients can cause a few problems:
- Updates to your business logic will require a new version rolled out to your clients. This can be easy if it's a small user base or if the clients are on a local network and your IT department can roll out the new clients easily. You should have some sort of update procedure in place.
- Scalability. If you find you need to scale out to support more users for the business logic that sits in the client you can't. If the business logic is behind your web services you can farm them and scale out much more easily. Having business logic in stored procedures may hurt scalability too as it's easier to scale out a business logic layer than to scale out your database usually. If you find that performance is suffering in the business logic in the stored procedures you have to go out and buy a bigger database machine.
- Security. Clients will have access to the busienss logic code that sits on their machines. This means that the code can be reverse engineered and could provide access to information or processes that you don't want them to then it's not a good place for the business logic. A further problem with security is that a malicious client could just post directly to your web services bypassing any business logic validation that exists on the client. Unless this side of things is in the stored procedures you can't enforce client side business logic.
- Reuse. If in the future you have requirements for seperate developments to reuse your services, you would then need to implement the business logic that sits client side again in the new development. In SOA your usually looking to gain the ability to reuse your web services across developments, having business logic in the clients makes this harder to acheive.
I've forgotten if your stiving for SOA or if it was mentioned by someone else in a reply but this approach seems to fall a little foul of the two tenents: Boundaries ere explicit & Services are autonomous.
Of course this all depends on your particular situation, as always :-)
Regards
Ed Hill
DQM
I found some thorough critique in Jeromy Carriere's MSDN Webcast: Patterns and Anti-Patterns in SOA. This doesn't use explicit boundaries: it would be awkward to change a stored proc without affecting the client, for instance. Also, the details of the "contract" are not spelled out in the interface: you can't tell, for instance, when you're supposed to pass an integer Supplier_ID along with a string Serial_Nbr. The client code has to "know" about the stored procedure independent of the interface.
Having said that, I'm not convinced that Service Oriented Architecture is the best model for services we don't want called by anything (at this point) except our specific client.
Jack Tripper
I have a question, Kit
You said you passed a DataSet as input...
What if you passed, in the case of "p1,p2,p3" three individual SqlParameters assigning to them, 'course, three strings in the Value property
I always thought that the original version was that one. Why do you need to use a DataSet as input, instead of those three individual p1, p2 and p3
RubenPieters
Yeah, I think Ron calls it loosy goosy
It's not just SOA though. In OO you would be suffering from a lack of encapsulation. Where does your business logic lie
Sounds like the business logic is in the clients or the in database....
Regards
Ed Hill
jrcdude
Your method is very similar to the method I use in my own database layer.
There is some speed advantage to having everything inline and avoiding object creation and looping where possible. However, this also creates code that's harder to read and understand, greatly increases the risk of inconsistant coding practices and typos, and can make debugging more difficult. These problems get multiplied when there are more than one developer on a project.
Generally, I've found that unless very high speed performance is a strong requirement for a project, it is best to use methods that emphasize maintainability and stability over outright performance.
borice
Thanks for the recommendation. Average time (of hundreds) was .77 sec / round trip vs. .55 sec -- after I changed my OneWsMethod to use string arrays instead of a DataSet to pass parameter names and values. (It still returns a DataSet.)
My initial thought is that the performance impact is probably worth the benefit in simplicity and maintainability.
azaclauson