Home » Server Options » Replication » Pure .NET Stored Procedures
Pure .NET Stored Procedures [message #146929] Mon, 14 November 2005 15:12 Go to next message
maher
Messages: 5
Registered: November 2005
Location: malaysia
Junior Member
We are considering the pros and cons of whether our client/server application should use a combination of stored procedure that are either native SQL or managed code based versus an approach where the stored procedures are only using managed code. Our database platforms will be Oracle (10GR2) and SQL Server (2005). Our understanding is that managed code is best used for complex manipulation that would be anywhere between difficult to extremely difficult for us to do in SQL (PL/SQL and T-SQL). The standard data manipulation of retrieve, insert, update and delete might still be best done as pure SQL stored procedures. Using managed code there instead would essentially create an unnecessary wrapper that can be done more efficiently as a pure SQL.

Our thoughts of still moving ahead with a complete managed code set of stored procedures are:
  • Totally DB independent Code. We can move the same code from 10G to SQL2005 or vice versa;
  • Our developers do not need to know T-SQL or PL-SQL.

At the very least we think managed stored procedures is a better approach that having the code in-line within the client application as the approach will give us:
  • more efficiency as it is processed on the server (as it would for pure SQL based stored procedures);
  • stronger security (as it would for pure SQL based stored procedures);
  • easier code reuse;

If we did decide to go this route:
  1. Any limitations of utilization a pure .NET stored procedure system?
  2. What is the downside of this approach?
  3. Performance or other issues?
  4. Industry best-practices in this field? Is it so bad to go this way, or just rare?


Re: Pure .NET Stored Procedures [message #147636 is a reply to message #146929] Fri, 18 November 2005 11:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Totally DB independent Code. We can move the same code from 10G to SQL2005 or vice versa;
Please rephrase the above. I am not sure what you mean here.
If that would mean, the same 'generic' code to be used in both SQLServer and Oracle, it will endup as a disaster.
I can quote you too many examples (Products in market).
Oracle and SQLServer are very different technology.
Both treat the data very differently.
Re: Pure .NET Stored Procedures [message #147637 is a reply to message #146929] Fri, 18 November 2005 12:03 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
for poignant views please see
The Cost Of Database Independence

[Updated on: Fri, 18 November 2005 12:05]

Report message to a moderator

Previous Topic: Clarify. Complete Refresh
Next Topic: How to monitor refresh rate of MV?
Goto Forum:
  


Current Time: Thu Mar 28 09:30:13 CDT 2024