.NET Stored Procedures

.NET Stored Procedures

• Neilgd

  > * more efficiency as it is processed on the server (as it would
  > for pure SQL based stored procedures);
  
  LOL! You think that using .NET code is going to outperform set-based T-SQL 
  code in all cases  Egads!
  
  Use the SQL language for what it is for. Porting between SQL Server and 
  Oracle should be a non-issue if you stick to standard SQL that's supported 
  on both; even if that is not possible or feasible, the changes should be 
  minor (and there are tools that can help with that). Plus, unless you're 
  Celko, what are the real odds that you're going to be switching between 
  platforms enough that this concern comes before performance 
  
  Use managed code when SQL doesn't do what you need, or doesn't do it well. 
  String manipulation and regular expressions come to mind. In most cases, 
  SQL should be your first choice, IMHO. After all, you are still dealing 
  with a relational database.
  
  If your developers don't understand SQL, that doesn't make it okay for them 
  to do all their data manipulation in a programming language! Do you think 
  that will leave your database(s) in better shape, or worse  Be honest! 
  
  
  

  
  

• luoo

  I think you are correct in your understanding of the benefits of managed code vs. T-SQL.  T-SQL is still a much better choice for straight CRUD operations.  And I agree completely with your views on encapsulation and re-use... However, I think some of your other points are not correct.

   

  A) Ability to transition seamlessly between Oracle and SQL Server via the same managed stored procedures, with no code change   I think not!  I don't know a lot about Oracle's CLR hosting, but I doubt it supports the SqlPipe object or the context connection, for instance.

   

  B) Developers won't need to learn SQL   Again, I think you are mistaken.  Even in CLR procedures, you still need T-SQL for data access.  You might not need to be a SQL expert, as you can offload some of the complexity to the managed code, but you'll still need a basic understanding of the language.

   

  Unfortunately, there is no easy solution to this problem.  Interoperability is a complex issue, and the only people who even claim to have solutions (and I don't agree with them) are ORM vendors.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <msdnjw@discussions.microsoft.com> wrote in message news:a716d662-7392-45ef-af9c-b962b0a12bd9@discussions.microsoft.com...

  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

  
  

• Mick_RW

  Another problem I find in a pure .NET stored procedures based approach is that writing SQL code from within .NET (e.g. C#) is a bit cumbersome and debugging it is not that easy. It's much easier in development to write lengthy SQL operations as SQL stored procedure and debug them as such, and then only do the more programming-intensive operations in .NET.
  
  
  
• .NET Stored Procedures